This discussion is archived
7 Replies Latest reply: Aug 5, 2012 4:38 PM by rp0428 RSS

Instance crash?

916413 Newbie
Currently Being Moderated
Imagine these circumstances:

1. I make an update to one/multiple rows of a table, but do not commit
2. My instance crashes.

How does Oracle manage this? Please correct me if i am wrong

Below is what i have understood
1. SMON does the instance Recovery (Rolling Forward only) for below cases
     a)Uncommited transaction will be recovered if only dirty buffer is written to the datafile even not committed [ alter system checkpoint ]
     b)Uncommited transaction will be recovered if only Redo is generated or log switches occur, or the logs are archived [alter system switch 
       logfile/alter system archive log current]
2. Uncommited transaction will be not be recovered if all above of these doesn't happen.Then SMON will do [ Rolling Forward + Rolling Backward ]
  • 1. Re: Instance crash?
    ab8926 Newbie
    Currently Being Moderated
    913410 wrote:
    Imagine these circumstances:

    1. I make an update to one/multiple rows of a table, but do not commit
    2. My instance crashes.
    When the instance crashes..

    SMON will perform instance recovery..
    COMMITTED transactions will be rolled forward with REDO
    and UNCOMMITTED transactions will rolled backward from UNDO
  • 2. Re: Instance crash?
    Aman.... Oracle ACE
    Currently Being Moderated
    913410 wrote:
    Imagine these circumstances:

    1. I make an update to one/multiple rows of a table, but do not commit
    2. My instance crashes.

    How does Oracle manage this? Please correct me if i am wrong

    Below is what i have understood
    1. SMON does the instance Recovery (Rolling Forward only) for below cases
         a)Uncommited transaction will be recovered if only dirty buffer is written to the datafile even not committed [ alter system checkpoint ]
         b)Uncommited transaction will be recovered if only Redo is generated or log switches occur, or the logs are archived [alter system switch 
           logfile/alter system archive log current]
    2. Uncommited transaction will be not be recovered if all above of these doesn't happen.Then SMON will do [ Rolling Forward + Rolling Backward ]
    This is quite well explained here,
    http://docs.oracle.com/cd/E11882_01/server.112/e25789/startup.htm#CNCPT1301

    Aman....
  • 3. Re: Instance crash?
    Mustafa KALAYCI Journeyer
    Currently Being Moderated
    when you startup the database after crash, smon will recover everything first! even uncommitted transactions. system will be in state just before it's crash and then whichever transactions were uncommitted, they will be rollbacked and vice versa for committed transactions.
  • 4. Re: Instance crash?
    Vijay.Cherukuri Newbie
    Currently Being Moderated
    HI,

    Please see : Re: Oracle Crash before uncommitted transaction

    The specified item was not found.

    Also see: Mark's reply in http://dbaforums.org/oracle/lofiversion/index.php?t4600.html
  • 5. Re: Instance crash?
    mk_dba Explorer
    Currently Being Moderated
    redologs files are written almost in real time.
    All commited transection information will be always in the redo file.

    So, when a instance crashes, smon will roll forward everyting using redo file.
    Now whatever is not committed will be rolled back using undo.
  • 6. Re: Instance crash?
    Tareq Alkhateeb Newbie
    Currently Being Moderated
    when you update a table, even no commit issued, this not mean that the updated data not written to disk.
    1. if the data was written to disk, when the instance starts the SMON rollback the transaction by applying the undo data from the undo tablespace.
    2. if the data was not written to disk, when the instance starts the SMON roll forward the change to disk (because a redo log entry created), and then rollback the changes.

    regards,
    Tareq Alkhateeb
  • 7. Re: Instance crash?
    rp0428 Guru
    Currently Being Moderated
    >
    1. I make an update to one/multiple rows of a table, but do not commit
    2. My instance crashes.

    How does Oracle manage this?

    Below is what i have understood
    >
    Please explain how it is you understand that UNCOMMITTED transactions can EVER be recovered? What is your reason for thinking this?

    This is a case where logical thinking and common sense will give you the answer.

    When an instance is running each uncommitted transaction belongs to a SESSION.

    When an instance crashes THERE ARE NO SESSIONS!

    When an instance is recovered THERE ARE NO SESSIONS!

    So how could Oracle possibly recover any uncommitted transaction? Ask yourself - what session would a recovered uncommitted transaction belong to?

Legend

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