checkpoint詳解（部分轉）

checkpoint是什麼？

checkpoint是資料庫的一個內部事件，

這個事件啟用以後會觸發資料庫寫程式(DBWR)將資料緩衝(DATA BUFFER CACHE)中的髒資料塊寫出到資料檔案中。

 

checkpoint的作用是什麼？

checkpoint主要2個作用：

1、保證資料庫的一致性，

這是指將髒資料寫出到硬碟，保證記憶體和硬碟上的資料是一樣的；

2、縮短例項恢復的時間，例項恢復要把例項異常關閉前沒有寫出到硬碟的髒資料通過日誌進行恢復。

如果髒塊過多，例項恢復的時間也會很長，檢查點的發生可以減少髒塊的數量，從而提高例項恢復的時間。

 

checkpoint就像word的自動儲存一樣。

 

checkpoint的型別：

完全檢查點：

定義：清除髒列表(DIRTY LIST OR CHECKPOINT ENQUEUE)中所有資料塊。

什麼時候發生：ALTER SYSTEM CHECKPOINT; SHUTDOWN;

增量檢查點:

定義：根據檢查點的條件清除髒列表中的部分資料塊，直到滿足所有檢查點條件為止。

什麼時候發生：CKPT程式每3秒被喚醒，CKPT檢查當前的所有checkpoint條件，

如果任何一個條件不能被滿足，那麼CKPT發出增量檢查點。

檢查點條件有哪些？

90％ OF THE SMALLEST REDO LOGFILEITPUB

FAST_START_MTTR_TARGET

FAST_START_IO_TARGET

LOG_CHECKPOINT_TIMEOUT

LOG_CHECKPOINT_INTERVAL

 

90％ OF THE SMALLEST REDO LOGFILE :

意味著最後一次增量檢查點與當前日誌檔案末尾所差的redo block數量如果超過最小redo log的90％，那麼就會觸發增量檢查點。

FAST_START_MTTR_TARGET：例項恢復的時間限制，

將這個時間換算成redo blocks數量，當log buffer中未寫入log file的redo block數量超過這個值，就會觸發增量檢查點。

FAST_START_IO_TARGET：例項恢復所需要讀取的redo blocks數量，

當log buffer中未寫入log file的redo block數量超過這個值，就會觸發增量檢查點。

LOG_CHECKPOINT_TIMEOUT：2次增量檢查點的時間間隔。

LOG_CHECKPOINT_INTERVAL：最後一次增量檢查點與當前日誌檔案末尾所差的redo block數量。

 

注意：增量檢查點並不是將髒列表中的所有髒塊都寫出到資料檔案中，而是寫出一部分，保證滿足所有條件即可。

 

 

 

 

 

相關概念：

RBA checkpoin rba on-disk rba RBA:redo block address 重作日誌地址

logfile sequence number(4bytes)

logfile block number(4bytes)

redo entry offset(2bytes)

checkpoint rba：最後一次檢查點對應的重作日誌地址，意味著這個地址之前的redo log都是例項恢復不需要的。

例項恢復的起點

on-disk rba：當前日誌中最新的重作日誌地址。

例項恢復的終點

 

相關檢視：x$kcccp v$instance_recovery v$instance_recovery例項恢復對應的檢視:

actual_redo_blks:最後一次檢查點到當前日誌尾所差的redo blocks數量；

target_redo_blks:所有檢查點條件中最小的條件相差的redo blocks數量；

log_file_size_redo_blks:最小日誌組的90％大小所對應的redo blocks數量；--這是一個增量檢查點條件

log_chkpt_timeout_redo_blks:有log_checkpoint_timeout引數所轉換的redo blocks數量； --這也是一個增量檢查點條件

target_mttr:有fast_start_mttr_target引數所限制的例項恢復的最大時間

estimated_mttr:根據當前最後一次檢查點與日誌尾所差的redo blocks數量估算出來的mttr。

 

x$kcccp 增量檢查點對應的檢視：

CPLRBA_SEQ：最後一次增量檢查點對應rba的第一部分--日誌序列號；

CPLRBA_BNO：最後一次增量檢查點對應rba的第二部分--日誌塊數；

CPLRBA_BOF：最後一次增量檢查點對應rba的第三部分--日誌偏移量；

CPODR_SEQ：日誌尾的rda的第一部分--日誌序列號；

CPODR_BNO：日誌尾的rda的第二部分--日誌塊數；

CPODR_BOF：日誌尾的rda的第二部分--日誌偏移量；

CPHBT：檢查點心跳數。

 

實驗測試：

1、完全檢查點：

 

SQL> show parameter log_checkpoint_

NAME                                 TYPE        VALUE

------------------------------------ ----------- ---------------------

log_checkpoint_interval              integer     0

log_checkpoint_timeout               integer     1800

log_checkpoints_to_alert             boolean     FALSE

 

SQL> alter system set log_checkpoints_to_alert=true scope=both;

 

系統已更改。

 

SQL> show parameter log_check

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------

log_checkpoint_interval              integer     0

log_checkpoint_timeout               integer     1800

log_checkpoints_to_alert             boolean     TRUE

 

SQL> alter system checkpoint;

 

系統已更改。

 

日誌中的資訊：完全檢查點立即執行。

Beginning global checkpoint up to RBA [0x6b.861.10], SCN: 5529715

Completed checkpoint up to RBA [0x6b.861.10], SCN: 5529715

 

從v$instance_recovery 中看到actual_redo_blks瞬間為0，說明完全檢查點清除髒列表上的所有的髒塊。

 

同時也會完成之前沒有完成的日誌切換檢查點，這時查詢v$log，active的狀態轉變為inactive。

 

第1個視窗

SQL> alter system checkpoint;

 

系統已更改。

 

第2個視窗

SQL> select actual_redo_blks from v$instance_recovery;

 

ACTUAL_REDO_BLKS

----------------

              43

 

SQL> select actual_redo_blks from v$instance_recovery;

 

ACTUAL_REDO_BLKS

----------------

              43

             

當第一個視窗執行完全檢查點後              

 

SQL> select actual_redo_blks from v$instance_recovery;

 

ACTUAL_REDO_BLKS

----------------

               0

 

此時檢視警報日誌：

Beginning global checkpoint up to RBA [0x6b.892.10], SCN: 5529786

Completed checkpoint up to RBA [0x6b.892.10], SCN: 5529786

 

RBA十六進位制6b轉成十進位制就是107，是當前的線上日誌序列號：

SQL> select group#,sequence#,status from v$Log;

 

    GROUP#  SEQUENCE# STATUS

---------- ---------- ----------------

         1        105 INACTIVE

         2        106 INACTIVE

         3        107 CURRENT

         4        104 INACTIVE

 

SQL> select group#,member from v$logfile where group#=3;

 

    GROUP#

----------

MEMBER

-----------------------------------------------------------

         3

D:\ORACLE\PRODUCT\10.2.0\ORADATA\WEIBIN\REDO03.LOG

 

 

SQL> alter system dump logfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\WEIBIN\REDO03.LOG';

 

系統已更改。

 

 

找出對應的轉儲日誌，位置在user_dump_dest指定的目錄下面

 

SQL> show parameter user_dump_dest

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

user_dump_dest                       string      D:\ORACLE\PRODUCT\10.2.0\ADMIN

                                                 \WEIBIN\UDUMP

 

SQL> select spid from v$process where addr = (

  2  select paddr from v$session where sid = (select distinct sid from v$mystat));

 

SPID

------------

2220

 

在D:\ORACLE\PRODUCT\10.2.0\ADMIN\WEIBIN\UDUMP目錄下對應轉儲日誌名為：weibin_ora_2220.trc

找到對應的rba

 

REDO RECORD - Thread:1 RBA: 0x00006b.00000892.0010 LEN: 0x0090 VLD: 0x06

SCN: 0x0000.005460ba SUBSCN:  1 06/29/2011 11:17:11

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 1 rdba: 0x0040067a BFT:(1024,4195962) non-BFT:(1,1658)

                   scn: 0x0000.005460af seq: 0x02 flg:0x06

 Block Written - afn: 2 rdba: 0x00800019 BFT:(1024,8388633) non-BFT:(2,25)

                   scn: 0x0000.005460ab seq: 0x02 flg:0x04

 Block Written - afn: 2 rdba: 0x00800029 BFT:(1024,8388649) non-BFT:(2,41)

                   scn: 0x0000.005460ad seq: 0x02 flg:0x04

 

REDO RECORD - Thread:1 RBA: 0x00006b.00000892.00a0 LEN: 0x0054 VLD: 0x02

SCN: 0x0000.005460ba SUBSCN:  1 06/29/2011 11:17:11

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 2 rdba: 0x00800039 BFT:(1024,8388665) non-BFT:(2,57)

                   scn: 0x0000.00546078 seq: 0x02 flg:0x04

 Block Written - afn: 2 rdba: 0x00800047 BFT:(1024,8388679) non-BFT:(2,71)

                   scn: 0x0000.005460a3 seq: 0x07 flg:0x04

 

REDO RECORD - Thread:1 RBA: 0x00006b.00000892.00f4 LEN: 0x0054 VLD: 0x02

SCN: 0x0000.005460ba SUBSCN:  1 06/29/2011 11:17:11

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 2 rdba: 0x00800049 BFT:(1024,8388681) non-BFT:(2,73)

                   scn: 0x0000.005460af seq: 0x02 flg:0x04

 Block Written - afn: 2 rdba: 0x00800059 BFT:(1024,8388697) non-BFT:(2,89)

                   scn: 0x0000.005460a2 seq: 0x01 flg:0x04

 

REDO RECORD - Thread:1 RBA: 0x00006b.00000892.0148 LEN: 0x0054 VLD: 0x02

SCN: 0x0000.005460ba SUBSCN:  1 06/29/2011 11:17:11

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 2 rdba: 0x00800069 BFT:(1024,8388713) non-BFT:(2,105)

                   scn: 0x0000.0054608f seq: 0x02 flg:0x04

 Block Written - afn: 2 rdba: 0x00800079 BFT:(1024,8388729) non-BFT:(2,121)

                   scn: 0x0000.00546093 seq: 0x02 flg:0x04

 

REDO RECORD - Thread:1 RBA: 0x00006b.00000892.019c LEN: 0x0054 VLD: 0x02

SCN: 0x0000.005460ba SUBSCN:  1 06/29/2011 11:17:11

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 2 rdba: 0x00800089 BFT:(1024,8388745) non-BFT:(2,137)

                   scn: 0x0000.005460a4 seq: 0x01 flg:0x04

 Block Written - afn: 2 rdba: 0x00800139 BFT:(1024,8388921) non-BFT:(2,313)

                   scn: 0x0000.00546093 seq: 0x03 flg:0x04

 

REDO RECORD - Thread:1 RBA: 0x00006b.00000893.0010 LEN: 0x0054 VLD: 0x02

SCN: 0x0000.005460ba SUBSCN:  1 06/29/2011 11:17:11

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 2 rdba: 0x008003dc BFT:(1024,8389596) non-BFT:(2,988)

                   scn: 0x0000.005460ab seq: 0x01 flg:0x04

 Block Written - afn: 3 rdba: 0x00c00a65 BFT:(1024,12585573) non-BFT:(3,2661)

                   scn: 0x0000.005460a4 seq: 0x01 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006b.00000893.0064 LEN: 0x0054 VLD: 0x02

SCN: 0x0000.005460ba SUBSCN:  1 06/29/2011 11:17:11

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c00a6c BFT:(1024,12585580) non-BFT:(3,2668)

                   scn: 0x0000.005460a4 seq: 0x01 flg:0x06

 Block Written - afn: 3 rdba: 0x00c0697f BFT:(1024,12609919) non-BFT:(3,27007)

                   scn: 0x0000.005460ad seq: 0x02 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006b.00000893.00b8 LEN: 0x0054 VLD: 0x02

SCN: 0x0000.005460ba SUBSCN:  1 06/29/2011 11:17:11

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c079f0 BFT:(1024,12614128) non-BFT:(3,31216)

                   scn: 0x0000.005460ad seq: 0x02 flg:0x06

 Block Written - afn: 3 rdba: 0x00c07ed4 BFT:(1024,12615380) non-BFT:(3,32468)

                   scn: 0x0000.005460a4 seq: 0x01 flg:0x06

 

注意以上紅色部分，這條redo record的SCN: 0x0000.005460ba，5460ba轉換成十進位制為5529786和檢查點scn是一致的。

 

SQL> select file#, checkpoint_change# from v$datafile;

 

     FILE# CHECKPOINT_CHANGE#

---------- ------------------

         1            5556107

         2            5556107

         3            5556107

         4            5556107

         5            5556107

         6            5556107

         7            5556107

         8            5556107

         9            5556107

        10            5556107

        11            5556107

 

已選擇11行。

 

SQL> select to_char(5556107,'xxxxxxx') from dual;

 

TO_CHAR(

--------

  54c78b

 

SQL> select count(*) from v$bh where dirty='Y';

 

  COUNT(*)

----------

        57

 

SQL> alter system checkpoint

  2  ;

 

系統已更改。

 

SQL> select count(*) from v$bh where dirty='Y';

 

  COUNT(*)

----------

         0

 

SQL> select file#,checkpoint_change# from v$datafile;

 

     FILE# CHECKPOINT_CHANGE#

---------- ------------------

         1            5556412

         2            5556412

         3            5556412

         4            5556412

         5            5556412

         6            5556412

         7            5556412

         8            5556412

         9            5556412

        10            5556412

        11            5556412

 

已選擇11行。

 

SQL> select to_char(5556412,'xxxxxxx') from dual;

 

TO_CHAR(

--------

54c8bc

SQL> select group#,status from v$log;

 

    GROUP# STATUS

---------- ----------------

         1 INACTIVE

         2 INACTIVE

         3 INACTIVE

         4 CURRENT

 

SQL> select group#,member from v$logfile where group#=4;

 

    GROUP#

----------

MEMBER

-----------------------------------------------------------------------

         4

D:\ORACLE\PRODUCT\10.2.0\ORADATA\WEIBIN\REDO04.LOG

SQL> alter system dump logfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\WEIBIN\REDO04.LOG';

系統已更改。

找到54c8bc的redo record：正好是57個髒塊

REDO RECORD - Thread:1 RBA: 0x00006c.000021ef.0010 LEN: 0x00d0 VLD: 0x06

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 6 rdba: 0x01801b70 BFT:(1024,25172848) non-BFT:(6,7024)

                   scn: 0x0000.0054c8a7 seq: 0x04 flg:0x04

 Block Written - afn: 7 rdba: 0x01c00261 BFT:(1024,29360737) non-BFT:(7,609)

                   scn: 0x0000.0054c897 seq: 0x03 flg:0x06

 Block Written - afn: 7 rdba: 0x01c00262 BFT:(1024,29360738) non-BFT:(7,610)

                   scn: 0x0000.0054c874 seq: 0x02 flg:0x06

 Block Written - afn: 7 rdba: 0x01c00264 BFT:(1024,29360740) non-BFT:(7,612)

                   scn: 0x0000.0054c89b seq: 0x02 flg:0x06

 Block Written - afn: 7 rdba: 0x01c00265 BFT:(1024,29360741) non-BFT:(7,613)

                   scn: 0x0000.0054c893 seq: 0x02 flg:0x06

 Block Written - afn: 7 rdba: 0x01c00266 BFT:(1024,29360742) non-BFT:(7,614)

                   scn: 0x0000.0054c874 seq: 0x02 flg:0x06

 Block Written - afn: 7 rdba: 0x01c00267 BFT:(1024,29360743) non-BFT:(7,615)

                   scn: 0x0000.0054c874 seq: 0x02 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021ef.00e0 LEN: 0x0064 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 7 rdba: 0x01c00525 BFT:(1024,29361445) non-BFT:(7,1317)

                   scn: 0x0000.0054c893 seq: 0x02 flg:0x06

 Block Written - afn: 6 rdba: 0x018000b2 BFT:(1024,25166002) non-BFT:(6,178)

                   scn: 0x0000.0054c89d seq: 0x02 flg:0x04

 Block Written - afn: 6 rdba: 0x01800115 BFT:(1024,25166101) non-BFT:(6,277)

                   scn: 0x0000.0054c899 seq: 0x02 flg:0x04

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021ef.0144 LEN: 0x0064 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 6 rdba: 0x018006a6 BFT:(1024,25167526) non-BFT:(6,1702)

                   scn: 0x0000.0054c893 seq: 0x09 flg:0x04

 Block Written - afn: 6 rdba: 0x018006b4 BFT:(1024,25167540) non-BFT:(6,1716)

                   scn: 0x0000.0054c857 seq: 0x01 flg:0x04

 Block Written - afn: 6 rdba: 0x018006b6 BFT:(1024,25167542) non-BFT:(6,1718)

                   scn: 0x0000.0054c8a9 seq: 0x02 flg:0x04

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021ef.01a8 LEN: 0x0064 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 2 rdba: 0x00800009 BFT:(1024,8388617) non-BFT:(2,9)

                   scn: 0x0000.0054c8ab seq: 0x02 flg:0x04

 Block Written - afn: 2 rdba: 0x00800019 BFT:(1024,8388633) non-BFT:(2,25)

                   scn: 0x0000.0054c8af seq: 0x02 flg:0x04

 Block Written - afn: 2 rdba: 0x00800029 BFT:(1024,8388649) non-BFT:(2,41)

                   scn: 0x0000.0054c870 seq: 0x02 flg:0x04

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f0.001c LEN: 0x0064 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 2 rdba: 0x00800039 BFT:(1024,8388665) non-BFT:(2,57)

                   scn: 0x0000.0054c8a7 seq: 0x02 flg:0x04

 Block Written - afn: 2 rdba: 0x00800049 BFT:(1024,8388681) non-BFT:(2,73)

                   scn: 0x0000.0054c8ad seq: 0x02 flg:0x04

 Block Written - afn: 2 rdba: 0x00800059 BFT:(1024,8388697) non-BFT:(2,89)

                   scn: 0x0000.0054c89d seq: 0x02 flg:0x04

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f0.0080 LEN: 0x0054 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 2 rdba: 0x00800069 BFT:(1024,8388713) non-BFT:(2,105)

                   scn: 0x0000.0054c8a9 seq: 0x02 flg:0x04

 Block Written - afn: 2 rdba: 0x00800079 BFT:(1024,8388729) non-BFT:(2,121)

                   scn: 0x0000.0054c8a5 seq: 0x02 flg:0x04

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f0.00d4 LEN: 0x0054 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 2 rdba: 0x00800089 BFT:(1024,8388745) non-BFT:(2,137)

                   scn: 0x0000.0054c899 seq: 0x02 flg:0x04

 Block Written - afn: 2 rdba: 0x00800099 BFT:(1024,8388761) non-BFT:(2,153)

                   scn: 0x0000.0054c8b1 seq: 0x02 flg:0x04

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f0.0128 LEN: 0x0054 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 2 rdba: 0x00800148 BFT:(1024,8388936) non-BFT:(2,328)

                   scn: 0x0000.0054c8a5 seq: 0x02 flg:0x04

 Block Written - afn: 2 rdba: 0x00800247 BFT:(1024,8389191) non-BFT:(2,583)

                   scn: 0x0000.0054c8b1 seq: 0x03 flg:0x04

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f0.017c LEN: 0x0054 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 2 rdba: 0x008002d9 BFT:(1024,8389337) non-BFT:(2,729)

                   scn: 0x0000.0054c8ab seq: 0x04 flg:0x04

 Block Written - afn: 2 rdba: 0x0080031f BFT:(1024,8389407) non-BFT:(2,799)

                   scn: 0x0000.0054c874 seq: 0x08 flg:0x04

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f0.01d0 LEN: 0x0054 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 2 rdba: 0x00800321 BFT:(1024,8389409) non-BFT:(2,801)

                   scn: 0x0000.0054c8ad seq: 0x02 flg:0x04

 Block Written - afn: 2 rdba: 0x008003ee BFT:(1024,8389614) non-BFT:(2,1006)

                   scn: 0x0000.0054c8a3 seq: 0x03 flg:0x04

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f1.0034 LEN: 0x0054 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 2 rdba: 0x008003f0 BFT:(1024,8389616) non-BFT:(2,1008)

                   scn: 0x0000.0054c8af seq: 0x02 flg:0x04

 Block Written - afn: 2 rdba: 0x00800592 BFT:(1024,8390034) non-BFT:(2,1426)

                   scn: 0x0000.0054c870 seq: 0x01 flg:0x04

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f1.0088 LEN: 0x0054 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c01315 BFT:(1024,12587797) non-BFT:(3,4885)

                   scn: 0x0000.0054c893 seq: 0x02 flg:0x06

 Block Written - afn: 3 rdba: 0x00c05ca0 BFT:(1024,12606624) non-BFT:(3,23712)

                   scn: 0x0000.0054c893 seq: 0x03 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f1.00dc LEN: 0x0074 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c05ccd BFT:(1024,12606669) non-BFT:(3,23757)

                   scn: 0x0000.0054c8a7 seq: 0x02 flg:0x06

 Block Written - afn: 3 rdba: 0x00c05cce BFT:(1024,12606670) non-BFT:(3,23758)

                   scn: 0x0000.0054c8a3 seq: 0x02 flg:0x06

 Block Written - afn: 3 rdba: 0x00c05ccf BFT:(1024,12606671) non-BFT:(3,23759)

                   scn: 0x0000.0054c89b seq: 0x02 flg:0x06

 Block Written - afn: 3 rdba: 0x00c05cd5 BFT:(1024,12606677) non-BFT:(3,23765)

                   scn: 0x0000.0054c874 seq: 0x06 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f1.0150 LEN: 0x0044 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c05cd7 BFT:(1024,12606679) non-BFT:(3,23767)

                   scn: 0x0000.0054c89f seq: 0x02 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f1.0194 LEN: 0x0044 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c05cdf BFT:(1024,12606687) non-BFT:(3,23775)

                   scn: 0x0000.0054c8ab seq: 0x02 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f1.01d8 LEN: 0x0044 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c05f00 BFT:(1024,12607232) non-BFT:(3,24320)

                   scn: 0x0000.0054c8ab seq: 0x02 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f2.002c LEN: 0x0044 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c05f18 BFT:(1024,12607256) non-BFT:(3,24344)

                   scn: 0x0000.0054c8ab seq: 0x02 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f2.0070 LEN: 0x0044 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c06fb0 BFT:(1024,12611504) non-BFT:(3,28592)

                   scn: 0x0000.0054c8a9 seq: 0x02 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f2.00b4 LEN: 0x0044 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c07442 BFT:(1024,12612674) non-BFT:(3,29762)

                   scn: 0x0000.0054c8af seq: 0x02 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f2.00f8 LEN: 0x0044 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c079c4 BFT:(1024,12614084) non-BFT:(3,31172)

                   scn: 0x0000.0054c893 seq: 0x02 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f2.013c LEN: 0x0044 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c079c7 BFT:(1024,12614087) non-BFT:(3,31175)

                   scn: 0x0000.0054c89f seq: 0x02 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f2.0180 LEN: 0x0044 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c079ea BFT:(1024,12614122) non-BFT:(3,31210)

                   scn: 0x0000.0054c8af seq: 0x02 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f2.01c4 LEN: 0x0044 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c079ec BFT:(1024,12614124) non-BFT:(3,31212)

                   scn: 0x0000.0054c8ad seq: 0x02 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f3.0018 LEN: 0x0044 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c07a54 BFT:(1024,12614228) non-BFT:(3,31316)

                   scn: 0x0000.0054c8ab seq: 0x02 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f3.005c LEN: 0x0074 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c07eca BFT:(1024,12615370) non-BFT:(3,32458)

                   scn: 0x0000.0054c874 seq: 0x02 flg:0x06

 Block Written - afn: 3 rdba: 0x00c07ecb BFT:(1024,12615371) non-BFT:(3,32459)

                   scn: 0x0000.0054c893 seq: 0x02 flg:0x06

 Block Written - afn: 3 rdba: 0x00c07ecc BFT:(1024,12615372) non-BFT:(3,32460)

                   scn: 0x0000.0054c893 seq: 0x02 flg:0x06

 Block Written - afn: 3 rdba: 0x00c07ecd BFT:(1024,12615373) non-BFT:(3,32461)

                   scn: 0x0000.0054c89f seq: 0x02 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f3.00d0 LEN: 0x0054 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c07ecf BFT:(1024,12615375) non-BFT:(3,32463)

                   scn: 0x0000.0054c897 seq: 0x02 flg:0x06

 Block Written - afn: 3 rdba: 0x00c07ed0 BFT:(1024,12615376) non-BFT:(3,32464)

                   scn: 0x0000.0054c897 seq: 0x02 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f3.0124 LEN: 0x0044 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 3 rdba: 0x00c07f71 BFT:(1024,12615537) non-BFT:(3,32625)

                   scn: 0x0000.0054c89f seq: 0x02 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f3.0168 LEN: 0x0044 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 1 rdba: 0x0040067a BFT:(1024,4195962) non-BFT:(1,1658)

                   scn: 0x0000.0054c8b1 seq: 0x02 flg:0x06

 

REDO RECORD - Thread:1 RBA: 0x00006c.000021f3.01ac LEN: 0x0044 VLD: 0x02

SCN: 0x0000.0054c8bc SUBSCN:  1 06/29/2011 14:18:20

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:23.1

 Block Written - afn: 1 rdba: 0x0040068a BFT:(1024,4195978) non-BFT:(1,1674)

                   scn: 0x0000.0054c8b1 seq: 0x03 flg:0x06

總結一下：完全檢查點會清空buffer cache中所有髒塊(有些特殊塊不包含在內)，

當alter system checkpoint 命令發出，完全檢查點會立刻執行。

如果是在生產庫，由於髒塊數量比較多，完全檢查點的時間會很長，並佔用一定的系統資源，這時作業系統的IO會變忙。

 

2、增量檢查點：設定增量檢查點的意義是通過提高檢查點發生的次數，將髒塊不斷的，一點一點的寫出到資料檔案，

這樣可以避免由於完全檢查點引起的高IO負載。

a、第一個發生增量檢查點的條件：90％ OF THE SMALLEST REDO LOGFILE

一般很少有人更改log_checkpoint和fast_start引數，那些引數的預設值都設定的很大，

所以還沒到那些引數的閥值的時候，90％最小日誌的條件就生效了（增量檢查點是任何一個條件成立以後就會發生）。

實驗一把：

SQL> show parameter log_checkpoint

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_checkpoint_interval integer 0

log_checkpoint_timeout integer 1800

log_checkpoints_to_alert boolean TRUE

SQL> show parameter fast_start

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

fast_start_io_target integer 0

fast_start_mttr_target integer 0

fast_start_parallel_rollback string HIGH

SQL> create table test as select * from dba_objects;

Table created.

ps：增量檢查點的觸發條件的當前值都可以在v$instance_recovery中看到，而x$kcccp可以看到最後一次檢查點的位置(用rba表示)和當前日誌尾的位置(用rba表示)。

看看當前各個增量檢查點觸發條件的值：

SQL> select actual_redo_blks act,target_redo_blks target,LOG_FILE_SIZE_REDO_BLKS logfile,LOG_CHKPT_TIMEOUT_REDO_BLKS log_check,target_mttr tmttr,estimated_mttr es_mttr from v$instance_recovery;

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

62967 170577 184320 170577 0 14

--LOG_FILE_SIZE_REDO_BLKS的值是184320，這正好是日誌檔案的90％

--日誌塊是0.5K一個，換算成M，184320*0.5/1024＝90 日誌檔案是100M

--再看一下最後一次檢查點的位置：

SQL> select CPLRBA_SEQ,CPLRBA_BNO,CPODR_SEQ,CPODR_BNO from x$kcccp;

CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO

---------- ---------- ---------- ----------

47 107610 47 170607

--最後一次檢查點發生在47號日誌的17610塊,當前日誌也是47號，日誌尾在170607塊。

--這時從另一個視窗不斷的執行 insert into test as select * from test where rownum < 100000;

--同時不斷查詢v$instance_recovery，可以看到新產生大量的日誌塊:

SQL> select actual_redo_blks act,target_redo_blks target,LOG_FILE_SIZE_REDO_BLKS logfile,LOG_CHKPT_TIMEOUT_REDO_BLKS log_check,target_mttr tmttr,estimated_mttr es_mttr from v$instance_recovery;

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

63008 170612 184320 170612 0 14

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

82829 184320 184320 190439 0 14

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

103118 184320 184320 210728 0 14

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

103197 184320 184320 210807 0 20

--此時redo log也發生了切換：

SQL> select * from v$Log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

1 1 47 104857600 3 NO ACTIVE 1210034 11-DEC-06

2 1 48 104857600 3 NO CURRENT 1213051 11-DEC-06

3 1 46 104857600 3 YES INACTIVE 1209429 11-DEC-06

--這裡注意：噹噹前日誌被寫滿以後，進行日誌切換，這時觸發了一個log switch checkpoint，但是僅僅是觸發，而並沒有完成。

--後臺警報日誌:

Mon Dec 11 15:06:26 2006

Beginning log switch checkpoint up to RBA [0x30.2.10], SCN: 0x0000.0012827b

Thread 1 advanced to log sequence 48

Current log# 2 seq# 48 mem# 0: /u01/app/oracle/oradata/novo/redo02.log

--另外注意，47號日誌的狀態是ACTIVE，active的意思是這個日誌組還包含例項恢復所需要的日誌。這也說明log switch checkpoint並沒有立即工作

--這時看看最後一次檢查點的位置：

SQL> select CPLRBA_SEQ,CPLRBA_BNO,CPODR_SEQ,CPODR_BNO from x$kcccp;

CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO

---------- ---------- ---------- ----------

47 107610 48 6161

--最後一次檢查點的位置沒變，還在47號日誌上面，而當前日誌尾已經到了48號的6161塊上。這也印證了為什麼44號日誌的狀態是ACTIVE的。

--再看看當前是否滿足增量檢查點的觸發條件：

SQL> select actual_redo_blks act,target_redo_blks target,LOG_FILE_SIZE_REDO_BLKS logfile,LOG_CHKPT_TIMEOUT_REDO_BLKS log_check,target_mttr tmttr,estimated_mttr es_mttr from v$instance_recovery;

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

103361 184320 184320 210971 0 20

--沒有滿足，那麼繼續insert

--不斷觀察v$instance_recovery：

SQL> select actual_redo_blks act,target_redo_blks target,LOG_FILE_SIZE_REDO_BLKS logfile,LOG_CHKPT_TIMEOUT_REDO_BLKS log_check,target_mttr tmttr,estimated_mttr es_mttr from v$instance_recovery;

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

63008 170612 184320 170612 0 14

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

82829 184320 184320 190439 0 14

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

103118 184320 184320 210728 0 14

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

103197 184320 184320 210807 0 20

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

103361 184320 184320 210971 0 20

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

123466 184320 184320 231076 0 22

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

143954 184320 184320 251564 0 22

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

159205 184320 184320 258499 0 22

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

184159 184320 184320 291769 0 26

--這時actual redo blocks已經到了184149了，馬上接近90％ of redologfile，意味著再有一些日誌進來以後，就會觸發增量檢查點。

--這時47號日誌的狀態還是沒有變化的，還是active：

SQL> select * from v$Log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

1 1 47 104857600 3 NO ACTIVE 1210034 11-DEC-06

2 1 48 104857600 3 NO CURRENT 1213051 11-DEC-06

3 1 46 104857600 3 YES INACTIVE 1209429 11-DEC-06

--再次插入資料：

--actual redo blocks超過90％ of logfile，這時應該觸發增量檢查點，最後一次查詢是增量檢查點發生之後的actual redo blocks值，已經小於90％ of logfile。

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

204281 184320 184320 372918 0 28

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

204281 184320 184320 372918 0 28

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

183929 184320 184320 373265 0 28

--注意增量檢查點只是寫出一部分髒資料，只要保證actual redo blocks小於90％ of logfile就可以了。

--這時查詢x$kcccp，發現最後一次檢查點的位置已經升高，但依舊在47號日誌上面：

SQL> select CPLRBA_SEQ,CPLRBA_BNO,CPODR_SEQ,CPODR_BNO from x$kcccp;

CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO

---------- ---------- ---------- ----------

47 185730 48 118320

--再次插入資料，並查詢v$instance_recovery

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

184588 184320 184320 393585 0 30

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

184588 184320 184320 393604 0 30

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

183868 184320 184320 393604 0 30

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

183869 184320 184320 393604 0 30

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

183881 184320 184320 393617 0 28

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

183881 184320 184320 393617 0 28

--由於再次超過閥值，增量檢查點再次發生，並寫出了一些髒塊，此時查詢x$kcccp，發現最後一次檢查點的位置已經提到48號日誌：

SQL> select CPLRBA_SEQ,CPLRBA_BNO,CPODR_SEQ,CPODR_BNO from x$kcccp;

CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO

---------- ---------- ---------- ----------

48 6072 48 188945

--觀察alert log：log switch checkpoint 完成。

Mon Dec 11 15:29:08 2006

Completed checkpoint up to RBA [0x30.2.10], SCN: 0x0000.0012827b

--這裡注意RBA已經指向日誌 0x30，轉換成10進位制就是，16×3＝48，這與上面x$kcccp查到的情況是相符的。

--由於最後一次檢查點的位置已經超過47號日誌，那麼47號日誌對於例項恢復來說就沒有用了，來看看47號日誌的狀態：

--觀察v$log，47號日誌的狀態已經變為INACTIVE

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

1 1 47 104857600 3 NO INACTIVE 1210034 11-DEC-06

2 1 48 104857600 3 NO CURRENT 1213051 11-DEC-06

3 1 46 104857600 3 YES INACTIVE 1209429 11-DEC-06

總結一下：通常所說的logfile switch 觸發檢查點，實際上就是給出一個標記，而並不真正去寫髒塊。等待增量檢查點做到了那個標記，再標識完成。

 

b、第二個發生增量檢查點的條件：LOG_CHECKPOINT_TIMEOUT

這個引數說白了就是控制2次增量檢查點之間所發生的時間間隔，超過這個間隔，就會觸發增量檢查點。

從另外一個角度理解，就是髒塊在buffer cache中所能存在的最大時間。

為了讓實驗更明顯，把這個引數設小點，1分鐘：

SQL> show parameter log_checkpoint

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_checkpoint_interval integer 0

log_checkpoint_timeout integer 1800

log_checkpoints_to_alert boolean TRUE

SQL> alter system set log_checkpoint_timeout = 60;

System altered.

SQL> show parameter log_checkpoint;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_checkpoint_interval integer 0

log_checkpoint_timeout integer 60

log_checkpoints_to_alert boolean TRUE

--檢視v$instance_recovery檢視：

SQL> select actual_redo_blks act,target_redo_blks target,LOG_FILE_SIZE_REDO_BLKS logfile,LOG_CHKPT_TIMEOUT_REDO_BLKS log_check,target_mttr tmttr,estimated_mttr es_mttr from v$instance_recovery;

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

7 15 184320 15 0 7

--看到target 和 log_CHECKPOINT_TIMEOUT的值是一樣的，而且變的特別小，只要actual redo blocks超過這個數量就會觸發增量檢查點。

--下面重複上面的那個實驗，不斷的插入資料，以產生redo log，然後觀察檢查點發生的情況：

--先看看redo log的狀態：

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

1 1 47 104857600 3 NO INACTIVE 1210034 11-DEC-06

2 1 48 104857600 3 NO CURRENT 1213051 11-DEC-06

3 1 46 104857600 3 YES INACTIVE 1209429 11-DEC-06

--增量檢查點的位置：

SQL> select CPLRBA_SEQ,CPLRBA_BNO,CPODR_SEQ,CPODR_BNO from x$kcccp;

CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO

---------- ---------- ---------- ----------

48 189440 48 189462

--插入資料，並觀察v$instance_recovery

SQL> insert into test select * from test where rownum < 100000;

99999 rows created.

--觀察v$instance_recovery

SQL> select actual_redo_blks act,target_redo_blks target,LOG_FILE_SIZE_REDO_BLKS logfile,LOG_CHKPT_TIMEOUT_REDO_BLKS log_check,target_mttr tmttr,estimated_mttr es_mttr from v$instance_recovery;

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

20433 20433 184320 20433 0 10

SQL> /

ACT TARGET LOGFILE LOG_CHECK TMTTR ES_MTTR

---------- ---------- ---------- ---------- ---------- ----------

56 85 184320 85 0 10

--1分鐘後發生了增量檢查點，actual redo blocks減少了不少：

--看看增量檢查點的位置：

SQL> select CPLRBA_SEQ,CPLRBA_BNO,CPODR_SEQ,CPODR_BNO from x$kcccp;

CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO

---------- ---------- ---------- ----------

48 189486 49 5126

SQL> /

CPLRBA_SEQ CPLRBA_BNO CPODR_SEQ CPODR_BNO

---------- ---------- ---------- ----------

49 5126 49 5183

--檢查點的位置提升了2次，並提升到了49號日誌，這時看到alert log中的log switch checkpoint也完成了：

Mon Dec 11 16:12:50 2006

Beginning log switch checkpoint up to RBA [0x31.2.10], SCN: 0x0000.00128ded

Thread 1 advanced to log sequence 49

Current log# 3 seq# 49 mem# 0: /u01/app/oracle/oradata/novo/redo03.log

Mon Dec 11 16:13:56 2006

Completed checkpoint up to RBA [0x31.2.10], SCN: 0x0000.00128ded

--這裡注意從checkpoint begin到end的時間間隔是1分鐘，這與log_checkpoint_timeout的條件是吻合的。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25964700/viewspace-700999/，如需轉載，請註明出處，否則將追究法律責任。