详解Oracle 10g、11g和CHECKPOINT相关的初始化参数
这篇文章我们将详细的讨论一下10g、11g Database和CHECKPOINT相关的初始化参数以及ARCHIVE_LAG_TARGET参数的含义和作用。
下面是这次讨论的版本：
SQL> select * from v$version where rownum <=2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
我们这里讨论的参数，10g和11g是一致的。
下面是Oracle Database 10g、11g和CHECKPOINT相关的4个参数：
SQL> show parameter checkpoint;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
SQL> show parameter mttr
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 0
1.fast_start_mttr_target参数。
MTTR是Mean Time To Recover的缩写，下面是Oracle对这个参数的解释：
FAST_START_MTTR_TARGET
| Property | Description |
|---|---|
| Parameter type | Integer |
| Default value | 0 |
| Modifiable | ALTER SYSTEM |
| Range of values | 0 to 3600 seconds |
| Basic | No |
| Real Application Clusters | Multiple instances can have different values, and you can change the values at runtime. |
FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform crash recovery of a single instance. When specified,FAST_START_MTTR_TARGET is overridden by LOG_CHECKPOINT_INTERVAL.
FAST_START_MTTR_TARGET指定的是单实例数据库执行实例恢复的限制时间，指定该参数之后将覆盖LOG_CHECKPOINT_INTERVAL初始化参数，该参数的默认值为0。
我们将FAST_START_MTTR_TARGET参数设置为120秒，将log_checkpoints_to_alert设置为TRUE：
SQL> alter system set fast_start_mttr_target=120;
System altered.
SQL> show parameter mttr
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 120
SQL> alter system set log_checkpoints_to_alert=true;
System altered.
SQL> show parameter checkpoint
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean TRUE
log_checkpoints_to_alert=TRUE使得Oracle数据库告警日志将详细的记录检查点（常规检查点和增量检查点）发生的信息。
通过观察告警日志发现，在120秒内Oracle数据库没有发生任何的检查点操作（常规检查点和增量检查点），下面我们来查询一下V$INSTANCE_RECOVERY视图：
SQL> select target_mttr,estimated_mttr,ckpt_block_writes from v$instance_recovery;
TARGET_MTTR ESTIMATED_MTTR CKPT_BLOCK_WRITES
----------- -------------- -----------------
29 17 118
下面是对这3个字段的解释：
| TARGET_MTTR | NUMBER |
Effective MTTR (mean time to recover)
target value in seconds. The TARGET_MTTR value is calculated based
on the value of the FAST_START_MTTR_TARGET parameter (the TARGET_MTTR value is used internally), and is usually an
approximation of the parameter's value. However, if the FAST_START_MTTR_TARGET parameter value is very small (for example,
one second), or very large (for example, 3600 seconds), the
calculation will produce a target value dictated by system limitations. In such
cases, the TARGET_MTTR value will be the shortest calculated time,
or the longest calculated time that recovery is expected to take.
If FAST_START_MTTR_TARGET is not specified, the value of this field is the current estimated MTTR. |
| ESTIMATED_MTTR | NUMBER | Current estimated mean time to recover (MTTR) based on the number of dirty buffers and log blocks (0 if FAST_START_MTTR_TARGET is not specified). Basically, this value tells you how long you could expect recovery to take based on the work your system is doing right now. |
| CKPT_BLOCK_WRITES | NUMBER | Number of blocks written by checkpoint writes |
TARGET_MTTR的含义是：有效的MTTR目标值（单位秒），TARGET_MTTR是基于FAST_START_MTTR_TARGET参数计算出来的值（该值用于内部使用），常常是一个近似参数的值，如果FAST_START_MTTR_TARGET参数值过小或过大，计算出来的目标值将受到系统的限制，在这种情况下，TARGET_MTTR预期恢复的值将是最短的计算时间，或者最长的计算时间。
ESTIMATED_MTTR的含义是：如果FAST_START_MTTR_TARGET不等于0，该值是基于dirty buffers和log blocks的数目估算出来的当前MTTR，这个值基本能告诉我们基于当前的系统负载预期恢复的时间。
CKPT_BLOCK_WRITES的含义是：检查点操作将要写入的块数目。
当ESTIMATED_MTTR>TARGET_MTTR将触发数据库执行检查点操作，完成之后在重新计算检查点时间。如果ESTIMATED_MTTR的值持续大于TARGET_MTTR的值，那么可能是IO资源存在瓶颈。
由此我们可以得出这样一个结论：FAST_START_MTTR_TARGET设置的时间跟数据库的运行时间无关，只跟数据库根据dirty buffers和log blocks的数目计算出来的MTTR时间有关。
从Oracle 10g开始，Oracle能自动调整检查点，当FAST_START_MTTR_TARGET设置为0，Oracle自动调整检查点功能即被启用。在告警日志中能看到如下的信息：
Wed Apr 16 13:16:37 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
自动调整检查点功能将利用IO不繁忙的时候执行检查点操作，这将一定程度提高高峰期数据库的性能。如果对数据库恢复时间没有固定的要求，可以保留FAST_START_MTTR_TARGET的默认值。
2.LOG_CHECKPOINT_INTERVAL参数。
下面是Oracle对该参数的解释：
LOG_CHECKPOINT_INTERVAL
| Property | Description |
|---|---|
| Parameter type | Integer |
| Default value | 0 |
| Modifiable | ALTER SYSTEM |
| Range of values | 0 to 231 - 1 |
| Basic | No |
| Real Application Clusters | Multiple instances can have different values. |
LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. This number refers to physical operating system blocks, not database blocks.
Regardless of this value, a checkpoint always occurs when switching from one online redo log file to another. Therefore, if the value exceeds the actual redo log file size, checkpoints occur only when switching logs. Checkpoint frequency is one of the factors that influence the time required for the database to recover from an unexpected failure.
LOG_CHECKPOINT_INTERVAL指定的是增量检查点和最后写道redo log中的块之间的redo log块的数目，超过了这个数据将触发检查点。该值设定的是物理操作系统block，不是数据库块。无论这个值是多少，当数据库Redo Log发生切换时都会触发检查点。因此，如果这个值超过了redo log文件的大小，只有当切换日志是才会发生检查点。
如果设置了FAST_START_MTTR_TARGET值，LOG_CHECKPOINT_INTERVAL将被忽略。
我们将LOG_CHECKPOINT_INTERVAL参数设置为100：
SQL> alter system set log_checkpoint_interval=100;
System altered.
SQL> show parameter checkpoint
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_checkpoint_interval integer 100
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean TRUE
SQL> show parameter mttr
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 0
查询V$INSTANCE_RECOVERY视图：
SQL> select target_redo_blks,actual_redo_blks,log_file_size_redo_blks,log_chkpt_interval_redo_blks from v$instance_recovery;
TARGET_REDO_BLKS ACTUAL_REDO_BLKS LOG_FILE_SIZE_REDO_BLKS LOG_CHKPT_INTERVAL_REDO_BLKS
---------------- ---------------- ----------------------- ----------------------------
100 78 184320 100
下面是对着几个字段的解释：
| ACTUAL_REDO_BLKS | NUMBER | Current actual number of redo blocks required for recovery |
| TARGET_REDO_BLKS | NUMBER | Current target number of redo blocks that must be processed for recovery. This value is the minimum value of the following 3 columns, and identifies which of the 3 user-defined limits determines checkpointing. |
| LOG_FILE_SIZE_REDO_BLKS | NUMBER | Maximum number of redo blocks required to guarantee that a log switch does not occur before the checkpoint completes. |
| LOG_CHKPT_TIMEOUT_REDO_BLKS | NUMBER | Number of redo blocks that need to be processed during recovery to satisfy the LOG_CHECKPOINT_TIMEOUT parameter. The value displayed is not meaningful unless that parameter has been set. |
| LOG_CHKPT_INTERVAL_REDO_BLKS | NUMBER | Number of redo blocks that need to be processed during recovery to satisfy the LOG_CHECKPOINT_INTERVAL parameter. The value displayed is not meaningful unless that parameter has been set. |
LOG_CHKPT_INTERVAL_REDO_BLKS的含义是在恢复期间满足LOG_CHECKPOINT_INTERVAL参数需要处理的redo block数目。这个参数只在设置了LOG_CHECKPOINT_INTERVAL才有意义。
LOG_FILE_SIZE_REDO_BLKS的含义是没有发生日志切换，没有完成CHECKPOINT之前要求保留的最大redo块。
TARGET_REDO_BLKS的含义是当前恢复必须处理的目标块数目，这个值是LOG_FILE_SIZE_REDO_BLKS、LOG_CHKPT_TIMEOUT_REDO_BLKS、LOG_CHKPT_INTERVAL-REDO_BLKS 3个值的最小值，以确定3个用户定义的CHECKPOINT限制值哪个生效。
ACTUAL_REDO_BLKS的含义是当前恢复操作要处理活动的redo block数目。
如果ACTUAL_REDO_BLKS>=TARGET_REDO_BLKS将触发检查点操作。
3.LOG_CHECKPOINT_TIMEOUT参数。
下面是Oracle对LOG_CHECKPOINT_TIMEOUT的解释：
LOG_CHECKPOINT_TIMEOUT
| Property | Description |
|---|---|
| Parameter type | Integer |
| Default value | 1800 |
| Modifiable | ALTER SYSTEM |
| Range of values | 0 to 231 - 1 |
| Basic | No |
| Real Application Clusters | Multiple instances can have different values. |
LOG_CHECKPOINT_TIMEOUT specifies (in seconds) the amount of time that has passed since the incremental checkpoint at the position where the last write to the redo log (sometimes called the tail of the log) occurred. This parameter also signifies that no buffer will remain dirty (in the cache) for more than integer seconds.
Specifying a value of 0 for the timeout disables time-based checkpoints. Hence, setting the value to 0 is not recommended unless FAST_START_MTTR_TARGET is set.
LOG_CHECKPOINT_TIMEOUT指定的是自动上一次增量检查点到最后的redo log写发生经过的总的时间（单位秒），这个参数意味着一旦增量检查点间隔时间超过了该值，将触发新的增量检查点。
将该值设置为0将禁用基于时间的检查点，因此，除非设置了FAST_START_MTTR_TARGET，否则不建议将该参数设置为0，该参数的默认值为1800，30分钟。
我们将LOG_CHECKPOINT_TIMEOUT设置为80：
SQL> alter system set log_checkpoint_timeout=80;
System altered.
SQL> show parameter checkpoint
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_checkpoint_interval integer 100
log_checkpoint_timeout integer 80
log_checkpoints_to_alert boolean TRUE
SQL> show parameter mttr
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 0
从告警日志可以看出：
Wed Apr 16 04:04:13 2014
ALTER SYSTEM SET log_checkpoint_timeout=80 SCOPE=BOTH;
Wed Apr 16 04:04:14 2014
Incremental checkpoint up to RBA [0x5.15fb.0], current log tail at RBA [0x5.15fb.0]
Wed Apr 16 04:05:32 2014
Incremental checkpoint up to RBA [0x5.1601.0], current log tail at RBA [0x5.1609.0]
Wed Apr 16 04:06:50 2014
Incremental checkpoint up to RBA [0x5.1617.0], current log tail at RBA [0x5.1617.0]
Wed Apr 16 04:08:08 2014
Incremental checkpoint up to RBA [0x5.1624.0], current log tail at RBA [0x5.1624.0]
查看V$INSTANCE_RECOVERY视图：
SQL> select target_redo_blks,actual_redo_blks,log_file_size_redo_blks,log_chkpt_timeout_redo_blks,log_chkpt_interval_redo_blks from v$instance_recovery;
TARGET_REDO_BLKS ACTUAL_REDO_BLKS LOG_FILE_SIZE_REDO_BLKS LOG_CHKPT_TIMEOUT_REDO_BLKS LOG_CHKPT_INTERVAL_REDO_BLKS
---------------- ---------------- ----------------------- --------------------------- ----------------------------
24 14 184320 24 100
TARGET_REDO_BLKS=LOG_CHKPT_TIMEOUT_REDO_BLKS,说明当前是log_checkpoint_timeout在起作用。TARGET_REDO_BLKS和LOG_CHKPT_TIMEOUT_REDO_BLKS会随时发生变化。
4.ARCHIVE_LAG_TARGET参数。
下面是Oracle对ARCHIVE_LAG_TARGET参数的解释：
ARCHIVE_LAG_TARGET
| Property | Description |
|---|---|
| Parameter type | Integer |
| Default value | 0 (disabled) |
| Modifiable | ALTER SYSTEM |
| Range of values | 0 or any integer in [60, 7200] |
| Basic | No |
| Real Application Clusters | Multiple instances should use the same value. |
ARCHIVE_LAG_TARGET limits the amount of data that can be lost and effectively increases the availability of the standby database by forcing a log switch after the specified amount of time elapses.
A 0 value disables the time-based thread advance feature; otherwise, the value represents the number of seconds. Values larger than 7200 seconds are not of much use in maintaining a reasonable lag in the standby database. The typical, or recommended value is 1800 (30 minutes). Extremely low values can result in frequent log switches, which could degrade performance; such values can also make the archiver process too busy to archive the continuously generated logs.
ARCHIVE_LAG_TARGET限制大量的数据丢失，通过超过指定时间之后强制redo log切换，有效的增加了standby database的高可用性。值为0将禁用该特性，否则这个值表示秒数，Oracle推荐的值为1800（30分钟），较低的值将导致过于频繁的切换，将影响性能，这样的值将使得归档进程忙于归档，不断的生成日志。我们将ARCHIVE_LAG_TARGET设置为30：
SQL> alter system set archive_lag_target=30;
System altered.
SQL> show parameter archive_lag_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 60
可以看到该参数的最小值是60。
查看操作系统归档目的地：
[oracle1@redhat5 2014_04_16]$ ll
total 23060
-rw-r----- 1 oracle1 oinstall 4848128 Apr 16 02:04 o1_mf_1_3_9ntx62g5_.arc
-rw-r----- 1 oracle1 oinstall 15600640 Apr 16 03:00 o1_mf_1_4_9nv0g8c6_.arc
-rw-r----- 1 oracle1 oinstall 3097600 Apr 16 04:31 o1_mf_1_5_9nv5r5ly_.arc
-rw-r----- 1 oracle1 oinstall 6144 Apr 16 04:32 o1_mf_1_6_9nv5t4of_.arc
-rw-r----- 1 oracle1 oinstall 6144 Apr 16 04:33 o1_mf_1_7_9nv5w10y_.arc
-rw-r----- 1 oracle1 oinstall 6144 Apr 16 04:34 o1_mf_1_8_9nv5xx3k_.arc
从上面的数据可以看到，Oracle每分钟生成一个归档Redo Log。
注意：
对于不繁忙的数据库更应该设置该值，不繁忙的系统可能几小时才切换一次redo log，如果在切换前由于断电或其他原因导致CURRENT或ACTIVE的Redo log损坏，那么丢失的将是几小时的数据。
--end--
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30251003/viewspace-1700813/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
