Oracle Checkpoint (Checkpoint)

A checkpoint is a database event that writes the modified data from the cache to the disk and updates the control file and the data file.
Checkpoints fall into three categories:
1) Local checkpoint: A single instance performs a checkpoint operation for all data files in the database, and all dirty buffers belonging to this instance are written to the data file.
Trigger command:
Svmrgrl>alter System checkpoint Local;
This command shows the triggering of a local checkpoint.
2) Global Checkpoint: All instances (corresponding parallel data servers) perform a checkpoint operation for all data files in the database, and all dirty buffers belonging to this instance are written to the data file.
Trigger command
Svrmgrl>alter System checkpoint Global;
This command shows the triggering of a global checkpoint.
3) file checkpoint: All instances need to perform a checkpoint operation of the data file set, such as using the hot backup command alter TABLESPACE users begin backup, or the table space offline command alter TABLESPACE users offline, A checkpoint operation will be performed for all data files that belong to the users table space.

Checkpoint processing steps:
1) Gets the instance status queue: the instance state queue is obtained when the instance state transitions, and Oracle obtains this queue to ensure that the database is open during checkpoint execution;
2) Gets the current checkpoint information: Gets the structure of the checkpoint record information, which includes the current checkpoint time, the active thread, the current thread for checkpoint processing, and the address information of the recovery cutoff point in the log file;
3) Buffer identification: Identify all dirty buffers, when the checkpoint found a dirty buffer to identify it to be refreshed, the dirty buffer identified by the system process DBWR write operations, the contents of the dirty buffer to write data files;
4) Dirty cache refresh: After the DBWR process writes all dirty buffers to the disk, a flag is set to identify the write operations that have completed dirty buffers to disk. The system process LGWR and CKPT processes will continue to be checked until the end of the DBWR process;
5) Update the control files and data files.
Note: The control file and data file header contain checkpoint structure information.
In both cases, the checkpoint information in the file header (when the current checkpoint information is obtained) is not updated:
1) The data file is not in hot backup mode, at this time Oracle will not know when the operating system will read the file header, and the backup copy at the beginning of the copy must have a checkpoint SCN;
Oracle maintains a checkpoint register in the data file header, ensuring that the current version of the data file is used during normal operation and prevents the incorrect version of the data file from being recovered, even in the case of a hot backup, where the counter is incremented, and the checkpoint counter for each data file Also remains in the control file relative to the data file entry.
2) Check that the SCN is less than the checkpoint SCN in the file header, this indicates that the changes generated by the checkpoint have been written to disk, and during the processing of the global checkpoint, this may occur if a hot backup quick checkpoint is updating the file header. It should be noted that Oracle captures the check SCN before actually doing a lot of work on checkpoint processing and is likely to be interrupted by a command like the hot backup command alter TABLESPACE USERS begin backup for fast checkpoint processing.
Prior to the data file update, Oracle verifies its data consistency and, when the validation is complete, updates the header of the data file to reflect the current checkpoint, and the data files that are not validated for the data file and the error that was written are ignored, and if the log file is overwritten, the file may require media recovery. In this case, the Oracle system process DBWR the data file offline.
Checkpoint Algorithm Description:
Dirty buffers are linked with a new queue, called a checkpoint queue. For each change in the buffer, there is a redo value associated with it. Checkpoint queues contain dirty log buffers that are sorted by their location in the log file, that is, in the checkpoint queue, the buffers are sorted by their low redo values. It is important to note that since the buffer is linked to the queue in the first order of dirty, the link cannot be changed if it has been changed before the buffer is written, and once the buffer is linked to the checkpoint queue, it stays in that position until it is written out.
The Oracle system process DBWR writes buffers in ascending order of the low redo values of this queue when responding to a checkpoint request. Each checkpoint request specifies a redo value, and once DBWR writes out a cache redo value equal to or heavy rain checkpoint redo value, the checkpoint processing is complete and will be logged to the control file and data file.
Because the buffers on the checkpoint queue are sorted by low redo values, and the DBWR also writes out checkpoint buffers in the order of low redo values, there may be multiple checkpoint requests active, and when DBWR writes out buffers, the buffer redo values at the front of the checkpoint queue are checked for consistency with the checkpoint redo values, If the redo value is less than all checkpoint requests for the low redo value of the pre-checkpoint queue buffer, the processing is complete. DBWR continues to write checkpoint buffers when there is an incomplete active checkpoint request.
Algorithm Features:
1) DBWR can know exactly which buffers to write for satisfying the checkpoint request;
2) Ensure that the checkpoint that completes the oldest (with the lowest redo value) is guaranteed to be written at each checkpoint.
3) You can differentiate multiple checkpoint requests based on the checkpoint redo values, and then complete the processing in their order.

1. The nature of the checkpoint (Checkpoint)

Many documents describe Checkpint as being very complex, which poses a barrier to our correct understanding of checkpoints, and now the checkpoint becomes a very complex issue. In fact, the checkpoint is just a database event, and its fundamental significance is to reduce the time of crash Recovery (Crash Recovery) .

When modifying data, it is necessary to first read the data into memory (Buffer Cache), and while modifying the data, Oracle records the Redo information (Redo) for recovery. Because of the presence of the redo information, Oracle does not need to write the changed data back to disk immediately when it commits (it is inefficient to write immediately), and the Redo (Redo) exists just so that the data can be recovered after the database crashes.

In the most common case, the database can be crash because of a power outage, so the data that has been modified in memory and not written to the file will be lost. After the next database startup, Oracle can replay transactions by redo log (Redo), that is, roll forward, restore the database to its pre-crash state, and the database can be opened for use, after which Oracle can roll back uncommitted data.

In this process, the most common concern is how long the database will take to open. That is, how many redo logs need to be read to complete the roll forward. Of course, users hope that the shorter the better, Oracle is through a variety of means to continuously optimize the process, shorten the recovery time.

Checkpoints exist to shorten the recovery time.

When the checkpoint occurs (the SCN at this time is called the checkpoint SCN), Oracle notifies the DBWR process to write the modified data, that is, the dirty data (Dirty data) before the checkpoint SCN, to the disk from the buffer cache. When the write is complete, the CKPT process updates the control file and the data file header, logs the checkpoint information, and identifies the change.

Knowledge of Oracle SCN can be found in one of my other articles: understanding of DBA Entry Oracle SCN (System change number)

The 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 2011-11-16 16:06:06

2 913306 2011-11-16 16:06:06

3 913306 2011-11-16 16:06:06

4 913306 2011-11-16 16:06:06

Sql> select dbid,checkpoint_change# from V$database;

DBID checkpoint_change#

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

1294662348 913306

After the checkpoint is complete, the data that was modified before this checkpoint has been written back to the disk, and the corresponding redo record in the redo log file is no longer useful for crash/instance recovery.

3 log groups are marked, assuming that the database completes and records the last checkpoint at T1 Point in time, and the database crash at T2 time. Then the next time the database starts, Redo no longer needs to be resumed at the T1 point, and Oracle needs to reapply the redo log (Redo) that the database generated between the points of T1 to T2.

It can be easily seen that the frequency of checkpoints has a great impact on the recovery time of the database, and if the checkpoint frequency is high, then the redo log that needs to be applied when recovering is relatively small, and the check time can be shortened. However, it is important to note that the relative nature of the internal operations of the database is extremely strong, and the common language checkpoint also brings performance problems, especially for databases that are frequently updated. So the optimization of the database is a systematic project, can not be hasty.

Further, if Oracle can allow the SCN primary key of the checkpoint to approximate the latest update of the redo in the event of performance permitting, the ultimate balance can be achieved, allowing Oracle to minimize recovery time.

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

2. General checkpoints and incremental checkpoints

To differentiate between Oracle8, Oracle real-time checkpoints are often referred to as regular checkpoints (conventional Checkpoint), which are based on certain conditions (Log_checkpoint_interval, Log_ Checkpoint_timeout parameter settings and log switch, etc.).

Starting with Oracle 8, Oracle introduced the concept of an incremental checkpoint (Inctrmental Checkpoint).

In the new version, Oracle has introduced a checkpoint queue (checkpoinnt) mechanism, in contrast to the previous version, where each dirty block is moved to the checkpoint queue, in the order of the low RBA (the first comparison data block modifies the corresponding redo Byte Address), and the order of the database on the checkpoint queue does not change if a block of data has been modified several times.

When the checkpoint is executed, the DBWR is written out in the order of low RBA from the checkpoint queue, so that the instance checkpoint can be continuously promoted and phased, and the CKPT process uses a very lightweight control file update protocol to write the current minimum RBA to the control file.

Because incremental checkpoints can be continuous, checkpoint RBA can be closer to the last state of the database than regular checkpoints, which can greatly reduce recovery time in instance recovery of a database.

Furthermore, with incremental checkpoints, the DBWR can be written continuously, thus avoiding peak writes from regular checkpoints for the country requisition of I/O, which can be clearly seen in the sense of this improvement.

In the database, the incremental checkpoint is implemented with the Fast-start checkpointing feature, starting with Oracle 8i, which includes the Oracle Enterprise Edition Fast-start Fault recovery components, through the query 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

The component contains 3 key features that can speed up system recovery after a failure and improve system availability.

Fast-start checkpointing;

Fast-start On-demand Rollback;

Fast-start Parallel Rollback;

The Fast-start checkpointing feature is primarily implemented in Oracle 8i by parameter fast_start_io_target, in Oracle 9i, where Fast-start checkpointing is mainly via parameters Fast_ Start_mttr_target to achieve.

3.fast_start_mttr_target

The Fast_start_mttr_target parameter is introduced starting with Oracle 9i, which defines the time the database is crash restored, in seconds, and within 0-3,600 seconds.

In Oracle 9i, Oracle recommends setting this parameter in place of the Fast_start_io_targe, Log_checkpoint_timerout, and Log_checkpoint_insterval parameters.

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

Beginning with Oracle 9i R2, Oracle introduced a new view to provide MTTR recommendations:

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 actions such as the number of I/OS that the system needs to perform under different fast_start_mattr_target settings. The user can adjust the fast_start_mttr_target according to the suggestion of the database.

This recommendation information for the mobile phone receives the Oracle 9i new introduced initialization parameter Statistics_level control, when this parameter is set to typical or all, the MTTR recommendation information is called by the phone:

Sql> Show Parameter Statistics_level

NAME TYPE VALUE

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

Statistics_level string Typical

You can also query the current settings of Mttr_advice through the V$statistics_level view:

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

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

Mttr Advice predicts the impact of different MTTR settings on number of physical I/Os enabled enabled typical V$mttr_targe T_advice NO

The current instance recovery state of the database can be obtained through the View V$instance_recovery query:

Sql> select * from V$instance_recovery;

Recovery_estimated_ios 53

Actual_redo_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 average recovery time (MTTR) parameter estimated by the current database: Estimated_mttr.

The estimated value of estimated_mttr is based on the amount of data in dirty Buffer and the number of log blocks, which tells us that if the database is lost at this time, then the instance recovery will take time.

In the V$instance_revovery view, target_mttr represents the desired recovery time, and the parameter should normally be equal to the Fast_start_mttr_target parameter setting value (but if fast_start_mttr_ The value defined by the TARGET parameter is large or small, and target_meer may not be equal to the Fast_start_mttr_target setting).

When estimated_mttr approaches or exceeds the Fast_start_mttr_target parameter setting (V$instance_recovery target_mttr), the system will trigger a checkpoint, and after the write-out, the system recovery information will be recalculated:

View Code

In a busy system, estimated_mttr>target_mttr may be observed, perhaps because DBWR is busy writing out, or even appearing, a situation in which checkpoint cannot be completed in time.

4. Oracle 10g automatic checkpoint adjustment

Starting with Oracle 10g, the database can implement auto-tuning checkpoints, and with auto-tuning checkpoints, Oracle databases can improve database efficiency by leveraging the system's low I/O load windows to write dirty data in memory. As a result, timely database administrators set unreasonable checkpoint-related parameters, and Oracle still has the ability to control the crash recovery time of the database within a reasonable range through automatic tuning.

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

In general, if you must strictly control the instance or node recovery time, you can set Fast_start_mttr_target to the expected time value, and if the recovery time is not tightly controlled, you can enable the Oracle by not setting the Fast_start_mttr_target parameter 10g auto-tuning feature.

After you cancel the Fast_start_mttr_target parameter setting:

View Code

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

View Code

Check the V$instance_recovery view to see the changes to Oracle 10g:

View Code

In the above view, the Writes_autotune field data refers to the number of write-down times that are performed by the auto-tuning checkpoint, while ck_block_writes refers to the BLOCK that is written by the checkpoint.

Regarding the mechanism of checkpoint, we focus on the principle, as for the specific algorithm implementation, do not need to pursue too much, as long as the understanding of this principle of the rules, understand Oracle will become easy things.

Oracle's algorithmic improvement is an optimization, and the tuning of the database is not the case, and the optimization of Oracle is a great benefit to understanding and optimizing the Oracle database.

5. Get checkpoint information from control files

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

View Code

Here the low cache RBA (Revovery block address) refers to the lowest RBA addresses in the cache, which need to be resumed from here in case recovery or crash recovery.

On disk DBA is the highest redo value on disks, and at least this value is applied to redo at the time of recovery.

In addition to the checkpoint queue (CKPTQ), there is another queue and checkpoint in the database, which is the file checkpoint queue, commonly referred to as FILEQ, and the introduction of file checkpoints provides the performance of table space-related checkpoints. Each dirty buffer is linked to both queues at the same time, CKPTQ contains the instance all Buffer,fileq that need to perform checkpoints contain the checkpoint buffer that is required to perform a particular file, each containing a file queue, You need to use Fileq when you perform a tablespace checkpoint request, and you typically touch the publishing space checkpoint when you perform offline operations on the tablespace. Both CKPTQ and Fileq are doubly linked lists, with two address information recorded in each queue, namely the address information for the previous and next buffer. Note that only dirty buffer contains the CKPTQ information, otherwise NULL, the information is similar to "Ckptq:[null]fileq:[null".

Checkpoint (checkpoint) working mechanism

The checkpoint is a database event that writes the modified data from the cache to the disk and updates the control and data files, which are summarized as follows:

Checkpoints fall into three categories:
1) Local checkpoint: A single instance performs a checkpoint operation for all data files in the database, and all dirty buffers belonging to this instance are written to the data file.
Trigger command:
Svmrgrl>alter System checkpoint Local;
This command shows the triggering of a local checkpoint.
2) Global Checkpoint: All instances (corresponding to parallel data ServerPerforms a checkpoint operation on all data files of the database, and all dirty buffers belonging to this instance are written to the data file.
Trigger command
Svrmgrl>alter System checkpoint Global;
This command shows the triggering of a global checkpoint.
3) file checkpoint: All instances need to perform a checkpoint operation of the data file set, such as using hot Backupcommand alter TABLESPACE users begin backup, or the table space Offline command alter TABLESPACE USERS offline, will perform a checkpoint operation for all data files that belong to the users table space.

Checkpoint processing steps:
1) Gets the instance status queue: the instance state queue is obtained when the instance state transitions, and Oracle obtains this queue to ensure that the database is open during checkpoint execution;
2) Gets the current checkpoint information: Gets the structure of the checkpoint record information, which includes the current checkpoint time, the active thread, the current thread for checkpoint processing, and the address information of the recovery cutoff point in the log file;
3) Buffer identifier: When the data is modified in buffer cache, it is automatically taken as a dirty buffer and added to the dirty buffer queue of the checkpoint queue.

4) Dirty buffer refresh: When the checkpoint occurs, the dirty buffer queue in the CKPTQ finds the largest lrba so far, and notifies the DBWR process to write all dirty buffers to disk, and then sets a flag to identify the finished write operation to the disk. To flush the dirty buffer queue (DML can continue at this time). The system process LGWR and CKPT processes will continue to be checked until the end of the DBWR process;

5) Update the control files and data files.
Note: The control file and data file header contain checkpoint structure information.
In both cases, the checkpoint information in the file header (when the current checkpoint information is obtained) is not updated:
1) The data file is not in hot backup mode, at this time Oracle will not know when the operating system will read the file header, and the backup copy at the beginning of the copy must have a checkpoint SCN;
Oracle maintains a checkpoint register in the data file header, ensuring that the current version of the data file is used during normal operation and prevents the incorrect version of the data file from being recovered, even in the case of a hot backup, where the counter is incremented, and the checkpoint counter for each data file Also remains in the control file relative to the data file entry.
2) Check that the SCN is less than the checkpoint SCN in the file header, this indicates that the changes generated by the checkpoint have been written to disk, and during the processing of the global checkpoint, this may occur if a hot backup quick checkpoint is updating the file header. It should be noted that Oracle captures the check SCN before actually doing a lot of work on checkpoint processing and is likely to be a hot backup command like
Alter tablespace command interrupt when USERS begin backup for fast checkpoint processing.
Prior to the data file update, Oracle verifies its data consistency and, when the validation is complete, updates the header of the data file to reflect the current checkpoint, and the data files that are not validated for the data file and the error that was written are ignored, and if the log file is overwritten, the file may require media recovery. In this case, the Oracle system process DBWR the data file offline.

Checkpoint Algorithm Description:
Dirty buffers are linked with a new queue, called a checkpoint queue. For each change in the buffer, there is a redo value associated with it. Checkpoint queues contain dirty log buffers that are sorted by their location in the log file, that is, in the checkpoint queue, the buffers are sorted by their lrba. Need to note algorithm features:
3) You can differentiate multiple checkpoint requests based on the checkpoint redo values, and then complete the processing in their order.
1) DBWR can know exactly which buffers to write for satisfying the checkpoint request;
2) The checkpoint is guaranteed to point to the oldest check point (with the lowest redo value) each time the checkpoint is written, since the buffer is linked to the queue in the first order of dirty, so if there is another change in the buffer before it is written, the link cannot be changed, and once the buffer is linked to the checkpoint queue , it stays in that position until it is written out.

The Oracle system process DBWR writes out buffers in ascending order of the Lrba of this queue when responding to a checkpoint request. Each checkpoint request specifies a redo value, and once DBWR writes out a cache redo value equal to or heavy rain checkpoint redo value, the checkpoint processing is complete and will be logged to the control file and data file.
Because the buffers on the checkpoint queue are sorted by low redo values, and the DBWR also writes out checkpoint buffers in the order of low redo values, there may be multiple checkpoint requests active, and when DBWR writes out buffers, the buffer redo values at the front of the checkpoint queue are checked for consistency with the checkpoint redo values, If the redo value is less than all checkpoint requests for the low redo value of the pre-checkpoint queue buffer, the processing is complete. DBWR continues to write checkpoint buffers when there is an incomplete active checkpoint request. Ext.: http://www.cnblogs.com/Ronger/archive/2011/12/09/2281650.html

Oracle Checkpoint (Checkpoint)