Checkpoint and checkpoint Optimized Parameters

Metalink also saw this error. It happened today.

Checkpoint tuning and troubleshooting guide

This topic enables DBAs to have a better understanding of the checkpoint and checkpoint optimization parameters:
-Fast_start_mttr_target
-Log_checkpoint_interval
-Log_checkpoint_timeout
-Log_checkpoints_to_alert

It also explains how to explain and handle the problems that appear in alert In. log file
Checkpoint error "'checkpoint not complete' and 'cannot allocate new log"

What is checkpoint?

Checkpoint is a database event used to synchronize modified data blocks with disk data files in the memory. It provides
Means to maintain data consistency after the transaction is committed. The mechanism for writing dirty data to Oracle disks is not synchronous with transaction commit.

Checkpoint has two targets: 1. ensure data consistency. 2. enables the database to be quickly restored. How to quickly restore it?
Because the database will set checkpoints for all changes in the data file and keep increasing it, it does not need to request checkpoints
The previous redo log. Checkpoint ensures that all data in the cache is written to the corresponding data file to prevent unexpected instances.
Data loss caused by failure.

Oracle writes this dirty data only under certain conditions:
The subsequent process requires the size of 1/4 db_block_buffer Parameters
Every three seconds
When a checkpoint is generated

A checkpoint has five event types:
Switching of each redo log
The arrival of the delay parameter log_checkpoint_timeout.
The corresponding bytes (log_checkpoint_interval * size of Io OS blocks) are written to the current redo log.

Io OS blocks: # fstyp-V/dev/vg00/lvol1 in UNIX
Vxfs
Version: 5
F_bsize: 8192

Alter system switch logfile command will directly cause checkpoint
Alter system checkpoint

The following process may occur during the Checkpoint Process:
Dbwr writes all dirty data to the data file
Lgwr updates the SCN of control files and data files

Checkpoints and Optimization
Checkpoints is a difficult point for database optimization. Frequent checkpoints can achieve rapid recovery, but it also makes the performance
Decrease. How does DBA solve this problem?

Depending on the number of database data files, a checkpoint may be high-speed. Because all data files are stored in the checkpoint
Will be frozen during this period. More frequent checkpoints can quickly restore the database. This is also the reason for the customer's tolerance for unexpected system downtime.
However, in some special cases, frequent checkpoints cannot guarantee quick recovery. Let's assume that the database is at 95% of the time.
Internal is normal, 5% the instance fails to be used, and recovery is required. Most customers prefer a 95% adjustment.
Performance rather than 5% downtime.

This assumption shows that performance is the first priority, so our goal is to reduce the checkpoints frequency during optimization.

Optimization of checkpoints includes four key initialization parameters:
-Fast_start_mttr_target
-Log_checkpoint_interval
-Log_checkpoint_timeout
-Log_checkpoints_to_alert

Each parameter is described in detail:
Fast_start_mttr_target

Since Oracle9i, The fast_start_mttr_target parameter is the preferred method for adjusting the checkpoint.
Fast_start_mttr_target can specify the number of seconds required to restore a single instance. Increase based on internal statistics
Checkpoint automatically adjusts the checkpint target to meet the needs of fast_start_mttr_target.
V $ instance_recovery.estimated_mttr displays the estimated number of seconds to restore. This value will be displayed
Even if fast_start_mttr_target is not specified.
V $ instance_recovery.target_mttr indicates the MTTR target in a short period of time.
V $ mttr_target_advice: displays the number of I/O and other I/O generated by the workload set by the current MTTR.
This view helps you assess the balance between optimization and recovery.

Log_checkpoint_interval

The log_checkpoint_interval parameter specifies the maximum number of redo blocks at intervals.
If fast_start_mttr_target is specified, log_checkpoint_interval cannot be set to 0.

In most Unix systems, the OS block size is 512 bytes. Setting log_checkpoint_interval = 10000 means
The increasing checkpoint cannot be appended to the current log because it is more than 5 MB. If your redo log is 20 mb
Issue four checkpoints for each redo log.

Log_checkpoint_interval will affect the setting of this parameter when a checkpoint occurs,
Keep it changing with the size of the redo log file. Frequent checkpoints are one of the reasons that affect database recovery.
A short checkpoint interval means that the database will be quickly restored and resource utilization is increased.

This parameter also affects the database rollback time. The actual recovery time is based on this time. Of course, there are also failure types and
The number of logs to be archived.

Log_checkpoint_timeout
This parameter specifies the interval at which the checkpoint is sent. In other words, it specifies the amount of time dirty data is written.

The checkpoint frequency affects the database recovery time. A long interval requires a longer time for database recovery.
We recommend that you use log_checkpoint_interval to control the checkpoint instead of log_checkpoint_timeout.
, Log_checkpoint_timeout sends a checkpoint every n seconds regardless of the transaction commit frequency. This may cause some
There is no necessary checkpoint when the transaction has changed. Unnecessary checkpoints must be avoided.

There is another easy misunderstanding: log_checkpoint_timeout will issue a log switch occasionally.
The log switch triggers a checkpoint, but the checkpoint does not lead to a log switch. The only manual method
Alter system switch logfile or reset the redo logs size can cause frequent switch.

The size of online redo logs is critical for optimization and recovery.