Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to understand Oracle checkpoint". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to understand Oracle checkpoint.

Checkpoint literacy

What is checkpoint?

In the database system, writing logs and writing data files are the two most IO-consuming operations in the database. In these two operations, writing data files belongs to decentralized writing, and writing log files is written sequentially. Therefore, in order to ensure the performance of the database, the database usually ensures that the logs are written to the log files before the commit is completed. On the other hand, dirty data blocks are saved in the data cache (buffer cache) and then written to the data file in batches from time to time. In other words, log write and commit operations are synchronous, while data write and commit operations are not synchronous. In this way, there is a problem that when a database crashes, it is not guaranteed that all the dirty data in the cache will be written to the data file, so the log file will be used for recovery when the instance is started. restore the database to the state it was before the crash to ensure the consistency of the data. Checkpoint is an important mechanism in this process to determine which redo logs should be scanned and applied to the restore.

Generally known as checkpoint is a database event (event), the checkpoint event is issued by the checkpoint process (LGWR/CKPT process). When the checkpoint event occurs, DBWn will write dirty blocks to disk, and the headers of data files and control files will also be updated to record checkpoint information.

The role of checkpoint

There are two main functions of checkpoint:

1. Ensure the consistency of the database, which means writing dirty data to the hard disk to ensure that the memory and the data on the hard disk are the same

two。 To shorten the time for instance recovery, it is necessary to recover the dirty data that was not written to the hard disk before the instance was shut down abnormally. If there are too many dirty blocks, the instance recovery time will be very long, and the occurrence of checkpoints can reduce the number of dirty blocks, thus increasing the instance recovery time.

Popularly speaking, checkpoint is like the automatic save of word.

Checkpoint classification

Full checkpoint (Normal checkpoint)

Incremental checkpoint (Incremental checkpoint)

Terminology related to checkpoint

Before explaining how checkpoint works, let's look at some related terms.

RBA (Redo Byte Address), Low RBA (LRBA), High RBA (HRBA)

RBA is the address of the redo log block (redo log block), which is equivalent to the ROWID in the data file, through which the redo log block is located. RBA consists of three parts:

1. Log file serial number (4 bytes)

two。 Log file block number (4 bytes)

3. Start offset bytes of the redo log record in the log block (2 bytes)

The commonly used forms of RBA are:

LRBA

The location where the redo log records generated when a dirty block in the data cache (buffer cache) is updated for the first time in the redo log file is called LRBA.

HRBA

The location where the redo log records generated when a dirty block in the data cache (buffer cache) was last updated is called HRBA in the redo log file.

Checkpoint RBA

When a checkpoint event occurs, the checkpoint process records the address of the redo log block written at that time, namely RBA, and the recorded RBA is called checkpoint RBA. Dirty blocks in the buffer cache protected by the log from the previous checkpoint RBA to the current checkpoint RBA will then be written to the data file.

Buffer checkpoint Queues (BCQ)

Oracle forms a checkpoint queue according to the LRBA order of all the dirty blocks modified in the data cache, which mainly records the time order in which the buffer cache first changed, and then the DBWn process writes the dirty blocks to the data file according to the checkpoint queue order, which ensures that the buffer that changes first can be written to the data file first. BCQ was introduced to support incremental checkpoint.

Active checkpoint Queue (ACQ)

The ACQ contains all active checkpoint requests. Every time there is a new checkpoint request, a record is added to the ACQ, and the ACQ record contains the corresponding checkpoint RBA. After the checkpoint is completed, the corresponding records will be removed from the queue.

Full checkpoint (normal checkpoint)

Complete checkpoint working process

A checkpoint operation can be divided into three different phases:

In the first phase, the checkpoint process starts a checkpoint event and records the checkpoint RBA, which is usually the current RBA.

In the second stage, the checkpoint process tells the DBWn process to write dirty blocks in all pre-checkpoint RBA buffer cache to disk.

After determining that the dirty blocks are written to disk, the checkpoint process writes / updates the checkpoint information (SCN) to the data file and the control file.

The operation of updating SCN is done by the CKPT process. After Oracle 8.0, the CKPT process is enabled by default. If the CKPT process is not enabled, then the corresponding operation will be done by the LGWR process.

When does normal checkpoint happen?

The following actions will trigger the checkpoint event:

Log switch, through ALTER SYSTEM SWITCH LOGFILE.

DBA issues the checkpoint command through ALTER SYSTEM checkpoint.

When hot standby is made to the data file, the checkpoint for the data file will also be carried out, ALTER TABLESPACE TS_NAME BEGIN BACKUP/END BACKUP.

When running ALTER TABLESPACE/DATAFILE READ ONLY.

When the SHUTDOWN command is issued.

Pay special attention to:

1. Log switching causes checkpoint events to occur, but checkpoint does not cause log switching.

two。 Log switching triggers normal checkpoint, not incremental checkpoint, but the priority of log switch checkpoint is very low. When a log switch checkpoint occurs, it does not immediately notify the DBWn process to write the data file, but this log switch checkpoint will be marked as complete when other reasons cause the checkpoint or the RBA writing to the data file to exceed the checkpoint RBA of log switch checkpoint. Update both the control file and the data file header. We can then do an experiment to verify this statement.

What is the relationship between checkpoint and SCN?

In Oracle, SCN is equivalent to its clock. In real life, we use clock to record and measure our time, while Oracle uses SCN to record and measure changes in the entire Oracle system.

Checkpoint in Oracle occurs at a specific "point in time", and SCN is used to measure this "point in time", so when a checkpoint occurs, the SCN is written to the header to record the checkpoint.

Incremental checkpoint

Incremental checkpoint working process

Because every complete checkpoint needs to write all the dirty blocks of buffer cache to the data file, this will result in a large IO consumption, and frequent full checkpoint operations will have a great impact on the performance of the system. For this reason, the concept of incremental checkpoint introduced by Oracle, dirty blocks in buffer cache will be continuously written to disk in the order of BCQ queues. At the same time, the CKPT process will check the writing progress of DBWn every 3 seconds and record the corresponding RBA information in the control file.

With incremental checkpoint, you no longer need to apply the redo log from the full checkpoint before the crash, but only from the RBA recorded in the control file, which saves recovery time.

Prerequisites for incremental checkpoint

Restore demand setting (FAST_START_IO_TARGET/FAST_START_MTTR_TARGET)

LOG_checkpoint_INTERVAL parameter valu

LOG_checkpoint_TIMEOUT parameter valu

Minimum log file size

Number of dirty blocks in buffer cache

Characteristics of incremental checkpoint

An incremental checkpoint is a continuously active checkpoint.

There is no checkpoint RBA, because this checkpoint is going on all the time, so there is no concept of checkpoint RBA involved in normal checkpoint.

Checkpoint advanced in memory only

The RBA information completed by the incremental checkpoint is recorded in the control file.

Incremental checkpoint reduces instance recovery time.

Incremental checkpoint related parameter settings

Log_checkpoint_interval

Set the number of redo log blocks between the two checkpoint (redo log blocks and system data blocks are the same). When the number of redo log blocks reaches the set value, checkpoint will be triggered.

Log_checkpoint_timeout

Set the interval between two checkpoint, and the incremental checkpoint will be triggered when the timeout value is reached. Oracle recommends that you do not use this parameter to control because the transaction size is not distributed equally in time. Setting this value to 0 disables this setting.

Fast_start_io_target

Because the number of log blocks redone by log_checkpoint_interval does not reflect the modification of dirty data blocks in buffer cache, Oracle introduces this parameter to trigger checkpoint when dirty data blocks reach a certain number, but this parameter actually controls the number of IO required for recovery.

Fast_start_mttr_target

This parameter was introduced in 9i to replace the previous three parameters. It defines the time required for instance recovery after a data block crash. Oracle is actually inherently interpreted as two parameters: fast_start_io_target and log_checkpoint_interval. If these two parameters are not explicitly specified, the calculated value will take effect.

The maximum value fast_start_mttr_target can set is 3600, that is, an hour. There is no limit to its minimum value, but this does not mean that you can set an arbitrarily small value, which is limited by the minimum dirty buffer (minimum 1000), initialization time and file opening time.

When setting this parameter, we should comprehensively consider the information of IO, capacity and CPU of the system, and make a good balance between system performance and fault recovery time.

Setting this parameter to 0 disables fast-start checkpointing, which can effectively load the system but increase the recovery time of the system.

If fast_start_io_target or log_checkpoint_interval is specified, they automatically override the value calculated by the fast_start_mttr_target parameter.

In 10g, the database can automatically adjust the execution frequency of checkpoints according to the setting values of various system parameters, so as to obtain the best recovery time and minimize the impact on the normal operation of the system. Through automatic checkpoint adjustment, Orach can write dirty blocks to data files during low IO operation, so the system can still get a reasonable system recovery time even if DBA does not set checkpoint-related parameter values or sets an unreasonable value.

The incremental checkpoint in 10g can better reflect its continuous activity. In 10g, incremental checkpoint is not triggered under a specific condition, but automatically triggered by the database according to the system parameter settings.

The difference from complete checkpoint

Full checkpoint writes checkpoint information to the control file and data file headers.

Incremental checkpoint only writes RBA information to the control file.

View the checkpoint actions of the system

We can open the trace of checkpoint by setting LOG_checkpointS_TO_ALERT to TRUE, so that we can track the operation of checkpoint.

ALTER SYSTEM SET LOG_checkpointS_TO_ALERT=TRUE

After this setting, the checkpoint of the system will be recorded in the alert_$SID.log file.

Some relevant information about the occurrence of a full checkpoint is also saved in the V$DATAFILE_HEADER, including the time when the checkpoint occurred and the number of times the corresponding SCN has been checkpoint.

Select file# NO, status, tablespace_name, name, dbms_flashback.get_system_change_number CUR_SCN

To_char (resetlogs_time, 'YYYY-MM-DD HH24:MI:SS') RST_DT, resetlogs_change# RST_SCN

To_char (checkpoint_time, 'YYYY-MM-DD HH24:MI:SS') CKPT_DT, checkpoint_change# CKPT_SCN, checkpoint_count CKPT_CNT

From v$datafile_header

/ * *

NO STATUS TABLESPACE_NAME CUR_SCN RST_DT RST_SCN CKPT_DT CKPT_SCN CKPT_CNT

-

1 ONLINE SYSTEM 533541 2008-01-12 16:51:53 446075 2008-08-04 22:03:58 532354 65

2 ONLINE UNDOTBS1 533541 2008-01-12 16:51:53 446075 2008-08-04 22:03:58 532354 28

3 ONLINE SYSAUX 533541 2008-01-12 16:51:53 446075 2008-08-04 22:03:58 532354 65

4 ONLINE USERS 533541 2008-01-12 16:51:53 446075 2008-08-04 22:03:58 532354 64

5 ONLINE EXAMPLE 533541 2008-01-12 16:51:53 446075 2008-08-04 22:03:58 532354 24

, /

Complete checkpoint

-- Let's execute one first.

ALTER SYSTEM checkpoint

Here are the results of the data in the alert file

Mon Aug 4 22:22:08 2008

Beginning global checkpoint up to RBA [0x8.c9d4.10], SCN: 533714

Completed checkpoint up to RBA [0x8.c9d4.10], SCN: 533714

-- We can see SCN 533714 in full checkpoint.

Let's compare the results in V$DATAFILE_HEADER.

NO STATUS TABLESPACE_NAME CUR_SCN RST_DT RST_SCN CKPT_DT CKPT_SCN CKPT_CNT

-

1 ONLINE SYSTEM 533790 2008-01-12 16:51:53 446075 2008-08-04 22:22:08 533714 66

2 ONLINE UNDOTBS1 533790 2008-01-12 16:51:53 446075 2008-08-04 22:22:08 533714 29

3 ONLINE SYSAUX 533790 2008-01-12 16:51:53 446075 2008-08-04 22:22:08 533714 66

4 ONLINE USERS 533790 2008-01-12 16:51:53 446075 2008-08-04 22:22:08 533714 65

5 ONLINE EXAMPLE 533790 2008-01-12 16:51:53 446075 2008-08-04 22:22:08 533714 25

See, the checkpoint time and checkpoint SCN have been recorded in the header of the data file.

Checkpoints during log switching

Let's do a log switch first.

ALTER SYSTEM SWITCH LOGFILE

-- then look at the records in alert.

Mon Aug 4 22:31:39 2008

Beginning log switch checkpoint up to RBA [0x9.2.10], SCN: 534450

Thread 1 advanced to log sequence 9

Current log# 2 seq# 9 mem# 0: / u/app/oracle/oradata/orcl/redo02.log

Mon Aug 4 22:35:58 2008

Completed checkpoint up to RBA [0x9.2.10], SCN: 534450

We can see that the checkpoint was completed after a period of time (here is 4 minutes)

Let's take a look at the results in V$DATAFILE_HEADER.

NO STATUS TABLESPACE_NAME CUR_SCN RST_DT RST_SCN CKPT_DT CKPT_SCN CKPT_CNT

-

1 ONLINE SYSTEM 534770 2008-01-12 16:51:53 446075 2008-08-04 22:31:44 534450 67

2 ONLINE UNDOTBS1 534770 2008-01-12 16:51:53 446075 2008-08-04 22:31:44 534450 30

3 ONLINE SYSAUX 534770 2008-01-12 16:51:53 446075 2008-08-04 22:31:44 534450 67

4 ONLINE USERS 534770 2008-01-12 16:51:53 446075 2008-08-04 22:31:44 534450 66

5 ONLINE EXAMPLE 534770 2008-01-12 16:51:53 446075 2008-08-04 22:31:44 534450 26

-- here we can find that the SCN recorded in V$DATAFILE_HEADER is the same as the SCN of checkpoint where log switching occurs.

This proves that log switching updates the headers of data files, and the checkpoint of log switching is a relatively low-level operation.

-- it won't be done immediately, which is also for performance reasons.

Incremental checkpoint View

It is currently known that only incremental checkpoint triggered after LOG_checkpoint_TIMEOUT sets a non-zero value is recorded in the alert file, and incremental checkpoint triggered by other conditions is not recorded in the alert file.

The following is the incremental checkpoint record generated when LOG_checkpoint_TIMEOUT is set to 1800s

Sun Aug 3 19:08:56 2008

Incremental checkpoint up to RBA [0x8.e17.0], current log tail at RBA [0x8.1056.0]

Sun Aug 3 19:39:00 2008

Incremental checkpoint up to RBA [0x8.1be0.0], current log tail at RBA [0x8.1c6e.0]

Sun Aug 3 20:09:04 2008

Incremental checkpoint up to RBA [0x8.2af5.0], current log tail at RBA [0x8.2b6a.0]

Sun Aug 3 20:39:07 2008

Incremental checkpoint up to RBA [0x8.3798.0], current log tail at RBA [0x8.3851.0]

Sun Aug 3 21:09:10 2008

Incremental checkpoint up to RBA [0x8.47b9.0], current log tail at RBA [0x8.48bb.0]

Sun Aug 3 21:39:14 2008

Incremental checkpoint up to RBA [0x8.548d.0], current log tail at RBA [0x8.5522.0]

Mon Aug 4 21:05:18 2008

View fast_start_mttr_target

You can see some MTTR-related information by looking at the V$INSTANCE_RECOVERY dynamic performance view.

SELECT TARGET_MTTR,ESTIMATED_MTTR,CKPT_BLOCK_WRITES,CKPT_BLOCK_WRITES FROM V$INSTANCE_RECOVERY

TARGET_MTTR

The value of the parameter FAST_START_MTTR_TARGET set by the user.

ESTIMATED_MTTR

Based on the current number of dirty blocks and log blocks, it is estimated how long it will take to recover.

CKPT_BLOCK_WRITES

The number of blocks completed at the checkpoint.

CKPT_BLOCK_WRITES

Additional database writes due to checkpoints (because of unnecessary checkpoints, setting a very small system recovery time will have a negative impact on performance. To help administrators monitor the impact of a small setting of this parameter on the database, this view shows this column)

Dependent view

V$ view

V$DATAFILE_HEADER

View the full checkpoint information of the data file.

V$INSTANCE_RECOVERY

View fast_start_mttr_target settings and information about the system MTTR.

X$ view

X$BH

LRBA and HRBA (There is also a recovery RBA which is used to record the progress of partial block recovery by PMON.) used to view dirty blocks.

X$TARGETRBA

View incremental checkpoint RBA,target RBA and on-disk RBA.

X$KCCCP

There is also information about incremental checkpoint RBA,target RBA.

X$KCCRT

Full checkpoint (full thread checkpoint) RBA information.

Supplementary explanation

After writing this article, I read the discussion on itpub to update my point of view. (http://www.itpub.net/viewthread.php?tid=1053847)

There is a lot of debate about the difference between incremental checkpoint and full checkpoint, especially about whether log switching is incremental or complete, but in fact, there is no mention of incremental checkpoint (incremental checkpoint) or full checkpoint (full checkpoint) in Oracle documents.

My view is that it is not necessary to distinguish between incremental and complete, what we really need to understand is what kind of behavior checkpoint will have in different situations, and then configure the database according to these behaviors, set the corresponding parameters, and formulate the corresponding backup / recovery strategy, that's all.

Here is a list of common checkpoint behaviors to write:

1. Statements such as alter system checkpoint first record the current scn, then push the DBWn process to write dirty data, the checkpoint ends when it is written to the recorded scn, and then the ckpt process writes the recorded scn to the control file and data file header.

two。 When the parameter log_checkpoint_timeout is set, when the timeout value is reached, the ckpt process records the progress of DBWn writing dirty data, that is, to that scn, and the checkpoint information is only recorded in the control file. At the same time, if LOG_checkpointS_TO_ALERT is set, we will get this information in alert:

Sun Aug 3 19:08:56 2008

Incremental checkpoint up to RBA [0x8.e17.0], current log tail at RBA [0x8.1056.0]

3. The ckpt process records the progress of checkpoint to the control file every 3 seconds, which is similar to the above, except that it cannot be seen in alert, and the control file is not written every time you wake up.

4. Similar to what is generated by alter system switch logfile, the scn,ckpt process that first records the time when the command is issued does not push DBWn to write dirty data, but lets DBWn write dirty data according to its own state. When writing to the recorded scn, the chpt process updates the control file and data file header. In this case, you can see the message on alert:

Mon Aug 4 22:31:39 2008

Beginning log switch checkpoint up to RBA [0x9.2.10], SCN: 534450

Thread 1 advanced to log sequence 9

Current log# 2 seq# 9 mem# 0: / u/app/oracle/oradata/orcl/redo02.log

Mon Aug 4 22:35:58 2008

Completed checkpoint up to RBA [0x9.2.10], SCN: 534450

At this point, I believe you have a deeper understanding of "how to understand Oracle checkpoint", might as well come to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.