Difference between Redo and Undo logs
Redo information, in a nutshell, is stored ONLY for recovery purposes (whether instance recovery or database recovery) whereas UNDO has extended functionality and usage. Redo stores undone changes as well. To be prcise, redo = do again (roll forward) and undo = erase the changes (roll back). I'll try and explain couple of scenarios.
Redo log stores all information about changes to database blocks. You insert 1000 rows--Oracle will generate redo and store it in log buffer (which will be later written into redo logs and then archived logs for recovery purposes). And the changes will be stored in UNDO (in case you want to rollback the insert later). Not only that. You now update 1000 rows and commit it. Oracle does not "have to necessarily" mark those blocks as committed. Instead it updates the data block(s) with a reference to the undo segment information. In fact Oracle only marks that UNDO segment slot as committed at this point and not necessarily the data block. Now a different session throws a select query on the table and Oracle duly visits that data block but the data block header will say now that there is an uncommitted change in the block(s). Here comes UNDO in play. Oracle will now go to the UNDO segment header and check the slot where it will say it's committed--so it will go back to the data block and do a cleanout of dirty blocks (delayed clean out). Another scenario is, you throw a query on a million record table and while it's visiting the required blocks........another session comes and updates some of the blocks that you need. Now Oracle will have to look at the datablock header and then go to UNDO and get that information. So UNDO has much more to do than the redo.
Now, coming to your second question, I think it will be much easier to explain now after the first question. Database files will have to store only the committed transactions, in an ideal situation. Otherwise, you cannot recover your database in case of disaster. On the other hand, REDO doesn't care whether it's a committed transaction or an uncommitted transaction. It has to store all changes to the blocks. Now if your database crashes, all the transactions will be applied to your database files using archived logs (or redo logs based on the DR situation) and then uncommitted transactions will be undone (rolled back).
Rememeber database files are online and they are written to by DBWR process. So we have database files, redo log files and archive log files.
In order to avoid scanning the entire log when doing a recovery, database performs checkpoints that summarize the status of the database. This checkpoint operation provides a shortcut to recovery. At checkpoint time, the database knows that all dirty pages have been written to disk (i.e. database files). At time of recovery, the log (that includes both completed and incomplete transactions) is used to bring the database back to a consistent state. The system locates the time of last checkpoint and finds that position in the log file. It then rolls forward all completed (read commited) transactions that occurred after the last checkpoint, and rolls back all transactions that were not committed, but began before the last checkpoint. So that is where online redo log files are used.
Now imagine you had to back up your 100+GB database every 10 minutes. That would be a waste of space! So you take a backup of your database at time t and the archiver process backups redo logs periodically to archive logs so that redo log files can be overwritten and RMAN will use the last backup plus the archive log files to recover your database to point in time.
Now I mentioned checkpoint process. The Checkpoint Process regularly initiates a checkpoint, which uses DBWR to write all dirty blocks back to the datafiles, thus synchronizing the database. Imagine a single checkpoint is running and all redo log files have been used up. At this point, Oracle will wait until all the previously queued dirty blocks have been written from the buffer to disc (database files) before any of the redo log files can be considered redundant and available for re-use (i.e. can be overwritten). This will result in the following message in the alert.log:
I am sure you have done the following:
When you mount a database, Oracle associates the started instance with the database. Oracle control files are opened and read. However no verification such as rollback/recovery is carried out
An open command opens data files, redo logs, it performs database consistency and auto recovery. At this stage the database is now ready to be used by all valid users.
A checkpoint process is an integral part of database logging and recovery. The operation itself is known as "checkpoint." A checkpoint writes both the log records , plus writing dirty (modified) data buffers to disk. It is essential that a checkpoint forces the flushing of log records from redo buffers to redo log filese. This makes sense in that if we flushed the modified data buffers to disk and do not flush the log records to disk and the system crashed, we would have modified records on disk with no log information associated with them. We need to flush the log buffers before writing modified data buffers so that the database integrity from a transactional point of view is maintained. BTW, a buffer is a database block, which happens to be in memory. A checkpoint causes the latest SCN to be written (updated) into the datafile header and control files.
The checkpoint frequency is affected by different parameters and equally importantly the size of redo logs. A change of redo log file will force a checkpoint in the database. Checkpoint is a resource intensive activity and it needs to be kept to a reasonable number to ensure an optimum recovery time and little or no impact on the database activity. You should use big enough redo logs - to reduce normal checkpoint frequency.
The following events trigger a checkpoint.
) Redo log switch (alter system switch logfile).
2) LOG_CHECKPOINT_TIMEOUT has expired
3) LOG_CHECKPOINT_INTERVAL has been reached
4) Manaul checkpoint force (alter system checkpoint)
No,the checkpoint process doesn't, It's job checkpointing process by updating the file headers of the data files.The Database Block Writer (DBWn) responsible for writing dirty blocks to disk.Basically chekpointing is the process of two sub process.
1) Write Dirty buffer from database buffer cache to datafile which is done by DBWR.
2) CKPT update alls datafile header with the SCN to memorize that i have written alls data buffer block from database buffer cache to that time when it occurs by SCN.
Redo information, in a nutshell, is stored ONLY for recovery purposes (whether instance recovery or database recovery) whereas UNDO has extended functionality and usage. Redo stores undone changes as well. To be prcise, redo = do again (roll forward) and undo = erase the changes (roll back). I'll try and explain couple of scenarios.
Redo log stores all information about changes to database blocks. You insert 1000 rows--Oracle will generate redo and store it in log buffer (which will be later written into redo logs and then archived logs for recovery purposes). And the changes will be stored in UNDO (in case you want to rollback the insert later). Not only that. You now update 1000 rows and commit it. Oracle does not "have to necessarily" mark those blocks as committed. Instead it updates the data block(s) with a reference to the undo segment information. In fact Oracle only marks that UNDO segment slot as committed at this point and not necessarily the data block. Now a different session throws a select query on the table and Oracle duly visits that data block but the data block header will say now that there is an uncommitted change in the block(s). Here comes UNDO in play. Oracle will now go to the UNDO segment header and check the slot where it will say it's committed--so it will go back to the data block and do a cleanout of dirty blocks (delayed clean out). Another scenario is, you throw a query on a million record table and while it's visiting the required blocks........another session comes and updates some of the blocks that you need. Now Oracle will have to look at the datablock header and then go to UNDO and get that information. So UNDO has much more to do than the redo.
Now, coming to your second question, I think it will be much easier to explain now after the first question. Database files will have to store only the committed transactions, in an ideal situation. Otherwise, you cannot recover your database in case of disaster. On the other hand, REDO doesn't care whether it's a committed transaction or an uncommitted transaction. It has to store all changes to the blocks. Now if your database crashes, all the transactions will be applied to your database files using archived logs (or redo logs based on the DR situation) and then uncommitted transactions will be undone (rolled back).
Rememeber database files are online and they are written to by DBWR process. So we have database files, redo log files and archive log files.
In order to avoid scanning the entire log when doing a recovery, database performs checkpoints that summarize the status of the database. This checkpoint operation provides a shortcut to recovery. At checkpoint time, the database knows that all dirty pages have been written to disk (i.e. database files). At time of recovery, the log (that includes both completed and incomplete transactions) is used to bring the database back to a consistent state. The system locates the time of last checkpoint and finds that position in the log file. It then rolls forward all completed (read commited) transactions that occurred after the last checkpoint, and rolls back all transactions that were not committed, but began before the last checkpoint. So that is where online redo log files are used.
Now imagine you had to back up your 100+GB database every 10 minutes. That would be a waste of space! So you take a backup of your database at time t and the archiver process backups redo logs periodically to archive logs so that redo log files can be overwritten and RMAN will use the last backup plus the archive log files to recover your database to point in time.
Now I mentioned checkpoint process. The Checkpoint Process regularly initiates a checkpoint, which uses DBWR to write all dirty blocks back to the datafiles, thus synchronizing the database. Imagine a single checkpoint is running and all redo log files have been used up. At this point, Oracle will wait until all the previously queued dirty blocks have been written from the buffer to disc (database files) before any of the redo log files can be considered redundant and available for re-use (i.e. can be overwritten). This will result in the following message in the alert.log:
Thread 1 advanced to log sequence 17973
Current log# 3 seq# 17973 mem# 0: /oracle/data/HAM1/log3aHAM1.dbf
Current log# 3 seq# 17973 mem# 1: /oracle/data/HAM1/log3bHAM1.dbf
Thread 1 cannot allocate new log, sequence 17974
Checkpoint not complete
Current log# 3 seq# 17973 mem# 0: /oracle/data/HAM1/log3aHAM1.dbf
Current log# 3 seq# 17973 mem# 1: /oracle/data/HAM1/log3bHAM1.dbf
Thread 1 advanced to log sequence 17974
I am sure you have done the following:
alter database mount;
Database altered.
When you mount a database, Oracle associates the started instance with the database. Oracle control files are opened and read. However no verification such as rollback/recovery is carried out
alter database open;
Database altered.
An open command opens data files, redo logs, it performs database consistency and auto recovery. At this stage the database is now ready to be used by all valid users.
A checkpoint process is an integral part of database logging and recovery. The operation itself is known as "checkpoint." A checkpoint writes both the log records , plus writing dirty (modified) data buffers to disk. It is essential that a checkpoint forces the flushing of log records from redo buffers to redo log filese. This makes sense in that if we flushed the modified data buffers to disk and do not flush the log records to disk and the system crashed, we would have modified records on disk with no log information associated with them. We need to flush the log buffers before writing modified data buffers so that the database integrity from a transactional point of view is maintained. BTW, a buffer is a database block, which happens to be in memory. A checkpoint causes the latest SCN to be written (updated) into the datafile header and control files.
The checkpoint frequency is affected by different parameters and equally importantly the size of redo logs. A change of redo log file will force a checkpoint in the database. Checkpoint is a resource intensive activity and it needs to be kept to a reasonable number to ensure an optimum recovery time and little or no impact on the database activity. You should use big enough redo logs - to reduce normal checkpoint frequency.
The following events trigger a checkpoint.
) Redo log switch (alter system switch logfile).
2) LOG_CHECKPOINT_TIMEOUT has expired
3) LOG_CHECKPOINT_INTERVAL has been reached
4) Manaul checkpoint force (alter system checkpoint)
No,the checkpoint process doesn't, It's job checkpointing process by updating the file headers of the data files.The Database Block Writer (DBWn) responsible for writing dirty blocks to disk.Basically chekpointing is the process of two sub process.
1) Write Dirty buffer from database buffer cache to datafile which is done by DBWR.
2) CKPT update alls datafile header with the SCN to memorize that i have written alls data buffer block from database buffer cache to that time when it occurs by SCN.
Hi Mudassar,
ReplyDeleteAfter reading your explanation on Redo and Undo I got the basic difference about the purpose of these two. I am using 11g and I still have some uncleared things in my mind. Which are :
1. If I open 4 connections simultaneously ,which selects and updates(differently) same data then I see that all of them will be having a different data after simultaneous updates. So my question is, where this updated data is? at Undo.? If yes then does it mean the uncommitted data is going to Undo segments/tablespace .?
2. Does each process/connection have their own Undo space.?
3. Which Oracle process writes data to Undo.?
4. If I issues commit on 1 connection then I see the committed data reflected on all the 4 connections. I this the way it works.?
5. I just want to know, exactly how data flows from Dirty buffers to the Datafiles (including Undo).
Kindly clarify my these doubts.
- I am just a beginner trying to grab things in a way they are .
Thanks
Vaibhav
Hi Vaibhav,
ReplyDeleteSorry for the late reply had been a busy week.
Well please find the answers to your questions:
1) This is indeed a vast topic to discuss. In oracle for Data Consistency and Integrity there is something called locking mechanism implemented. These locks are of two types- mutual(shared) or exclusive. Whenever you run a select query a mutual or shared lock is being acquired and when you run a update query exclusive lock is being acquired. In mutual lock 1 or many processes can acquire the lock and access the resources simultaneously but in exclusive lock only one process at a time can access the resources. This quite obvious because in select query you are just reading the data and in update you are modifying the data. So imagine what havoc it can create if while updating a mutual lock is acquired. Data integrity wont be achieved and each and every process will modify the data according to it own needs messing it u all completely.
So coming back to your question, when you acquire a session that session values are maintained across all the acquiring sessions until and unless any process commits that session. The new values are highlighted only when the session is committed. Else you will see the old values. Now where these values are maintained. All the session related uncommitted data is maintained in the UNDO tablespace and all the committed data is maintained in the REDO logs. So it is quite obvious that when we go for a instance recovery after the crash, all the committed data is applied from redo logs (process called as roll forward) and uncommitted data is lost (process called roll backward).
Please note here that data is periodically written to datafiles while data is regularly or you can say continuously written to the redo log files. So when you commit any changes it is obvious that data is written to the redo logs but you cant guarantee that data is written to the respective datafiles.
Ghosh!!!!!!! I hope that clarifies your doubt...
2) No undo space is common and shared among all the processes. While it is true that depending upon the locking mechanism the access to these spaces are granted.
ReplyDelete3) It has to be DBWR process. Please note that any data that is being written to the datafiles is taken care by DBWR process and UNDO tablespace has nothing in background then UNDO Datafile ;)
ReplyDelete4) try it out you will see the difference. Keep connecting to one session and execute the commit command on that row or table from another session. Tell me what you find.. Its interesting to observe few facts than reading them :)
ReplyDelete5) Ok you need to clarify this question what actually you need.
ReplyDeleteIn simple terms as I can understand your question, buffers are nothing but temporary spaces, when they get full they need to be flushed somewhere otherwise data will be lost if the buffers are overwritten.
So whenever the data buffers are 1/3rd full (I dont know exact logic why oracle defined it) it forcefully writes the data from the buffers to datafiles or redo logs or when someone issues a checkpoint or commit statement the data is written from buffers to disk becuase by issuing this commands the user guarantees that this data is required by him in future so if case of recovery you need to access this data from somewhere and that's where you get it...
Hope this will clarify your questions... hoping to hear more from you...
thank u so much................. very useful...............
ReplyDeleteMudassar,
ReplyDeleteDoes redo log contain undo information too apart from the changes that happen in the data...?
Thanks,
M
To give a straight answer to this one NO. The sole purpose of using redo is recovery and undo is well erase the changes in case of inconsistency. Read the first para again.
DeleteHi Mudassar,
ReplyDeleteSo whenever the data buffers are 1/3rd full (I dont know exact logic why oracle defined it) it forcefully writes the data from the buffers to datafiles or redo
I have question here, A RMAN full backup is started and its running, meanwhile translogs are getting created before completing full backup some other archive backup job backed up and cleared the arch log, how can we be sure that all these transaction are updated to database ?
During backup redo logs will be written into database ?
EVen after deletion of ach log during the full backup, will actual data be written to data files from buffer ?
One question at a time.
Delete1) How can we be sure the transactions are updated?
Ans: Login into you database and check the SCN number?
SELECT current_scn FROM V$DATABASE;
From archive log and RMAN back up log check the last SCN backed up. The number will be very close to the one seen in database. The other transactions that are not backed up will be present in archive logs and redo logs for sure for point in time recovery. The archiver process will take care of it not to delete the archive logs, the ones that are not backed up. But in case someone forcefully deletes the archive logs using some OS commands like rm [-f] you have lost that data. Rest be assured archiver wont delete the arch logs that are not backed up.
2) During backup redo logs will be written into database ?
Ans: Archiving is continuous process and yes this will be done even during backup.
3) EVen after deletion of ach log during the full backup, will actual data be written to data files from buffer ?
Ans: I am not sure what you actually mean by this one. Flushing of data from dirty buffers to datafiles is continuous autonomous process and nothing can interrupt it until and unless that are some serious issues with your DB. So I am more inclined to say yes but will be more confident if I get the crux of question asked.
Dude, very complex explanation for redo and undo. Make it simple man.
ReplyDeleteThank You and I have a swell present: Whole Home Renovation Cost house renovation quotes
ReplyDelete