Difference Between Archive, Redo and Undo Logs.
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.
Regards,
Suresh R
oracle dba .
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.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.
Regards,
Suresh R
oracle dba .
Hi Suresh,
ReplyDeleteNice information
Just want to add.
Roll forward ie cache recovery happen before db gets open and
Roll back ie transaction recovery happen after db gets open.
Thanks
Jamsher