Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Location
    Brazil
    Posts
    30

    Question Unanswered: Difference between Undo Segment and Redo Log Buffer

    Hello,
    I'm reading some material here and I'm not sure whether I got it right.

    Is it correct to say that the UNDO SEGMENT holds the old AND NEW values for each DML transcation issued by a user process ? I read it holds the NEW values as well, and thought at first the reason would be to match the DML statements with the undo segment entries, but I learned later that this is actually achieved via SCNs. So I'm confused why - if at all - it does hold the NEW values as well.

    One important difference I guess is that the UNDO SEGMENT is a phyisical area of the database, existent within the database files, whereas the REDO LOG BUFFER does only exist on the SGA, in memory. Correct ?

    What does the Redo Log Buffer exactly writes ? It simply keeps adding all the DML statements being issued by user processes till once in a while the LGWR flushes the buffer to the log files on disk ? Or it writes unissued DML statements like the following to "reverse" modifications ?

    Example of what I'm thinking of: I issue the following update (change the user name from Daniel to John, on user id 570):
    UPDATE temp SET name = 'John' where id = 570;

    Does the Redo Log Buffer writes an entry to reverse this change, like "UPDATE temp SET name = 'Daniel' where id 570;

    ???
    This would generate a lot of work, I think this assumption is wrong.


    Thanks for helping clarifying things out,
    Abraham

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    I read it holds the NEW values as well
    Can you tell us where you read this? I don't think I've ever seen that in the documenation.
    One important difference I guess is that the UNDO SEGMENT is a phyisical area of the database, existent within the database files, whereas the REDO LOG BUFFER does only exist on the SGA, in memory. Correct ?
    Not really, no. The important difference is that UNDO segments are used to store the before images (the 'OLD values) of a row (or a block depending on circumstances) whereas the redo log buffer stores redo entries i.e. ... let's give an example:
    Note, this is a simplified version of what goes on, but it should pretty much get the point across.
    I issue the statement UPDATE emps SET sal = sal*2 WHERE dpt = 20.
    There are 2 people in dept 'Bob' and 'Mary'. Bob currently earns 2000 mary earns 2500.
    There will efectively be 2 redo entries for this
    1.UPDATE emps SET sal = 4000 WHERE rowid = 'the rowid pointing to bobs row'
    UPDATE emps SET sal = 2000 WHERE rowid = 'the rowid pointing to bobs row'
    and
    2. UPDATE emps SET sal = 5000 WHERE rowid = 'the rowid pointing to marys row'
    UPDATE emps SET sal = 2500 WHERE rowid = 'the rowid pointing to marys row'
    There will also be transaction and scn information logged with each entry.
    These entries are used for RECOVERY when necessary.
    Now, what will also happen, is that in the buffer cache, the rows in question (i.e. Bob and Mary's rows) will be locked (so that no other sessions can alter those rows) and an undo block is made available, the before images of these two rows are put into this block (note, bigger transactions will use more blocks) along with transaction information. and the values are changed in the table clock(s). These undo blocks are used for read consistency and rollback.
    When the transaction is commited, the contents of the redo_log_buffer is written to the online redo log files and the locks are released from the two table rows (mary and bob) making those values available to other sessions. The undo block may or may not be made immediately available to be overwritten. The values in the buffer for Bob and Mary's salary have not yet been written to disk (they are 'Dirty Blocks') their original salaries are currently on disk, however their new salaries are 'safe'. Let's say, these dirty blocks have not yet been written to disk and someone kicks the plug out of the back of the server. Everything shuts down immediately. The salaries on disk are 2500 and 2000. But Oracle told us that it had committed their salry increase to 5000 and 4000. When we put the plug back in and Oracle starts up, it recognises that the database crashed, goes to the online redo log files and 'replays' ALL of the redo entries that were previously lost by the server crash, the salries again have a value of 4000 and 5000 as appropriate.

  3. #3
    Join Date
    Oct 2003
    Location
    Brazil
    Posts
    30
    Thanks for the elaborated reply.
    1st of all, sorry, I guess I read too many pages at once. There is no NEW in the sentence regarding what is kept in the Undo segment. I'm studying with the book
    OCP Oracle9i Database: Fundamentals I Exam Guide
    by Jason S. Couchman and Sudheer N. Marisetti.

    Now... I see what problem really was: everytime I read "redo", I kept linking it with the idea of a "rework" to get the database back to the state it was previously to the change. I did not read "redo" like "work to be done over again", just as in the example you depicted.

    So all the time I wondered how did the redo entries look like, but I think your example not only sorted this out but brought me to the proper comprehension. If these books did only provide some examples here and there... not too difficult, but a huge difference.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    The redo logs contain all changes. So, as in your example

    Code:
    UPDATE temp SET name = 'John' where id = 570;
    This is saved in the redo log. At this point, since you haven't committed, the reversal of this stmt is in UNDO. However, if you do decide to rollback, UNDO is utilized to change 'John' back to 'Daniel', which is also recorded in the REDO logs. Then let's assume you commit.

    So, in the case of recovery when you start from your most recent backup and roll forward, you'd see something like

    Code:
    ...restoring last full backup
    UPDATE temp SET name = 'John' where id = 570;
    UPDATE temp SET name = 'Daniel' where id = 570;
    You see this even though you rolled back, because of the manner in which Oracle chooses to make changes to rows, even if you later might rollback those changes.

    UNDO is only meant for rollbacks (and flashback query). REDO is used for recovery.

    --=cf

  5. #5
    Join Date
    Dec 2007
    Posts
    253
    UNDO is only meant for rollbacks (and flashback query).
    You missed read consistency

  6. #6
    Join Date
    Oct 2003
    Location
    Brazil
    Posts
    30
    So considering we are going to rollback, and we have in our undo statement the following, (the topmost entry was placed last)

    1-----UPDATE temp SET name = 'John' where id = 570;
    2-----UPDATE temp SET name = 'Daniel' where id = 570;

    ,after issuing UPDATE temp SET name = 'John' where id = 570; (changing the original entry had 'Daniel' to 'John'),

    Oracle identifies we need to go back to line 2 by means of SCN - it can't figure it out simply by looking at the undo segment above, he needs to check for a timestamp as well (my guess). In other words, intuitively, I would guess that if Oracle was told to rollback, it would check the undo segment and go back to the 1st statement that matches the change being done - in this example, the line UPDATE temp SET name = 'John' where id = 570;
    but that would not rollback correctly, because rollback would only be achieved by getting back to the state originated by the 2nd line, UPDATE temp SET name = 'Daniel' where id = 570; , which is the value from which the user tried to change, to John.

    (the question was: Oracle then, in a rollback, needs to resort to the SCNs to identify which entry to rollback into. Correct ?)

  7. #7
    Join Date
    Dec 2007
    Posts
    253
    The update statement will NOT be held in the UNDO Block, the 'old' VALUES of that row will be stored in the UNDO block. The DML statements (and DDL et al but let's not confuse matters ) i.e. UPDATE... SET are stored as REDO Entries in the Redo Log (buffer and file)
    Oracle would actually track the transaction id for rollback rather than the SCN as such (although SCNs are involved) Remember, every transaction has its own ID and multiple changes could all have the same transaction id (so long as the belong to the same transaction. A rollback will use the values in the undo blocks to 'reset' the values in the data blocks (now here I'm a bit fuzzy, but I think that Oracle will simply overwrite the actual data blocks, but it may require another undo block to do this. These undo values will be 'linked' via their transaction id.

  8. #8
    Join Date
    Oct 2003
    Location
    Brazil
    Posts
    30
    Well well well...
    just when I thought I was getting close...
    Ok, I was confusing again the Undo segment with Redo.
    Undo holds values... Redo holds the statements. I think it can't get easier than this to remember
    Thanks also for making me realize I was not paying attention to the fact that rollbacks rollback transactions, the best case scenario is to have a transaction at the statement level but that is not mandatory (I recalled that when reading "multiple changes could all have the same transaction ID").

    Anyways, I am just trying to get over the surprise I had when requesting my certificate after passing exam 1Z0-007, what, at least the moment I took it, would make me an Oracle Associate (OCA). But to my surprise now there is this DB Fundamentals I exam being required along the one I passed, so I wanted to give it a try to see if it's "not that complicated" and finally put my hands on the diploma, but... looks like the path got harder.


    Enough for today's class
    Thank you guys.

  9. #9
    Join Date
    Dec 2007
    Posts
    253
    You may already have read this, but if not, I strongly reccomend The concepts manual Best of luck wit the rest of your studies (It's nice to see someone actually putting the required effort in - a rare thing in many cases nowadays)

  10. #10
    Join Date
    Jan 2013
    Posts
    1
    Quote Originally Posted by pablolee View Post
    Can you tell us where you read this? I don't think I've ever seen that in the documenation.
    Not really, no. The important difference is that UNDO segments are used to store the before images (the 'OLD values) of a row (or a block depending on circumstances) whereas the redo log buffer stores redo entries i.e. ... let's give an example:
    Note, this is a simplified version of what goes on, but it should pretty much get the point across.
    I issue the statement UPDATE emps SET sal = sal*2 WHERE dpt = 20.
    There are 2 people in dept 'Bob' and 'Mary'. Bob currently earns 2000 mary earns 2500.
    There will efectively be 2 redo entries for this
    1.UPDATE emps SET sal = 4000 WHERE rowid = 'the rowid pointing to bobs row'
    UPDATE emps SET sal = 2000 WHERE rowid = 'the rowid pointing to bobs row'
    and
    2. UPDATE emps SET sal = 5000 WHERE rowid = 'the rowid pointing to marys row'
    UPDATE emps SET sal = 2500 WHERE rowid = 'the rowid pointing to marys row'
    There will also be transaction and scn information logged with each entry.
    These entries are used for RECOVERY when necessary.
    Now, what will also happen, is that in the buffer cache, the rows in question (i.e. Bob and Mary's rows) will be locked (so that no other sessions can alter those rows) and an undo block is made available, the before images of these two rows are put into this block (note, bigger transactions will use more blocks) along with transaction information. and the values are changed in the table clock(s). These undo blocks are used for read consistency and rollback.
    When the transaction is commited, the contents of the redo_log_buffer is written to the online redo log files and the locks are released from the two table rows (mary and bob) making those values available to other sessions. The undo block may or may not be made immediately available to be overwritten. The values in the buffer for Bob and Mary's salary have not yet been written to disk (they are 'Dirty Blocks') their original salaries are currently on disk, however their new salaries are 'safe'. Let's say, these dirty blocks have not yet been written to disk and someone kicks the plug out of the back of the server. Everything shuts down immediately. The salaries on disk are 2500 and 2000. But Oracle told us that it had committed their salry increase to 5000 and 4000. When we put the plug back in and Oracle starts up, it recognises that the database crashed, goes to the online redo log files and 'replays' ALL of the redo entries that were previously lost by the server crash, the salries again have a value of 4000 and 5000 as appropriate.
    you explained greatly sir.. thank you !!

Posting Permissions

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