This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Dec 28, 2007 7:44 PM by Aman.... RSS

Commit/Rollback and Redo Log files....

Aman.... Oracle ACE
Currently Being Moderated
Hi there,
Probably its a very simple question but still will ask. Some of the answers I know but I hope I shall get some out-of-the-box answers so asking this.
1) Why do we need redo log files? Why can't we work without redo log files? I mena to say that if redo log files were missing from teh standard architecture than what will be the consequences ?
2) What happens when we issue a commit and Rollback?
Hope to get some replies.
With thanks in advance,
Aman....
  • 1. Re: Commit/Rollback and Redo Log files....
    Maran Viswarayar Pro
    Currently Being Moderated
    Why do we need redo log files?
    Without this you will not be able to recover the database if there is a crash of datafiles
    Why can't we work without redo log files?
    You will lose the transaction if the DB crashes..Oracle designed the architecture to use REDOLOG file to protect against faliures...In NOARCHIVE the redogets overwritten so you will lose data...
    redo log files were missing from teh standard architecture than what will be the consequences
    You will lose your business

    ?What happens when we issue a commit and Rollback

    When you commit the transactions are immediately written to the redolog file to protect the data since the commit does not writes the data into the datafiles

    rollback---

    The previous image will be be restored from the UNDO tablespace since UNDO holds the old image of the Data..
  • 3. Re: Commit/Rollback and Redo Log files....
    Maran Viswarayar Pro
    Currently Being Moderated
    www.juliandyke.com/Presentations/TransactionInternals.ppt

    The presentation is on the following link

    Wonderful explanation
  • 4. Re: Commit/Rollback and Redo Log files....
    51034 Newbie
    Currently Being Moderated
    Redo is needed to re-perform transactions in the event of some disaster. Without redo logs, you would have something similar to Microsoft Access: a repository of data that is essentially disposable, because there'd be no guarantee of getting transactions recovered.

    So essential is redo considered to the proper operation of an Oracle database that if you lose an online redo log during normal database running, the database will either crash completely or will (eventually) hang.

    Oracle's guarantee is that if you commit a transaction, you will never lose it (though you are free to choose to lose it under special circumstances). Redo is essential to making that guarantee a reality. Hence, without redo logs, Oracle will simply not function.

    As for your second question. Four things (essentially) happen when you commit:

    1. An SCN is written into the redo log buffer for your transaction. That is the "marker" which says "Aman's transaction XXXX has just been committed". During any possible recovery, it's this marker that will enable your transaction to be re-performed and NOT rolled back again.
    2. LGWR flushes redo to the current redo log
    3. Once LGWR is notified the write is successful, you are notified that the commit is complete
    4. Locks are released

    When you rollback, you simply do the reverse of a transaction: redo is written of the before and after image of the data, the before image is written to undo, the actual data is modified (to the original value it had before you started the original transaction) and a commit is then issued 'under the hood' (meaning locks get released and redo gets written to disk).

    A rollback is, in fact, no different to a 'roll forward': if you change X to Y, involving Z number of things going on in the database under the covers to make it happen, a rollback is simply a change from Y to X with more or less the same Z number of things happening to make it occur.
  • 5. Re: Commit/Rollback and Redo Log files....
    Girish Sharma Guru
    Currently Being Moderated
    Yes Maran, this presentation is good, but i would like to quote:

    There is no comparision (and may not be in future too) between ACE Members explanation and other (even documentation) sources because;
    1.     Having real time experience.
    2.     To the point and crystal clear (0% doubt) understanding for a topic.
    3.     Problem solving with different point and corners.
    4.     Only ACE Members are the “Ultimate Guru” to the oracle topics.
    5.     Thanks to God; there are Oracle ACE Members; who shares their knowledge, experience with us…!

    Thank you howardjr Sir.
  • 6. Re: Commit/Rollback and Redo Log files....
    611828 Newbie
    Currently Being Moderated
    Nice .ppt !!! Thanks4url
  • 7. Re: Commit/Rollback and Redo Log files....
    Aman.... Oracle ACE
    Currently Being Moderated
    HI Maran,
    Thanks for the reply. I assumed these answers already.
    ----You will lose the transaction if the DB crashes..Oracle designed the architecture to use REDOLOG file to protect against faliures...In NOARCHIVE the redogets overwritten so you will lose data...----
    It will be like splitting hair but think about it, data is already in the data files no?It is coming right away from the buffer cache after some particular events?
    ----
    redo log files were missing from teh standard architecture than what will be the consequences
    You will lose your business
    ----
    Hmm let the business worry about itself, I am just trying to understand this redo concept origin :-).

    -----
    When you commit the transactions are immediately written to the redolog file to protect the data since the commit does not writes the data into the datafiles

    rollback---

    The previous image will be be restored from the UNDO tablespace since UNDO holds the old image of the Data..-----
    That I knew . I am looking for some sort of with this that happened sort of stuff.
    But thanks anyways for the reply. Its alwyas a pleasure to read your replies.
    Aman....
  • 8. Re: Commit/Rollback and Redo Log files....
    Aman.... Oracle ACE
    Currently Being Moderated
    Hi sir,
    As like always, the best reply.
    --
    Redo is needed to re-perform transactions in the event of some disaster. Without redo logs, you would have something similar to Microsoft Access: a repository of data that is essentially disposable, because there'd be no guarantee of getting transactions recovered.
    ---
    Thats what I thought too, I don't know about other databases that how they maintain this change data but they must not be doing it the way Oracle does.
    For the same answer, I am asked that data is already in the data files from the cache, than why does Oracle needs redo logs to be available?Why it cant work just with the datafiles?I amnot sure that what could be the reason for that, can you please explain this too?
    --
    So at commit, a marketr sort of thing comes up inthe redo stream and makes it known as 'permanent'.Couple of doubts about undo/rollback sir:
    1) When we change 10-20,where will be the undo first generated? I guess it should be in the data buffer cache and with the scn info, Oracle would create a CR block for the read consistancy. I am not able to link physical undo segment and transaction table with it. May be I am missing something.
    I guess it shoudl be that Oracle should grab an undo segment, make an entry in the transction table of undo tablespace and than maintain the undo copy in the memory.But I read some where that undo is generated in the memory first. May be I am wrong, please explain this.
    Thanks again for the best explanation as like always.
    Aman....

    Message was edited by:
    Aman....
  • 9. Re: Commit/Rollback and Redo Log files....
    51034 Newbie
    Currently Being Moderated
    I am asked that data is already in the data files from the cache, than why does Oracle needs redo logs to be available?

    Excellent question... the answer to which is contained back in that list of what happens at a commit. Number 1 in that list is "an SCN 'marker'; is written into the redo stream" -and it's that SCN which says 'This transaction has just committed'. Without that marker, the transaction is viewed as uncommitted.

    In other words, you are absolutely correct to say that the data is already in the data files. But DBWR flushes regardless of whether something is committed or not. Therefore the data files during normal operation of the datafiles will contain BOTH committed and uncommitted data. It's the redo stream that allows us to work out which is which. So you still end up needing the redo logs to make sense of what's in the datafiles.

    That's not true after a clean shutdown, by the way -the only time in a database's life when you can point to the datafiles and say, truthfully, 'I guarantee they only contain committed transactions'. At a clean shutdown, either everything is committed, or somethings get rolled back (which is as final and unambiguous as a commit), and the entire database is checkpointed. At that point, the redo is not needed to make sense of the database contents. But if you have an instance crash (or do a shutdown abort, which amounts to the same thing), you have to have that redo available to be able to reperform some transactions, rollback others, and leave others alone that were already in their rolled forward and committed states at the time of the crash.

    An undo segment is just a segment like the EMP table is. The only thing that makes it special is that, unlike EMP, its contents can be overwritten. My point is that when I say that a transaction 'generates undo', it generates it in exactly the same place you'd first write anything to EMP: in the buffer cache. Your server process actually gets assigned an undo segment algorithmically: it then reads the segment header block from that undo segment to determine which other block is available to store the undo that's about to be generated. That read of the undo segment header block takes place in the buffer cache, just as nearly all Oracle blocks get copied into the buffer cache and read from there. Your server process then marks the first free data block in the undo segment as its exclusive property (because transactions can't share undo segment blocks). It then finds a free buffer in the buffer cache (again, as you would if you were about to read from any normal table) and that new free buffer will be the place the undo gets written to -and, eventually, it will be flushed down over the top of the undo segment block that was previously reserved.

    In other words, because transactions can't share undo blocks, there is no need to read the contents of the undo block up into memory from disk before you start over-writing its contents. If you were inserting a new record into EMP, of course, that's exactly what you have to do: find a free buffer, read the EMP block from disk into that free buffer, modify the buffer and then have the buffer written back to disk.

    But because only you have access to an undo block, you can guarantee that, for all intents and purposes, the contents of the block on disk are irrelevant -because those original, physical contents must relate to transactions which have long since been committed and are therefore of no continuing interest or worth. (Because of undo_retention in later versions, you can also state with some confidence that the contents must be at least undo_retention seconds old).

    You can therefore take a bit of a shortcut: you certainly reserve the block address by making a change to the undo segment header block. But you can then simply grab a free buffer in memory and write to it knowing full well that at any time it can be written down to disk, obliterating the unneeded physical contents of the "real" undo block that happens to be there.

    This essentially means that, as far as an undo block is concerned, the difference between a block on disk and a buffer in memory is -effectively- non-existant. You can write to an empty buffer knowing that it can be safely written to disk at any time, because you have reserved the disk block address for your own exclusive use, thanks to the consultation you had with the undo segment header block. You wouldn't have been able to reserve the block on disk in the first place if it was in use by another uncommitted transaction or (ideally) not yet past the undo_retention time. So if you have been able to reserve a block, it can only be because its contents are effectively irrelevant. Therefore you don't need to worry about its physical contents and simply have to find a buffer in memory that can be saved over the top of those irrelevant contents.

    I hope that makes some sense for you, anyway!

    Regards
    HJR
  • 10. Re: Commit/Rollback and Redo Log files....
    Maran Viswarayar Pro
    Currently Being Moderated
    Thats an excellent explanation!!!

    Cheers
    Maran

    Message was edited by:
    Maran Viswarayar
  • 11. Re: Commit/Rollback and Redo Log files....
    611515 Newbie
    Currently Being Moderated
    Maran,

    Great very useful for junior DBAs like me.
  • 12. Re: Commit/Rollback and Redo Log files....
    Aman.... Oracle ACE
    Currently Being Moderated
    Hi sir,
    Excellent reply as like always.Makes me say again that its a great loss that I couldnt get a chance to sit in your DBA classes.Excellent!
    ---
    So what I am getting from it is that redo is good enough because in any case otherwise we may be ending up with the transactions about which we don't know anything , whether they are over,pending or discarded.That's why we need redo logs to be maintained because when a committ will be issued, due to the SCN , Oracle will be able to tell us that the follwing transaction has got committed at this scn number and here is a commit 'flag' to prove it.Is it correct sir?
    Kind of splitting hair question again.
    We have already data in the data files from the buffer cache, I agree that it inconsistent but it is there.This SCN mechanism which is maintained with the help of redologs, what was the drawback(possibly) that Oracle removed with not implementing the same mechanism right away with the use of the Data files only?I mean to say that the same SCN mechanism , why Oracle couldn't make it happenwith the use of the datafiles?
    I have one possible answer in my mind which I want to be corrected by you for this.If that would had happened than as we are writing back and forth into the datafiles without worrying about the status of the blocks than if at some time ,a commit had happened, Oracle again had to read the entire number of changed blocks 'again' and than has to update them saying now they got finally committed.This would had killed the database within few hours due to back and forth reading and writing data to/from disk.To remove this the delayed block cleanout mechanism is working.We update the transaction record with the help of redo logs and later on modify the buffers over the disk.
    This is what I have in my mind for my question's answer.Please correct me and also explain what else can be the possibility that Oracle didnt use the Data files for the transction management?
    ------------
    Very nice sir and yes It does make absolute sense to me though I had to read it 3 times :-).
    oK so if I got it correctly than is this what is happening?
    When we will read a block from the disk to the cache, Oracle will read a block from the disk , find a free buffer in the cache and maintain our record into it. Than later when we are going to update this block( make it dirty) than or server process will find the undo segment into the disk,maintain an entry in the V$transction table for the transaction information.Now after doing this, a free block in the undo segment in this disk is marked for the use for this transcation but Oracle doesnt write anything into it at the moment.It will maintain this entry and with this information , in the data buffer cache , will let the change happen.Now this would maintain 2 enteries in the buffer cache,one changed data block from the normal segment and one block containing the before image.This buffer can be later on written on the disk without having any problems as the current transasction exclusively owns it so it can write it back to the disk.Is this correct sir?
    Doubts
    1) So does this mean that when we change some thing than just the segment header information goes to the buffer cache not the underlying buffer which actualy contains the before images?
    2) When we talk about transction,there are 3 major enteries maintained in the V$Transction, XIDUSN, XID SLOT and XIDSQN.I understand the XIDUSN, what is the XIDSLOT and XIDSQN?They are meant to have some relation with the undo segment only thats what I know.Please explain this to me.
    3)oracle maintains a concept of write ahead logging?Does this concept means that before letting the change happen,Oracle will maintain the before image ?
    4)When we say that a UBA( Undo Byte Address) is maintained in the block header than this refers to the file,block which contains the undo image of our changed block correct sir?
    Thanks and best regards,
    Aman....

    Message was edited by:
    Aman....
  • 13. Re: Commit/Rollback and Redo Log files....
    51034 Newbie
    Currently Being Moderated
    I mean to say that the same SCN mechanism , why Oracle couldn't make it happenwith the use of the datafiles?

    Datafiles are internally chopped up into proper Oracle blocks -usually 2KB minimum, quite often 8KB or 16KB or even bigger. Redo logs are internally chopped up into "operating system blocks" -almost invariably 512 bytes. Guess which do you think would be quicker to write to disk?

    A commit isn't a commit until you are specifically told it's a commit. That involves a **foreground** wait. There are not that many foreground waits, but waiting on a commit is very definitely one of them. You don't want that wait to be any longer than it has to be. Sitting around for 16KB to be flushed to disk is a non-starter. Waiting for 512 bytes to be flushed is a much more bearable proposition.

    The answer to your specific question is, therefore, simple: speed.

    Part 2: If you update the EMP table, there will be (at least!) three buffers involved. One containing the changed EMP data. One containing the undo for the changes. And one being the undo segment header buffer which you will modify to 'reserve' an undo segment block. All three will be in the buffer cache. All three get modified in the buffer cache. All three can, at any time, be written back down to corresponding blocks on disk.

    With your second question, you are getting into internals, which I don't think is helpful. It is enough to know, I think, that an undo segment records which transaction has written -or reserved space to be eventually written to- undo data, and the "age" of that transaction as represented by the system-wide SCN in place at the time it started (you need to know that to be able to do read-consistency for other readers properly).

    However, since you ask: the undo segment header has a sort-of transaction table to keep tabs on all concurrent transactions. As one transaction finishes, slots in that transaction table can be re-used, and XIDSQN is simply a sequence number that increments every time a transaction slot is re-used. Obviously, XIDSLOT is the slot number in use by any given transaction at any one time.

    Write ahead logging in the Oracle context simply means: what happens when you say update emp set sal=999? The answer is

    1) Take locks
    2) Generate redo
    3) Generate undo
    4) Modify the data

    The point is that 2 & 3 happen before 4: you generate the transaction record before actually modifying the data. It's safer that way: you can guarantee that the information needed to rollback or re-perform the transaction is safe, even if the data itself is lost.
  • 14. Re: Commit/Rollback and Redo Log files....
    NitinJoshi Explorer
    Currently Being Moderated
    Dear Sir,
    The answers you give on this forum , is the Knowledge we can't get anywhere.
    Please do keep writing your impeccable answers here.

    Regards!

    BTW
    if you are an aussie, its time to be happy. as they are top on test match(India Vs. Aust.)
1 2 Previous Next