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

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, GAVRIEL.

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

Last updated: December 10, 2021 - 3:09 am UTC

Version: 19.3.0.0

Viewed 1000+ 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"

Rating

  (3 ratings)

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

Comments

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.

samchj, December 09, 2021 - 3:32 am UTC

Hi Tom, is that feature for 19c or exactly exist at 11g\12c?
when I backup a database from standby, and try to recover using backup control file and got oral-660[3020] /ora-10567/ora10561, I just thought caused by this issue. I used oracle 11.2.0.4
Connor McDonald
December 10, 2021 - 3:09 am UTC

The logging on undo blocks has existed since Oracle has existed (well, probably back to Oracle 3 or 4)

history essay

bore, December 10, 2021 - 6:44 am UTC

Term Undo was introduced in 9i. Old Rollback segments began to be called manual undo management. New mechanism is called automatic undo management.
In the manual undo, the rollback segments are explicitly created by the user in regular tablespaces. Automatic undo stores the rollback segments in undo tablespace (SYS still uses the system tablespace).
In both mechanisms, blocks of rollback segments are written to redo.

More to Explore

Administration

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