Introduction to DBA Checkpoint)

1. essence of checkpoints

Many documents describe Checkpint very complex and bring obstacles to our correct understanding of the checkpoint. As a result, the checkpoint becomes a very complicated problem. Actually,A checkpoint is only a database event. Its fundamental significance lies in reducing the Crash Recovery time..

When modifying data, you must first read the data into the memory (Buffer Cache). When modifying the data, Oracle will record the Redo information for restoration. Due to the existence of Redo information, Oracle does not need to write the changed data back to the disk at the time of submission (the efficiency of instant writing will be low), and Redo (Redo) to restore the data after the database crashes.

In the most common case, the database can be Crash due to power failure, so the data that has been modified in the memory and has not been written into the file will be lost. After the next database is started, Oracle can repeat the transaction by Redo, that is, roll forward, restore the database to the State before the crash, and then the database can be opened for use, oracle can then roll back uncommitted data.

In this process, the most important concern is how long the database can be opened. That is, the number of redo logs to be read before the rollback can be completed. Of course, the user hopes that the shorter the time, the better, Oracle is constantly optimizing this process through various means, shortening the recovery time.

The checkpoint exists to shorten the recovery time.

When a CheckPoint occurs (at this time, the SCN is called the Checkpoint SCN), Oracle will notify the DBWR process to change the modified Data, that is, the Dirty Data before the CheckPoint SCN (Dirty Data) write Data from the Buffer Cache to the disk. After the write is complete, the CKPT process updates the control file and data file header, records the checkpoint information, and identifies the change.

For more information about Oracle SCN, see my other article: getting started with DBA: Oracle SCN (System Change Number)

Checkpoint SCN can be queried from the database:

SQL> select file #, CHECKPOINT_CHANGE #, to_char (CHECKPOINT_TIME, 'yyyy-mm-dd hh24: mi: ss') cpt from v $ datafile;

FILE # CHECKPOINT_CHANGE # CPT

-----------------------------------------------

1 913306 16:06:06

2 913306 16:06:06

3 913306 16:06:06

4 913306 16:06:06

SQL> select dbid, CHECKPOINT_CHANGE # from v $ database;

DBID CHECKPOINT_CHANGE #

----------------------------

1294662348 913306

After the checkpoint is completed, all the data modified before the checkpoint has been written back to the disk. The corresponding redo records in the redo log file are no longer useful for crash/instance recovery.

Three log groups are marked. It is assumed that the database completes and records the last checkpoint at T1, and the database Crash is performed at T2. Then, at the next database startup, the Redo logs generated by the database between T1 and T2 no longer need to be restored. Oracle needs to apply the Redo logs generated by the database between T1 and t2 ).

It can be easily seen that the checkpoint frequency has a great impact on the database recovery time. If the checkpoint frequency is high, the redo logs that need to be applied during recovery are relatively small, check time can be shortened. However, it should be noted that the internal operations in the database are extremely strong, and ordinary checkpoints in Mandarin will also cause performance problems, especially frequently updated databases. Therefore, database optimization is a system project and cannot be hasty.

Further, we can know that if Oracle can make the SCN primary key of the checkpoint approach the latest update of Redo when the performance permits, an optimal balance can be obtained, this allows Oracle to minimize the recovery time.

To achieve this goal, Oracle has been improving the checkpoint algorithm in different versions.

 

2. Regular and incremental checkpoints

To differentiate, Oracle Real-Time checkpoints before Oracle8 are generally called common checkpoints ), such checkpoints start with certain conditions (log_checkpoint_interval, log_checkpoint_timeout parameter settings, and log switch ).

Starting from Oracle 8, Oracle introduced the concept of Inctrmental Checkpoint.

Compared with earlier versions, Oracle introduces the Checkpoinnt Queue mechanism in the new version. in the database, each dirty data block is moved to the checkpoint Queue, in the order of Low RBA (the Redo Byte Address corresponding to the data block modification for the first time), if a data block has been modified multiple times, the order of the database in the checkpoint queue does not change.

When the check point is executed, DBWR is written from the Check Point queue in the order of Low RBA, so the instance check points can be constantly improved and phased. The CKPT process uses a very lightweight control file update protocol, write the current minimum RBA to the control file.

Because incremental checkpoints can be performed continuously, the checkpoint RBA can be closer to the database's final state than the conventional checkpoint, thus greatly reducing the recovery time in database instance recovery.

In addition, through incremental checkpoints, DBWR can be continuously written, so as to avoid the peak write from the conventional checkpoints for the I/O Country requisition, through which you can clearly see the significance of this improvement.

In databases, incremental checkpoints are implemented through the Fast-Start Checkpointing feature, starting from Oracle 8i. This feature includes the Fast-Start Fault reult Component of Oracle Enterprise Edition, query the v $ option view to understand this feature:

SQL> select * from v $ version where rownum <2;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Prod

SQL> col parameter for a30

SQL> col value for a20

SQL> select * from V $ option where Parameter = 'fast-Start Fault Recovery ';

PARAMETER VALUE

--------------------------------------------------

Fast-Start Fault Recovery TRUE

This component has three main features, which can accelerate system recovery after failure and improve system availability.

Fast-Start Checkpointing;

Fast-Start On-Demand Rollback;

Fast-Start Parallel Rollback;

The Fast-Start Checkpointing feature is mainly implemented by the FAST_START_IO_TARGET parameter in Oracle 8i. in Oracle 9i, Fast-Start Checkpointing is mainly implemented by the FAST_START_MTTR_TARGET parameter.

 

3. FAST_START_MTTR_TARGET

FAST_START_MTTR_TARGET is introduced from Oracle 9i. This parameter defines the database Crash recovery time, in seconds, and the value ranges from 0 ~ 3600 seconds.

In Oracle 9i, we recommend that you set this parameter to replace FAST_START_IO_TARGE, LOG_CHECKPOINT_TIMEROUT, and LOG_CHECKPOINT_INSTERVAL.

By default, in Oracle 9i, The FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL parameters have been set to 0.

SQL> show parameter fast_start_io

NAME TYPE VALUE

-----------------------------------------------------------------------------

Fast_start_io_target integer 0

SQL> show parameter interval

NAME TYPE VALUE

-----------------------------------------------------------------------------

Log_checkpoint_interval integer 0

Starting from Oracle 9i R2, Oracle introduced a new view to provide MTTR suggestions:

SQL> select * from v $ mttr_target_advice;

MTTR_TARGET_FOR_ESTIMATE ADVICE_STATUS DIRTY_LIMIT ESTD_CACHE_WRITES ESTD_CACHE_WRITE_FACTOR ESTD_TOTAL_WRITES ESTD_TOTAL_WRITE_FACTOR ESTD_TOTAL_IOS ESTD_TOTAL_IO_FACTOR

------------------------------------------------------------------------------------------------------------------------------------------------------------------

This view evaluates the I/O operations required by the system under different FAST_START_MATTR_TARGET settings. You can adjust FAST_START_MTTR_TARGET based on database recommendations.

The phone number of this suggestion information receives the control of the new initialization parameter statistics_level introduced by Oracle 9i. When this parameter is set to Typical or ALL, The MTTR suggestion information is sent to the mobile phone:

SQL> show parameter statistics_level

NAME TYPE VALUE

-----------------------------------------------------------------------------

Statistics_level string TYPICAL

You can also use the v $ statistics_level view to query the current settings of MTTR_Advice:

SQL> select * from v $ statistics_level where STATISTICS_NAME = 'mttr Advice ';

STATISTICS_NAME DESCRIPTION SESSION_STATUS SYSTEM_STATUS ACTIVATION_LEVEL STATISTICS_VIEW_NAME SESSION_SETTABLE

Certificate certificate ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MTTR Advice Predicts the impact of different MTTR settings on number of physical I/OS ENABLED TYPICAL V $ MTTR_TARGET_ADVICE NO

The recovery status of the current database instance can be obtained through the view v $ instance_recovery query:

SQL> select * from v $ instance_recovery;

RECOVERY_ESTIMATED_IOS 53

Actual_redoc_blks 376

TARGET_REDO_BLKS 184320

LOG_FILE_SIZE_REDO_BLKS 184320

LOG_CHKPT_TIMEOUT_REDO_BLKS

LOG_CHKPT_INTERVAL_REDO_BLKS

FAST_START_IO_TARGET_REDO_BLKS

TARGET_MTTR 0

ESTIMATED_MTTR 18

CKPT_BLOCK_WRITES 27

OPTIMAL_LOGFILE_SIZE

ESTD_CLUSTER_AVAILABLE_TIME

WRITES_MTTR 0

WRITES_LOGFILE_SIZE 0

WRITES_LOG_CHECKPOINT_SETTINGS 0

WRITES_OTHER_SETTINGS 0

WRITES_AUTOTUNE 104

WRITES_FULL_THREAD_CKPT 0

From the v $ instance_recovery view, you can see the estimated average restoration time (MTTR) parameter of the current database: ESTIMATED_MTTR.

ESTIMATED_MTTR is estimated based on the amount of data in the Dirty Buffer and the number of log blocks. This parameter value tells us that if the database is lost at this time, it will take time to recover the instance.

In the V $ instance_revovery view, TARGET_MTTR represents the expected recovery time. Generally, the parameter should be equal to the value set by the FAST_START_MTTR_TARGET parameter (however, if the value defined by the FAST_START_MTTR_TARGET parameter is extremely large or small, TARGET_MEER may not be equal to the setting of FAST_START_MTTR_TARGET ).

When ESTIMATED_MTTR is close to or exceeds the value of the FAST_START_MTTR_TARGET parameter (v $ instance_recovery TARGET_MTTR), the system initiates a checkpoint. After the checkpoint is written, the system recovery information is recalculated:

RECOVERY_ESTIMATED_IOS 24

ACTUAL_REDO_BLKS 43

TARGET_REDO_BLKS 184320

LOG_FILE_SIZE_REDO_BLKS 184320

LOG_CHKPT_TIMEOUT_REDO_BLKS

LOG_CHKPT_INTERVAL_REDO_BLKS

FAST_START_IO_TARGET_REDO_BLKS

TARGET_MTTR 0

ESTIMATED_MTTR 18

CKPT_BLOCK_WRITES 73

OPTIMAL_LOGFILE_SIZE

ESTD_CLUSTER_AVAILABLE_TIME

WRITES_MTTR 0

WRITES_LOGFILE_SIZE 0

WRITES_LOG_CHECKPOINT_SETTINGS 0

WRITES_OTHER_SETTINGS 0

WRITES_AUTOTUNE 183

WRITES_FULL_THREAD_CKPT 0

In a busy system, you may find ESTIMATED_MTTR> TARGET_MTTR, which may be because DBWR is busy writing, or even the Checkpoint cannot be completed in time.

 

4. Oracle 10 Gb automatic checkpoint Adjustment

Starting from Oracle 10 Gb, the database can automatically adjust the checkpoint. Using the automatically adjusted checkpoint, the Oracle database can use the system's low I/O load period to write dirty data in the memory, this improves database efficiency. Therefore, when the database administrator sets unreasonable checkpoint-related parameters in a timely manner, Oracle can still control the database's Crash Recovery ing time to a reasonable range through automatic adjustment.

When the FAST_START_MTTR_TARGET parameter is not set, the automatic checkpoint adjustment takes effect.

Generally, if you must strictly control the instance or node recovery time, you can set FAST_START_MTTR_TARGET as the expected time value. If the recovery time is not strictly controlled, you can not set the FAST_START_MTTR_TARGET parameter, to enable the auto-tuning feature of Oracle 10 Gb.

After the FAST_START_MTTR_TARGET parameter is canceled:

SQL> show parameter fast_start_mttr

NAME TYPE VALUE

-----------------------------------------------------------------------------

Fast_start_mttr_target integer 0

When starting the database, you can see the following information from the alter file:

Thu Nov 17 20:27:23 2011

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Check the v $ instance_recovery view to check the 10 Gb change in Oracle:

SQL> select * from v $ instance_recovery;

RECOVERY_ESTIMATED_IOS 53

Actual_redoc_blks 376

TARGET_REDO_BLKS 184320

LOG_FILE_SIZE_REDO_BLKS 184320

LOG_CHKPT_TIMEOUT_REDO_BLKS

LOG_CHKPT_INTERVAL_REDO_BLKS

FAST_START_IO_TARGET_REDO_BLKS

TARGET_MTTR 0

ESTIMATED_MTTR 18

CKPT_BLOCK_WRITES 27

OPTIMAL_LOGFILE_SIZE

ESTD_CLUSTER_AVAILABLE_TIME

WRITES_MTTR 0

WRITES_LOGFILE_SIZE 0

WRITES_LOG_CHECKPOINT_SETTINGS 0

WRITES_OTHER_SETTINGS 0

WRITES_AUTOTUNE 104

WRITES_FULL_THREAD_CKPT 0

In the preceding view, the WRITES_AUTOTUNE field data refers to the number of write operations performed by the checkpoint automatically adjusted, while CK_BLOCK_WRITES refers to the number of blocks written by the checkpoint.

Regarding the checkpoint mechanism, we focus on the principle. As for the implementation of specific algorithms, we do not need to investigate too much. As long as we understand the principles and rules, understanding Oracle is easy.

Oracle's algorithm improvement is a kind of optimization, and the adjustment and optimization of the database is nothing more than this. Learning from Oracle optimization has great benefits for understanding and optimizing the Oracle database.

5. Obtain the checkpoint information from the Control File

In the control file dump, you can see the progress record of the checkpoint process:

**************************************** ***********************************

CHECKPOINT PROGRESS RECORDS

**************************************** ***********************************

(Size = 8180, compat size = 8180, section max = 11, section in-use = 0,

Last-recid = 0, old-recno = 0, last-recno = 0)

(Extent = 1, blkno = 2, numrecs = 11)

THREAD #1-status: 0x2 flags: 0x0 dirty: 34

Low cache rba :( 0x23. 19d5. 0) on disk rba :( 0x23. 1a68. 0)

On disk scn: 0x0000. 000d847d 11/14/2011 15:25:37

Resetlogs scn: 0x0000. 0006ce7b 11/10/2011 22:40:23

Heartbeat: 767211774 mount id: 1294947385

THREAD #2-status: 0x0 flags: 0x0 dirty: 0

Low cache rba :( 0x0. 0.0) on disk rba :( 0x0. 0.0)

Here, the low cache rba (Revovery block address) refers to the lowest RBA address in the Cache. It must be restored from here when the instance is recovered or crashed.

On disk dba is the highest redo value On the disk. The application redo value must at least reach this value during restoration.