Printable View
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?
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.
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.
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.Quote:
Originally posted by kmesser
If the instance crashes, rollback is rebuilt using redo. It is not just reused from the 'pre-crash' state.
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?
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.
Thanks again. I really appreciate you sharing your obviously extensive knowledge so freely.
quote:
--------------------------------------------------------------------------------
Originally posted by kmesser
If the instance crashes, rollback is rebuilt using redo. It is not just reused from the 'pre-crash' state.
--------------------------------------------------------------------------------
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."
================================
Detail:
Oracle8i Concepts
Release 2 (8.1.6)
Part Number A76965-01
4 Data Blocks, Extents, and Segments
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.
OK, now I'm officially confused. Who's right?
What is confusing you?
In which part dba_admin's mesage contradicts my explanation?
I'm assuming the following part confuses you:
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:Quote:
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."
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.
Great. Thanks again.
I'd still like to understand this at a lower level. In what locations does Oracle store the information that a change in a datafile is uncommitted? Is that merely because there is an undo in rollback? Clearly it isn't all in online redo, since log switches could goof that up.
This information is stored in the Block Header in the datafiles.
I would like to see what JModic has to say about this but in the meantime you may take a look at this article for a better explanation --
http://asktom.oracle.com/pls/ask/f?p...D:275215756923
- Rajeev
"Restore" vs "Rebuild"? I think it's an issue of wording. ( I think "recover" is actually better.)
Now, I believe jmodic has made this very clear! Oracle must recover rollback segment before it can do the "Rollback" in the instance recovery.
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!
Wow. Tons of great info.
Thanks to all (again)!
Thanks for the link. That's a good explanation.
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.Quote:
Originally posted by rsuri
This information is stored in the Block Header in the datafiles.
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.Quote:
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!
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.
Perfect!
:>