Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Swayamjit.

Asked: June 23, 2016 - 9:44 am UTC

Answered by: Connor McDonald - Last updated: January 22, 2018 - 1:45 am UTC

Category: Database - Version: 11.2.0.3

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: On Oracle Database 12c, Part 1

You Asked

Hi Tom,

I have a doubt regarding the role of undo segments in instance recovery, it is clear that the roll forward operation also generates the undo segments in the undo tablespace but I want to understand from where it gets the past image of the record to generate the undo segments and roll back the transaction during instance recovery.

Kindly clarify.
Thanks

and we said...

OK, let's say at 9am, the power (and hence your server) goes out.

Leading up to that point in time, your database probably had

- plenty of committed changes from completed transactions
- a batch of un-committed changes from active transactions

There are two things to keep in mind here:

1) Some of those *committed* changes, may NOT be reflected in the datafiles
2) Some of those *uncommitted* changes may BE reflected in the datafiles.

Because the "datafiles" are not actually a reflection of the exact state of your database, its the redo logs (or the sum total of every redo log entry every made). It's almost like datafiles are a "performance enhancement" designed to save you from reading every redo log in the history of the database in order to see the current state.

So when your server powers back up again, we need use the redo logs to get our datafiles to a consistent state.

So we roll the redo changes forward to bring our datafiles to a point where all the committed and potentially *uncommitted* changes are applied. In doing do, we've also brought forward the files we use for our UNDO tablespace (which stores info on how to undo a transaction).

Now we need to back out (rollback) those uncommitted changes. So we can use the freshly recovered undo information, to now reverse out those uncommitted changes.

Thus at the end of the recovery, we have our desired consistent state - just those changes that committed successfully before the power was lost.

Hope this helps.

and you rated our response

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Perfect and exactly what I was looking for

June 24, 2016 - 3:03 am UTC

Reviewer: Swayamjit Mallick from India

Thanks for clearing the doubt, explained much comprehensively.

Undo recovery

January 18, 2018 - 5:27 am UTC

Reviewer: Akshay from India

So how is the uncommitted data written to the datafile rolled back? I mean , what if the data pertaining To that perticular uncommitted data is prior to the checkpoint position in the ORLs... ?
Now in that case, how does oracle undo the changes done to the datafile?
For eg: if the value of x=5 has been changed to 6 by a user... dbwr writes this change to the datafile... now the redo will also have this entry... after sometime the checkpoint position will be past this transaction in the redo log... and during instance recovery this perticular transaction will not be included.
How will the value of x be changed to 5 again?
Connor McDonald

Followup  

January 22, 2018 - 1:45 am UTC

" if the value of x=5 has been changed to 6 by a user... dbwr writes this change to the datafile... now the redo will also have this entry."

And the *undo* information has also been written to the redo log. The redo stream would contain something like:

a-This is transaction "123",
b-record the undo, how to change x=6 back to x=5
c-record the change, I've changed to x=5 to x=6
d-transaction "123" has committed

If I never got to (d), then at instance recovery time, I'll apply that redo and end up at state (c), ie, I have uncommitted transaction 123, which I now need to rollback. And I can roll that back, because I resurrected the undo information in step (b).