how do uncommitted transactions span log switches?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: how do uncommitted transactions span log switches?

  1. #1
    Join Date
    Aug 2000
    Posts
    462
    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 DBA and Developer

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Aug 2000
    Posts
    462
    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.
    Oracle DBA and Developer

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Aug 2000
    Posts
    462
    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?
    Oracle DBA and Developer

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Aug 2000
    Posts
    462
    Thanks again. I really appreciate you sharing your obviously extensive knowledge so freely.
    Oracle DBA and Developer

  8. #8
    Join Date
    Mar 2001
    Posts
    286

    Question

    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.

  9. #9
    Join Date
    Aug 2000
    Posts
    462
    OK, now I'm officially confused. Who's right?
    Oracle DBA and Developer

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Question

    What is confusing you?

    In which part dba_admin's mesage contradicts my explanation?

    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width