user4508393 wrote: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.
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.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).
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)I amnot sure that what you wanted to say from this statement. Sorry but I didn't get it.
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.
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.HTH
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.
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.
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?