Minimizing gaps between Redo logs and Data Files

Checkpointing is a critical process in database management and computing systems that ensures data integrity and system recovery in case of failures. It involves the process of transferring data and transaction logs from volatile memory (such as RAM) to non-volatile storage (such as a hard disk or SSD) at specific intervals or under certain conditions. The main reason checkpointing causes data to be written from the buffer to non-volatile storage is to maintain a consistent state of the database and minimize the amount of data loss and recovery time in the event of a system crash or power failure. When transactions are executed in a database, they are initially stored in a buffer in volatile memory to allow for fast access and manipulation. This buffer acts as a temporary holding area for data being read from or written to the database. However, data stored in volatile memory is at risk of being lost if the system crashes or loses power, as this type of memory requires continuous power to maintain the stored information.
Checkpointing addresses this risk by periodically forcing the writing of all outstanding changes in the buffer to the permanent storage. This process ensures that the data on permanent storage is up-to-date with the most recent changes made in the buffer, up to the point of the last checkpoint. In the event of a system restart, the database can use these checkpoints to restore the database to its most recent consistent state by redoing transactions that were committed after the last checkpoint and undoing transactions that were not completed. Furthermore, checkpointing helps in optimizing the recovery process by limiting the amount of transaction log data that must be scanned and processed during recovery. Without checkpointing, the system would have to process the entire transaction log from the beginning, which could be time-consuming and resource-intensive, especially for systems with a high volume of transactions.
In summary, checkpointing causes data to be written from the buffer to non-volatile storage to ensure data durability, maintain database consistency, and facilitate efficient system recovery by establishing known good points from which the system can recover quickly and accurately in the event of a failure.

Checkpoint advancing can also trigger the database writer to write dirty blocks back to disk. This minimizes the number of changes that must be redone in the event the system crashes or the Oracle instance fails. Recall that each change to a database has a number assigned to it, and that this number corresponds to the redo log record written for that change. To prevent any inordinately large gaps between content in the redo logs and content in the datafiles, Oracle periodically writes the changes back to the datafiles. This is referred to as checkpointing.

The database buffer cache is part of the reason that checkpointing needs to occur. The buffer cache exists to keep the most frequently accessed data blocks in memory. Blocks that are changed a lot will tend to stay at the most recently used end of the LRU list. Because of this, they might never be forced back to disk by incoming data, as the previous lesson describes. Instead, they might sit in memory forever. You could make a million changes to them, and none of those changes would be written to disk. Obviously, that is not a good situation. Enter checkpointing. Oracle periodically takes a system change number, and ensures that all changes up through and including that change are written to disk. By default, a checkpoint occurs every time a redo log switch occurs.

A checkpoint is a crucial mechanism in consistent database shutdowns, instance recovery, and Oracle Database operation generally. The term checkpoint has the following related meanings:

1. A data structure that indicates the checkpoint position, which is the SCN in the redo stream where instance recovery must begin The checkpoint position is determined by the oldest dirty buffer in the database buffer cache. The checkpoint position acts as a pointer to the redo stream and is stored in the control file and in each data file header.
2. The writing of modified database buffers in the database buffer cache to disk

Every Oracle database has a set of two or more redo log files. The set of redo log files is collectively known as the redo log for the database. A redo log is made up of redo entries (also called redo records). The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost. To protect against a failure involving the redo log itself, Oracle allows a multiplexed redo log so that two or more copies of the redo log can be maintained on different disks.
The information in a redo log file is used only to recover the database from a system or media failure that prevents database data from being written to the datafiles. For example, if an unexpected power outage terminates database operation, then data in memory cannot be written to the datafiles, and the data is lost. However, lost data can be recovered when the database is opened, after power is restored. By applying the information in the most recent redo log files to the database datafiles, Oracle restores the database to the time at which the power failure occurred. The process of applying the redo log during a recovery operation is called rolling forward.