1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Architecture

Discussion in 'Server Administration and Options' started by kamran0931, Dec 23, 2011.

  1. kamran0931

    kamran0931 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi
    i was studying Oracle architecture i learnt evrything but i am a bit confused.
    For example if i want to update a row then through what procedure will it go?
    First of all user will connect to the server using user process through server process, after establishing connection PGA will be allocated
    then server process will check the sql statement in shared pool of SGA, if it's there then ok but if not then it will parse it and will keep it
    in shared pool. Then server process will check the acquire data in the database buffer cache if its there then it will update it otherwise it will retrieve the data from the data files.
    So if a the old value of a row is 100 and i want to update it to 200. Then;
    1)Database buffer cache will have 200 value? correct?
    2)The before image copy will be written into the undo segment? correct? which is 100.
    3)what will be stored in redo log buffer? Regarding my example of updating from 100 to 200 please explain
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    So if a the old value of a row is 100 and i want to update it to 200. Then;
    1)Database buffer cache will have 200 value? correct?

    Maybe. If the update is not committed then no, only your copy of the block will have that value. The update must be committed to write to the buffer so the changes can be flushed to disk.

    2)The before image copy will be written into the undo segment? correct? which is 100.

    Sort of. UNDO is a series of change vectors so unless supplemental logging is enabled then only a vector to the changed column and its original value are written to the undo segment.

    3)what will be stored in redo log buffer? Regarding my example of updating from 100 to 200 please explain

    The redo logs will contain sufficient data to either replay the transaction if necessary (a committed transaction did not get flushed to disk prior to a database or transaction failure) or roll it back (should the transaction not be committed before the controlfiles reconcile with the datafile headers). Oracle provides an API called DBMS_LOGMNR to 'read' the redo/archived redo logs and provide both the redo statements and the undo statements [ an API used by oracle for Streams, Logical Data Guary and Golden Gate to replicate data across servers ] however this API has restrictions on which data types it can provide that information for restricting its usefulness in providing a byte-for-byte copy of a database or tablespace.
     
  3. kamran0931

    kamran0931 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    The redo logs will contain "sufficient data"
    what will be that sufficient data?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Read here:

    http://oratips-ddf.blogspot.com/2008/10/workin-in-mines.html

    and play around with the DBMS_LOGMNR package to see what the redo/archivelogs contain. I provided the name to this API in my prior post in the hope that you would investigate it rather than asking to be spoon fed information.