Its been a really long time since I wrote a post. These days seems to be very busy with all Fusion middle ware floating around me. Anyways, here I start again. This post is a kind of kickoff post. I was going through Expert one-on-one book by Thomas Kyte and read a wonderful basic recovery related article from the book. Ohh by the way…everyone should read Expert one-on-one atleast once in his/her life. A must read book, not only for DBA but specifically for developers.
So here is the recovery concept go. This is a very basic concept about how Oracle does crash recovery. I will be putting the same story here, but in my words.
We know the basic DML transactions are insert, update and delete. We will consider a simple transaction and understand : what data gets generated during the transaction, where it is stored and how oracle database recovers in case of crash.
Lets say an insert statement on EMP table
insert into EMP (EMPNO, NAME, JOB, SAL) values (10, ‘Deo’, ‘DBA’, 100);
Following things will happen
- This statement will modify the data blocks in buffer cache
- This statement will also fetch the index blocks in the buffer cache and modify the same
- This statement will generate undo data in buffer cache
- This statement will generate redo data in redo log buffer. This entries in redo log buffer is to protect the blocks in buffer cache.
So the picture in SGA will looks as shown below
So till now the user has executed an insert statement, its not yet committed. The blocks in buffer cache has been protected by entries in redo log buffer.
What if the system crashes at this stage?
If the system crashes at this point, then everything in the memory will get wiped out. It will be like nothing happened. There is no record of this transaction in any of the files (datafiles or redo log file). This is OK. We dont need anything that was in SGA.
What if the buffer cache fills up at this stage?
We are have done the insert, but we have not yet committed. Changed blocks are in buffer cache. Also redo blocks are not yet flushed to online redo log files and are still present in redo log buffer. At this stage suppose db buffer cache fills up. Now DBWR process needs to make a room for new blocks to be fetched for some other user session. In that case DBWR will ask LGWR process to flush the redo log buffer into online redo log files. So the redo log records generated by insert transaction will get flushed to online redo log file. Note that we have NOT yet commited the insert record.
This behavior of DBWR to ask LGWR process to flush redo data is perfect. imagine what will happen if the data in buffer cache gets written to database files before redo data gets flushed into redo log files. If system craches at a point after buffer cache data gets updated in data files and before redo data from redo log buffer gets flushed into online redo log files, then when the database reboots, there is no way to tell whether our insert transaction was commetted or not. Since our transaction was not commited, how will Oracle know that it needs to rollback the insert transaction. That’s why data in redo log buffer gets flushed to online redo log files first. The scenario will look like as shown in fig 2 below.
Now the blocks in database buffer cache are protected by the entries in redo log files. Some of this blocks might get written to datafiles in order for DBWR process to make room, or none of them gets written to datafiles if DBWR process is able to make room without touching these blocks. It depends.
What if the system crashes at this stage?
At this stage if the system crashes then all the data in memory will get wiped out. The data blocks in buffer cache will be gone. But we still have the entries in online redo log files. So when the oracle performs crash recovery it reads the online redo log file and roll-forward the transactions. Redo log file has data to regenerate the data blocks that were present in the buffer cache before system crash. So oracle will read the redo log files and generate the data block, the index block and undo data block as well. Note here that undo data block is also treated here as a normal data block and changes to undo data block will make redo entries. So while doing crash recovery it will generate the 3 data blocks and scenario will be similar to fig 2. On further reading of redo log file, oracle will come to know that this insert transaction was never committed. So Oracle will use the undo data generated for this transaction to rollback the data. This will delete the insert entry.
Now before system crash if the data in buffer cache has been applied to datafiles then oracle will reapply the latest data generated and overwrite the previously inserted data. Now everything is back the way it was.
This scenario covers the rudimentary details of crash recovery. Here the recovery is a 2 step process, first oracle roll forwards all the transactions and then it starts rolling back the transactions which are not committed (from the undo data that it generates during roll forward process).
Similar concept applies to the update and delete statements as well.
What happens to the undo data?
The undo data which gets generated during transactions (example in the above scenerio) will be stored for some time. If there is no space left in memory this undo data is flushed to undo segments in undo tablespace and it will reside in undo tablespace for a minimum time period defined by UNDO_RETENTION init.ora paramter. This undo data is used during rolling back a transaction. Please note that for rolling back a transaction redo log files are never used. Redo logs and archive logs are referred (or read) only during recovery of database. During all other time the data is written to redo logs files but never read from it. So for rolling back the transaction undo data saved in undo tablespace is required.
Hope this helps !!
References:
Expert One-on-One – By Thomas Kyte
’
,
*
’
=
Persistent Storage Solutions 