shutdown abort and uncommitted changes – interesting article for the new oracle dba



came across this interesting discussion in oracle forums. As a new dba if you are wondering what happens to the uncommitted changes in case the database crashes or someone accidentally issues a shutdown abort then read on.

 

Imagine these circumstances:

1. I make an update to one row of a table, but do not commit – my change is written to rollback and redo log;
2. Some time passes, and my dirty buffer is written to the data file even though I did not commit;
3. Several log switches occur, and the logs are archived;
4. My instance crashes.

Now I have uncommitted data in my data file, and my online redo logs do not contain any information about this change. How does Oracle manage this?


jmodic

06-05-2001, 03:05 AM

Oracle will perform instance recovery upon startup. Instance recovery consists of two phases.

First Oracle performs roll-forward phase, where all entries in online redo log are applied to datafiles. After this Oracle performs roll-back phase, where all transactions that have not been commited yet are rolled back. Remember that untill transaction has not been commited or explicitely rolled back it must be still in the rollback segment. Since your transaction has not been commited it will be rolled back during crash recovery.


kmesser

06-05-2001, 06:46 AM

If the instance crashes, rollback is rebuilt using redo. It is not just reused from the ‘pre-crash’ state. And in my scenario, a log switch has occurred, meaning the online redo does not contain the transaction in question. This is the critical part of my question.


jmodic

06-05-2001, 09:29 AM

Originally posted by kmesser
If the instance crashes, rollback is rebuilt using redo. It is not just reused from the ‘pre-crash’ state.
It is wrong to say rollback segments are rebuild using redo. Yes, during roll-forward process rollback segments are modified the same way other tablespaces are, but only with changes that have not yet been checkpointed and were in the active redolog in the moment when crash occured. Changes that occured before log switch were allready written to the database files (also to RBS tablespace) during a checkpoint at log switch, no matter if the changes have been commited or not.

So in your case, when system crashed the commited and uncommited changes before the last log switch are allready in datafiles, hence the pre-change immage of uncomitted changes are still in rollback segments. After the rollforward part of instance recovery also the changes (commited and uncomited) that are in the active redolog are written to datafile and the undo information of all the uncommited transaction is in the rollback segments. So all the uncommited transactions are simply rolled back at the end and when the instance recovery is complete, all the commited transactions at the moment of crash are in the database, while all the uncommited transactions are lost.

No matter how many log switches have occured after your update in point 1., if there were no commit the undo information for this transaction must still be present in the rollback segment and can not be overwritten until the transaction is commited or rolled back. After instance recovery, this transaction will be rolled back if there was no commit for this transaction in the active redo log (the one that has not been checkpointed yet at the moment when instance crashed).

So as long as rollback isn’t lost, all is well. Thanks again.

Just for kicks, what if I do lose my rollback (maybe that’s why my instance fails)? Am I now in a situation where I must recover by applying backups and archived logs?


jmodic

06-05-2001, 10:49 AM

Well this is different situation, now you are not dealing with instance recovery but with media recovery.

As you know, you have to restore your datafiles and then apply all the archived redo logs. Once all the archlogs are applied you have the same situation as in previous case of instance recovery: Oracle will roll forward the remainig active redo log (the one that has not been archived yet) and then roll back all uncommited transactions.

This is correct according Oracle Documentation:

“Rollback entries change data blocks in the rollback segment, and Oracle records all changes to data blocks, including rollback entries, in the redo log. ”

“If a system crash occurs, Oracle automatically restores the rollback segment information, including the rollback entries for active transactions, as part of instance or media recovery.”

Logging Rollback Entries

Rollback entries change data blocks in the rollback segment, and Oracle records all changes to data blocks, including rollback entries, in the redo log. This second recording of the rollback information is very important for active transactions (not yet committed or rolled back) at the time of a system crash. If a system crash occurs, Oracle automatically restores the rollback segment information, including the rollback entries for active transactions, as part of instance or media recovery. Once the recovery is complete, Oracle performs the actual rollbacks of transactions that had been neither committed nor rolled back at the time of the system crash.

I’m assuming the following part confuses you:

Originally posted by dba_admin
quote:
“If a system crash occurs, Oracle automatically restores the rollback segment information, including the rollback entries for active transactions, as part of instance or media recovery.”

Automatically restoring the rollback segment information doesn’t mean that oracle applies changes from redo into *empty* rollback segments, it applies changes to RB segments as they were in the moment of system crash. Consider the following sequence:

T0: RedoA is active redo log
T1: log swich occurs, data from RedoA is written to datafiles, including to RBS segments; RedoB becomes active redo
T2: System crashes. In this moment information from RedoB was not yet written into RB segments.

After starting the instance, oracle has to perform instance recovery. Before it starts recovery, the RBS segments allready contain information up to T1, so everything that was in RedoA is allready in RBS. During rollforward part of instance recovery the information from RedoB will be *added* to rollback segments. When this is finished, RBS will contain the information as they did in T1 plus additionaly applied information from RedoB. Finaly all the uncommited transaction will be rolled back.

By the way, jmodic said

“T1: log swich occurs, data from RedoA is written to datafiles, including to RBS segments; RedoB becomes active redo”

Here is what I believe:

When log switch occurs, RedoA should be “ARCHIVED” to archived logs instead of “written to datafiles.” I believe there is big difference between “datafiles” and “archived logs.”

Or, do you mean the redo records in the redo logs will be written to correspoding data files. No, only DBWR will write data from DB Buffer back to corresponding datafiles. And that only happen when DBWR is triggered. LGWR and DBWR will not be triggered at the same time except during CHECKPOINT.

I will be glad to hear any comments. Thank you!

 

Originally posted by rsuri

This information is stored in the Block Header in the datafiles.

Well, it would be an overkill if Oracle had to scan all the datablock headers in every datafile in the database during rollback phase of crash recovery. It really doesn’t have to do that. After rolling forward, all the informations about each and every uncommited transactions are available in the rollback segments. Remember, uncommited undo information can not be overwritten by any other transaction, it must remain in RB segment until commited or rolled back. So your uncommited transactions might be days or weeks old, but they will still be there in the RB segments! Oracle just needs to scan RB segments and rolls those transactions back.

Originally posted by dba_admin
By the way, jmodic said

“T1: log swich occurs, data from RedoA is written to datafiles, including to RBS segments; RedoB becomes active redo”

Here is what I believe:

When log switch occurs, RedoA should be “ARCHIVED” to archived logs instead of “written to datafiles.” I believe there is big difference between “datafiles” and “archived logs.”

Or, do you mean the redo records in the redo logs will be written to correspoding data files. No, only DBWR will write data from DB Buffer back to corresponding datafiles. And that only happen when DBWR is triggered. LGWR and DBWR will not be triggered at the same time except during CHECKPOINT.

I will be glad to hear any comments. Thank you!

You are absolutelly correct. I might have done some unnecessary simplifications in my explanation. First assumption (for simplicity sake): checkpoint parameters are set so that checkpoints will occure only during log switches.

What I’ve written about what happens during log switch (implicitly during checkpoint) was realy badly phrased, your explanation is much more accurate. What I realy ment (and this was the whole point in this topic) was, that when logswitch (checkpoint) occurs the uncomitted transactions changes, that are allready written in the redo log files, are also written into database files. And your intervention here was correct – this information is not written from redo log file, but from buffer cache. But the point is, even uncommited transaction changes are written into database files when log switch occurs, so they can’t be lost no matter how many log switches have occured from that moment on untill the database crashed.

“The information in a redo log file is used only to recover the database from a system or media failure that prevents database data from being written to the datafiles. For example, if an unexpected power outage terminates database operation, then data in memory cannot be written to the datafiles, and the data is lost. However, lost data can be recovered when the database is opened, after power is restored. By applying the information in the most recent redo log files to the database datafiles, Oracle restores the database to the time at which the power failure occurred.”

My understanding is that this describes a situation in which the data changes have been written to memory and to the redo log but not to the hard disk. So that means that at some point Oracle bothered to write the changes to the hard disk, but just not to the data files. Why? If you’re going to write the data to the hard disk, just write it to the data files. What am I missing here?

 

 

My understanding is that this describes a situation in which the data changes have been written to memory and to the redo log but not to the hard disk.

Not correct.
Say you have a LONG, LONG, LONG running DML which changes more data than you have RAM.
The changed data IS written to data file & keep in mind this is prior to any COMMIT;
Now the system crashes.
Oracle utilizes the contents of the REDO file to ROLLBACK this uncommitted changes in the datafile.

 

 

Oracle utilizes the contents of the REDO file to ROLLBACK this uncommitted changes in the datafile
Not correct. Oracle utilizes the contents of the UNDO segments to Rollback uncomitted changes. Since Undo segments are, themselves, in data files (and may not have been flushed to disk), they are protected by Redo.

Application of Redo is a Rollforward. This also restores the Undo segments to the correct state. It is after this that Oracle does the Rollback, based on the Undo segments.

The simplest way to think of Redo (and the Archival of the Redo) is to think of how you can backup and restore a database.

How would you backup a database while it is in use ? You can’t lock all the datafiles to prevent writes to them. Yet, transactions may be updating different blocks in different datafiles even as the backup is in progress. Say your backup starts with datafile 1 (or even datafiles 1,2,3,4 in parallel) at time t0. By time t5, it has copied 20% of the datafile to tape or alternate disk backup location. Along comes a transaction that updates the 100th block (somewhere within the 10-11% range) of datafile 1 and also the 60th block of datafile 5. Meanwhile, the backup continues running, already having taken a prior image of the 100th block and not being aware that the block has been changed. At time t25 it completes datafile 1 (or datafiles 1,2,3,4) and starts backing up datafile 5. Now, when it copies the 60th block of datafile 5, it (the backup utility) doesn’t know that this block is inconsistent with the backup image of the 100th block of datafile 1.
Instead of 1 transaction imagine 100 or 1000 transactions occurring while the backup is running.
Surely, Oracle must be able to regenerate a consistent image of the whole database when it is restored ?
That is what the Redo stream provides. The Redo stream is written to Archivelogs so that it can be backed up — no Archivelog file is “in flux” (particularly if you use RMAN to backup the Archivelogs as well !).

Had Oracle been merely writing to the datafiles alone, without a Redo stream, there is no way it could recreate a consistent database — whether after Crash Recovery OR after Media Recovery.

Now, approach this from another angle. A database consists of 10 or 100 or 500 datafiles. You have 10 or 100 or 1000 sessions issuing COMMITs to complete their transactions, which could be of 1 row or 100 rows or 1million rows, each transaction of a different size. Should the 1000 sessions be forced to wait while Oracle writes all those updated blocks to disk in different datafiles — how many blocks can it write in “an instant” ?
But what if Oracle manages to write much less information — the bare minimum (called “change vectors”) to re-play every transaction to a single file serially ? That would be much faster. Imagine writing to 500 datafiles concurrently, having to open the file, progess to the required block address and update the block, for each block changed in each file VERSUS writing much lesser information serially to a single file — if the file is full, switch to another file, but keep writing serially.

 

 

Author: admin