Writing Redo from the Buffer to Log Files in Oracle 26ai

How LGWR writes the redo

LGWR writes redo sequentially into the current online redo log group. If the group has multiple members, each member receives the same redo. LGWR coordinates the work and can delegate concurrent operations, including writes and completion notifications, to LGnn workers named LG00 through LG99. The worker population depends on the environment.

Redo transport is a separate responsibility. TTnn workers support asynchronous shipping to standby destinations; they are not an extra step required for the local buffer-to-online-log write. See Oracle's LGWR process description.

Oracle documents several circumstances that cause LGWR to write:

• A transaction commits.
• An online redo log switch occurs.
• Approximately three seconds have elapsed since the previous write.
• The buffer is one-third full or contains 1 MB of buffered redo.
• DBW needs the corresponding redo written before writing a dirty buffer.

These are write conditions, not a fixed schedule or a promise of one I/O per transaction. A flush may contain work from many sessions, including transactions that have not committed. Persisting their redo does not automatically commit those transactions.

Write-ahead logging and datafile writes

Before DBW writes a dirty buffer, the redo associated with its changes must already be persistent. If necessary, DBW signals LGWR and waits for the required log write. This write-ahead rule provides recovery information before the changed block reaches a datafile.

The rule does not require every datafile write to occur after commit. DBW may write blocks containing uncommitted changes, while blocks changed by committed transactions may remain in memory. Recovery accounts for this ordering through redo and undo. The process architecture documentation explains the coordination between LGWR and DBW.

What makes a commit durable?

For a normal synchronous commit, Oracle waits for the required redo, including the transaction's commit record, to become persistent before reporting success. Some earlier redo may already have reached the logs. The remaining log write establishes durability without waiting for DBW to write every modified block.

This separation is the basis of fast commit. Following an instance failure, Oracle can recover recorded changes even when their data blocks had not reached the datafiles. A successful commit therefore promises a recoverable transaction; it does not promise that every affected buffer has been written.

The following statement makes the synchronous commit behavior explicit:

COMMIT WRITE WAIT IMMEDIATE;

WAIT controls when success returns to the client, while IMMEDIATE controls write scheduling. The documented default is WAIT IMMEDIATE, although COMMIT_WAIT and COMMIT_LOGGING settings can affect a statement that omits the WRITE clause.

BATCH allows deferred scheduling and can still be combined with WAIT. NOWAIT can return before the redo becomes persistent, so a crash can lose acknowledged work. It changes the application's durability expectations and is not a general remedy for slow commits. See the COMMIT reference.

Normal concurrent commits can already share an LGWR write through group commit. Several waiting transactions can have their redo persisted together; explicit BATCH is not a prerequisite. With synchronous Data Guard transport, configured remote acknowledgement requirements can also affect commit latency.

For example, session A updates a row and commits while session B is still processing a longer transaction. The log write needed for A can include redo already generated by B. A's commit record establishes A's committed state; B's presence in the same write does not commit B. If the instance then fails, recovery can reapply recorded changes and use undo to reverse B's uncommitted work. The same log stream therefore contains information needed to recover both completed and interrupted transactions.

There is also a difference between a durable commit and receiving its acknowledgement. If the write succeeds but communication fails before the client receives success, the application may not know the outcome. A lost response does not prove rollback. Applications must resolve an uncertain outcome before blindly repeating work.

How online redo log groups are reused

A group is the unit to which LGWR writes and between which it switches. A member is a physical file within that group. Two members of one group contain the same redo; they are not two alternating destinations. The database requires at least two online redo groups, and the examples below use three.

When the current group fills, LGWR switches to another available group. It eventually cycles back to reuse an earlier group, assigning a new sequence number within the thread. A stable group number identifies the configured group; its changing sequence number identifies a particular use of that group.

Reuse is conditional. The old log must no longer be required for instance recovery. In ARCHIVELOG mode, the required archiving must also have completed. ARCn copies online redo for archival after a switch; archiving is separate from LGWR's initial write. Neither group rotation nor commit means that a log can be overwritten immediately.

A log switch initiates checkpoint activity, but that work need not finish instantly. The previous group can remain ACTIVE while needed for instance recovery. CKPT coordinates checkpoint information and DBW writes dirty blocks. Oracle covers the sequence in Managing the Redo Log.

For an authorized lab demonstration, a DBA with the required privileges in the CDB root can request a switch:

ALTER SYSTEM SWITCH LOGFILE;

This changes database operation and is unnecessary for inspection. Re-query afterward rather than assuming the previous group immediately becomes INACTIVE or archived. If the next group is unavailable, investigate checkpoint progress, archiving, and file errors.

Multiplexing addresses a different availability problem. If one member becomes inaccessible, LGWR can continue with accessible members and records the error in diagnostic output. Losing every usable member of a required group prevents normal progress. This is why the member inventory matters even when the current group appears to be receiving redo: a surviving member does not mean the intended redundancy is intact.

Finding information about your redo log files

SHOW CON_NAME

SELECT name, cdb, log_mode
FROM v$database;

Inspect group state with V$LOG

SELECT group#, thread#, sequence#,
       ROUND(bytes / 1024 / 1024) AS size_mib,
       members, archived, status
FROM v$log
ORDER BY thread#, group#;

Illustrative output; values vary by database:

GROUP# THREAD# SEQUENCE# SIZE_MIB MEMBERS ARCHIVED STATUS
------ ------- --------- -------- ------- -------- --------
     1       1       483      512       2 NO       CURRENT
     2       1       482      512       2 YES      ACTIVE
     3       1       481      512       2 YES      INACTIVE

Group 1 is the current group for thread 1. Group 2 is noncurrent but still needed for instance recovery, despite already being archived. Group 3 is no longer needed for instance recovery. Its archived status is a separate fact.

MEMBERS counts physical copies within each group. Dividing BYTES by 1024 twice expresses size in MiB. Another possible state, UNUSED, identifies a group not yet written in the relevant context. Use the V$LOG reference when interpreting other states.

Inspect physical members with V$LOGFILE

SELECT group#, type,
       NVL(status, 'IN USE') AS member_status,
       member
FROM v$logfile
WHERE type = 'ONLINE'
ORDER BY group#, member;

Illustrative output for the same three groups:

GROUP# TYPE   MEMBER_STATUS MEMBER
------ ------ ------------- ----------------------------------
     1 ONLINE IN USE        D:\oradata\CDB1\redo01a.log
     1 ONLINE IN USE        E:\oradata\CDB1\redo01b.log
     2 ONLINE IN USE        D:\oradata\CDB1\redo02a.log
     2 ONLINE IN USE        E:\oradata\CDB1\redo02b.log
     3 ONLINE IN USE        D:\oradata\CDB1\redo03a.log
     3 ONLINE IN USE        E:\oradata\CDB1\redo03b.log

The query uses NVL to display a null member status as IN USE. This label does not mean the group is ACTIVE or CURRENT. All six members are in use, although their groups have different recovery states.

For member status, STALE means incomplete contents, INVALID means inaccessible, and DELETED means no longer used. Status alone does not identify the cause of a problem. The TYPE filter selects online members and excludes standby redo entries. See V$LOGFILE.

Actual member paths may use ASM or Oracle Managed Files naming. Different drive letters in this illustration do not establish physical storage independence. The next lesson examines how to arrange multiplexed members for protection.

Inspect retained history with V$LOG_HISTORY

SELECT thread#, sequence#,
       TO_CHAR(first_time, 'YYYY-MM-DD HH24:MI:SS') AS first_redo_time,
       first_change#, next_change#, resetlogs_change#
FROM v$log_history
ORDER BY first_time DESC, thread#, sequence# DESC, recid DESC
FETCH FIRST 20 ROWS ONLY;

This three-row excerpt illustrates possible results:

THREAD# SEQUENCE# FIRST_REDO_TIME     FIRST_CHANGE# NEXT_CHANGE# RESETLOGS_CHANGE#
------- --------- ------------------- ------------- ------------ -----------------
      1       482 2026-09-12 14:10:00       5328000      5341000           5000000
      1       481 2026-09-12 14:05:00       5316000      5328000           5000000
      1       480 2026-09-12 14:00:00       5301000      5316000           5000000

FIRST_TIME identifies the first redo entry's time, not archive completion. The SCN columns describe recorded change information. Thread and resetlogs context help distinguish sequences that could otherwise look identical across threads or database incarnations.

These are retained control-file records. A history row does not establish that its archived file still exists or that a usable backup is available. The view also is not a transaction audit. See the V$LOG_HISTORY reference.

Read the three examples together. Sequence 483 identifies the current use of group 1; the member query shows the two filenames belonging to that group. Earlier sequence 482 appears in history and remains ACTIVE in the group snapshot. These facts answer different questions: where current writes go, which physical copies exist in the configuration, and what earlier redo remains relevant to recovery.

Observe progress along the write path

Group and member views show configuration and state. Wait events and counters help identify where work is delayed:

Inspect the following counters during a known workload interval:

SELECT name, value
FROM v$sysstat
WHERE name IN ('redo size', 'redo writes',
               'redo buffer allocation retries')
ORDER BY name;

SELECT event, total_waits,
       ROUND(time_waited_micro / 1000000, 3) AS total_wait_seconds
FROM v$system_event
WHERE event IN ('log buffer space', 'log file parallel write',
                'log file sync',
                'log file switch (checkpoint incomplete)',
                'log file switch (archiving needed)')
ORDER BY event;

Compare snapshots and calculate differences, accounting for instance restarts. Redo bytes generated during an interval divided by elapsed seconds gives a redo generation rate. Cumulative totals alone do not describe the current workload.

For example, an increase of 104857600 redo bytes across ten seconds represents an average of 10 MiB per second. Record the workload and interval alongside that result. Compare allocation retries over the same interval, then investigate any corresponding waits. A high lifetime count without recent increases does not establish a current buffer shortage.

From the CDB root, these queries report instance-wide activity. Concurrent sessions accumulate waits, so total wait seconds can exceed elapsed time. Foreground and background counts also represent different operations. Commit synchronization includes more than storage latency; one slow write may delay many committers. Consult Oracle's wait-event descriptions before attributing a delay to one component.

Check buffer sizing without guessing

SHOW PARAMETER log_buffer

LOG_BUFFER controls redo-buffer memory. It is not dynamically modifiable or PDB-modifiable. Default allocation depends on the environment, including SGA size, CPUs, and redo strands; it is not a universal fixed value.

Investigate sustained buffer-space waits and allocation retries alongside log I/O, checkpointing, and archiving. A larger buffer can absorb bursts but cannot make storage faster or unblock reuse. Flashback and Data Guard configurations have additional sizing considerations. Use the parameter reference and redo-buffer tuning guidance for the actual deployment.

Release-specific redo prioritization

Starting with Oracle AI Database 26ai Release Update 23.26.3, LOG_REDO_PRIORITIZATION can prioritize redo generation when free buffer space becomes scarce. It defaults to FALSE, is dynamically changeable at instance level, and is not PDB-modifiable. RAC instances can use different settings.

The feature can delay high-redo batch work to help OLTP sessions progress. It does not increase storage throughput or change commit durability. Evaluate it for the installed RU and workload rather than enabling it routinely. Earlier 26ai installations may not expose the parameter. See LOG_REDO_PRIORITIZATION.

When reviewing an instance, connect the observations: identify the current group, inspect its members, check retained history, and relate waits to the relevant stage. The next lesson explains multiplexing redo log files, which protects the physical members receiving this redo stream.