Undo & Redo

Oracle Undo and Redo Mechanisms Explained

This document delves into the fundamental concepts of Undo and Redo in Oracle Database, clarifying their roles in ensuring data consistency, transaction integrity, and database recovery. It specifically addresses why undo information is stored in redo logs in addition to undo segments/tablespaces.

Redo and Undo: The Basics

• Redo (Redo Log Buffer / Redo Log Files): Records all changes made to the database. This includes DML (Data Manipulation Language) operations, DDL (Data Definition Language) operations, and internal Oracle operations. Redo is primarily used for roll-forward during recovery (applying changes to bring datafiles up to date).

  • The Log Writer (LGWR) process writes both undo and redo for every DML in a transaction (committed/uncommitted), irrespective of whether a checkpoint has taken place or not.

  • Redo/undo for committed changes will always be in redo logs (LGWR writes on commit).

  • Redo/undo for uncommitted changes may be in redo logs (LGWR writes every 3 seconds, on commit, or when 1/3 full).

• Undo (Undo Segments / Undo Tablespace): Records information needed to reverse (undo) changes made by transactions. Undo is primarily used for:

  • Rollback: Reversing uncommitted transactions.

  • Read Consistency: Providing a consistent view of data to queries that started before changes were committed.

  • Flashback Features: Enabling features like Flashback Query and Flashback Version Query.

Interaction with Checkpoints

A checkpoint is a database event that signals DBWR to write all dirty buffers (modified data blocks) from the database buffer cache to the datafiles.

• On Checkpoint:

  • Undo present in buffers in the database buffer cache is written to the undo tablespace.

  • Dirty buffers containing committed/uncommitted data are written to datafiles.

• Implications:

  • Datafiles may contain uncommitted data (buffers dirtied by uncommitted transactions and a checkpoint takes place before commit).

  • Committed data may not yet be in datafiles (if checkpointing has not occurred after the commit).

Where Undo Information Resides

Undo information can exist in multiple places:

• Redo Logs: Contains both redo and undo records for all changes.

• Buffer Cache: Undo information for recent changes may be present in the buffer cache if a checkpoint has not yet written it to the undo tablespace.

• Undo Tablespace: This is the persistent storage for undo information, written from the buffer cache during checkpoints.

  • Undo information in the undo tablespace will never be overwritten until the transaction that generated it is no longer active (either committed or rolled back).

Why Undo is Also Stored in Redo Logs

Let's consider scenarios if undo were only stored in undo buffers and the undo tablespace:

Scenario I: Instance Crash

1. A change is made but not committed.

2. The redo for the change is in redo logs.

3. The undo for the change is in the buffer cache.

4. A checkpoint has not taken place, so the undo for the change is not yet in the undo tablespace.

5. The instance crashes.

6. The undo information present in the buffer cache is wiped out (as buffer cache is volatile memory).

7. As part of instance recovery, redo is applied, and datafiles now contain the uncommitted data.

8. Problem: The database cannot be opened because the undo information needed to roll back the uncommitted change is not available, leading to an inconsistent database state.

Scenario II: Media Recovery (Datafile Loss)

1. A hot backup of a tablespace is taken.

2. The datafile for the tablespace is lost or corrupted.

3. The tablespace is taken offline, and the datafile is restored from backup.

4. The datafile needs to be recovered (roll-forward).

5. The redo logs and archivelogs contain redo data for both committed and uncommitted transactions.

6. Assumption (for this scenario): Redo logs and archivelogs do not contain undo data.

7. As part of recovery, redo for all committed/uncommitted changes is read from online/archive redo logs and applied.

8. Problem: The tablespace cannot be brought online because the undo information needed to roll back uncommitted changes is not available.

Conclusion: The Necessity of Undo in Redo Logs

To resolve the problems highlighted in the scenarios above, undo information is also stored in redo logs.

During the roll-forward phase of instance or media recovery:

• As changes (redo records in redo logs) are applied to datafiles, the undo information also present in the redo logs is used to generate undo segments.

• These newly generated undo segments are then subsequently used during the rollback phase of instance or media recovery to roll back any uncommitted changes, ensuring the database achieves a consistent state.

This dual storage of undo information (in redo logs for recovery and in undo segments/tablespace for active transactions, read consistency, and flashback) is fundamental to Oracle's robust transaction management and recovery capabilities.