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

This article focuses on "what is an Oracle checkpoint". Interested friends may wish to take a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what an Oracle checkpoint is.

1. Checkpoint concept-chkpoint

A checkpoint is a database event that exists to reduce crash recovery crash recovery time.

The checkpoint event is triggered by the background process CKPT. When the checkpoint occurs, CKPT informs the DBWR process to write the dirty database dirtybuffer to the data file, update the data file header and control the checkpoint information on the file.

The SCN of the data file header is CHECKPOINT SCN.

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:

Log file sequence number (4 bytes)-find the corresponding log file address based on this.

Log file block number (4 bytes)-based on this to find the log file block where the corresponding log entry is located.

The number of starting offset bytes (2 bytes) of the redo log recorded in the log block-find the corresponding log entry.

The commonly used forms of RBA are:

The location where the redo log records generated when a dirty block in the LRBA data cache (buffer cache) is updated for the first time in the redo log file is called LRBA. The location where the redo log records generated when a dirty block in the HRBA 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.

1. Overview of checkpoint

Checkpoint is an indispensable mechanism for oracle in database consistency shutdown, instance recovery, and basic oracle operations, and contains the following related meanings:

A. the location of the checkpoint (checkpoint position) is a data structure, and the SCN number recorded in the redo stream is at the beginning of database instance recovery.

The location of the checkpoint is determined by the oldest dirty data location that exists in the data buffer pool, the location of the checkpoint is equivalent to a pointer to the redo stream, and the information of the checkpoint is stored in the header of the control file and the data file.

B. write the modified dirty data in the data buffer to disk.

2. The purpose of checkpoint

A. reduce the recovery time when the instance is restored or the media is restored

B. make sure that the dirty data in the data buffer has been written to the disk

C. Ensure that all submitted data is written to disk during database consistency shutdown

3. When does the database start checkpoint

The CKPT process is responsible for writing checkpoint information to data file headers and control files, including the following types of checkpoints

A, thread checkpoint (thread checkpoint or database checkpoint)

The database writes all data modified by redo in the data buffer to disk. Before certain actions, the collection of this thread checkpoint in all instances is called a database checkpoint (database checkpoint), which occurs in the following situations:

-- when database consistency is closed

-- when the ALTER SYSTEM CHECKPOINT statement

-- when switching online logs

-- when the ALTER DATABASE BEGIN BACKUP statement

B, tablespace and data file checkpoint (table and data file checkpoints)

The database writes all data modified by redo in the data buffer to disk before the specific action, the checkpoint in the table space is a collection of data file checkpoints, each data file is within this tablespace, and such checkpoints occur in the following situations:

Set a tablespace to read-only

-- set a tablespace to offline

-when the size of the data file changes

-- when performing ALTER TABLESPACE BEGIN BACKUP

C, incremental checkpoint (incremental checkpoints)

Incremental checkpoint is a kind of thread checkpoint, which is to avoid writing a large amount of dirty data to disk during online log switching. DBWn checks every three seconds to see if any data is to be written to disk. When the DBWn process needs to write dirty data to disk, thus promoting the location of the checkpoint, causing the CKPT process to write checkpoint location information to the control file. However, it is not written to the header file.

D, other checkpoints include instance and media recovery checkpoints, checkpoints when schema objects are dropped and truncated

4. Related process CKPT

The full name of the CKPT process is checkpoint process, which is responsible for:

A. update checkpoint information in control files and header files

B. notify the DBWn process to write dirty data to disk

Checkpoint information includes:

A, checkpoint location

B 、 SCN

C, the location in the online log file to start the recovery

The CKPT process is not responsible for writing dirty data to disk, nor for writing data from redo buffers to online log files

The DBWn process is responsible for saving dirty data, and the LGWR process is responsible for saving books in the redo buffer.

How checkpoints work:

In the database, when you modify the data, you need to read the data and buffer cache in memory first. At the same time, ORACLE will record the redo redo information for recovery. With the redo log information, ORACLE does not need to write the changed data back to disk immediately when the transaction is committed, because it will be inefficient to write immediately. Redo information also exists for the data to crash after the data can be recovered. If there is a power outage, the data that has been modified and not written to the data file in memory is lost, and the next time the database starts, the transaction can be redone by redoing the log (whether committed or not), that is, roll forward. Restore the database to its pre-crash state, then the database can be opened for use, and ORACLE rolls back uncommitted transactions.

Checkpoints exist to shorten the time it takes to recover the above data.

When a checkpoint occurs, the SCN called checkpoint scn,ORACLE will notify DBWR to write the modified data, that is, the dirty data dirty data before the checkpoint scn, to disk from buffer cache. After the writing is completed, the CKPT process will update the control file and data file header accordingly, record the checkpoint information, and identify the change.

After the checkpoint is completed, the previously modified data of the checkpoint has been written to the data file, and the corresponding redo records in the redo log are no longer useful for instance recovery (physical recovery is useful).

#

two。 Incremental checkpoint concepts incremental checkpoint and CKPTQ,FILEQ checkpoint queues, checkpoint queue,CKPTQ

Within the database, each dirty data block is recorded in the checkpoint queue, arranged in LRBA order (the redo block address corresponding to the data block is modified by LOW RBA for the first time, and the later modified RBA is called HRBA). If a data block is modified multiple times, the order of the data block on the checkpoint queue does not change.

The CKPTQ information in the buffer header of a non-dirty block is empty.

When performing an incremental checkpoint, the DBWR is written out from the checkpoint queue in LOW RBA order, and the first modified data can be written out in order of priority, so the instance checkpoint can not be promoted.

At the same time, the CKPT process periodically uses the lightweight control file update protocol to write the current minimum RBA to the control file. When CKPT carries out the lightweight update, it does not rewrite the checkpoint information and the data file header information in the data file in the control file, but only records the control file checkpoint SCN,controlfile checkpointed at scn and writes the enhanced RBA information according to the incremental checkpoint.

With incremental checkpoints, the database can change all writes out to incremental progressive writes, thus greatly reducing the impact on database performance, while checkpoint queues further associate RBA with checkpoints so that the starting point for recovery can be determined by checkpoints.

Related to CKPTQ: file checkpoint queue file queue FILEQ and object queue Obj-Q

File checkpoints improve the performance of tablespace checkpoints TABLESPACE CHECKPOINT. Each dirty buffer is linked to both CKPTQ and FILEQ,CKPTQ. All BUFFER,FILEQ that need to be checkpointed include BUFFER that belong to specific files that need to be checkpointed. Each file contains a file queue, and FILEQ is used when performing tablespace checkpoint requests. Tablespace OFFLINE touches the publication space checkpoint.

The role of the 3.CKPT process in incremental checkpoints:

The CKPT process monitors the length of the checkpoint queue. When the checkpoint queue length reaches a certain limit, CKPT will tell DBWR to write dirty blocks.

CKPT calculates a Target rba (target rba) based on the parameter setting and the speed and busyness of Target rba O, and DBWR flushes all dirty blocks before Target rba to disk along the checkpoint queue. When CKPT notifies DBWR Target rba, CKPT's task ends. Don't wait for DBWR to finish writing all the dirty blocks before Target rba.

Tell DBWR to write dirty blocks, which is one of the tasks of CKPT, and another task of CKPT is to check the write progress of DBWR every 3 seconds.

The first block of the checkpoint queue is called the checkpoint location. CKPT writes dirty blocks along the checkpoint queue. CKPT checks where the checkpoint queue is written along the checkpoint queue every 3 seconds, and sets this location to the checkpoint location. In other words, the blocks before the checkpoint location are all blocks that have been flushed to disk by DBWR.

This 3-second work to check the progress of DBWR is also an important task for CKPT. CKPT records the location of the checkpoint in the control file every 3 seconds, and of course other information such as heartbeat is recorded in the control file.

CKPT works every 3 seconds and CKPT periodically triggers DBWR. Together, these two operations are called incremental checkpoints.

The way 4.dbwr writes dirty blocks on CKPTQ:

In the checkpoint queue, dirty blocks are arranged in LRBA order, and DBWR is triggered at a certain time.

The hardware ability, the number of dirty blocks and the number of Redo are the basis of whether DBWR writes dirty blocks.

When (how often) does DBWR judge the three values: 3s

That is, DBWR wakes up in 3 seconds and determines whether to trigger-"incremental checkpoint write" based on three indicators.

5.fast_start_mttr_target and incremental checkpoint 1, about the concept of FAST_START_MTTR_TARGET:-- this paragraph Baidu

Is a parameter that speeds up instance recovery, and we can define a reasonable and acceptable value in seconds based on the service level. For example, set it to 60s, that is, 2 minutes.

Assuming that this value is reasonable, once the instance crashes, the instance should be able to be restored within 60s. It is reasonable that the value should not be too large or too small. If the instance is too large, it will take a long time to recover, and if it is too small, it will lead to the timely writing of a large amount of data, which will increase the Ibank O.

The main factor affecting the recovery time of an instance is the distance from the nearest checkpoint to the tail of the online redo log. The longer the distance, the longer the time required for cache recovery, undo and redo. So how to effectively shorten the distance between the nearest checkpoint and the tail of the online redo log is the purpose of FAST_START_MTTR_TARGET.

There are many triggers for checkpoints, such as log switching, database shutdown, start and end backup tablespaces, and so on. There are also many categories of checkpoints, such as complete checkpoints, partial checkpoints, incremental checkpoints and so on.

The value of FAST_START_MTTR_TARGET is actually a trigger condition that triggers the checkpoint. When the recovery time required for the dirty buffer generated in memory (estimated_mttr) reaches the specified time of the FAST_START_MTTR_TARGET, the checkpoint process is triggered. Once the checkpoint process is triggered, it informs the DBWn process that dirty data will be written to the data file in the checkpoint queue order, thus shortening the distance between the last checkpoint location and the online redo log and reducing the time required for instance recovery.

II. Setting of FAST_START_MTTR_TARGET parameters

After 9i (including 9i): fast_start_mttr_target: in terms of instance recovery time (hardware capacity, number of dirty blocks, number of Redo)

After 10G, the default value of fast_start_mttr_target is 0, that is, the self-tuning checkpoint is enabled: self tune checkpoint. The influencing factors of self-tuning checkpoint are hardware capacity, number of dirty blocks and number of Redo.

Self-tuning checkpoint corresponds to the implied parameter: _ disable_selftune_checkpointing:

_ disable_selftune_checkpointing Disable self-tune checkpointing FALSE

SYS@ bys3 > show parameter statistics_level-this parameter is typical or all, and FAST_START_MTTR_TARGET is set to a non-zero value to enable MTTR Advisory

NAME TYPE VALUE

-

Statistics_level string TYPICAL

SYS@ bys3 > show parameter mttr

NAME TYPE VALUE

-

Fast_start_mttr_target integer 0

From the information on database startup in the alert log, you can find:

[oracle@bys3 ~] $cat alert_bys3.log | grep MTTR

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Explicitly setting alter system set FAST_START_MTTR_TARGET= 0 will turn off automatic adjustment, and restarting the database can be found in the alter log:

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

When FAST_START_MTTR_TARGET is set to non-zero

SYS@ bys3 > alter system set fast_start_mttr_target=25

That is, if the statistics_level parameter is typical or all, and FAST_START_MTTR_TARGET is set to a non-zero value, MTTR Advisory is enabled.

At this point, there will be no MTTR information in the alert log-because MTTR Advisory has been started normally

About the use of the view when starting MTTR Advisory:-- the content of this view is empty when MTTR Advisory is not opened.

SYS@ bys3 > select mttr_target_for_estimate, dirty_limit,estd_cache_writes, estd_cache_write_factor, estd_total_writes, estd_total_write_factor from v$mttr_target_advice

MTTR_TARGET_FOR_ESTIMATE DIRTY_LIMIT ESTD_CACHE_WRITES ESTD_CACHE_WRITE_FACTOR ESTD_TOTAL_WRITES ESTD_TOTAL_WRITE_FACTOR

18 1067 57 1 806 1

17 1000 57 1 806 1

19 1268 57 1 806 1

20 1507 57 1 806 1

SYS@ bys3 > select target_mttr,estimated_mttr from v$instance_recovery

TARGET_MTTR ESTIMATED_MTTR

--

20 12

-- mttr_target_for_estimate has a value of the closest target time 20 and the target_mttr time 20 calculated by the system.

At the same time, several groups of different mttr_ target values and dirty_limit,cache_write,io values are given for selection, and the appropriate mttre values are set.

Checkpoint (Checkpoint) optimization and troubleshooting guidelines (document ID 1526118.1)

Type: status: last main update: last update: language: BULLETINPUBLISHED2015-6-232016-12-7English simplified Chinese?

Applicable to:

Oracle Database-Enterprise Edition

The information contained in this document is applicable to all platforms

Use

This document is intended to give database administrators a better understanding of incremental checkpoints (Checkpoint) and describes the following four initialization parameters used for Checkpoint optimization:

-FAST_START_MTTR_TARGET

-LOG_CHECKPOINT_INTERVAL

-LOG_CHECKPOINT_TIMEOUT

-LOG_CHECKPOINTS_TO_ALERT

In addition, this document describes how to interpret and handle checkpoint errors: "Checkpoint not Complete" and "Cannot Allocate New Log" reported in the ALERT.LOG file.

detailed information

Table of contents:

1. What is a checkpoint?

two。 Checkpoints and performanc

3. Parameters related to incremental checkpoint

4. Redo (redo) logs and checkpoints

5. Understand checkpoint error messages "Cannot allocate new log" and "Checkpoint not complete"

6. Oracle version information

7. Using Statspack to determine the checkpoint problem

Checkpoint optimization and error handling

1. What is a checkpoint?

A checkpoint is a database event that synchronizes modified blocks of data in memory with data files on disk. Through Checkpoint Oracle to ensure that the modified data by transaction can be synchronized to disk. Modified blocks are not synchronously written to disk when Oracle transaction commits.

Checkpoints serve two purposes: (1) to ensure data consistency, and (2) to achieve faster database recovery. How to recover more quickly? "because all database changes are recorded in the data file until the checkpoint is generated, there is no need to apply redo log entries that precede the checkpoint." Checkpoints must ensure that all modified buffered data in the cache is actually written to the appropriate data file to avoid possible data loss in the event of a crash (instance or disk failure).

Oracle writes dirty cache to disk only under certain conditions:

-the number of data blocks to be scanned by shadow process exceeds 1/4 of the db_block_buffers parameter.

-every three seconds.

-when generating a checkpoint.

Checkpoints are implemented through five types of events:

-each time you switch the redo log file.

-when the delay of LOG_CHECKPOINT_TIMEOUT is reached.

-data of size (LOG_CHECKPOINT_INTERVAL* OS block size (bytes)) already exists in the current redo log file.

-directly implemented by the ALTER SYSTEM SWITCH LOGFILE command.

-use the ALTER SYSTEM CHECKPOINT command directly.

The following actions occur during the checkpoint:

-DBWR writes all modified database blocks in the buffer cache back to the data file

-the checkpoint process (ckpt) updates the headers of all data files to reflect the time when the last checkpoint occurred (SCN)

two。 Checkpoints and performanc

The optimization of checkpoints often puts database administrators in a dilemma. Frequent checkpoints can achieve faster database recovery, but it can also lead to database performance degradation. So how does DBA solve this problem?

Depending on the number of data files in the database, the checkpoint will be a highly resource-intensive operation because all data file headers will be frozen during the checkpoint. With regard to the frequency setting of checkpoints, performance needs to be weighed. The higher the frequency of checkpoints, the faster recovery can be achieved after a database crash. This is why some customer sites that cannot tolerate unexpected system downtime often choose this option. However, in many cases, frequent checkpoints can lead to performance degradation, which makes the above point of view not fully established. We assume that the database is up and running 95% of the time, and the remaining 5% of the unrun time is due to occasional instance crashes or hardware failures that require database recovery. For most customer sites, optimizing 95% performance makes more sense than a very small 5% downtime.

The hypothetical performance of this document is your primary consideration, and the corresponding suggestions are given accordingly. Therefore, your goal is to minimize the frequency of checkpoints through optimization.

Optimizing checkpoints involves the following four key initialization parameters:

-FAST_START_MTTR_TARGET

-LOG_CHECKPOINT_INTERVAL

-LOG_CHECKPOINT_TIMEOUT

-LOG_CHECKPOINTS_TO_ALERT

These parameters are discussed in more detail below.

At the same time, suggestions are given to deal with the "checkpoint not complete" messages that appear in the alert log, which indicate that the redo log and checkpoints need to be optimized.

3. Parameters related to incremental checkpoint

Note: log file switching will always overwrite checkpoints caused by the following parameters.

FAST_START_MTTR_TARGET

Since Oracle 9i, the FAST_START_MTTR_TARGET parameter has become the preferred method for optimizing incremental checkpoint targets. With FAST_START_MTTR_TARGET, you can specify the number of seconds it takes for the database to perform a crash recovery of a single instance. "based on internal statistics, incremental checkpoints automatically adjust checkpoint targets to meet the requirements of FAST_START_MTTR_TARGET."

V$INSTANCE_RECOVERY.ESTIMATED_MTTR displays the currently estimated average recovery time (MTTR) in seconds. This value is also displayed even if FAST_START_MTTR_TARGET is not specified.

V$INSTANCE_RECOVERY.TARGET_MTTR displays valid MTTR targets (in seconds) enforced by the system.

V$MTTR_TARGET_ADVICE displays the number of Icano generated by the current workload under the current MTTR settings, as well as the estimated number of iUnites that will be generated by the current workload under other MTTR settings. This view helps users make a tradeoff between runtime performance and setting up FAST_START_MTTR_TARGET for fast recovery.

LOG_CHECKPOINT_INTERVAL

The LOG_CHECKPOINT_INTERVAL parameter specifies that the incremental checkpoint target should lag behind the maximum number of redo blocks at the end of the current log.

If FAST_START_MTTR_TARGET is specified, LOG_CHECKPOINT_INTERVAL should not be set or set to 0. On most Unix systems, the operating system block size is 512 bytes.

That is, setting the value of LOG_CHECKPOINT_INTERVAL to 10000 means that the lag of the incremental checkpoint target relative to the end of the current log should not exceed 5120000 (5m) bytes. "based on this calculation, if the size of the redo log is 20m, 4 checkpoints are generated for each log."

LOG_CHECKPOINT_INTERVAL affects when a checkpoint occurs, which means that special attention should be paid to the setting of this parameter to keep it updated with changes in the size of the redo log file. The frequency of checkpoints is one of the factors that affect the time it takes for a database to recover from an unexpected failure. The longer the interval between checkpoints, the longer it takes for the database to recover in the event of a system crash. A shorter checkpoint interval means faster database recovery, but at the cost of more resources consumed by checkpoint operations.

This parameter also affects the time required to complete the database restore operation during the roll forward phase of the recovery. The actual recovery time depends on this time, as well as other factors, such as the type of failure (instance or system crash, media failure, and so on) and the number of archived redo logs that need to be applied.

LOG_CHECKPOINT_TIMEOUT

The LOG_CHECKPOINT_TIMEOUT parameter specifies the maximum number of seconds that the incremental checkpoint target should lag behind the end of the current log.

In other words, it specifies how long the dirty cache in the buffer cache can remain dirty.

The frequency of checkpoints affects the time it takes for the database to recover from an unexpected failure. The longer the interval between checkpoints, the more time it takes for the database to recover.

Oracle recommends using LOG_CHECKPOINT_INTERVAL instead of LOG_CHECKPOINT_TIMEOUT to control the checkpoint interval, which initiates the checkpoint every "n" second, regardless of transaction frequency. This can lead to unnecessary checkpoints in the event of a change in transaction volume. Whenever possible, unnecessary checkpoints must be avoided to achieve the best performance.

Many people have the misconception that once LOG_CHECKPOINT_TIMEOUT is set to a given value, the system starts log switching at that interval, enabling the recovery window for stand-by database configuration. Log switching causes checkpoints, but checkpoints do not cause log switching. The only way to cause log switching is to use ALTER SYSTEM SWITCH LOGFILE for manual operation or to resize redo logs to cause more frequent switching. This is controlled by the operating system block rather than the interval.

The size of online redo logs is critical to performance and recovery.

"for information on redo logs and checkpoints, refer to the other sections below."

LOG_CHECKPOINTS_TO_ALERT

With LOG_CHECKPOINTS_TO_ALERT, you can log checkpoints to the alert log.

This helps to determine whether the checkpoint occurs at the desired frequency.

Before Oracle9i, this parameter was static.

Oracle generally recommends that this parameter be set to TRUE because the overhead is small and can be ignored, but the information in the alert log can be very useful.

For more details on how the above instance parameters affect checkpoints, see Note:76713.1

4. Redo logs and checkpoints

A checkpoint occurs each time you switch logs. If the previous checkpoint is already in progress, the checkpoint caused by the log switch overwrites the current checkpoint.

At this point, redo logs of the right size are needed to avoid unnecessary checkpoints caused by frequent log switching. In addition, the interval between the incremental checkpoint target and the end of the log is limited by the 90% minimum online log file size setting. This ensures that in most cases, log switching does not have to wait for a checkpoint. Therefore, the log file size should be configured large enough. A good idea is to switch logs every 20 minutes at most. Too small log files increase checkpoint activity and degrade performance. Oracle recommends that users set all online log files to the same size and that each thread has at least two log groups. Alert logging is a valuable tool to monitor the speed at which log switching occurs, and how quickly subsequent checkpoints occur.

The following is an example of how logs are found to switch too frequently through alert logs:

Fri May 16 17:15:43 1997

Thread 1 advanced to log sequence 1272

Current log# 3 seq# 1272 mem# 0: / prod1/oradata/logs/redologs03.log

Thread 1 advanced to log sequence 1273

Current log# 1 seq# 1273 mem# 0: / prod1/oradata/logs/redologs01.log

Fri May 16 17:17:25 1997

Thread 1 advanced to log sequence 1274

Current log# 2 seq# 1274 mem# 0: / prod1/oradata/logs/redologs02.log

Thread 1 advanced to log sequence 1275

Current log# 3 seq# 1275 mem# 0: / prod1/oradata/logs/redologs03.log

Fri May 16 17:20:51 1997

Thread 1 advanced to log sequence 1276

Current log# 1 seq# 1276 mem# 0: / prod1/oradata/logs/redologs01.log

If the redo log is switched every 3 minutes, you will notice performance degradation. This indicates that the redo log is not large enough to handle the transaction load effectively.

For more information about how to estimate the appropriate size of the redo log file, see Note:1038851.6. For an example of how to resize online redo log files, see Note:1035935.6.

5. Understand checkpoint error messages "Cannot allocate new log" and "Checkpoint not complete"

Sometimes you can see the following corresponding message in the alert.log file:

Thread 1 advanced to log sequence 248

Current log# 2 seq# 248 mem# 0: / prod1/oradata/logs/redologs02.log

Thread 1 cannot allocate new log, sequence 249

Checkpoint not complete

This information indicates that Oracle wants to reuse an redo log file, but the current checkpoint location is still in the log. In this case, the Oracle must wait until the checkpoint location passes the log. "because the lag of the incremental checkpoint target from the end of the current log will never exceed 90% of the minimum log file size, this can be encountered if the DBWR write speed is too slow, or log switching occurs before the log is full, or the log file is too small." While the database is waiting for a checkpoint, the redo generation process stops until the log switch is complete.

6. Oracle version information

In Oracle8i, the initialization parameter FAST_START_IO_TARGET causes the incremental checkpoint to automatically adjust its target so that the number of blocks required for recovery is no more than the value set by FAST_START_IO_TARGET. Since Oracle 9i, this parameter has been deprecated and replaced by the parameter FAST_START_MTTR_TARGET.

7. Using Statspack to determine the checkpoint problem

You can collect Statspack snapshots every 15 minutes or so, and these snapshot reports gather useful information about the number of checkpoints that have started, the number of checkpoints that have been completed, and the number of database buffers written when checkpoints occur. In addition, statistics about redo activities are included. By collecting and comparing these snapshot reports, you can have a complete understanding of checkpoint performance at different periods.

Another concern in the Statspack report is the wait event, which clearly points to problems with redo log throughput and checkpoints:

Log file switch (checkpoint incomplete)

Log file switch (archiving needed)

Log file switch/archive

Log file switch (clearing log file)

Log file switch completion

Log switch/archive

Log file sync

If one or more of the above wait events occur frequently and the relevant values are large, then you need to take action, such as adding more online redo log files, or increasing their size and / or modifying checkpoint parameters.

Referenc

NOTE:76713.1-8i Parameters that Influence Checkpoints

NOTE:1038851.6-How to Estimate Size of Redo Logs

FAST_START_MTTR_TARGET parameters in 11G

SQL > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

PL/SQL Release 11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0-Production

NLSRTL Version 11.2.0.1.0-Production

SQL > col name for A20

SQL > col value for A30

SQL > select name,value from v$spparameter where name like 'fast_start_mt%'

NAME VALUE

Fast_start_mttr_targ

Et

Information in alert.log

Successful open of redo thread 2

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Sat Jul 13 13:27:28 2013

SMON: enabling cache recovery

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Concept description

1. On FAST_START_MTTR_TARGET parameters

Is a parameter that speeds up instance recovery, and we can define a reasonable and acceptable value in seconds based on the service level. For example, set it to 60s, that is, 1 minute.

Assuming that this value is reasonable, once the instance crashes, the instance should be able to be restored within 60s. It is reasonable that the value should not be too large or too small. If it is too big, the example will be restored.

It takes a long time to reply, but if it is too small, it will lead to the timely writing of a large amount of data, which increases the Icano of the system.

The main factor affecting the recovery time of an instance is the distance from the nearest checkpoint to the tail of the online redo log. The longer the distance, the more cache recovery and undo,

The longer the redo. So how to effectively shorten the distance between the nearest checkpoint and the tail of the online redo log is the purpose of FAST_START_MTTR_TARGET.

There are many triggers for checkpoints, such as log switching, database shutdown, start and end backup tablespaces, and so on. There are also many categories of checkpoints, such as complete checkpoints, departments.

Divided into checkpoints, incremental checkpoints, and so on.

The value of FAST_START_MTTR_TARGET is actually a trigger condition that triggers the checkpoint. Recovery time required when dirty buffer is generated in memory (estimated_mttr)

If the specified time of the FAST_START_MTTR_TARGET is reached, the checkpoint process is triggered. Once the checkpoint process is triggered, it will notify the DBWn process that it will dirty the number according to the checkpoint queue order

It is written to a data file, which shortens the distance between the last checkpoint location and the online redo log, and reduces the time required for instance recovery.

Two important views

V$instacne_recovery

V$mttr_target_advice

3. Set FAST_START_MTTR_TARGET

Set the value of FAST_START_MTTR_TARGET according to the actual needs, which needs to take into account the acceptable instance recovery time, bearable Ibino throughput, and so on.

Suppose we set this value to

SQL > alter system set fast_start_mttr_target = 30

Examine the values provided by the view v$instacne_recovery during periods of frequent transactions

1. On FAST_START_MTTR_TARGET parameters

Is a parameter that speeds up instance recovery, and we can define a reasonable and acceptable value in seconds based on the service level. For example, set it to 60s, that is, 1 minute.

Assuming that this value is reasonable, once the instance crashes, the instance should be able to be restored within 60s. It is reasonable that the value should not be too large or too small. If it is too big, the example will be restored.

It takes a long time to reply, but if it is too small, it will lead to the timely writing of a large amount of data, which increases the Icano of the system.

The main factor affecting the recovery time of an instance is the distance from the nearest checkpoint to the tail of the online redo log. The longer the distance, the more cache recovery and undo,

The longer the redo. So how to effectively shorten the distance between the nearest checkpoint and the tail of the online redo log is the purpose of FAST_START_MTTR_TARGET.

There are many triggers for checkpoints, such as log switching, database shutdown, start and end backup tablespaces, and so on. There are also many categories of checkpoints, such as complete checkpoints, departments.

Divided into checkpoints, incremental checkpoints, and so on.

The value of FAST_START_MTTR_TARGET is actually a trigger condition that triggers the checkpoint. Recovery time required when dirty buffer is generated in memory (estimated_mttr)

If the specified time of the FAST_START_MTTR_TARGET is reached, the checkpoint process is triggered. Once the checkpoint process is triggered, it will notify the DBWn process that it will dirty the number according to the checkpoint queue order

It is written to a data file, which shortens the distance between the last checkpoint location and the online redo log, and reduces the time required for instance recovery.

For the specific classification description of instance recovery and inspection, please refer to:

Oracle instance recovery

Oracle instance and Oracle database (Oracle architecture)

Second, the query of FAST_START_MTTR_TARGET = 0

Many articles describe that FAST_START_MTTR_TARGET = 0, that is, it is not set, indicating that the automatic checkpoint feature is enabled. Here is a paragraph from the official document of Oracle.

The link to the original text is: Fast-Start Fault Recovery

Fast-start checkpointing refers to the periodic writes by the database writer (DBWn) processes for the purpose of writing changed data blocks from the Oracle buffer cache to disk and advancing the thread-checkpoint. Setting the database parameter FAST_START_MTTR_TARGET to a value greater than zero enables the fast-start checkpointing feature. Fast-start checkpointing should always be enabled for the following reasons:

It reduces the time required for cache recovery, and makes instance recovery time-bounded and predictable. This is accomplished by limiting the number of dirty buffers (data blocks which have changes in memory that still need to be written to disk) and the number of redo records (changes in the database) generated between the most recent redo record and the last checkpoint.

Fast-Start checkpointing eliminates bulk writes and corresponding I/O spikes that occur traditionally with interval- based checkpoints, providing a smoother, more consistent I/O pattern that is more predictable and easier to manage. If the system is not already near or at its maximum I/O capacity, fast-start checkpointing will have a negligible impact on performance. Although fast-start checkpointing results in increased write activity, there is little reduction in database throughout, provided the system has sufficient I/O capacity.

From the description of the bold mark in the first paragraph, when a value greater than 0 is set to FAST_START_MTTR_TARGET, the automatic checkpoint function is not enabled. That is fast-start

Checkpointing, to be more accurate, should be to quickly start the checkpoint function.

Take a look at the following description, which is from the English version of the Oracle 10g OCP workshop I 14-17 tutorial (Edition 3.1December 2008)

Explicit setting of the FAST_START_MTTR_TARGET parameter to 0 disables automatic checkpoint tuning.Explicit setting of the FAST_START_MTTR_TARGET parameter to a value other than 0 also enables the Redo Log Advisor.

As you can see from the above description, if FAST_START_MTTR_TARGET is set to turn off the checkpoint auto-adjustment feature.

3. Set FAST_START_MTTR_TARGET

Set the value of FAST_START_MTTR_TARGET according to the actual needs, which needs to take into account the acceptable instance recovery time, bearable Ibino throughput, and so on.

Suppose we set this value to

SQL > alter system set fast_start_mttr_target = 30

Examine the values provided by the view v$instacne_recovery during periods of frequent transactions

SQL > desc vested instancerecovery;-- View the structure of the v$instance_recovery view

Name Null? Type

-

RECOVERY_ESTIMATED_IOS NUMBER

ACTUAL_REDO_BLKS NUMBER

TARGET_REDO_BLKS NUMBER

LOG_FILE_SIZE_REDO_BLKS NUMBER

LOG_CHKPT_TIMEOUT_REDO_BLKS NUMBER

LOG_CHKPT_INTERVAL_REDO_BLKS NUMBER

FAST_START_IO_TARGET_REDO_BLKS NUMBER

TARGET_MTTR NUMBER

ESTIMATED_MTTR NUMBER

CKPT_BLOCK_WRITES NUMBER

OPTIMAL_LOGFILE_SIZE NUMBER

ESTD_CLUSTER_AVAILABLE_TIME NUMBER

WRITES_MTTR NUMBER

WRITES_LOGFILE_SIZE NUMBER

WRITES_LOG_CHECKPOINT_SETTINGS NUMBER

WRITES_OTHER_SETTINGS NUMBER

WRITES_AUTOTUNE NUMBER

WRITES_FULL_THREAD_CKPT NUMBER

Two fields:

TARGET_MTTR-- > A value calculated with reference to the value set in the fast_start_mttr_target parameter

ESTIMATED_MTTR-- > the system is based on the values calculated in dirty buffer

What might happen.

1.TARGET_MTTR > ESTIMATED_MTTR-A large number of transactions will lead to this situation

2.TARGET_MTTR

< ESTIMATED_MTTR --数据库刚刚启动时，几乎没有事务时会出现这种情况 SQL>

Select recovery_estimated_ios,actual_redo_blks, target_redo_blks

2 target_mttr,estimated_mttr

3 from v$instance_recovery

RECOVERY_ESTIMATED_IOS ACTUAL_REDO_BLKS TARGET_REDO_BLKS TARGET_MTTR ESTIMATED_MTTR

55 147 707 33 27

You can adjust this parameter through v$mttr_target_advice according to the TARGET_MTTR value in the case of load

Fourth, enable MTTR Advisory

Two parameters need to be set.

STATISTICS_LEVEL-- > set to typical or all

FAST_START_MTTR_TARGET-- > set to non-zero value

SQL > show parameter mttr;-the target mttr_time is set to 30s

NAME TYPE VALUE

-

Fast_start_mttr_target integer 30

SQL > select target_mttr,estimated_mttr from vastly instantly recovered;-- the mttr calculated by the system is 33

TARGET_MTTR ESTIMATED_MTTR

--

33 27

SQL > select mttr_target_for_estimate tar_est,dirty_limit,estd_cache_writes est_c_w

2 estd_cache_write_factor est_c_w_f,estd_total_writes est_t_w,estd_total_write_factor est_t_w_f

3 from v$mttr_target_advice

TAR_EST DIRTY_LIMIT EST_C_W EST_C_W_F EST_T_W EST_T_W_F

--

60 5028 3762. 7376 3762. 7376

34 1000 5100 1 5100 1

68 6248 3762. 7376 3762. 7376

52 3808 3762. 7376 3762. 7376

45 2735 3845. 7539 3845. 7539

-- mttr_target_for_estimate has a value of the nearest target time 30, and the target_mttr time calculated by the system 33

-- at the same time, several groups of different mttr_ target values and dirty_limit,cache_write,io are given for DBA to select and set the appropriate mttr.

Five, two important views

V$instacne_recovery

V$mttr_target_advice

Because of the inconsistency between LGWR and DBWR in Oracle, Oracle introduces the concept of checkpoint, which is used to synchronize the database and ensure the consistency of the database. In Oracle, there are two types of checkpoints: full checkpoints and incremental checkpoints. Let's describe the role of these two checkpoints respectively:

1. Complete checkpoint

Before Oracle8i, the checkpoints that occurred in the database were all full checkpoints. The full checkpoint writes all dirty data blocks in the data buffer to the corresponding data file, and updates the latest checkpoint scn to all data file headers and control files. Ensure that the database is in a consistent state. It should be noted that when a full checkpoint is generated, CKPT does not update the SCN at the time when the current complete checkpoint occurs to the control file and data file header, but updates the SCN corresponding to the dirty buffer that has just been written by DBWn to the control file and data file header at the time when the checkpoint is triggered, that is to say, the SCN for updating the control file and data file header lags behind the SCN at the time when the full checkpoint occurs. The principle of recovery from this is also easy to understand, because when the checkpoint occurs, the dirty buffer has not been written, so it cannot be updated to the current SCN immediately. It should be noted that before oracle8, because there is no chekpoint queue and no concept of incremental checkpoints, when a complete checkpoint occurs, DBWn will write all dirty buffer out to the data file in an unordered way. At this time, Oracle will freeze all DML operations and wait for all dirty buffer to be written out. Huge IO often affects the performance of the database. Later, with the development of Oracle database and the continuous growth of buffer cache, oracle realized that this single Full checkpoint mechanism could no longer meet the needs, so after Oracle 8i, it put forward the concept of incremental checkpoints, established checkpoint queue, and let dirty buffer header be arranged in queue according to the order (LRBA) of the first change. In this way, DBWn can only be written in the order of queue, while other processes can continue without waiting for dbwr to finish writing. So the concept of incremental checkpoint comes into being.

A full checkpoint occurs after 8i only in the following two situations:

DBA manually executes alter system checkpoint commands

The database is normal shutdown (immediate,transcational,normal).

2. Incremental checkpoint

To put it bluntly, that is

CKPT checks the progress of DBWn writes every 3 seconds and records the checkpoint location (checkpoint position) and updates heartbeat information in the control file

And

CKPT periodically triggers DBWn to write dirty data in checkpoint queue.

Together, these two operations are called incremental checkpoints. -- > maybe this description is too general, let's move on: -)

We all know that modified data blocks are collectively referred to as dirty data blocks (dirty buffer) in oracle. All the dirty blocks are strung together by a linked list, called checkpoint queue (checkpoint queue). In buffer cache, each block has a buffer header abbreviated as BH, and in BH there is a ckptq entry that records pointers to one block and the next block on the checkpoint queue. If a block is not in the checkpoint queue, its ckptq entry is empty. All the dirty blocks are strung into a two-way linked list through the ckptq item oracle. This two-way linked list is the checkpoint queue.

Oracle introduced the concept of checkpoint queue (checkpoint queue) from 8i, which is used to record all the current dirty buffer information in the database. The dirty buffer information is stored in checkpoint queue according to the time when the block is modified (when the block is changed for the first time, the block is immediately added to the checkpoint queue), and the entries involved mainly include RBA (Redo Block Address The redo log is used to identify the number of the block that changed in the redo log during the transaction) and the data file number and block number of the block.

No matter how many times a buffer is changed, its position in the checkpoint queue remains the same, and checkpoint queue records only its earliest RBA (the earliest RBA is actually Low RBA, the corresponding RBA when the block was first modified), thus ensuring that the earliest changed blocks can be written to the data file from memory as soon as possible. DBWR will be triggered every certain time (DBWn is not written only when checkpoints occur, it has about 10 conditions to trigger write operations), refresh dirty blocks along the order of checkpoint queues, while the CKPT process monitors the length of checkpoint queues when the length of checkpoint queues reaches a certain limit (there are several parameters to determine the length of checkpoing queue It will be mentioned below, such as log_checkpoint_timeout,fast_start_mttr_target, etc.), CKPT will tell DBWR to write dirty blocks. CKPT will calculate a Target rba (target rba) based on the setting of several parameters and the speed and busy degree of Target rba. DBWn will write all the corresponding dirty blocks before Target rba from memory to the data disk file along the checkpoint queue according to the Low RBA order of Target rba. When CKPT finishes notifying DBWn Target rba, CKPT's task ends. He doesn't wait for DBWn to finish writing all the dirty blocks before Target rba. So here CKPT just acts as a notification to the DBWn process to write.

When the full check point is generated, on the one hand, Oracle informs DBWn for the next batch write operation, on the other hand, it writes the SCN corresponding to the dirty buffer that DBWn has just finished writing to the data file header and control file at the time when the checkpoint is triggered. This SCN is checkpoint scn. However, Oracle takes into account that the interval of checkpoint SCN is still too large, because the trigger conditions of checkpoints are limited, and the cycle may be long. In some cases, for example, it takes about 5 minutes for a checkpoint to be triggered. Then restarting the system crash at this time means that it will probably take about 5 minutes for the system to start. So Oracle adopts the concept of heartbeat, reflecting the progress of DBWn writes to the control file at a frequency of 3 seconds, so that the system crash will be restored from a more recent point in time when it is restarted. The goal of Oracle is to shorten the crash recovery time! So another task of CKPT is to check the write progress of DBWn every 3 seconds. DBWn writes dirty blocks along the checkpoint queue. Because there is an order here, the progress of DBWn writing is measurable. When writing to which buffer, the scn of the first change of the buffer (corresponding to LRba) is the latest scn of all the current data files block, but because it is unable to record the progress of DBWn in a timely manner, Oracle chose some strategies. This includes checkpoint location updates and heartbeats for the CKPT process, so CKPT checks where the DBWn is written along the checkpoint queue every 3 seconds and sets this location to the checkpoint location (checkpont position). In other words, the blocks before the checkpoint location are all blocks that have been flushed to disk by DBWn. So we can understand that the CKPT process sets and records a checkpoint location every 3 seconds according to the progress of DBWn writes, that is, the checkpoint location is determined by DBWn's progress in writing to Target RBA (if no dirty buffer is generated, then the checkpoint location information will not be updated). Therefore, every 3 seconds, CKPT updates and records the rba of the data block corresponding to the checkpoint location (low cache rba- represents the log entry that starts recovery when Instance Recovery) to the CHECKPOINT PROGRESS RECORDS area of the control file, and of course, other information such as heartbeat is recorded in the control file at the same time. After DBWn writes the dirty buffer in the checkpoint queue to the data file, the location of the checkpoint is moved back accordingly.

The checkpoint location (checkpoint position) can actually be understood directly as a rba, which points to a redo record that focuses on doing some rework in the log file. For the redo record before this checkpoint, the dirty buffer in the corresponding buffer cache has been written into the data file, and the corresponding data dirty block may still be in memory for the redo record after this location. If an instance crash occurs, you only need to find the checkpoint location (low cache rba) in the log file, where all redo log files are applied, and the roll forward operation is complete. After the instance crashes, start the database again, and oracle reads the low cache rba in the control file, which is the checkpoint location. Apply the redo log from here to the location of the on disk rba. On disk rba is the rba of the last redo record of the redo log file on disk. If the rba of a redo record is higher than on disk rba, it means that the redo record has not been written to the log file, and it is impossible for him to be restored when the crash occurs. On disk rba is the end point of the oracle roll forward operation. Rba higher than this should still reside in log buffer. It has not been written to the log file by LGWR. So it can't be used for recovery.

During the checkpoint process of DBWn writing dirty buffer, Oracle can also continue to generate dirty buffer,DBWn, and it is not necessary to write all dirty buffer to disk at once (which is different from a full checkpoint), which improves the efficiency of the checkpoint, so that when the database is to be restored, it starts from this latest location instead of from the checkpoint scn (the last full checkpoint location) in the data file. This will shorten the recovery time.

The Concept of Oracle 11g mentions: An incremental checkpoint is a type of thread checkpoint partly intended to avoid writing large numbers of blocks at online redo log switches. DBWn checks at least every three seconds to determine whether it has work to do. When DBWn writes dirty buffers, it advances the checkpoint position, causing CKPT to write the checkpoint position to the control file, but not to the data file headers.

Therefore, we need to note that the incremental checkpoint does not update the data file header and the SCN information of the database SCN and data file entries in the control file, but the CKPT process updates the low cache rba information in the control file every 3 seconds, that is, the location of the checkpoint.

After the checkpoint location changes, Oracle mainly uses four parameters and one mechanism to control the distance between the checkpoint location and the last redo log entry (the length of the checkpoint queue).

Fast_start_io_target (Oracle 9i has since been abandoned)

This parameter is used to indicate the total number of IO that needs to be generated when Instance Recovery occurs in the database, which is estimated by the AVGIOTIM of v$filestat. For example, if we have a database that needs to be recovered within 10 minutes after the occurrence of Instance Crash, assuming that the IO of OS is 500 per second, then the database will generate about 500106030000 IO when Instance Recovery occurs, that is, we will be able to set fast_start_io_target to 30000.

Fast_start_mttr_target

We can see from above that it is troublesome for fast_start_io_target to estimate the location of checkpoints. In order to simplify this concept, Oracle introduced fast_start_mttr_target as a parameter from 9i to indicate the time that Instance Recovery occurred in the database, in seconds. This parameter is also easy for us to understand literally, where mttr is an abbreviation for mean time to recovery. As in the example above, we can set fast_start_mttr_target to 600. Note that when fast_start_mttr_target is set, the parameter fast_start_io_target will no longer take effect, and the parameter fast_start_io_target has been abolished by Oracle since 9i.

Log_checkpoint_timeout

This parameter is used to indicate the time interval in seconds between the checkpoint location and the end of the redo log file, which is 1800 seconds by default. "compared to fast_start_mttr_target, it is also a time, but its time value represents the time it takes to complete the restore operation, that is, the time it takes to apply all logs from the last checkpoint to the end of the log." This parameter log_checkpoint_timeout indicates the elapsed time from the last checkpoint to the end of the log.

Log_checkpoint_interval

This parameter represents the location of the checkpoint and the number of redo log blocks at the end of the redo log, expressed as OS blocks.

90% OF SMALLEST REDO LOG

In addition to the above four initialization parameters, Oracle actually sets the first 90% of the end of the redo log file to the checkpoint location. These parameters may specify different locations in each redo log, and Oracle identifies the location closest to the end of the log file as the checkpoint location.

After Oracle 9i, it is recommended that only fast_start_mttr_target be set for checkpoint frequency. As needed, you can also set the maximum time for a dirty block to stay dirty by setting log_checkpoint_timeout, while the other two parameters, fast_start_io_target,log_checkpoint_interval, are no longer recommended.

What is an incremental checkpoint?

Recently, I have some doubts about incremental checkpoints and global checkpoints. I have looked at a lot of information and feel that it is very complex and difficult to understand. Below post some of your own experience, if there is something wrong, I hope to be corrected.

1, what is a checkpoint

Checkpoint is a database event, which refers to the progress of the current database dbwr process to write dirty blocks from db buffer to data files, reducing the time for instance recovery after the instance crashes, and is the starting point for instance recovery.

2, classification of checkpoints

Global checkpoints: usually automatically generated when dba manually generates alter system checkpoint or normally shuts down the database.

Incremental checkpoints: this complexity can start with the conditions written by dbwr

Conditions written by dbwr

1, when the checkpoint occurs

2, the dirty mass reached 1/3.

3, there are no free blocks

4,timeout occurs

5,rac ping request is made

6, offline, read-only, hot backup of tablespace

7, drop and truncate of the table

First, as can be seen from the above, when dbwr writes, it is not just when the checkpoint occurs, that is, even if the checkpoint does not occur, dbwr may be writing dirty blocks. Then if the database only records the global checkpoint, after the instance crashes, many dirty blocks that have been written to the data file by dbwr will also be applied to the log. Increased the time for instance recovery.

Second, through incremental checkpoints: oracle has a checkpoint queue in db buffer, which is also called a dirty queue. The ckpt process will read out the progress of dbwr writes every three seconds and record this point in the control file, but it will not record the header of the data file, that is to say, it records the real-time writes of dbwr. In this way, when the instance crashes, the instance recovery needs to be carried out, and the database can read the incremental checkpoint from the control file as the starting point for instance recovery, reducing the instance recovery time.

Two concepts:

1CKPT records the checkpoint location in the control file every 3 seconds. Of course, other information such as' heartbeat'is recorded in the control file at the same time. CKPT works every 3 seconds and CKPT triggers DBWR periodically. Together, these two operations are called incremental check.

2, the incremental checkpoint does not update the data file header and the SCN information of the database SCN and data file entries in the control file, but the CKPT process updates the low cache rba information in the control file every 3 seconds, that is, the location of the checkpoint.

Modified blocks are collectively referred to as dirty blocks in oracle. All the dirty blocks are strung together by a linked list and are called checkpoint queues. In buffer

In cache, each block has a buffer header referred to as BH, and in BH there is a ckptq entry that points to the checkpoint team.

Lists pointers to the previous block and the next block. If a block is not in the checkpoint queue, its ckptq entry is empty. Through the ckptq entry oracle

All the dirty blocks are strung together into a two-way linked list. This two-way linked list is the checkpoint queue.

1, only dirty blocks will be in the checkpoint queue, and the ckptq of non-dirty blocks is empty.

2, when the block is changed for the first time, the block is immediately added to the checkpoint queue. If the dirty block in the checkpoint queue is modified again, it will not be changed in the

The position in the queue of checkpoints.

3, the order of dirty blocks in the checkpoint queue: according to point 2, all dirty blocks are arranged in the order of the time when they were changed for the first time. To be more precise: press

According to the lrba arrangement of blocks.

* * what is rba?lrba?hrba?

Rba is to redo the block address, for example, the user issued a update command to update block A, block A now becomes a dirty block, oracle will do it for him.

Generate a redo record. The location of this redo record in the redo log file is rba (redo block address). After a while, fake

Such as: block An is still a dirty block, at this time. The user issues another command to update block A, which generates another redo record. The first update command pair

The rba that should redo the record is called the lrba (low rba) of block A, and the rba corresponding to the second update command is called hrba (high rba).

In fact, according to the lrba, it is arranged in the order in which the blocks were modified for the first time.

Let's talk about the way DBWR writes dirty blocks. after having a checkpoint queue, dirty blocks are arranged in the chronological order in which they get dirty for the first time. Every time DBWR reaches a certain

The opportunity will be triggered to refresh the dirty blocks in the order of the checkpoint queue. There are several parameters in oracle to determine the length of the checkpoint queue.

Degrees. There is another CKPT process that monitors the length of the checkpoint queue. When the length of the checkpoint queue reaches a certain limit, CKPT will notify DBWR.

CKPT will calculate a Target rba (target rba) according to the setting of the parameter and the speed and busy degree of the I rba, and the CKPT will follow the

Click on the checkpoint queue and flush all the dirty blocks before Target rba to disk. When CKPT notifies DBWR Target rba, the task of CKPT is

It's over。 He won't wait for DBWR to finish writing all the dirty blocks before Target rba. Tell DBWR to write dirty blocks, which is one of CKPT's tasks, CKPT

Another task is to check the write progress of DBWR every 3 seconds. The front block of the checkpoint queue is called the checkpoint location. DBWR is along the

If the checkpoint queue writes dirty blocks, CKPT checks where DBWR writes along the checkpoint queue every 3 seconds, and sets this location to the checkpoint

Location. In other words, the blocks before the checkpoint location are all blocks that have been flushed to disk by DBWR. This work of checking the progress of DBWR once in 3 seconds

It is also an important task for CKPT. CKPT records the checkpoint location in the control file every 3 seconds, of course, in the control file at the same time.

There is also other information such as' heartbeat'. CKPT works every 3 seconds and CKPT periodically triggers DBWR. Together, these two operations are called incremental checks.

Point.

It's too hard to draw a mind map, just hit it by hand.

The corresponding buffers of a disk block in buffer is called a dirty block. When it is modified to a dirty block, the data file is not written synchronously, but to log buffer synchronously.

When there are too many dirty blocks in buffer or for other reasons, there are many ways to trigger DBWR to write dirty blocks to data files, freeing up buffer space. Each way may write dirty blocks in a different priority, that is, according to different chains, one of which is to write in the order of lrba.

First statically explain some nouns, and then discuss the process of incremental checkpointing.

The physical address of the redo buffer memory pointed to by the dirty block of RBA (redo buffer address). Lrba refers to the rba when the buffers is first modified, that is, the rba when the buffers becomes a dirty block. How many times it becomes dirty, it will remain the same, that is, the lrba will not change. The dirty block was last called hrba by the dirty rba, and the rba between the two rba has no specific name, and their dirty block is fully redone logging modified by the transaction. All dirty blocks have lrba and hrba, and if they are dirty only once, the two rba are the same.

CKPTQ . The lrba of all dirty blocks is chained together to form a queue of CKPTQ checkpoints. It is not known whether buffer really has an independent physical structure of this queue, or whether all dirty block lrba information logically points down and conceptually forms CKPTQ. )

CKPT has a variety of parts of speech, refers to an action, such as CKPT, this checkpoint; refers to a process, such as CKPT process; refers to a logical point in time, checkpoint location.

LRBA . There is a LRBA message in each dirty block, and there is also a LRBA in the control file, specifically called low cache rba, which is read by the ckpt process from a dirty block.

On disk RBA . Remember that he points to the latest (last) redo log entry in log file, which is also read by the ckpt process from a dirty block.

Incremental checkpoint process and its role in instance recovery

Just serialized graphical redo log entries, there is no need to distinguish whether these entries are current logfile or not.

A dirty block corresponds to a transaction, but not necessarily to a redo log entry, because dirty blocks produce one redo log entry at a time.

The 1.CKPT process finds that CKPTQ is too long (how long is long, and some parameters in the oracle can be controlled) and intends to inform DBWR to let him brush out some dirty blocks and shorten the CKPTQ. From low cache rba to target rba is the value calculated by CKPT according to the CKPTQ length and the system Imax 0 busy program. Return immediately after the notification, that is, asynchronous notification until the DBWR is finished.

two。 Because of the pre-write protocol, DBWR tells LGWR to write dirty blocks to the redo log file before writing dirty blocks.

3.DBWR writes dirty blocks to the data file in the order of CKPTQ from top to bottom (the figure is getting newer and newer in time from top to bottom).

If it is a complete checkpoint, DBWR writes dirty data before the checkpoint SCN at this time according to dirty list

4. In the process of DBWR writing, CKPT will check the writing progress of DBWR every 3 seconds and write the progress information (that is, which dirty block) into the control file, called low cache rba. The action every 3 seconds is called heartbeat, and he is matched with low cache rba, such as dump from the control file, indicating that when checking DBWR for the 793694908 time, he wrote to the location of 0x5.15119.0.

Low cache rba: (0x5.15119.0) on disk rba: (0x5.15314.0)

On disk scn: 0x0000.0008ea68 10/20/2012 17:15:34

Resetlogs scn: 0x0000.0006ce7b 03/30/2012 11:15:03

Heartbeat: 793694908 mount id: 1363550103

It can be said that DBWR shortens the length of the CKPTQ, and low cache rba is the write progress of the DBWR recorded by CKPT.

What exactly is written every three seconds?

In order to reduce the performance impact of frequent incremental checkpoints, CKPT makes lightweight updates. He does not overwrite the checkpoint information and header information of the data file in the control file, but only records the SCN (Controlfile Checkpointed at scn) of the checkpoint and the RBA information of the dirty blocks written by DBWR at this time in the control file.

5. It is estimated that on disk rba is the value that LGWR writes to the control file when it writes the log file.

6. When he finished writing (the location of target rba), target rba and low cache rba were the same, and the lrba of this checkpoint was recorded in the control file.

7. The redo log location pointed to by the low cache rba in the control file must be below the on disk rba (the figure is written from the bottom up, the higher the newer). If it is on him, there will be dirty blocks written to the data file instead of the redo log file.

All the redo log entries between these two rba are the entries that need to be restored during recovery, called roll forward.

eight. His log buffer must be in log file for transactions that have been submitted, but there are also items in log file that have not been submitted. This dirty block is not committed, he is written to the data file and redo log file, and he is also restored to buffer during recovery. The uncommitted data can be removed by undo rollback, which is outside the scope of discussion.

Types of Oracle checkpoint

CKPT is responsible for writing checkpoints to control files and data file headers. There are the following types of checkpoints: Thread checkpoints, which is a database checkpoint, which occurs in the following cases: consistency closing database; ALTER SYSTERM CHECKPOINT statement; online redo log file switching; ALTER DATABASE BEGIN BACKUP statement Tablespace and data file checkpoints flushes the dirty data blocks in SGA of all data files in a table space to disk, and this kind of checkpoint occurs in the following cases: a table space read-only or offline normal; shrinks a data file; ALTER DATABASE BEGIN BACKUP statement Incremental checkpoints (incremental backup) DBWn refreshes dirty data blocks in SGA to the physical file every three seconds, while CKPT issues a checkpoint every three seconds, writing the checkpoint information to the control file, but does not update the header information of the data file. Other checkpoints other checkpoints include checkpoints restored by the instance, checkpoints triggered by deletions or truncate objects.

Checkpoint queue and incremental checkpoint explained by OCP knowledge points

The main purpose of checkpoints is to refresh dirty blocks in a manner that has minimal impact on the daily operation of the database. Dirty blocks continue to produce, how to refresh the dirty blocks to the disk? Before 8i, Oracle periodically locked all modification operations and refreshed all dirty blocks in Buffer cache. This way of refreshing dirty blocks is called a complete checkpoint, which greatly affects the efficiency. After 9i, a complete checkpoint occurs only when the database is closed.

Since 8i, Oracle has added the concept of incremental checkpoints, the main purpose of which is to refresh some dirty blocks on a regular basis. It is unreasonable to refresh the dirty blocks at once, because the dirty blocks continue to be produced and are endless. Stop all user modifications like a full checkpoint and refresh the dirty blocks before continuing, which will definitely greatly affect performance. Refreshing some blocks at once for all incremental checkpoints is the best solution to the dirty block problem. So, which blocks are refreshed each time you refresh? According to statistical research, it is most reasonable to refresh the earliest dirty blocks each time according to the order in which the blocks become dirty. To achieve this, Oracle sets up another linked list in Buffer cache, the checkpoint queue. Each block is linked to the end of the checkpoint queue when it gets dirty. Like a queue, those who come at 9:00 stand first, those who come at 9:05 rank second, and everyone who comes later stands at the end of the queue, which is a queue in the order in which they arrive. This is the case with checkpoint queues, where blocks are chained to the end when they get dirty. Therefore, the checkpoint queue arranges the blocks into a queue in the chronological order in which the blocks become dirty.

As shown in the figure above, each node in the checkpoint queue points to a dirty block. In fact, there is very little information in each node of the checkpoint queue, that is, the address of the corresponding block in the Buffer cache, the location of the redo corresponding to the dirty block in the log file, and the address of the previous node and the next node. Checkpoint queues also include LRU and dirty LRU, which are two-way linked lists. A two-way linked list is to record the addresses of the first and last two nodes in the node.

The blocks in the checkpoint queue header are the first to get dirty, so Oracle periodically wakes up DBWn to refresh the dirty blocks in the order of the checkpoint queue header, starting with the checkpoint queue header. While refreshing dirty blocks, new ones can still be linked to the tail of the checkpoint queue. This periodically awakens DBWn to refresh dirty blocks, and Oracle is called an incremental checkpoint.

As shown in the above figure, the dirty blocks pointed to by nodes 1, 2 and 3 have been refreshed to clean blocks. At the same time, two more blocks become dirty, and they are linked to the end of the checkpoint queue, nodes 9 and 10.

The head of the checkpoint queue, also known as checkpoint location, Checkpoint postion, these names do not have to be taken literally. In short, the checkpoint location is the checkpoint queue head. Check the information of the head node of the point queue (that is, the location of the checkpoint), Oracle will record it frequently in the control file, and will record it very frequently. Usually every three seconds, there is a special process CKPT that records the checkpoint location in the control file.

As shown in the figure above, the current checkpoint location is No.1 of the checkpoint queue. Another three seconds is up, and the CKPT process starts, recording the new checkpoint location in the control file:

The new checkpoint location is Node 4, which corresponds to the current dirty block with the earliest dirty time. Nodes 1, 2 and 3 have been removed from the checkpoint queue. Because their corresponding dirty pieces are no longer dirty. In general, the blocks after the checkpoint location in the control file are dirty. But sometimes there are exceptions, because the checkpoint location is only updated every three seconds. As in the figure above, the dirty blocks corresponding to Node 1, Node 2 and Node 3 have been refreshed, but because the three-second interval has not arrived, the checkpoint location still points to Node 1. Only when three seconds have elapsed will the checkpoint location be updated to Node 4.

Let's stop here with regard to checkpoint queues and checkpoint locations, and before giving a comprehensive introduction to what incremental checkpoints are, let's talk about an important role of checkpoint queues.

Let's first summarize what happens inside Oracle when the user modifies the block:

1. If the block is not in Buffer cache, read the block into Buffer cache

2. Mr. Cheng redoes the record, writes it into the log cache, and writes it to the log file when the user submits it.

3. Modify blocks in Buffer cache

4. The dirty mark bit of the block is set in the Buffer cache to mark that the block becomes a dirty block, and a new node is added at the end of the checkpoint queue to record the information of the new dirty block, including the position of the dirty block in the Buffer cache and the redo record position corresponding to the dirty block generated in step 2.

5. After the user submits, the corresponding redo record is written to the log file from the redo cache.

I will now add the log to the figure above:

Like the figure above, each node of the checkpoint queue holds the address of the dirty block and the number of the redo record corresponding to the dirty block. The position of the dirty block in the Buffer cache is random, and the user does not necessarily modify that block. But redo records are generated sequentially, in the same order as checkpoint queues. Because they are all produced when the block is modified and becomes dirty. Block An is modified first, the redo record of block An is ranked first, block B is modified later, and the redo record corresponding to block B is placed behind the redo record corresponding to block A. They are in the same order as they are in the checkpoint. Whenever the database crashes due to strangeness, such as abnormal crash, power outage, etc., there are many dirty blocks in Buffer cache that are not available and written to disk. Take the figure as an example, for example, there are still seven dirty blocks on the disk that have been modified by the user. Oracle has sent the feedback message "your modification is complete" to the user, and the user thinks that their modification has been completed and will be saved to the database all the time. However, due to the resolute power outage, the data in these dirty blocks was lost, and they didn't have time to write to disk.

How does Oracle solve this problem? Very simply, when the database is restarted, Oracle only needs to read the checkpoint location from the control file, and the redo record number is recorded in the checkpoint location. according to this number, Oracle can quickly locate the redo record n in the log file, it reads the redo data in the redo record n, and reproduces the user's modification operations to the database. Next, Oracle reads the redo data in the redo record Number1 and reproduces the user modification, which continues in the order of the log flow, blocking the last redo record. In the example above, the last redo record is article n + 6. After this process is completed, all the changes made by the user are reproduced and will not be lost at all. As long as your log file is complete and the log flow is complete, no information will be lost.

Some people may have a problem, after the redo record is generated, it is also sent to the redo cache, and then written to the log file by the redo cache. Under such a mechanism, there must be some redo records in the missed and written to the log file, the database suddenly crashed, resulting in the loss of these redo records. In this way, the dirty blocks corresponding to these redo records will not be restored. Users will still lose some data.

It is true that this will happen, but what is lost is useless information. Why did you say that. Oracle will write the redo record corresponding to the transaction modification dirty block into the log file every time the user issues the commit command. Only when this operation is completed will the user receive the "commit complete" message. For a complete transaction, when the user sees that the commit is completed, it means that the corresponding redo record must be written to the log file. Even if an abnormal panic occurs, it can definitely be recovered. When the user does not commit, or does not have time to commit, the database crashes, then the transaction is incomplete, the transaction must be rolled back, it does not need to be restored at all. For such an incomplete transaction, its corresponding redo record may be lost, but it doesn't matter, because the incomplete transaction does not need to be recovered at all. In other words, only the user's transaction is committed, and the user's modification must not be lost. However, there is also a premise that the log file must not be corrupted, and what DBA has to do is to ensure that the log file cannot be corrupted. DBA can use disk mirroring techniques such as RAID1, or multiple backup log files, and so on, as we talked about in the previous chapter.

The recovery we mentioned above is automatic and does not require the involvement of DBA, which is called instance recovery.

We've already talked about the role of checkpoint queues and incremental checkpoints, and their main purpose is to let DBWn refresh dirty blocks in the order of checkpoint queues. There is also instance recovery.

Let's discuss the setting of incremental checkpoints.

The checkpoint setting mentioned here mainly refers to the frequent incremental checkpoint setting. Note that incremental checkpoint is just a noun, so you don't have to take it literally. When an incremental checkpoint occurs, Oracle wakes DBWn to write dirty blocks along the checkpoint queue, which is called an incremental checkpoint. So how often does an incremental checkpoint occur? The frequency of this incremental checkpoint is very important and basically controls how often the DBWn refreshes the dirty block. If the DBWn activity is too frequent, it will affect the overall performance of the database. If the DBWn activity is too infrequent, it will squeeze too many dirty blocks, which will also affect the performance. Moreover, if an abnormal crash occurs, the instance needs to be restored. The more dirty blocks, the slower the instance recovery. Before 9i, DBA mainly depends on interval time to set the frequency of incremental checkpoints, such as allowing Oracle to have incremental checkpoints every 10 minutes. If this number is not set properly, it will have a great impact on database performance. And it may cause the instance recovery time to be too long. After 9i, especially in 10g, checkpoints have become so intelligent that they are rarely the culprit of Icano problems. Set the fast_start_mttr_target parameter to the desired instance recovery time in 9i, and the system will automatically control the frequency of incremental checkpoints. For example, if you want the instance recovery to be completed within 5 minutes, you can set this parameter to 300, that is, 300.

If the value set by this parameter exceeds the actual limit of the hardware, for example, if you set it to 60, you expect that in any case, the database can complete instance recovery within 1 minute. However, according to the dirty block generation speed of the database and the write performance of the storage device, the instance recovery cannot be completed within 1 minute. At this point, Oracle will automatically set the appropriate fast_start_mttr_target parameter value, and we can see the revised parameter value in the parameter file and the actual value in the Target_mttr column in the V$instance_recovery view. For example:

(for example)

We cannot set this value too small, because it is only an accident that the instance is restored. If fast_start_mttr_target is set to a small value in order for instance recovery to be completed as soon as possible, DBWn will be active frequently, which can cause performance problems. In order to avoid users setting unreasonable incremental checkpoint frequency, in 10G, if fast_start_mttr_target is set to 0Oracle, Oracle will automatically adjust the checkpoint frequency according to the speed of generating dirty blocks and the performance of storage hardware, so that the checkpoint frequency does not become the original cause of the problem.

The main task of checkpoints is to urge DBWn to refresh dirty blocks. If there are too many waiting events when DBWn refreshes dirty blocks, it means that there are too many dirty blocks, the write speed of storage devices is too slow, or the frequency of incremental checkpoints is too high or too low. The wait event for DBWn to write dirty blocks is Db file parallel write. If your incremental checkpoint frequency is low and you find this event, you should set the incremental checkpoint frequency higher after eliminating the storage device write performance problem. On the other hand, if your incremental checkpoint frequency itself is very high, there are Db file parallel write events, which means that the checkpoint frequency is too high.

In addition to it, there is a wait event related to DBWn and incremental checking. It is the Write complete waits event, which occurs when the foreground process modifies several blocks in the block that DBWn is about to write in batches. This event waits for the foreground process to wait for the DBWn to finish writing. There is too much waiting, indicating that there is a problem with the write performance of the storage device, or that the incremental checkpoints are too frequent.

We can see a lot of information about checkpoints in V$instance_recovery:

If the Estimated_mttr column is too large, the checkpoint is not frequent enough, and it also means that there are too many dirty blocks. At the same time, in the V$sysstat data view, there are two data background checkpoints started, background checkpoints completed, the first is the number of background process checkpoints started, and the latter is the number of background process checkpoints completed. The meaning of background process checkpoints is actually incremental checkpoints. Only incremental checkpoints are triggered by background processes. If you use the Alter system checkpoing command to allow the system to complete a full checkpoint, this is called a foreground checkpoint that has nothing to do with incremental checkpoints and will not be recorded in these two data. If there is often a difference between the two values, for example, the number of times the checkpoint starts is less than 1 times the number of times it is completed, it means that there are too many checkpoints started but not completed in time. This indicates that the checkpoint is too frequent or the checkpoint completes too slowly.

(for example, a large number of dirty blocks are generated, log files are relatively small 5MB, and checkpoints are triggered by switching the frequency of log files, and check waiting events at the same time.)

In most cases, the problem of checkpoint is actually the problem of DBWn writing I DBWn O. The wait event for DBWn to write dirty blocks is Db file parallel write, and there is also Write complete waits wait event, which occurs when the foreground process is about to modify several blocks in the block that DBWn is about to write in batches. This event is that the foreground process waits for DBWn to finish writing. There is too much waiting, which also shows that there is something wrong with DBWn.

Note that in addition to waiting for events, we can also use the V$filestat view described in the previous sections to help determine the problem with the data file's Iamp O problem. )

The Checkpoint that describes Oracle in the official documentation of Oracle are:

(1) Thread Checkpoint

(2) Tablespace and datafile checkpoint

(3) Incremental checkpoint.

We all have a clear understanding of the latter two kinds of checkpoint. But there may be some misunderstandings about the relationship between Thread checkpoint and database checkpoint and their differences between single nodes and RAC databases. Here are some examples to explain Thread checkpoint and the difference between Thread checkpoint in a single node and in RAC.

A single-node database (or in RAC database, only one node is reserved):

* to facilitate the observation of checkpoint, set log_checkpoints_to_alert so that the information of checkpoint can be printed to alert log:

SQL > show parameter log_checkpoints_to_alert

NAME TYPE VALUE

-

Log_checkpoints_to_alert boolean TRUE

1) do "alter system switch logfile" to the database: sys@R11203 > select file#, CHECKPOINT_CHANGE# from v$datafile

FILE# CHECKPOINT_CHANGE#

1 12717430780764

2 12717430780764

3 12717430780764

4 12717430780764

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.