Understanding Oracle Undo and Redo: Deep Dive into Transaction Mechanics

Overview

The article uses a series of hands‑on experiments to illustrate how Oracle implements undo and redo mechanisms within a transaction, why they are essential for data consistency, and how understanding their internal structures can aid in troubleshooting complex errors such as ORA‑600 startup failures.

Undo in Transactions

1.1 Parsing the Undo Segment Header

The experiment modifies two rows stored in different data blocks, then dumps the corresponding blocks to observe the undo segment header. Key fields displayed include:

xid = Undo‑Segment‑Number + Transaction‑Table‑Slot‑Number + Wrap

uba = Address of the last undo block used + Sequence + Last entry in undo‑record map

Additional flag values indicate transaction state (active, committed, fast‑commit, etc.). The dump shows that both blocks share the same transaction slot and undo block, confirming that undo records are column‑level rather than block‑level.

1.2 Parsing Undo Blocks and the Undo Chain

From the xid (0x0006.01f.000001a5) we identify slot 1f as active (state=10) and locate the undo block at DBA address c03fba. Dumping block 16314 of file 3 reveals two important pointers:

irb – points to the last undo record (rollback start)

icl – points to the first undo record (rollback end)

The undo block contains a linked list of undo records; each record’s rci points to the previous record, forming the undo chain. The article visualises the chain and notes that the chain is crucial for save‑point implementation and partial rollbacks.

The complete undo chain diagram demonstrates how each undo record links back to its predecessor, enabling Oracle to reconstruct previous data states during rollback.

Redo in Transactions

2.1 Parsing Redo Structure and How Redo Protects Undo

Redo records are composed of CHANGE VECTOR (CV) units. Each REDO RECORD may contain multiple CVs, each CV targeting a single data block. The article shows a dump of block 405 (the table’s data block) and the associated redo entries generated by a DELETE operation.

Key observations:

Redo is stored as a series of CHANGE VECTORs; each CV records the before‑image of a column.

The before‑image in redo is what protects undo – it allows recovery of the original value even if the undo block is lost.

A single REDO RECORD can span multiple blocks, but each CV applies to only one block.

These properties mean that redo drives forward the version of a data block, while undo provides the ability to roll back to a prior version; true backward versioning can only be achieved via media recovery.

2.2 Fast‑Commit Mechanism and the Real Meaning of DELETE

After deleting two rows (located in different blocks) and committing, a dump of block 405 still shows the original row count, but the deleted rows are absent from the visible data. Row‑level lock flags remain set, illustrating Oracle’s fast‑commit and delayed block‑clear behavior: the block header and row header are updated, but the actual data remains until overwritten.

Further inspection with BBED shows that the row’s data is still present in the block; only the row‑header flag is changed. Consequently, before the space is reused, the deleted data can be recovered by resetting the flag.

Practical Implications

Understanding the interplay of undo, redo, fast‑commit, and delete semantics helps DBAs diagnose ORA‑600 startup failures that often stem from corrupted redo/undo information after abnormal shutdowns. The article recommends examining alert logs, trace files, and using trace events (e.g., 10013, 10015, 10046) during the MOUNT phase to pinpoint the root cause.

It also points out that MOS (My Oracle Support) provides targeted searches for ORA‑600 error codes, but when MOS solutions fail, deep knowledge of undo/redo internals becomes essential for manual analysis.