The principle and function of Redo log

REDO LOG is a mechanism established by Oracle to ensure that committed transactions are not lost. In fact, the existence of REDO LOG is prepared for two scenarios, one is called instance recovery (INSTANCE RECOVERY), one is called MEDIA RECOVERY. The purpose of instance recovery is to ensure that the data in the BUFFER CACHE is not lost when the database fails, and will not cause inconsistency in the database. The purpose of media recovery is to recover data when a data file fails. Although the mechanisms used for these two recovery are similar, the two types of recovery are also very different, which is often confused by many DBAs.

REDO LOG data is organized according to THREAD. For single instance systems, there is only one THREAD. For RAC systems, there may be multiple THREADs. Each database instance has a separate set of REDOs. The LOG file has a separate LOG BUFFER, and an instance change is independently recorded into a THREAD REDO LOG file.

For media recovery and instance recovery, the first step is to roll forward through the information of REDO LOG. When doing rollforward, pass the database change vector recorded in the REDO LOG file (later We will introduce the database change vector CV in detail, and submit it to the relevant data file according to the SCN comparison, so that the state of the data file is scrolled forward. It should be noted that changes in the UNDO tablespace are also recorded in the REDO LOG, so the UNDO tablespace related data files are also rolled forward. When the current available REDO LOG or archive log is rolled, all database recovery aspects are completed. At this time, the database contains all the recorded changes, some of which have been committed, and some have not yet been submitted. In the latest state of the UNDO tablespace, we can also see some uncommitted transactions.

So the next thing the database needs to do is transaction-level processing, rolling back those transactions that have not yet been committed to ensure database consistency.

For a single instance system, instance recovery is generally performed when the database is restarted after a database instance abnormal failure. When the database executes SHUTDOWN ABORT or restarts due to operating system
, host, etc. After that, when ALTER DATABASE OPEN, it will automatically do instance recovery. In the RAC environment, if an instance is smashed, or the instance will take over, the instance will be restored for the instance. Unless all instances are paralyzed, the first instance of ALTER DATABASE OPEN will do instance recovery. This is why REDO LOG is an instance-private component, but the REDO LOG file must be stored on shared storage.

The CACHE mechanism of the Oracle database is performance-oriented. The CACHE mechanism should maximize the performance of the database, so CACHE is always delayed as much as possible. This mechanism greatly improves the performance of the database, but when the instance fails, some problems may occur.

First of all, in the case of an instance failure, some of the modifications to the data file may not be completely written to the disk, and some modified information about the data file that has been committed by the transaction may be lost in the disk file. Second, it is possible that some changes to the data file for a transaction that has not yet been committed have been written to the disk file. It is also possible that some of the data changed by an atom has been written to the file, and some of the data has not yet been written to the disk file. The instance recovery is to automatically complete the repair of the above data through the information recorded in the ONLINE REDO LOG file. This process is completely automatic and requires no human intervention.

In this mechanism, there are two issues that need to be resolved. The first is how to ensure that committed transactions are not lost, and the second is how to make time for database performance and instance recovery. Balanced, ensuring that database performance does not degrade, and that instance recovery is fast.

Resolving the first problem is relatively simple. Oracle has a mechanism called Log-Force-at-Commit, that is, when the transaction is committed, the REDO LOG data related to the transaction, including the COMMIT record, The REDO LOG file must be written from the LOG BUFFER, and the signal that the transaction commits successfully can be sent to the user process. Through this mechanism, it can be ensured that even if part of the BUFFER CACHE in the already submitted transaction has not been written to the data file, an instance failure occurs, and when the instance is restored, the inconsistent data can also be obtained through the information of the REDO LOG. Roll forward.

To solve the second problem, oracle is implemented through the checkpoint mechanism. In the Oracle database, the modification operation of BUFFER CAHCE is completed by the foreground process, but the foreground process is only responsible for reading the data block from the data file into the BUFFER CACHE, and is not responsible for the BUFFER CACHE write data file. The operation of BUFFER CACHE to write data files is done by the background process DBWR. DBWR can write back a portion of the data block to the data file according to the load condition of the system and whether the data block is used by other processes. Under this mechanism, the time when a data block is written back to the file may be random, and some of the first modified data blocks may be written to the data file later. The CHECKPOINT mechanism is an effective complement to this mechanism. When CHECKPOINT occurs, the CKPT process will ask the DBWR process to write all the modified blocks of a certain SCN back to the data file. So once this CHECKPOINT is completed, all the data changes before the SCN have been saved. If an instance failure occurs afterwards, then when the instance is restored, it is only necessary to start the change after the CHECKPOINT has been completed. CHECKPOINT The previous changes do not need to be considered.

So far, we have learned some basic principles of the instance recovery mechanism, and we can understand the working mechanism of REDO LOG in general. But I think we still need to go deeper. Learn some more in-depth insider. In fact, through the introduction of the old white above, you may already feel that the understanding of the instance recovery is very thorough, but in fact, there are many problems we have not solved. Some brain-loving readers may have to ask, is it possible that the changes in the data file have been written, but the REDO LOG information is still in the LOG BUFFER, and the REDO LOG is not written. How can this situation be restored?

Here we have to introduce a noun: Write-Ahead-Log, which is the log write priority. The log write priority includes two aspects of the algorithm. The first aspect is that the modified BUFFER CACHE data is not allowed to be written to the data file until the modified change vector of a BUFFER CACHE has not been written into the REDO LOG file. This ensures that it is impossible to include changes that are not recorded in the REDO LOG file in the re-data file; the second aspect is that the BUFFER CACHE is not written to the REDO LOG for the change vector of the UNDO information of a certain data. The modifications cannot be written to the data file.

The mechanism of media recovery and instance recovery is similar. The difference is that media recovery is performed when the stored data file fails. Media recovery cannot be performed automatically. You must manually perform recovery database or recover. The datafile command is implemented. In general, media recovery is a recovery from a recovered data file, so you need to use archived logs when doing media recovery.

Reprinting a cave from the white pelicans

A brief summary:

The role of the redo log: 1. Recovering the database as a medium Important file 2. As an instance to restore the database, maintaining database consistency plays an important role

When doing instance recovery, the important key to consistency is redo log file, oracle guarantees that all commit success information is first written to redo Logfile, then change the datafile. When changing the dataifle, it is first to write the buffer cache to the buffer cache to write the data file to the dbwr process, write the inconsistency caused by half of the power failure, Oracle uses the chkp process to write the datafile periodically. (checkpoint will periodically refresh the datafile that is greater than the previous SCN to force a writeback to the datafile).

But the redo log is written to the redo log buffer first. If the redo log buffer is not written to the redo logfile, what should I do? 1. The data of the modified BUFFER CACHE is not allowed to be written to the data file until the modified change vector of a BUFFER CACHE has not been written into the REDO LOG file. 2. The modification of this BUFFER CACHE cannot be written to the data file until the change vector of the UNDO information of a certain data is not written to the REDO LOG.