詳解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部落格 ” ，連結：http://blog.itpub.net/23135684/viewspace-1143137/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Flink Checkpoint 引數詳解
- oracle 安裝要用到的記憶體相關引數詳解Oracle記憶體
- MYSQL連線相關引數和狀態值詳解MySql
- 【DataGuard】部署Data Guard相關引數詳解 - Oracle官方文件描述Oracle
- 【DataGuard】部署Data Guard相關引數詳解
- Linux 核心引數 和 Oracle相關引數調整LinuxOracle
- Oracle checkpoint詳解Oracle
- Oracle的AMM和ASMM以及相關引數探究OracleASM
- 幾項網路安全相關的no引數詳解
- Oracle 並行相關的初始化引數Oracle並行
- Oracle checkpoint詳解一Oracle
- Oracle checkpoint詳解二Oracle
- OGG 11g Checkpoint 詳解
- Oracle 10g/11g 統計資訊相關Oracle 10g
- oracle相關的linux核心引數OracleLinux
- 關於 Oracle 10g EXPDP 的 EXCLUDE 引數Oracle 10g
- oracle 身份認證相關引數Oracle
- oracle sga配置相關的os 核心引數Oracle
- Oracle UNDO引數詳解Oracle
- Oracle11g自動記憶體管理(AMM)相關的初始化引數Oracle記憶體
- 詳解Oracle建立表的引數Oracle
- Linux 核心引數及Oracle相關引數調整LinuxOracle
- EBS相關日誌和引數
- linux中與Oracle有關的核心引數詳解LinuxOracle
- oracle初始化引數Oracle
- 【Oracle】-【sqlplus相關】-serveroutput引數OracleSQLServer
- oracle 安裝相關引數設定Oracle
- PyTorch常用引數初始化方法詳解PyTorch
- 聊聊Oracle Optimizer相關的幾個引數（中）Oracle
- 聊聊Oracle Optimizer相關的幾個引數（下）Oracle
- Oracle查詢優化器的相關引數Oracle優化
- Oracle優化相關的一些引數Oracle優化
- ORACLE初始化引數的配置Oracle
- Oracle 11G 資料庫重要的初始化引數設定Oracle資料庫
- Oracle 11g 關於 AWR 的引數設定Oracle
- (轉)Linux 核心引數及Oracle相關引數調整LinuxOracle
- oracle rac 核心引數詳解Oracle
- oracle RMAN引數配置詳解Oracle