Oracle Scratchpad

October 14, 2009

Nutshell – 1

Filed under: Infrastructure,redo,undo — Jonathan Lewis @ 8:22 pm BST Oct 14,2009

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”.

6 Comments »

  1. I tried to explain undo, redo and recovery to my non dba cousin 2 weeks ago… it’s harder to be concise than I realised.

    I’ll point him here next time (if I haven’t scared him off!).

    Comment by Graham Oakes — October 21, 2009 @ 11:39 am BST Oct 21,2009 | Reply

  2. [...] that undo also has to be described by redo ( see Nutshell – 1 ) you can see that most of the redo is actually from the undo – 38MB out of 42MB. So what [...]

    Pingback by Bitmap Updates « Oracle Scratchpad — October 21, 2009 @ 7:35 pm BST Oct 21,2009 | Reply

  3. [...] Jonathan Lewis-Nutshell-1 [...]

    Pingback by Blogroll Report 09/10/2009-16/10/2009 « Coskan’s Approach to Oracle — October 21, 2009 @ 11:29 pm BST Oct 21,2009 | Reply

  4. [...] [Back to Nutshell -1 (Redo and Undo)] [...]

    Pingback by Nutshell – 2 « Oracle Scratchpad — March 21, 2010 @ 8:17 pm BST Mar 21,2010 | Reply

  5. [...] tags: Jonathan Lewis, recovery, redo, undo by jametong 本文翻译自Jonathan Lewis的Blog 文章Nutshell – 1, [...]

    Pingback by 简述undo,redo与Recovery概念 | a db thinker's home — March 22, 2010 @ 4:08 am BST Mar 22,2010 | Reply

  6. […] Cet article est la traduction d’un article de Jonathan Lewis publié sur son blog. L’article original en anglais se trouve ici. […]

    Pingback by Undo et Redo et Recovery en quelques mots, par Jonathan Lewis | Oracle – Concepts et Exemples — November 19, 2013 @ 4:04 pm BST Nov 19,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,258 other followers