user628400 wrote:Thanks for the kind words.
this is awesom explanation and now I feel I am getting closer to what I was really looking for.
So now I understand that undo blocks are stored in redo and not the real values. My only question is that is it possible that if database crashes and while recovering, the undo data is not there tha's needed by redo or archive logs? I am thinking undo segments will have to be real big to help database recover upto the most recent archive/redo log. I am sure I am missing some more points here but it looks like if redo keeps the undo blocks then if DB is recovering from days old archive logs then undo needs to be big enough to hold all that data. I am surely missing something here badly.Well, you haven't understood still I guess. Okay, let's take a little deep dive.
user628400 wrote:What's the confusion about Undo? As I said before as well, Undo change is also the same change as like normal Data block change so it would also get logged into the redo log buffer just like that.
Thanks for your patience.
This is how I understand and I am sure I probably got the sequence all messesd up. But just so that I clearly understand it:
1. insert 100 and commit
2. write to redo log the block address or some pointer to undo segment say address UA which holds the value
3. update 100 to 200 and commit
4. Make another entry in undo log say address UB that now holds new value of 200
5. Write the pointer to this change in redo log which will be UB
6. Also, write another record in redo log about the transaction that took place in Step 5 i.e change of undo segment itself. (little confused at this point about where the values are stored in this step)
user628400 wrote:Well, I guess I have explained this before as well. Anyways, Undo would be having old image and the place where the entry has been put, that place's information would be recorded in the Redo.
Confusion is that when Undo records are also recorded in redo logs. Is it recorded in the same way that the redo log points to the change vector of undo and undo has a copy of value of what it itself changed?
For eg: If undo changed value of 100 to 200 does it have 3 different entries in undo:What is 100, 200 recording the values? You must read what I said before that Undo Segment is just as what a normal Segment is which means that when an entry is made in the Undo Segment, the information logged in the Redo log is same as like normal as a Data Block .
1. 100 to record old value for the row
2. 200 to record new value for the row
3. another entry of 200 in undo to record what was changed in undo segment itself? (This step is specifically the Step 6 where I have confusion).
. Redo stores the change vectors and not the actual table value.Correct. Just one thing to remember, redo contains the changed data. Whatever you update, it goes here.
2. Actual table values are stored in undo segment.What does it mean by "actual value" here? As I said many times before as well, Undo stores only old image . So if you have changed 100 to 200, only 100 goes into the Undo and should be as well since form here only, the CR(consistent read) block would be prepared.
3. Undo segment Stores the old data or the past image.Correct.
4. Redo logs have the pointer to the old data in undo segment using change vector for eg: if row gets changed from 100 to 200 redo will hold pointer to undo block where 100 is storedWhere did you read this from and where did I say this as well? Redo logs contain the change vectors of both your block as well as teh Undo block's changes. The pointer is kept in your block's transaction header only for the Undo and is called Undo Byte Address which tells oracle that where is your old value kept in Undo. Redo doesn't need to point to Undo. Tell me a good reason why it should be happening in any case?
5. Redo log also contains the changed image of undo data block. So for eg: when a change occurs to a data block of undo the same is logged in redo.Correct.
. Does undo also store the current image. For eg: If the row was modified from 100 to 200. Then does undo has only 100? Or does it have both 100 and 200? I am assuming undo has both the values if redo has to point to it. But if Undo has both the values then why do we say that undo has the past image. In this case 200 is the current image and 100 is the past image. How does this work?Repeat as long as you wont believe,
What if undo is not big enough and we have to recover a datafile for which the data is not in undo. As you mentioned redo doesn't have data. But if undo also doesn't have data then what happens? Even if redo need to rebuild the undo segment how will it do it if undo does't have enough data. I am trying to visualize the flow but it just seems like there will not be enough data to recover a datafile with small undo. From where will redo get the data?Redo doesn't need data. The change vector is the representation of the changes that you have done and is sufficient enough to recover your data. If you would lose undo data file, do you think that recovery can't be done? No, it would be still recoverable and from where, from Redo only! If your hypothesis would be correct, we could never recover Undo datafile since its gone!
We keep saying redo is very critical and should be multiplexed with multiple group and members. But it looks like undo is equally important because without actual data what can redo do?What do you think that happens when recovery is initiated? Does oralce applies Update, Insert, Delete statements like you would do to recover your changes because its the only time I can think that the actual values are needed. But it doesn't work like that. Redo's change vectors are used by oralce to recover it all and they represent changes only what have happened over the datafiles. Undo is not required here. Even if you would miss Undo(its gone) , we can recover it as well from the Redo. So all you need is Redo to be kept safe , rest you can blow it all!
user628400 wrote:Not a problem. Glad to see that finally you did get it.
Excellent! When I started this thread I started off with similar understanding that redo has new data and undo has past image but got confused along the way. I think I didn't understand what actually was change vector. I kept thinking that this vector just contains the address of old and new values into undo and that's what confused me. But thanks for taking time and calrifying it in detail and for being patient. Reason I asked my question the way I asked was so that I am not conufsed all the time.
So going back to my original question it looks like the reason we don't use redo for consistent read is for efficiency, optimization and performance reasons.Yes.
When a select is issued how does it know where in undo segment that block is for CR? Is there a undo header where SCNs are kept that tells oracle to fetch the past image from particular block in Undo?When a select is started , it starts with a Query SCN which is compared with the Active Transaction SCN set in the block's Transaction Header. If there is an active transaction running on teh block , for which the information is set in theheader, there must be an transaction entry kept in the Transaction Table which is in the Undo Segment only. Comparing the SCNs , its decided that whether to give you a consistent read or a current read. If the Query SCN is more recent than the transaction SCN that means the query can't see what's written in the block and needs a consistent copy to be there. As I mentioned before, UBA(Undo Byte Address) is set in the block's ITL , it can be find out what which undo segment contains your old copy of block, use it to prepare the CR buffer( Buffer type is 3 from X$BH) and ship to you.