Oracle 10g instance recovery Tuning

First, you must understand some concepts:

The information in the log file ensures that the transaction can be restored when the system fails. When a user finishes sending a commit, the commit complete information is always received only after the lgwr process writes the redo information required by the transaction to the log file (which may have been in the redo buffer before.

The dbwr process is always slower than the lgwr process (the dbwr process is a random write, the lgwr process is a sequential write, and the random write is slower than the sequential write)

When the dbwr process writes information in the cache area to the data file, it first notifies the lgwr process to write transaction-related redo information to the log file.

SCN can be understood as a tag. oracle adds a tag to each operation in the database. This label is added sequentially. Never return to 0 (unless the database is rebuilt)

Checkpoint is used by Oracle to record which data has been written to a data file.

The purpose of checkpoint is to ensure that the data before this checkpoint SCN is written to the data file by dbwr when the checkpoint occurs. Before dbwr is written, the lgwr process is triggered to write the related redo information to the log file. In this way, after the checkpoint is completed, the information before the checkpoint SCN will no longer need to be restored when the instance fails.

Understand instance recovery information:

The information required by instance recovery is the redo information from the last checkpoint to the end of the log file.
Because the data before the checkpoint has been consistently written into the data file, some of the subsequent data may have been written into the data file, but some have not been written into the data file.

The time required by instance recovery to restore the data file from the last checkpoint to the last SCN value recorded in the control file, the time used to apply the redo information between the two is the time required for instance recovery.

Instance recovery adjustment:

According to the above information, the most critical issue of instance recovery is the time when the last checkpoint occurred and the frequency of the checkpoint. Only when the time point of the last checkpoin occurrence is confirmed can the redo information required for recovery and the time required for recovery be determined.

The Optimization of the time consumed by the instance recovery is the adjustment of the fast_start_mttr_targe parameter. The unit is second, and the maximum value is 3600 seconds.

That is to say, the setting of fast_start_mttr_target directly affects the checkpoint occurrence frequency.

The time set by fast_start_mttr_targe is the time when the user wants the database to be used for instance recovery. That is, the time it takes to redo information between the last checkpoint of the application and the last two points of log information.

If the MTTR setting time is too small, the system checkpoint will be too frequent. When a checkpoint occurs, processes such as dbwr and lgwr will write data files to generate physical Io. Over time, the database performance will slow down;
If the MTTR setting time is too large, it takes too long to recover the instance when the instance fails.

The database can be automatically adjusted from 10 Gb. If fast_start_mttr_target = 0, you can see the following information in alert:
MTTR advisory is disabled because fast_start_mttr_target is not set
In this case, the database automatically adjusts the checkpoint Frequency Based on the load.

If the instance recovery time is strictly required, set the fast_start_mttr_target parameter. If not, we recommend that you use 10 Gb automatic adjustment.