Undo, Redo, and Transaction Management
This domain tests how Oracle guarantees read consistency and durability: undo for before-images, redo for durability, and the transaction semantics that link them.
Undo
Undo records before-images and serves rollback, read consistency, and Flashback Query. With automatic undo management, the undo tablespace is named by UNDO_TABLESPACE, and undo extents are ACTIVE, UNEXPIRED, or EXPIRED. UNDO_RETENTION sets a target, but unexpired undo can be reused when space runs short unless RETENTION GUARANTEE is set. V$UNDOSTAT and V$TRANSACTION show consumption and active transactions.
Redo
Redo stores change vectors for both data and undo changes. Server processes copy them into the redo log buffer, and LGWR writes to the current online redo log group. A log switch makes the next group current and triggers a checkpoint; in ARCHIVELOG mode, ARCn archives the filled group. Every group needs at least one member, and multiplexing members across disks protects against media failure. LGWR writes to all members of the current group, so losing all of them halts the instance.
Transactions
A commit makes the transaction's redo durable and records a commit SCN; DBWn writes the modified buffers later. The default isolation level is READ COMMITTED, giving statement-level consistency; SERIALIZABLE gives transaction-level consistency and can raise ORA-08177. The transaction slot stays active in the undo segment header until commit.
Worked example
A long-running report fails with ORA-01555: snapshot too old. Reasoning: the query needs the pre-change image of blocks it already read, but that undo was overwritten by active transactions. Remedies are a larger undo tablespace, a longer UNDO_RETENTION with RETENTION GUARANTEE, and reducing commit frequency in the batch job that generates the undo. Raising UNDO_RETENTION alone does nothing if the tablespace is too small and no guarantee is set.
Traps that cost marks
- Believing COMMIT waits for DBWn.
- Forgetting that undo changes themselves generate redo.
- Assuming UNDO_RETENTION is enforced without RETENTION GUARANTEE.