Nutshell – 1

Undo, redo, and recovery in a nutshell. (The “1” in the title may turn out to be optimistic, I offer no guarantee about further nutshells.)

In the most general case of updating a block – when you change one row in an table, say, or mark an index entry as deleted – Oracle does the following:

• creates some redo (called a change vector) to describe the change to the table block
• creates some undo to describe the older version of the changed data
  • this really means creating some redo (another change vector) to describe how to create that undo
• writes the change descriptions into the redo log buffer
  • the pair of change vectors go together (undo first) into a single redo record
• changes the undo block
• changes the table block

The redo must be written to disc before the table block and the undo block are written to disc. Eventually, though, the undo block and the changed table block will be written to disc – even if the change has not been commited.

A Common Question: If the database crashes before the transaction commits, how can Oracle recover the old version of the data if the new, uncommited version has overwritten it ?

Answer: After the database crash, the recovery process knows the last checkpoint time for each file, and applies redo to bring each datafile up to date. This mechanism applies to the files in the undo tablespace, just like any other permanent tablespace.

Once the database has been brought up to date the recovery process can see the latest version of both the table block and the undo block. Since the undo tablespace (including the undo segment header block, hence the transaction table) is up to date  the recovery process can detect that that transaction didn’t commit, so it can do a rollback to recover the old table data from the undo block.

Footnotes:

This description of data change is the most general example – there are special cases that increase the complexity of the mechanism. In particular, there is some special processing if your change is the start of a transaction, and in 10g the first few changes of a transaction now use a special optimisation on single-instance Oracle that takes advantage of “private redo threads”, and “in-memory undo”.

Related Links: Julian Dyke has a very nice Powerpoint presentation about redo internals on his website. The presentation has a datestamp of 2005 and doesn’t cover 10g and the changes made for private redo, but that’s not really a problem as the enhancements were mostly about timing rather than basic concepts.

See the Oracle Glossary for notes on the “undo segment header” and “transaction table”.