** An interesting question **
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: ** An interesting question **

  1. #1
    Join Date
    Mar 2002
    Posts
    200

    ** AN INTERESTING QUESTION **

    During recovery, the Archive Logs are applied on datafiles to ROLL-FORWARD all changes and ROLL-BACKWARD uncommitted transactions to bring the database to a consistent state.

    Since Oracle can identify the committed and uncommitted in the archive logs, why didn't Oracle design the operations in such a way that only the committed get applied to the datafiles? I mean if Oracle only applied the committed transactiosn, it can save the work of rolling back the uncommitted ones, isn't it? Correct me if I am wrong anywhere in my understanding.

    I know I have discussed this topic before, but this question still haunts me. Anyone interested to share their thoughts?

    QUESTER

    [Edited by quester on 09-18-2002 at 08:04 AM]

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Oracle can not find out which transactions are commited and which are not from the archive log he is currently applying. The transaction might start in the current log, but it might actually be commited in the next archive log (or in any archive log that is far ahead of the current one). So how would recovery process know which transaction to start recovering and which not?

    That is why after the roll-forward phase is completed Oracle has to rollback any uncommited transaction. And it finds uncommited transactions from rollback segments, not from archived or online redo logs.
    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
    Apr 2001
    Location
    Czechia
    Posts
    712
    There are many reasons to do that in this way.
    I think the most obvious is the FAST COMMIT. Oracle expects most of the transaction will be commited. Commit only takes the time the logwriter writes a piece of log buffer to redo log file. All other things have been done, data have been changed in the buffer.

    Thus, in most cases we enjoy the fast commit, sometimes we have to wait for long running rollback and in the rare case of recovery we have to wait for roll backward of uncommited transactions. Anyway, if I remember that well, database is opened just after all commited transactions are rolled forward and roll backward doesn't prevent using the database.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  4. #4
    Join Date
    Mar 2002
    Posts
    200
    Thanks for the response guys. That was really useful. BTW, I was thinking on these lines...since SCNs are generated for every committed transaction, Oracle can recognize those committed transactions; but then, SCNs are only written to data file headers and control file I guess. So incase committed data is just in the archive logs, perhaps there is no way to know whether it was committed or not as jmodic says.

    But if there is a flag in the archive logs that suggests that the data was committed (which I guess must be there), Oracle can apply only when it identifies the flag. But if the flag is in one archive log and the data in another, then it makes complete sense the way Oracle functions.

    Any thoughts / inferences?


  5. #5
    Join Date
    Jan 2002
    Location
    India
    Posts
    105
    dear sir
    I found everybody's response very good.

    But In last reply u r talking about storing Flag in archive log file that is not possible as said transactions occupies multiple archive log.

    Probabally we are asking Oracle to change there style of Recovery in which at a time one archive log file is applied.
    What we are expecting is Oracle shuld scan all the archive log file simultatnously and mark uncommited transactions.
    Which in turn mean following things

    1.No incomplete Recovery is possible
    2.And Recovery time is increased MTTR is getting higher and higher

    Viraj
    ----------------------------------
    9i OCA
    A Wise Man Knows How much he doesn't know !!!

  6. #6
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Oracle has 2 modes of write into log.
    -- sync mode (in most cases this is end of transaction)
    -- background writes ( each 3 sec or 1/3 of fill of a log buffer)
    when oracle write in sync mode (with commit) it generate and write commit marker.
    transaction is unrecovereble and has to be rollback during recover process
    until log block with commit marker has been write to log file.

  7. #7
    Join Date
    Mar 2002
    Posts
    200
    Originally posted by virajvk
    dear sir
    I found everybody's response very good.

    But In last reply u r talking about storing Flag in archive log file that is not possible as said transactions occupies multiple archive log.

    Probabally we are asking Oracle to change there style of Recovery in which at a time one archive log file is applied.
    What we are expecting is Oracle shuld scan all the archive log file simultatnously and mark uncommited transactions.
    Which in turn mean following things

    1.No incomplete Recovery is possible
    2.And Recovery time is increased MTTR is getting higher and higher

    Viraj
    ----------------------------------
    9i OCA
    That was a wonderful way of looking at it. Thanks a ton, really learning a lot of things here.

    P.S. Some maybe feeling I am asking silly questions and wasting time. May be it is. Since I am a baby growing in Oracle, I will have lot of inquisitive ness until I get a little matured. Kindly bear with me in case of any questions that may seem immatured.

    Many thanks and kind regards.

    QUESTER

  8. #8
    Join Date
    Jan 2002
    Location
    India
    Posts
    105
    dear sir

    This was not silly question indeed many times I had gone through such questions. That is why this is real good site for upcoming DBAs like me.

    I am having such similar Interesting question which I will be starting with new thread.

    Can some explain more about SYNC mode of Oracle

    viraj
    ---------------
    9i OCP


    A Wise Man Knows How much he doesn't know !!!

  9. #9
    Join Date
    Mar 2002
    Posts
    200
    I just incidently read that SCNs do get recorded in the archived log files; otherwise how is "Change-Based" recovery possible, which can recover to a SCN before the failure occured. So now we are sure that Oracle has SCNs (what I call commit flags) within the Archive Logs to identify which transactions were committed and which were not.
    So now, does it mean that Oracle can definitely identify just the committed transactions from Archive Logs, but applying just that would effect in an increased adverse overhaead as Vijay explained?

    Am I getting any clarity here to my original question?

    QUESTER

  10. #10
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    i think threads like this are a great deal of help to people just trying to figure out the internals of Oracle.

    I've learnt something today children

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