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

A checkpoint is a database event that writes modified data from the cache to disk and updates control files and data files.

Checkpoints are divided into three categories:

1) Local checkpoint: a single instance performs a checkpoint operation of all data files in the database, and all dirty caches belonging to this instance are written to the data file.

Trigger command:

Svmrgrl > alter system checkpoint local

This command shows the trigger of a local checkpoint.

2) Global checkpoint: all instances (corresponding to parallel data servers) perform a checkpoint operation of all data files in the database, and all dirty caches belonging to this instance are written to data files.

Trigger command

Svrmgrl > alter system checkpoint global

This command displays the trigger of a global checkpoint.

3) File checkpoint: all instances need to perform a checkpoint operation of the data file set, such as the hot backup command alter tablespace USERS begin backup or the tablespace offline command alter tablespace USERS offline, which will perform a checkpoint operation of all data files belonging to the USERS tablespace.

Checkpoint processing steps:

1) get the instance status queue: the instance status queue is obtained when the instance state changes, and ORACLE acquires this queue to ensure that the database is open during checkpoint execution

2) get current checkpoint information: get the structure of checkpoint record information, including 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) Cache identification: identify all dirty cache areas. When a checkpoint finds a dirty cache area, it will be marked as requiring refreshing. The identified dirty cache area is written by the system process DBWR, and the contents of the dirty cache area are written to the data file.

4) Dirty cache refresh: after writing all dirty caches to disk, the DBWR process sets a flag to indicate that the write operation from dirty cache to disk has been completed. The system processes LGWR and CKPT processes will continue to check until the end of the DBWR process

5) update 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 header (when the current checkpoint information is obtained) is not updated:

1) the data file is not in hot backup mode, so ORACLE will not know when the operating system will read the file header, and the backup copy must have a checkpoint SCN at the beginning of the copy.

ORACLE keeps a checkpoint counter in the data file header, ensures that the current version of the data file is used in normal operation, and prevents the wrong version of the data file from being restored during recovery; even in the hot backup mode, the counter is still incremented; the checkpoint counter of each data file is also retained in the corresponding data file item of the control file.

2) when you check that the SCN is less than the checkpoint SCN in the file header, this indicates that the changes caused by the checkpoint have been written to disk, which may occur if a hot backup quick checkpoint updates the file header during the global checkpoint process. It should be noted that ORACLE captures the checkpoint SCN before much work is actually done with checkpoint processing, and is likely to be interrupted by a command such as the hot backup command alter tablespace USERS begin backup for quick checkpoint processing.

ORACLE verifies its data consistency before updating the data file, and when the verification is complete, the data file header is updated to reflect the current checkpoint; both the unverified data file and the data file with write errors are ignored; if the log file is overwritten, the file may need to be media restored, in which case the ORACLE system process DBWR takes the data file offline.

Description of checkpoint algorithm:

The dirty cache is linked with a new queue, called the checkpoint queue. For each change to the cache, there is a redo value associated with it. Checkpoint queues contain dirty log caches that are sorted by their location in the log file, that is, in checkpoint queues, caches are sorted by their low redo values. It should be noted that because the cache is linked to the queue in the order in which it became dirty for the first time, if there are other changes to the cache before it is written out, the link cannot be changed accordingly. once the cache is linked to the checkpoint queue, it stays in this position until it is written out.

In response to the checkpoint request, the ORACLE system process DBWR writes out the cache in ascending order of the low repeat value of the queue. Each checkpoint request specifies a redo value. Once the cache redo value written by DBWR is equal to or the heavy rain checkpoint redo value, the checkpoint processing is completed and will be recorded to the control file and data file.

Because the cache on the checkpoint queue is sorted according to the low redo value, and DBWR also writes out the checkpoint cache in the order of low redo value, multiple checkpoint requests may be active. When DBWR writes out the cache, check the consistency of the cache redo value at the front of the checkpoint queue with the checkpoint redo value. If the redo value is less than the low redo value of the cache before the checkpoint queue, all checkpoint requests This means that the processing is complete. DBWR continues to write out the checkpoint cache when there are outstanding active checkpoint requests.

Features of the algorithm:

1) DBWR can know exactly which caches need to be written to meet checkpoint requests

2) make sure to point to the earliest (with the lowest redo value) checkpoint each time you write a checkpoint

3) multiple checkpoint requests can be distinguished according to the checkpoint redo value, and then processed in their order.

1. The nature of checkpoints (Checkpoint)

Many documents describe Checkpint as very complex, which brings obstacles to our correct understanding of checkpoints, as a result checkpoints have now become a very complex issue. In fact, a checkpoint is just a database event, and the fundamental meaning of its existence is to reduce crash recovery (Crash Recovery) time.

When you modify the data, you need to first read the data into memory (Buffer Cache). While modifying the data, Oracle will record the redo information (Redo) for recovery. Because of the redo information, Oracle does not need to write the changed data back to disk immediately on commit (it is inefficient to write immediately), and redo (Redo) exists so that the data can be recovered after the database crash.

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

In this process, people are usually most concerned about how long it will take for the database to be opened. That is, how many redo logs need to be read to complete the roll forward. Of course, users want this time as short as possible, and Oracle is constantly optimizing this process through various means to shorten the recovery time.

Checkpoints exist to shorten this recovery time.

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

The relevant knowledge of Oracle SCN can refer to another article of mine: how to get 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 hh34: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 modified by the checkpoint has been written back to disk, and the corresponding redo records in the redo log file are no longer useful for crash / instance recovery.

The following figure marks three log groups, assuming that the database completes and records the last checkpoint at T1 time point, and the database Crash at T2 time. Then the next time the database starts, the Redo before T1 time point no longer needs to be restored, and what Oracle needs to reapply is the redo log (Redo) generated by the database between T1 and T2.

As can be easily seen in the above figure, the frequency of checkpoints has a great impact on the recovery time of the database. If the frequency of checkpoints is high, there are relatively few redo logs to be applied during recovery, and the check time can be shortened. However, it should be noted that the relativity of the internal operation of the database is very strong, and ordinary checkpoints in Mandarin will also bring performance problems, especially those databases that are updated frequently. Therefore, the optimization of the database is a systematic project and should not be hasty.

Furthermore, we can know that if Oracle can make the SCN primary key of the checkpoint approach the latest update of Redo when performance permits, then we can finally get an optimal balance point, so that Oracle can minimize the recovery time.

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

two。 Regular checkpoint and incremental checkpoint

In order to distinguish, before Oracle8, the real-time checkpoint of Oracle is usually called regular checkpoint (Conventional Checkpoint). This kind of checkpoint starts according to certain conditions (log_checkpoint_interval, log_checkpoint_timeout parameter setting and log switch, etc.).

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

Compared with the previous version, in the new version, Oracle mainly introduces the checkpoint queue (Checkpoinnt Queue) mechanism. Within the database, every dirty data block will be moved to the checkpoint queue and arranged according to the order of Low RBA (comparing the Redo Byte Address corresponding to the block modification for the first time). If a data block has been modified many times, the order of the database on the checkpoint queue will not change.

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

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

Moreover, through incremental checkpoints, DBWR can continue to write out, thus avoiding peak writes from regular checkpoints to the expropriation of IWeiO countries. The significance of this improvement can be clearly seen in the following figure.

In the database, incremental checkpoints are implemented through the Fast-Start Checkpointing feature, which is included in the Fast-Start Fault Recovery component of Oracle Enterprise Edition starting with Oracle 8i. Learn about this feature by querying the v$option view:

SQL > select * from v$version where rownum 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 contains three main features, which can speed up the recovery of the system after failure and improve the availability of the system.

Fast-Start Checkpointing

Fast-Start On-Demand Rollback

Fast-Start Parallel Rollback

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

3.FAST_START_MTTR_TARGET

The FAST_START_MTTR_TARGET parameter, introduced from Oracle 9i, defines the time, in seconds, for the database to perform Crash recovery, with values ranging from 0mm to 3600 seconds.

In Oracle 9i, Oracle recommends setting this parameter instead of the FAST_START_IO_TARGE, LOG_CHECKPOINT_TIMEROUT, and LOG_CHECKPOINT_INSTERVAL parameters.

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

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 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 the number of operations that the system needs to perform, such as the number of times it needs to perform under different FAST_START_MATTR_TARGET settings. Users can adjust the FAST_START_MTTR_TARGET accordingly according to the suggestions of the database.

The phone with this suggestion message is controlled by the initialization parameter statistics_level introduced by Oracle 9i. When this parameter is set to Typical or ALL, the MTTR suggestion message is used 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_TARGET_ADVICE NO

The current instance recovery status of the database can be obtained from 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 estimated average recovery time (MTTR) parameter for the current database: ESTIMATED_MTTR.

The estimate of ESTIMATED_MTTR is based on the amount of data and the number of log blocks in Dirty Buffer, which tells us how long it will take for instance recovery if the database is losing money at this time.

In the V$instance_revovery view, TARGET_MTTR represents the expected recovery time, and usually the change parameter should be equal to the setting value of the FAST_START_MTTR_TARGET parameter (but if the value defined by the FAST_START_MTTR_TARGET parameter is very large or minimal, TARGET_MEER may not be equal to the setting of FAST_START_MTTR_TARGET).

When ESTIMATED_MTTR approaches or exceeds the FAST_START_MTTR_TARGET parameter setting (v$instance_recovery TARGET_MTTR), a checkpoint is triggered, and after writing out, 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, ESTIMATED_MTTR > TARGET_MTTR may be observed, which may be because DBWR is busy writing out, or even Checkpoint can not be completed in time.

4. Oracle 10g automatic checkpoint adjustment

Starting from Oracle 10g, the database can achieve automatically adjusted checkpoints. Using the automatically adjusted checkpoints, the Oracle database can take advantage of the system's low Imax O load period to write out dirty data in memory, thus improving the efficiency of the database. Therefore, even if the database administrator sets unreasonable checkpoint-related parameters, Oracle can still control the Crash Recovery time of the database within a reasonable range through automatic adjustment.

Automatic checkpoint adjustment takes effect when the FAST_START_MTTR_TARGET parameter is not set.

In general, if the instance or node recovery time must be strictly controlled, you can set FAST_START_MTTR_TARGET to the desired time value; if the recovery time is not strictly controlled, you can enable the auto-tuning feature of Oracle 10g by not setting the FAST_START_MTTR_TARGET parameter.

When canceling the FAST_START_MTTR_TARGET parameter setting:

SQL > show parameter fast_start_mttr

NAME TYPE VALUE

-

Fast_start_mttr_target integer 0

When you start the database, you can see the following information in the alter file:

Thu Nov 17 20:27:23 2011

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Examining the v$instance_recovery view, you can see that the Oracle 10g has changed:

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

In the above view, the WRITES_AUTOTUNE field data refers to the number of writes performed due to automatic adjustment of the checkpoint, while CK_BLOCK_WRITES refers to the number of Block written out due to the checkpoint.

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

The algorithm improvement of Oracle is a kind of optimization, and so is the adjustment and optimization of database. Learning from the optimization of Oracle is of great benefit to understanding and optimizing Oracle database.

5. Get checkpoint information from a control file

In the dump of the control file, you can see a record of the progress 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)

The low cache rba (Revovery block address) here refers to the lowest RBA address in the Cache, which needs to be recovered from here during instance recovery or crash recovery.

On disk dba is the highest redo value on disk, which must be at least achieved by applying redo during recovery.

In addition to checkpoint queues (CKPTQ), there is another queue in the database that is related to checkpoints: file checkpoint queues (FILE QUEUE), commonly known as FILEQ. The introduction of file checkpoints provides the performance of tablespace-related checkpoints. Each Dirty Buffer is linked to the two queues at the same time. The CKPTQ contains all the Buffer,FILEQ that needs to be checkpointed by the instance, including the checkpoint Buffer that belongs to a specific file, and each file contains a file queue. You need to use FILEQ when executing the tablespace checkpoint request. Usually, you will touch the publication space checkpoint when performing operations such as Offline on the tablespace. Both CKPTQ and FILEQ are bi-directional linked lists, and two address information is recorded in each queue, namely the address information of the previous block and the next block of Buffer. Note that only Dirty Buffer contains CKPTQ information, otherwise it is NULL, which is similar to "ckptq: [null] fileq: [null]".

Working mechanism of checkpoint (checkpoint)

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

Checkpoints are divided into three categories:

1) Local checkpoint: a single instance performs a checkpoint operation of all data files in the database, and all dirty caches belonging to this instance are written to the data file.

Trigger command:

Svmrgrl > alter system checkpoint local

This command shows the trigger of a local checkpoint.

2) Global checkpoint: all instances (corresponding to parallel data servers) perform a checkpoint operation of all data files in the database, and all dirty caches belonging to this instance are written to data files.

Trigger command

Svrmgrl > alter system checkpoint global

This command displays the trigger of a global checkpoint.

3) File checkpoint: all instances need to perform a checkpoint operation of the data file set, such as the hot backup command alter tablespace USERS begin backup or the tablespace offline command alter tablespace USERS offline, which will perform a checkpoint operation of all data files belonging to the USERS tablespace.

Checkpoint processing steps:

1) get the instance status queue: the instance status queue is obtained when the instance state changes, and ORACLE acquires this queue to ensure that the database is open during checkpoint execution

2) get current checkpoint information: get the structure of checkpoint record information, including 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) cache identification: when the data is modified in buffer cache, it will be automatically added to the dirty buffer queue of Checkpoint Queue.

4) dirty cache refresh: when a checkpoint occurs, it will find the largest LRBA so far in the dirty buffer queue in CKPTQ, and notify the DBWR process to write all dirty buffers to disk. After completion, set a flag to indicate that the write operation from dirty cache to disk has been completed in order to refresh the dirty buffer queue (DML can continue at this time). The system processes LGWR and CKPT processes will continue to check until the end of the DBWR process

5) update 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 header (when the current checkpoint information is obtained) is not updated:

1) the data file is not in hot backup mode, so ORACLE will not know when the operating system will read the file header, and the backup copy must have a checkpoint SCN at the beginning of the copy.

ORACLE keeps a checkpoint counter in the data file header, ensures that the current version of the data file is used in normal operation, and prevents the wrong version of the data file from being restored during recovery; even in the hot backup mode, the counter is still incremented; the checkpoint counter of each data file is also retained in the corresponding data file item of the control file.

2) when you check that the SCN is less than the checkpoint SCN in the file header, this indicates that the changes caused by the checkpoint have been written to disk, which may occur if a hot backup quick checkpoint updates the file header during the global checkpoint process. It should be noted that ORACLE captures and checks SCN before a lot of work is actually done with checkpoint processing, and is likely to be subjected to a hot backup command such as

Command interruptions when alter tablespace USERS begin backup performs quick checkpoint processing.

ORACLE verifies its data consistency before updating the data file, and when the verification is complete, the data file header is updated to reflect the current checkpoint; both the unverified data file and the data file with write errors are ignored; if the log file is overwritten, the file may need to be media restored, in which case the ORACLE system process DBWR takes the data file offline.

Checkpoint algorithm description:

The dirty cache is linked with a new queue, called the checkpoint queue. For each change to the cache, there is a redo value associated with it. Checkpoint queues contain dirty log caches that are sorted by their location in the log file, that is, in checkpoint queues, caches are sorted by their LRBA. Need to note the algorithm characteristics:

3) multiple checkpoint requests can be distinguished according to the checkpoint redo value, and then processed in their order.

1) DBWR can know exactly which caches need to be written to meet checkpoint requests

2) each checkpoint write is guaranteed to point to the earliest (with the lowest redo value) checkpoint; that is, because the cache is linked to the queue in the order in which it became dirty for the first time, if there are other changes to it before the cache is written out, the link cannot be changed accordingly, and once the cache is linked to the checkpoint queue, it stays in this position until it is written out.

In response to the checkpoint request, the ORACLE system process DBWR writes out the cache in ascending order of the queue's LRBA. Each checkpoint request specifies a redo value. Once the cache redo value written by DBWR is equal to or the heavy rain checkpoint redo value, the checkpoint processing is completed and will be recorded to the control file and data file.

Because the cache on the checkpoint queue is sorted according to the low redo value, and DBWR also writes out the checkpoint cache in the order of low redo value, multiple checkpoint requests may be active. When DBWR writes out the cache, check the consistency of the cache redo value at the front of the checkpoint queue with the checkpoint redo value. If the redo value is less than the low redo value of the cache before the checkpoint queue, all checkpoint requests This means that the processing is complete. DBWR continues to write out the checkpoint cache when there are outstanding active checkpoint requests.

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.