Log-Writing Process: Head and Tail of the Redo Buffer (LGWR)

The redo log buffer in the SGA is a circular queue. Two pointers describe its state:

• Head - where new redo records are appended by server processes.
• Tail - the next redo record that LGWR will flush to the online redo logs on disk.

As transactions generate redo, the head advances. LGWR continuously drains from the tail, writing sequentially to the online redo logs. On COMMIT, LGWR ensures that all redo for the committing transaction is on durable storage (write-ahead logging). Data block writes are decoupled and handled later by DBWn.

Head/Tail Timeline (9-step visual)

When LGWR flushes

• On COMMIT (and some DDL/ROLLBACK): LGWR flushes all redo for the transaction; commits may “group commit.”
• Timed/threshold flushes: periodic and size-based triggers keep latency predictable.
• Write-ahead enforcement: LGWR flushes before DBWn can write dirty data blocks.
• Log switch: finishing the tail of redo during a switch to the next redo group.

Data Guard & transport mode (LGWR vs ARCH)

For protected configurations, redo can be shipped to standbys:

• LGWR transport - real-time, can be SYNC/AFFIRM (commit waits for standby acknowledgment) or ASYNC/NOAFFIRM.
• ARCH transport - ARCn ships archived logs after a log switch (deferred vs. real-time).

-- Example: primary sending real-time redo to a standby
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 =
  'SERVICE=stby1 ASYNC NOAFFIRM LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
   DB_UNIQUE_NAME=STBY1' SCOPE=BOTH;

-- Force an immediate switch/ship if needed
ALTER SYSTEM ARCHIVE LOG CURRENT;

Monitoring & troubleshooting

-- Redo volume, commit counts, and LGWR work
SELECT name, value
FROM   v$sysstat
WHERE  name IN ('redo size','redo writes','redo write time','user commits');

-- Session wait vs. device wait (commit path)
SELECT event, total_waits, time_waited/100 AS seconds_waited
FROM   v$system_event
WHERE  event IN ('log file sync','log file parallel write')
ORDER  BY time_waited DESC;

-- Online redo groups and members (multiplexing)
SELECT l.group#, l.bytes/1024/1024 AS size_mb, l.status, lf.member
FROM   v$log l JOIN v$logfile lf USING (group#)
ORDER  BY l.group#, lf.member;

-- Recent log switches (cadence helps checkpoint stability)
SELECT sequence#, first_time, next_time
FROM   v$log_history
ORDER  BY first_time DESC
FETCH FIRST 20 ROWS ONLY;

Practical guidance

1. Place redo on low-latency storage and keep its queue depth healthy.
2. Right-size redo logs for a steady switch interval (avoid thrash and multi-hour gaps).
3. Batch commits when possible to benefit from group commit (without changing durability semantics).
4. Choose transport mode (LGWR vs ARCH; SYNC vs ASYNC) to match RPO/RTO needs.

Usefulness and Modernization Notes

• This lesson focuses the “head/tail” mental model on LGWR’s durable commit path and removes unrelated OS topics (e.g., Linux head/tail file viewing) from the critical path.
• Clarifies redo transport choices for Data Guard and adds diagnostic queries for commit latency vs. device latency.

Log Writing Process - Quiz