This discussion is archived
1 2 3 4 5 6 Previous Next 83 Replies Latest reply: Jan 7, 2012 1:03 PM by 909589 Go to original post RSS
  • 30. Re: Why not use Redo log for consistent read
    751685 Newbie
    Currently Being Moderated
    Hi Guys,

    I am Lakshmikanth Sripada. Just entered into this world of discussion community. And I am really impressed with the way the discussions are unfolded leading to granular level ,intricacies and in-detail knowledge of the points.

    Anyways... I appreciate the way Aman has explained and I really liked it and it could also be put in the following way to easy understanding.

    First things first -- > undo is also like any other data file.

    when a change comes in the database, the concerned block is retrieved from the disk (unless its not already exists) and then its modified to make the change. Now bcos of the change , the old value shud be maintained -- in undo tbspc.

    Now when its time to write , the following things are to be written..

    1) modified block to disk
    2) undo block to disk.

    since we know the redo is the one which need to be fed first the above writes, so it flushes there first
    and then to disk later.

    Meanwhile if instance fails suddenly due to a power failure or some damage then the following question comes up.

    Did the user say commit?

    If yes and he got the reply " commited successfully" then it is sure that redo has both -undo block and modifed block,

    and will be helpful incase the writes are not done yet ( called recovery)

    If not then Redo backs out the values to old using undo in case when modification is reached already to the disk (by DBWR), or else doesn't worry at all.

    Thats it.

    In between the transaction if somebody wants to read the data (which is changed already) then oracle gets it from undo tbspc. -- This is called consistent read.

    Consistency means -- the time the user asked for the data, oracle shud give the data how it was at that time even though it is changed in the due course of reading it.

    I hope I have thrown some more light on the above discussion.

    Thanks,
    Lakshmikanth S.
  • 31. Re: Why not use Redo log for consistent read
    Aman.... Oracle ACE
    Currently Being Moderated
    I am sorry if I shall sound like nick-picking but terms used should be correct.
    user4508393 wrote:
    First things first -- > undo is also like any other data file.
    IMHO, Undo datafile is a special datafile and so are undo segments. With the only similarity that they are also updated like normal segments, there are some serious considerations that one has to do when it comes to Undo datafile and its recovery. Yes, with the convetion, its also just a datafile but since its managed by Oracle itself, that makes it distinct.
    when a change comes in the database, the concerned block is retrieved from the disk (unless its not already exists) and then its modified to make the change. Now bcos of the change , the old value shud be maintained -- in undo tbspc.

    Now when its time to write , the following things are to be written..

    1) modified block to disk
    2) undo block to disk.

    since we know the redo is the one which need to be fed first the above writes, so it flushes there first
    and then to disk later.

    Meanwhile if instance fails suddenly due to a power failure or some damage then the following question comes up.

    Did the user say commit?

    If yes and he got the reply " commited successfully" then it is sure that redo has both -undo block and modifed block,
    Well, it doesn't matter that the user issues a commit or not. Oracle uses a mechanism called "write ahead logging" which will ensure that before the changes are marked "valid" in the buffer cache, the corresponding change vectors are already updated in the redo log buffer. This has no bearing over the user issued commit. With the commit, the buffer's ITL's may get cleared right away or after some time, if the buffer is sent to the disk before the commit could be cleared from the transaction header of the block( delayed block cleanout).
    and will be helpful incase the writes are not done yet ( called recovery)
    If not then Redo backs out the values to old using undo in case when modification is reached already to the disk (by DBWR), or else doesn't worry at all.
    I amnot sure that what you wanted to say from this statement. Sorry but I didn't get it.
    In between the transaction if somebody wants to read the data (which is changed already) then oracle gets it from undo tbspc. -- This is called consistent read.

    Consistency means -- the time the user asked for the data, oracle shud give the data how it was at that time even though it is changed in the due course of reading it.
    HTH
    Aman....
  • 32. Re: Why not use Redo log for consistent read
    751685 Newbie
    Currently Being Moderated
    Aman,

    I agree with you and infact you mean what I mean.
    Anyways, I have few doubts in the architecture of Oracle which are haunting me for so many days. I hope you will help me in making me understand clearly.

    It's actually little bit of internals.

    1) what necessary information redo stores during an insert , update, delete transactions which helps to recover in case any damages occur? --- I will owe you a lot if you can make me clear for each insert, update,delete with steps.

    Also, how the redo takes help of undo to do any recovery?

    2) Why there is a need of latch to be performed in shared pool?
  • 33. Re: Why not use Redo log for consistent read
    Aman.... Oracle ACE
    Currently Being Moderated
    Sripada,
    what necessary information redo stores during an insert , update, delete transactions which helps to recover in case any damages occur?
    Any point of time when you do a DML of any sort, the changes that are maintained in the Redo Log is in the terms of the Change Vector which is the internal representation of the change with the operational codes and other stuff, to tell Oracle that the change has successfully happened. The Change Vectors of one transaction are maintained within a single redo stream linked with that transaction only. When the recovery is required, these change vectors are applied to fill the "deltas" from the backup timeto the current time. It seems to many that at time of recovery, Oracle would be firing the DMLs again when actually its not the case.All what Oracle does is to fill those gaps which are there in between from the past image in the backup to the current record stored in the archivelog/redologs.
    how the redo takes help of undo to do any recovery?
    This would be to rollback and changes. I am not sure that its right to say that the redo takes the help of the Undo. Its the recovery process itself which does so since the idea of recovery is to maintain only the consistent image left finally inthe database.So if there was no commit issued for the tranaction and either a rollback or not even that was issued, the safest bet would be to revert back the changes using the Undo and that's all what happens when Undo data is being used.
    Why there is a need of latch to be performed in shared pool?
    How otherwise you would search in such a large memory chunk that where you statement is being kept by Oracle? Latch is nothing but a resource locator as as well as a protector of the same! So using it only you can get through the resource and also can be assured that no one would temper the resource whenyou are using it.

    HTH
    Aman....


    I
  • 34. Re: Why not use Redo log for consistent read
    751685 Newbie
    Currently Being Moderated
    Aman,

    I am very happy with your prompt reply but I guess you have explained it in a rather general way, which I am familiar with.

    I am expecting such as this :

    Let's say when insert occurs :

    In redo :

    Stores all the insert statements along with the data like
    insert into emp ('Sripada','TCS',IT',1);

    So that when instance recovery has to happen , it just rolls forward them in case the transaction is commited.

    In undo :

    It just has to store the rowids.
    When the user rolls back, using the rowids from undo it deletes the related rows.

    Similarly I want to know how it is done in the case of update,delete statements.

    First of all, please tell me whether redo stores the data or just the statements.

    Like when you say, update emp set sal=sal+100;

    From the above, does redo store all the rows effected or it just stores the above stmt?

    In all the DML if redo stores data, how it manages commit & rollbacks issued by the user.
    also tell me for other case.

    I think it only stores stmts,right?

    I am sorry if my last questions made you to think in different frame of mind when I said recovery.

    Thanks,
    Sripada
  • 35. Re: Why not use Redo log for consistent read
    Aman.... Oracle ACE
    Currently Being Moderated
    Sripada,

    In any kind of DML, only the changed entry gets stored in the Redo, not the entire table/block. That entry is called Change Vector which is NOT a statement that would be run by Oracle in the case of recovery. Its an operational information of your work that is recorded there. This is what I said in my last reply as well that most people believe that Oralce would re-fire the statements of Update, Delete, Insert while actually it won't. It would apply those change vectors only.

    Like you mentioned about the Insert, the corresponding values in Update, Delete get stored in redo and undo. Other than that, things would become too deeper and since I am in a session at the moment, I don't have time to show the complete dumps of Redo.
    does redo store all the rows effected or it just stores the above stmt?
    I gave the answer for this already I guess just now.
    n all the DML if redo stores data, how it manages commit & rollbacks issued by the user.
    What does it mean ? Commit, Rollback are seperate operations and I am not sure that I did get the context of your statement. Can you elaborate please?

    HTH
    Aman....
  • 36. Re: Why not use Redo log for consistent read
    751685 Newbie
    Currently Being Moderated
    Aman,

    Thanks for the info.

    Please digest the below explanation and give me the reply :


    When a user shoots an update statement that changes 1 million rows lets say.

    After some time say 50% of modified data is written to disk from SGA before commit/rollback is issued.

    Now lets examine 2 cases :

    when user says rollback :
    ----------------------------------
    how the data is rolled back using undo ( since half of the data is written already to disk) ?

    User says commit and got successful and after that immediately instance crashes :
    ------------------------------------------------------------------------------------------------------------------
    how the remaining 50 % of the modified data (which is in sga, but now not there since it crashed)
    will be got back by redo? ( I know the fact the redo must have the so called 'change vectors' for that data).


    Basically, I am asking a deep explanation of what "Rollforward does"? and what "Rollback does"

    We have read in so many books that when we shut abort , it does the above 2 one after the other when next startup

    Please give me clear idea on this. Here I know the change vector is the highlight point to be discussed on
    but I just want to know what is the change vector for an update, delete, insert.


    Thanks,
    Sripada
  • 37. Re: Why not use Redo log for consistent read
    751685 Newbie
    Currently Being Moderated
    Aman,

    Coud you please reply to me on my last post.

    Thanks,
    Sripada.
  • 38. redo atomicity, write ahead logging and log force at commit
    Franck Pachot Journeyer
    Currently Being Moderated
    Hi,

    I'll try to explain the 3 basic mecanisms that orale uses to guarantee recoverablility: redo atomicity, write ahead logging and log force at commit.
    And you must keep in mind that undo (rollback segment) blocks are database blocks as well as your data blocks (table segments, index segments,...)

    So you have 50% uncommited data that is on disk and 50% uncommited data that in in dirty buffers.

    In addition to that:
    - all redo has been written to redo thread (into redo log buffer in memory, maybe not yet on disk in redo log files) because redo is written before writting to any block.
    - all update undo has been written to rollback segments blocks (in buffer cache, and some of them may have been flushed to disk)
    - all redo for those undo blocks has been written to redo thread as for any block changes

    And oracle also guarantees that:

    - the redo vectors for data block and for undo block are in the same redo record. Oracle always keep atomicity at redo record level for related redo and undo

    - all redo related to the 50% changes that have been flushed to disk is on disk (in redo log files)
    Oracle will always ensure that redo has been flushed from redo log buffer to redo log files before flushing dirty buffers to disk. This is the write ahead logging.

    So at that point, if transaction is rolled back, all information is available from undo blocks: either from disk or still in buffer cache.
    When oracle needs to roll back, it reads undo blocks in the same way it reads data blocks: it looks in buffer cache, and if not found, it reads it from datafile to buffer cache.

    That answers your first question.

    If the instance fails at that point, then:
    - the 50% that were flushed to disk must be rolled back. So first oracle will read all redo from redo log files. That will bring back the undo that is needed to roll back those 50% changes. This is guaranteed by the write ahead logging and by record atomicity: we know that all redo covering changes that are on disk (in datafiles) are available from disk (in redo log files) and we knwo that those redo will also include the redo to recover the related undo blocks (rollback segments).
    - the 50% that were not flushed to disk is lost. But that do not matter as we need to roll back those changes.
    - the part of redo that was in redo log buffer but not yet written to disk is lost. But we don't need it as we know it covers only changes that were lost as as well - so no need to rollback them

    Now about your second question: User commits and the instance fails.

    When user commits, oracle guarantees that all redo related with that transaction is written to disk. This is why you sometimes see the 'log file sync' wait event when you commit: the commit will return only when the redo log buffer has been written to log files. This is the log force at commit.
    So, commited changes, even if they are not in datafiles, are always recoverable from redo log files.

    That answers your second question.

    Hi hope that answers the following question:
    during instance recovery, Oracle will first rollforward: that means that it applies all redo logs. all commited changes re-done. And some uncommited changes are re-done as well.
    That concerns data blocks, but also rollback segments.
    So the database has now recovered at least:
    - all commited data
    - the list of transactions that were not not completed (this is in rollback segments)
    - the undo that is needed to rollback those transactions (this is in rollback segments)
    Then oracle will rollback those transactions that were not completed. Exactly as if you did an alter system kill session

    Regards,
    Franck.
  • 39. Re: Why not use Redo log for consistent read
    Aman.... Oracle ACE
    Currently Being Moderated
    Sripada,

    I don't get the update emails so I just skip the threads at times. I believe you have got an explanation already. I shall be able to spend time on this question and few more(not from you ) on this weekend. But I guess, you should have got some idea already by now.

    Aman....
  • 40. Re: Why not use Redo log for consistent read
    KSG Explorer
    Currently Being Moderated
    Hi Aman,

    I appreciate the way you answer. I read all your post in this thread, which is an awesome explanation.

    I mess with some of the points. Hope will get good guidance.

    Could you please elobrate me on "what is change vector"?

    +When you do an update to an Oracle data block, whch means you change a salary value from 100 to 200, a change is done over the actual value(here 100). For this change, a Change Vector is generated which is actually an information that tells to oracle that this thing has happened and a code representation of the Update command for this value is generated.+

    Please elobrate me on "*an informaton that tells to oracle*"... where this information gets stored? what is the actual metadata? how it is managed?

    I learn that redolog contains only change vector not the changed blocks. Do archivelog too contains only change vector?

    Sorry if my question is silly..

    Thanks in advance
    KSG

    Edited by: KSG on Feb 15, 2010 5:49 PM " typo changes"
  • 41. Re: Why not use Redo log for consistent read
    user8896122 Newbie
    Currently Being Moderated
    Hi KSG

    have a look at

    http://jonathanlewis.wordpress.com/2010/02/09/why-undo/

    meanwhile....

    * Archivelog are no different from redo log. They are just archived form of redolog.

    Hope above links clear all you doubts.

    HTH

    Edited by: user8896122 on Feb 15, 2010 4:21 AM
    added a few lines
  • 42. Re: Why not use Redo log for consistent read
    KSG Explorer
    Currently Being Moderated
    Hi,

    I would like to request you to share your valuable observation on this issue.

    And I request experts to throw some more torch to this issue.

    Thanks
    KSG
  • 43. Re: Why not use Redo log for consistent read
    damorgan Oracle ACE Director
    Currently Being Moderated
    You need to be specific. If you have a specific question ... ask it.

    What about Jonathan Lewis' writing isn't clear?
  • 44. Re: Why not use Redo log for consistent read
    KSG Explorer
    Currently Being Moderated
    Hi Damorgan,

    Please find my previous post as follows ..

    Hi Aman,

    I appreciate the way you answer. I read all your post in this thread, which is an awesome explanation.

    I mess with some of the points. Hope will get good guidance.

    Could you please elobrate me on "what is change vector"?

    +When you do an update to an Oracle data block, whch means you change a salary value from 100 to 200, a change is done over the actual value(here 100). For this change, a Change Vector is generated which is actually an information that tells to oracle that this thing has happened and a code representation of the Update command for this value is generated.+

    Please elobrate me on *"an informaton that tells to oracle"* ... where this information gets stored? what is the actual metadata? how it is managed?

    I learn that redolog contains only change vector not the changed blocks. Do archivelog too contains only change vector?

    Sorry if my question is silly..

    Thanks in advance
    KSG


    Thanks
    KSG

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points