Shulou(Shulou.com)06/01 Report--

1. Concept

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:

Instance recovery (INSTANCE RECOVERY)

Media recovery (MEDIA RECOVERY).

The purpose of instance recovery is to ensure that the data in BUFFER CACHE will not be lost and will not cause database inconsistency when the database fails.

The purpose of media recovery is to recover data when a data file fails.

Although the mechanisms used by the two recoveries are similar, there are also very fundamental differences between the two recoveries, which is often confused by many DBA.

REDO LOG data is organized according to THREAD, for a single instance system, there is only one THREAD, for a RAC system, there may be multiple THREAD, each database instance has an independent set of REDO LOG files, has an independent LOG BUFFER, and the changes of an instance will be independently recorded in a THREAD REDO LOG file.

2. Recovery steps

For media recovery and instance recovery, the first step is to roll forward through the information of REDO LOG. During the roll forward, the change vector of the database recorded in the REDO LOG file is submitted to the relevant data file according to the comparison of SCN, so that the state of the data file scrolls forward. Note that changes to UNDO tablespaces are also recorded in REDO LOG, so data files related to UNDO tablespaces are also rolled forward. When you roll to the last available REDO LOG or archive log, all the work at the database recovery level is done. At this point, the database contains all the recorded changes, some of which have been committed and some that have not yet been committed. In the latest state of the UNDO table space, we can also see some transactions that have not yet been committed.

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

For single-instance systems, instance recovery is usually carried out when the database is restarted after an abnormal failure of the database instance. When the database executes SHUTDOWN ABORT or restarts due to operating system or host downtime, the instance recovery will be done automatically when ALTER DATABASE OPEN occurs. In RAC environment, if an instance goes down, the living instance will take over and restore the instance for the failed instance. Unless all the instances are down, the first instance that executes ALTER DATABASE OPEN will restore the instance. This is also why REDO LOG is a private component of the instance, but REDO LOG files must be stored on shared storage.

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

First of all, when the instance fails, the modification of the data file may not be fully written to the disk, and the modification information of some committed transactions may be lost in the disk file. Secondly, it is possible that some changes to the data file made by some transactions that have not 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, while some of the data has not been written to the disk file. 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 does not require human intervention.

In this mechanism, there are two problems that need to be solved:

The first is how to ensure that committed transactions are not lost.

The second is how to strike a balance between database performance and the time required for instance recovery, not only to ensure that database performance will not decline, but also to ensure fast instance recovery.

It is relatively simple to solve the first problem. Oracle has a mechanism called Log-Force-at-Commit, that is, when a transaction commits, the REDO LOG data related to the transaction, including COMMIT records, must be written to the REDO LOG file from LOG BUFFER, and the signal of a successful transaction commit can be sent to the user process. Through this mechanism, we can ensure that the instance failure occurs even if part of the BUFFER CACHE in the committed transaction has not been written to the data file, and the inconsistent data can be rolled forward through the information of REDO LOG when the instance is restored.

To solve the second problem, oracle is implemented through the checkpoint mechanism. In the Oracle database, the modification 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 to the BUFFER CACHE, not for BUFFER CACHE to write to the data file. The operation of BUFFER CACHE writing to the data file is done by the background process DBWR. DBWR can write back some data blocks to data files according to the load of the system and whether the data blocks are used by other processes. Under this mechanism, the time when a data block is written back to the file may be random, and some data blocks that are modified first may be written to the data file later. The CHECKPOINT mechanism is an effective supplement to this mechanism. When CHECKPOINT occurs, the CKPT process will require the DBWR process to write all the modified blocks of a SCN back to the data file. In this way, once the CHECKPOINT is completed, all data changes before the SCN have been saved. If there is an instance failure later, when you restore the instance, you only need to start with the changes after the completion of the CHECKPOINT, and you do not need to consider the changes before the CHECKPOINT.

So far, we have learned some basic principles of the instance recovery mechanism, and we can also generally understand how REDO LOG works. But I think we need to go a little deeper. Learn something more in-depth. In fact, through the above introduction, you may already think that you have a thorough understanding of the instance recovery, but in fact, there are many problems that we have not solved. Some thoughtful readers may want to ask, is it possible that the changes in the data file have been written, but the REDO LOG information is still in LOG BUFFER, not written into REDO LOG? how can this situation be restored?

Here we are going to introduce another term: Write-Ahead-Log, which means log writing priority. Log writing priority includes two algorithms:

The first aspect is that before the modified change vector of a BUFFER CACHE is written into the REDO LOG file, the modified BUFFER CACHE data is not allowed to be written to the data file, which ensures that the changes that are not recorded in the REDO LOG file cannot be included in the data file.

The second aspect is that when the change vector of the UNDO information of a data is not written to the REDO LOG, the modification of the BUFFER CACHE cannot be written to the data file.

3. Difference

The mechanism of media recovery and instance recovery is similar, except that media recovery is carried out when the stored data file fails, media recovery cannot be carried out automatically, and recover Database or recover datafile commands must be executed manually. Generally speaking, media recovery starts from a recovered data file, so archive logs are needed when doing media recovery.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.