Managing the Oracle Redo Log

The online redo log is one of the most important recovery structures in Oracle Database 23ai. It records the information Oracle needs to reproduce changes made to database blocks. If an instance fails before modified blocks are written from memory to the data files, Oracle can apply redo and recover the work that must survive.

A transaction is not acknowledged as committed merely because its changed blocks exist in the database buffer cache. Oracle first makes the transaction's redo durable. After the required redo and its commit record have been written to the online redo log, the database can report a successful commit to the session. This design gives Oracle strong transaction durability without forcing every changed data block to disk during each commit.

Properly managing redo is therefore a fundamental database administrator responsibility. The loss of required redo can interrupt database availability and may prevent complete recovery. This module introduces the redo architecture and prepares you for the administrative procedures covered in the following lessons.

When you finish this module, you should be able to:

1. Explain why online redo log files must be protected from loss.
2. List the redo log groups and members in a database.
3. Create new redo log groups and members.
4. Relocate or rename redo log files safely.
5. Drop obsolete redo log groups or members safely.
6. Manually force a log switch when an administrative task requires one.

When a transaction changes a database block through an operation such as INSERT, UPDATE, or DELETE, Oracle generates redo entries that describe the change. The entries contain change vectors: compact instructions that Oracle can use to reproduce changes to blocks. Server processes place these redo entries in the redo log buffer, a circular memory structure in the system global area (SGA).

Redo is not a copy of the SQL text, and the online redo log is not limited to committed transactions. It contains block-change information generated by committed and uncommitted work. Oracle also generates redo for changes to undo blocks associated with permanent objects. During recovery, this information enables Oracle to roll changes forward and then roll back transactions that did not commit.

Generating redo in memory and writing it sequentially is more efficient than synchronously writing many scattered data blocks for every transaction. The redo log buffer absorbs short bursts of activity, while the Log Writer process, known as LGWR, moves redo to persistent storage. Oracle can then schedule data-file writes independently through the Database Writer processes, known collectively as DBWn.

When a session issues COMMIT, Oracle coordinates the transaction's logical completion with a durable redo write:

1. Assign the commit SCN. Oracle assigns a system change number (SCN) that identifies the transaction's commit point in the database timeline.
2. Create the commit record. Oracle places a commit record in the redo log buffer with any remaining redo required to protect the transaction.
3. Signal LGWR. LGWR writes the necessary redo sequentially from the redo log buffer to the current online redo log group. In a multiplexed configuration, LGWR writes the same information to every member of that group.
4. Wait for durability. With normal commit processing, the foreground session waits until LGWR confirms that the commit record and its required redo are on durable storage. This wait is commonly represented by the log file sync wait event.
5. Acknowledge the commit. Oracle reports success to the session. The transaction's locks are released, and its changes become available to other sessions according to Oracle's read-consistency rules.

Commit Does Not Mean Every Data Block Is on Disk

LGWR writes redo; DBWn writes modified data blocks from the database buffer cache to the data files. These activities are deliberately separate. A successful commit does not wait for DBWn to write every block changed by the transaction. If the instance fails before those blocks reach the data files, Oracle can reconstruct the committed changes from redo during instance recovery.

The reverse situation is also possible. DBWn may write a block containing an uncommitted change before the transaction ends. This does not violate transaction integrity. If recovery is required, Oracle rolls redo forward to restore the complete block history and then uses undo to remove changes made by transactions that did not commit.

Committed Undo Can Remain Available

A commit ends the transaction, but Oracle does not necessarily erase its undo records immediately. Committed undo can remain available so other sessions can reconstruct older, read-consistent versions of data. It can also support features such as Flashback Query while the required undo has not been overwritten. Commit processing, undo retention, and the eventual reuse of undo space are related but separate concerns.

An instance failure can leave the data files physically behind the logical state recorded in redo. Committed changes may be present only in the online redo log, while some data-file blocks may contain changes made by transactions that never committed. Oracle resolves both conditions during instance recovery.

Recovery first rolls forward by applying redo generated after the relevant checkpoint position. This reconstructs changes in the data files and restores the undo information needed for transaction recovery. Oracle then rolls back changes belonging to transactions that were active but uncommitted when the failure occurred. The result retains committed work and restores transactional consistency. Instance recovery normally runs automatically when a database is opened after an abnormal shutdown.

Media recovery addresses damage to or loss of database files. A DBA restores an appropriate backup and applies archived redo logs followed by any required online redo. Online redo, archived redo, control-file information, and a sound backup strategy therefore operate as parts of one recovery design. Redo is essential, but it does not replace backups.

An Oracle database uses at least two online redo log groups in a circular sequence. LGWR writes to one group at a time. When the current group fills, or when a DBA requests a switch, LGWR stops writing to that group and begins writing to the next available group.

Redo log group

The logical unit selected as Oracle moves through the online redo log cycle.

Redo log member

A physical redo log file that belongs to a group.

Multiplexed group

A group with two or more members containing identical redo information.

Redo thread

The redo stream generated by an instance. A single-instance database normally has one thread; Oracle Real Application Clusters uses a separate thread for each active instance.

Members in the same group are written concurrently and have the same log sequence number. LGWR does not stripe one logical group's redo across its members; each member is a complete copy. Multiplexing protects the redo stream when one member becomes unavailable, but the members should reside on independent storage or failure domains to provide meaningful protection.

Before changing the redo configuration, inspect the existing groups, sizes, sequence numbers, archive state, and member locations. The following queries are read-only and provide a useful starting inventory:

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

SELECT group#, type, member, status
FROM   v$logfile
ORDER BY group#, member;

V$LOG describes logical groups. Its status values show whether a group is CURRENT, ACTIVE, INACTIVE, or otherwise unavailable for normal reuse. V$LOGFILE identifies the physical members and their paths. V$LOG_HISTORY provides recent log-switch history and can help a DBA assess whether groups are switching at an unexpected rate.

Use the results as evidence before creating, moving, or dropping files. Do not assume that an operating-system directory listing represents the database configuration. Oracle records redo log structure in the control file, and supported database commands must keep that metadata synchronized with the physical files.

Oracle recommends multiplexing the online redo log. Each group should normally contain multiple members placed on separate storage devices or independent failure domains. Storing two members on the same failed device does not provide the protection that multiplexing is intended to deliver.

A DBA should evaluate more than the number of copies. The database needs enough groups, and groups must be large enough, for LGWR, checkpoint activity, and archiving to proceed without avoidable contention. Groups that are too small can switch frequently and increase checkpoint and archival pressure. Groups that are unnecessarily large can increase the amount of redo involved in some recovery situations. Workload measurement and recovery objectives should guide the configuration.

Before dropping or relocating a group or member, verify its status and archive state. A current group is actively receiving redo and cannot simply be removed. An active group may still be required for instance recovery. When archiving is enabled, required redo should be archived before the group is reused or removed. Later lessons demonstrate the supported SQL statements and the safe order of operations.

Never move, rename, or delete an online redo log file at the operating-system level without coordinating the change with Oracle Database. An uncoordinated file operation can leave control-file metadata pointing to a missing member and can cause a log switch or instance failure at the worst possible time.

A log switch occurs when LGWR stops writing to the current online redo log group and starts writing to the next group. Oracle switches automatically when the current group fills. A DBA can also request a switch for an administrative purpose. Each time a group is reused, it receives the next log sequence number for its thread.

A checkpoint records recovery progress and coordinates the writing of dirty buffers toward the data files. A checkpoint and a commit are not the same event. Commit durability depends on the required redo reaching the online redo log; it does not depend on every changed data block reaching its data file. Checkpoint progress limits how far Oracle must scan and apply redo during instance recovery.

In ARCHIVELOG mode, the archiver processes copy filled online redo log groups to archive destinations before the groups are reused when archiving is required. Archived redo extends the recoverable history beyond the finite set of online groups. Together with backups, it supports media recovery and point-in-time recovery. A stalled archive destination can prevent LGWR from reusing a needed group, so archive destinations and space consumption must be monitored.

The core redo architecture remains the same in Oracle Database 23ai: server processes generate redo, LGWR protects the redo stream, DBWn writes database blocks independently, and recovery applies redo when necessary. Modern storage, Oracle Managed Files, Automatic Storage Management, multitenant databases, and Oracle RAC can affect how redo is deployed, but they do not remove the DBA's responsibility to protect and monitor it.

• Redo entries describe changes to database blocks and are first accumulated in the redo log buffer.
• LGWR writes redo sequentially to the current online redo log group and to every member of a multiplexed group.
• A normal commit is acknowledged after the required redo and commit record are durable; it does not wait for every changed data block to be written to a data file.
• Instance recovery rolls changes forward with redo and rolls uncommitted work back with undo.
• Redo groups are logical units, while members are the physical files that store identical copies of a group's redo.
• Multiplexing, independent storage, appropriate sizing, archiving, monitoring, and tested backups form a complete protection strategy.

Understanding the relationship among redo generation, commit processing, data-file writes, checkpoints, and recovery provides the foundation for every administrative procedure in this module. In the next lesson, you will examine why redo log protection is essential and how to reduce the risk of losing the redo required to keep an Oracle database recoverable.