Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Location
    HK
    Posts
    2

    Unanswered: DML effects on redo log buffer, undo segment, db buffer cache, db writer, log writer

    Hi All,

    When I'm studying the OCA, I'm still confused with the concept of the redo log buffer, undo segment, database buffer cache, database writer and log writer. As I know,
    i) undo data is for other users to read my updated but not yet committed data
    ii) redo log contains all transactions including undo data (recovery will apply redo and then undo)
    iii) selected data is on database buffer cache and if the data is updated, it will be sent back to the datafile by db wirter

    The questions are:
    1. Do the database buffer cache and undo segment contain the same uncommitted data?
    2. Are the database writer and log writer doing their duties at the same time? If so, recovery from redo log is not needed!?
    3. Are the Insert/Update/Delete statements not related to the database buffer cache, but they can directly write to the datafile?

    Thanks in advance!
    LinuxUser

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2007
    Posts
    71
    anacedent and gurus: pls excuse me on this, I want to try if I got it right. If you feel anything wrong, please feel free to correct them.

    linuxisgood: hi there, i'll try my best to answer them
    1. The database buffer cache is being used all the time, classified as something that is stored in memory instead of data files so that I/O will be minimized. It is an area in memory in which data are being manipulated and later on flushed into data files by the database writer process (DBWn). This happens when buffer becomes full or checkpoint occurs. For the undo segment, this is the part where Oracle stores the "before" image of the data before applying the changes so that it can be used for read consistency. One good example, is that when USERA updates record A to have a passing score (from a failing grade) and doesn't perform a commit, on the other hand, USERB tries to do a query to see the status of record A. Oracle save the "before" image (which is the failing grade status) and shows this data to USERB until USERA issues a commit statement.
    2. In my understanding, Log writer works first before database writer does because all changes are being held in memory first (redo buffers) and when data are commited or online redo logs is 1MB in size, Log writer (LGWn) writes them to (redo logs) that also triggers the database writer to flush out dirty pages from buffer cache into data files. Actually, oracle uses the redo logs to perform recovery of committed transactions and UNDO data to recovery uncommitted transactions.
    3. DML statements (INSERT, DELETE & UPDATE) are the core functions of SQL. They manipulate data in a part of memory which is database buffer cache and later on posted to the datafiles by the DBWn when buffer is full or checkpoint occurs.

    hope I was able to help.

  4. #4
    Join Date
    Mar 2007
    Location
    HK
    Posts
    2
    You gave me a clearer mind.

    When data in database buffer cache is modified, the before image is stored in the undo segment. And the executed DML is also stored in redo log buffer. Am I right?

    - Why doesn't Oracle use the redo log buffer to update the datafiles directly, but the redo log files?
    - Is it because there is a Oracle process to copy the whole dirty block from database buffer cache to datafiles directly?
    - Is there any case in that the data in the redo log buffer will lose accidentally so that the recovery is not complete?

    I've read several book and documents but I'm still confused with the concepts!

    LinuxUser

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Why doesn't Oracle use the redo log buffer to update the datafiles directly, but the redo log files?
    A session can do DML.
    These changes will be reflected in UNDO & possibly even datafiles (prior to COMMIT).
    The changes become permanent when the COMMIT is issued & the changes are written to the REDO log file.
    It is the writing of changed data to the REDO that completes the transaction and make the data permanent (recoverable in the event of any failure).
    If a crash or other failure occurs PRIOR to writing to the REDO, those changes are lost (ROLLED BACK) upon database restart.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •