How Oracle works?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How Oracle works?

  1. #1
    Join Date
    May 2002
    Posts
    163
    Hi everybody ,

    As a student and new starter I have basic doubts.

    (A)

    1. There are two users. USER1 and USER2 .
    2. USER1 has updated one table but did not commit the same.
    3. USER2 fires select statement on that table. He will not able to see the updated value. He will get the old value.

    QUES: Where from the old value he is getting? From Rollback Segment or from some other place?

    (B)

    1. 1. There are two users. USER1 and USER2 .
    2. USER1 has updated one table but commited the same.
    3. USER2 will get new updated value.

    QUES: Where from he is getting that new value? From Data Buffer or REDOLOG or from some other place?

    (C)

    1. There are two users. USER1 and USER2 .
    2. USER1 has updated one table but did not commit the same.
    3. An uncommited entry will also be there in redo log file.
    3. Database shuteddown with Abort.
    4. Oracle will not rollback any uncommitted transactions.

    QUES: What will happen then? How Oracle handles this scenario?


    (D)

    1. There are two users. USER1 and USER2 .
    2. USER1 has updated one table but did not commit the same.
    3. An uncommited entry will also be there in redo log file.
    3. Power goes off suddenly.
    4. Oracle will be not able to rollback any uncommitted transactions.

    QUES: What will happen then? How Oracle handles this scenario?


    (F) ************* IMP

    1. Is there any chance when UNCOMMITTED transactions will also be written into DATAFILE?


    Regards









    Nwcomer
    Student

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    A. User2 will get the before change image (old value)from rollback segments.

    B. User2 will get the updated value from db buffer or datafile.

    C. Oracle will rollback those uncommited transactions on next startup.

    D. Oracle will rollback those uncommited transactions on next startup.

    E. No, uncommited data will not be written to datafiles.

    Sanjay

  3. #3
    Join Date
    Sep 2001
    Posts
    62
    Hi,

    QA: User2 gets the old value from the rollback segment.

    QB: User2 gets the value from the buffer cache if the block is still there or it is read from the datafiles.

    QC: Oracle will always rollback uncommited transactions. Therefore uncommited data will always be lost when the database is shutdown using abort or if the instance fails.

    QD: Same as QC.

    QF: When the database is running normally both uncommited and commited data will exist in the datafiles. Oracle will write uncommited and commited data to datafiles when more space is required in the buffer cache. Only when the database is shutdown cleanlly (shutdwon immedaite, transactional etc) will datafiles contain commited data only.


    Hope this helps.







  4. #4
    Join Date
    May 2002
    Posts
    163

    Hi Sanjay,

    Thanks to see u again.

    Quote
    _______________________________________________________
    C. Oracle will rollback those uncommited transactions on next startup.

    D. Oracle will rollback those uncommited transactions on next startup.
    ________________________________________________________

    It is fine that Oracle will rollback all uncommited transactions on NEXT STARTUP.

    But once the database shutdown , next time WHERE FROM and HOW Oracele comes to know what are the uncommited transactions are there and they have to rollback?

    And with your answer another question has come into my mind that ,

    So whenever any abnormal shutdown happens manual database recovery will be not required. Oracle automatically internaly will synchronise the data . IS THAT CORRECT?

    Please explain me this elaborately. I was finding this answer from a long.

    I will appreciate if u please forward me your personal mailid.
    Mine is tintin_k2@yahoo.com

    Very kind regards



    Nwcomer
    Student

  5. #5
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    Since Oracle records each and every modification done to the database in th 'redo' logfiles, Oracle
    will perform 'Instance' recovery at the next start.

    Yes. Whenever an abnormal shutdown happens Oracle automatically (tries to) recover the database.

    Vijay.
    Say No To Plastics

  6. #6
    Join Date
    May 2002
    Posts
    163
    Hi Vijay,

    I know that while startup the database CNTROFILE checks and Synchronise everything. Then if database shuteddown abnormally then how it happens. What is the significance of maintaing SCN in everywhere.

    Does SCN helps to synchronise all infos.

    Please clarify the same.

    Thanks and Regards
    Nwcomer
    Student

  7. #7
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    The SCN is stored in all the file(s) header.
    Whenever the Instance is started Oracle will see whether the database is in SYNC, using this header SCN.
    If it does not match, it will try to recover the database using the 'Online' 'redo' logfiles.
    If it cannot succeed then Oracle will ask for 'Archived' logfiles for recovery.

    Vijay.
    Say No To Plastics

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