This discussion is archived
8 Replies Latest reply: Jan 22, 2013 11:36 PM by Aman.... RSS

committed trans in datafiles in oracle 10g

user10389461 Newbie
Currently Being Moderated
Hi DBA gurus,

I have a small doubt : If the datafiles contains both committed and uncommitted data then howcome there is only committed trans and uncommitted transx. will be rollbacked.
I know that while instance recovery SMON automatically does that but is there any other activity that triggers only committed data in datafiles or what initiates Instance recovery.

Thanks in advance.

Regards,
Shikha Rani
  • 1. Re: committed trans in datafiles in oracle 10g
    Catfive Lander Explorer
    Currently Being Moderated
    The datafiles do contain both committed and uncommitted transactions.
    Redo contains the information needed both to re-do transactions and to re-create the undo that would allow them to be rolled back.
    Therefore, at startup, SMON spots whether the database is in an inconsistent state and performs instance recovery if so. The technique it uses is, basically, to ask the control file for the time of the last checkpoint and then to see if there is any redo in the current redo log from a time after that. If there is, then the instance must have crashed and instance recovery wil be required. If you do a shutdown immediate, on the other hand, the last thing that happens is that a checkpoint is issued to ensure all buffers are flushed to disk -so, in that case, there is no redo after the time of that checkpoint, and therefore the shutdown is clean and no recovery is needed.
    If instance recovery is needed, SMON finds the time of that last checkpoint and begins replaying the redo from that point forward.
    As the redo is replayed, undo segments are populated as they were when the transaction was first performed.
    If a transaction commits during this roll-forward, so be it: that data replay is permanent.
    But when SMON gets to the end of the roll-forward phase, it then knows that there are many uncommitted transactions -but in re-creating those transactions, it has also re-created the undo that allows them to be reversed.
    So, now uncommitted transactions are rolled back.


    (I should say that this is a bit of a simplification, because Oracle enhanced the redo stream a few versions ago so that smon doesn't have to blindly replay ALL redo from the time of the last transaction: it can skip stuff it knows was committed and flushed to disk before the instance crash. But this is a detail that doesn't alter the general points made above significantly).
  • 2. Re: committed trans in datafiles in oracle 10g
    user10389461 Newbie
    Currently Being Moderated
    Thanks Lander,

    You are absolutely right SMON does that while Instance recovery.

    But what if the database is UP and running and it is not started up since a long time and there is no Instance recovery .. and as far as I know again chkpt initiates the DBWR to write dirty buffers to the disk [committed and uncommitted].

    Please do correct me If I am wrong.
  • 3. Re: committed trans in datafiles in oracle 10g
    Catfive Lander Explorer
    Currently Being Moderated
    If the database is running normally, then yes, the blocks of data on disk will contain committed and uncommitted transactions. (I think I confirmed that in my first sentence of my earlier reply).

    DBWR flushes buffers to disk on its own schedule, paying no regard to whether their contents are committed or uncommitted.
    But DBWR cannot write a block to disk until the redo which is associated with dirtying it has been written.
    That redo will be written whether or not there has been a commit.

    Once you have a dirty block on disk, and the redo needed to un-dirty it by transaction recovery, everything is safe and recoverable.

    I thought I should add to my answer:

    Suppose you have a transaction that starts at time A and runs until time K.
    At time D, there is a log switch, which causes a checkpoint, and therefore at time D, blocks dirtied at time A, B, C and D are now all written to disk, including undo generated by dirtying the blocks
    At time E, that transaction continues and dirties more blocks.
    At time F, there is another log switch, so blocks at time D, E and F are now all on disk (plus their corresponding undo)
    At time H, there is yet another log switch, so blocks at time G and H are now written to disk (along with the undo)
    This database is not in archivelog mode, and you've just switched all the way around the redo logs, and the transaction still hasn't finished or committed yet.

    So now you are still dirtying blocks at time I and J, and the redo from that is being written into the current redo log, over the top of the redo that was generated at times A->D. Redo from time A-D is no longer available. But that's OK, because the dirty blocks from that time ARE safe on disk, and the undo needed to clean them up is also safe on disk.

    Now at time K, you have an instance crash.
    SMON won't start the transaction from scratch. It doesn't need to. It simply starts re-dirtying blocks from time I and J.
    When it's reached time J, it now sees there's no commit, and therefore knows to roll back the entire transaction. It can roll back the blocks from time I and J using undo re-created the redo from the current log was being re-played. It can roll back the blocks from time A ->H because the undo for them is already available from the data files themselves, saved at the time of the various checkpoints.
  • 4. Re: committed trans in datafiles in oracle 10g
    user10389461 Newbie
    Currently Being Moderated
    many thanks to u for the nice clarification.
    Still have a doubt ..

    suppose at time F there is a committ and there is no log switch and time K there is no instance crash but a log switch .

    now from time A to K there are committed and uncommitted transx. in the datafiles and there is no instance crash yet then my doubt is howcome the committed data will b there in the datafiles and uncommitted data will be rolled back .

    I apologize if 'm bothering u.
  • 5. Re: committed trans in datafiles in oracle 10g
    Catfive Lander Explorer
    Currently Being Moderated
    I may not be understanding the subtlety of your question. You keep saying "suppose there is no instance crash".

    If there is no instance crash, then at time K you have uncommitted data in the data files (plus, other people's committed data). That's not a problem, because if you ask to read the data I've dirtied, we do real-time rollback of a copy of the dirty block, using the undo my transaction generated in order to work out what the data ought to look like as far as you are concerned.

    Uncommitted data in the data files won't be rolled back until I say "rollback". If I don't do that, and I don't do a commit, then the dirty data stays in the data files, pretty much indefinitely. But we will roll back COPIES of those blocks to provide a 'clean' version for other people to read when they need to.
  • 6. Re: committed trans in datafiles in oracle 10g
    user10389461 Newbie
    Currently Being Moderated
    Understood.Again thanks for a nice clarificaton .
  • 7. Re: committed trans in datafiles in oracle 10g
    Aman.... Oracle ACE
    Currently Being Moderated
    I should say that this is a bit of a simplification, because Oracle enhanced the redo stream a few versions ago so that smon doesn't have to blindly replay ALL redo from the time of the last transaction: it can skip stuff it knows was committed and flushed to disk before the instance crash. But this is a detail that doesn't alter the general points made above significantly).
    Just for my own clarification,I think it's not the committed data that is skipped by SMON but the data that's checkpointed only. During the +2nd pass+ , the SMON would skip the buffers that were checkpointed before by DBWR and were marked with bwr . Am I right?

    Regards
    Aman....
  • 8. Re: committed trans in datafiles in oracle 10g
    Aman.... Oracle ACE
    Currently Being Moderated
    user10389461 wrote:
    Hi DBA gurus,

    I have a small doubt : If the datafiles contains both committed and uncommitted data then howcome there is only committed trans and uncommitted transx. will be rollbacked.
    I know that while instance recovery SMON automatically does that but is there any other activity that triggers only committed data in datafiles or what initiates Instance recovery.
    I won't explain your original question as it's answered very nicely by Catfive but for the part that whether there is any activity that pushes the committed data right away into the datafiles, I shall just echo what's told already, the answer is a no. It's not that the committed data is not important for the datafile but what is more important is two things. One, the committed data must be written as fast as possible to the disk to make it saved which is done by LGWR using a very small unit of IO . Second, you do want first this to happen that there should be an image available to recover the file if there is any crash so before you do this, Oracle would be ensuring the recovery by writing the committed data to the log file. That's why at commit, there is no importance given to the datafile that it must be updated. As long as we hav ethe data in the redo log files and htey are intact, you can get the committed data whenever you want.

    HTH
    Aman....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points