Skip to Main Content
  • Questions
  • Undo blocks stored in the redo log buffer


Question and Answer

Connor McDonald

Thanks for the question, GAVRIEL.

Asked: October 05, 2021 - 4:43 am UTC

Last updated: October 11, 2021 - 10:53 am UTC


Viewed 100+ times

You Asked

hey tom I read from couple of article which state that when we issue a dml statement and commit, the changes made to the database and undo block is stored into the redo log buffer. what purpose does this undo block do exactly?

and Connor said...

The redo is the capture of *everything* that ever happens in your database. Writing information to undo is a change to the database, hence it needs to go into redo.

Here's an example of why we must do that.

You start "delete from my_huge_table" and that is running for 5mins when BOOM, the database crashes due to power failure. Its important to realise that *during* those 5mins, we were happily writing those *uncommitted* changes to disk - we dont "store them up" - we actually write those changes to the blocks/files. This is why a commit is instantaneous no matter how many rows you change.

When the power comes up, we need to get the database back in a working order, which means

a) any transactions that had committed but had not yet written all of their changes from memory to disk ... need to re-run on the database
a) any transactions that had *not* committed but had *already* written some of their changes from memory to disk ... need to backed out from the database

For (a), we use the redo logs to find those changes and apply them
For (b), we use the redo logs to bring back the *undo* information, and then we use that undo information to roll back that big delete that never finished.

Thats why the redo contains the undo. They are not opposites - better names would be: "undo" and "everything"


  (1 rating)


Transaction recovery use undo tablespace

frank, October 09, 2021 - 2:11 pm UTC

Hey tom from what i know the transaction recovery(which rollback the uncomitted transaction) use the undo information stored in undo tbs not in redo log

Is this concept wrong?
Connor McDonald
October 11, 2021 - 10:53 am UTC

Yes but we need to get that information *back* into the undo tablespace after a power outage, so that we can then *use* it to undo any uncomitted transactions.

We use the redo to bring the undo tablespace back to a correct state.

More to Explore


Need more information on Administration? Check out the Administrators guide for the Oracle Database