ckpt(checkpoint)機制研究

ckpt的作用：
1、保證資料庫的一致性：
    髒資料寫出到資料檔案，保證記憶體和資料檔案中的資料是一樣的。
2、縮短例項恢復的時間：
     例項恢復需要把例項異常關閉前沒有寫出到資料檔案中的髒資料通過日誌進行恢復。如果需寫出的髒塊數量過多，例項恢復時間也會較長，ckpt可以及時寫出髒塊，減少記憶體中髒塊的數量，從而加快例項恢復的時間。

 

例項展示：
session1>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

01:40:47 session1>select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
         3         ONLINE  /u01/app/oracle/oradata/orcl/redo03.log  NO
         2         ONLINE  /u01/app/oracle/oradata/orcl/redo02.log  NO
         1         ONLINE  /u01/app/oracle/oradata/orcl/redo01.log  NO

當前log狀態如下：
01:41:14 session1>select GROUP#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1         43          1 NO  CURRENT
         2         41          1 YES INACTIVE
         3         42          1 YES ACTIVE
如上所示，3#log處於active狀態。日誌檔案為active狀態，表示這個檔案的檢查點還沒有作完。而檢查點沒有做完的原因是dbwr執行的dirty buffer寫出還沒有執行完成，等待的過程就是等待將current日誌中修改過的資料塊資訊寫入到資料檔案的過程。

01:41:19 session1>select actual_redo_blks from v$instance_recovery;
ACTUAL_REDO_BLKS
----------------
             244

注：ACTUAL_REDO_BLKS(Current actual number of redo blocks required for recovery)，說明有244個髒塊需要寫入。

01:41:25 session1>select actual_redo_blks from v$instance_recovery;
ACTUAL_REDO_BLKS
----------------
             244

01:41:28 session1>select actual_redo_blks from v$instance_recovery;
ACTUAL_REDO_BLKS
----------------
             244

01:41:31 session1>

觸發checkpoint事件：
01:41:58 session1>alter system checkpoint;
System altered.

01:41:58 session1>select actual_redo_blks from v$instance_recovery;
ACTUAL_REDO_BLKS
----------------
             180

01:41:59 session1>select actual_redo_blks from v$instance_recovery;
ACTUAL_REDO_BLKS
----------------
               4

01:41:59 session1>select GROUP#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1         43          1 NO  CURRENT
         2         41          1 YES INACTIVE
         3         42          1 YES INACTIVE
檢視v$instance_recovery 中檢視中的actual_redo_blks數量和v$log中的3#log狀態，表明buffer cache中的髒塊已經寫完，active的狀態轉變為了inactive。

alert日誌的相關資訊如下：
Wed Nov 28 01:41:58 2012
Beginning global checkpoint up to RBA [0x2b.ea.10], SCN: 1209008
Completed checkpoint up to RBA [0x2b.ea.10], SCN: 1209008
Completed checkpoint up to RBA [0x2b.2.10], SCN: 1208924

 

檢查點發生後，觸發DBWn，CKPT獲取發生檢查點時對應的SCN，通知DBWn寫到這個SCN為止。alert日誌顯示"SCN: 1209008"，1209008就是觸發CKPT時的SCN。當checkpoint做完後，CKPT會把這個SCN更新到資料檔案頭上。
下面檢查當前資料檔案的scn號進行驗證：
session1>select file#,checkpoint_change# from v$datafile;
     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            1209008
         2            1209008
         3            1209008
         4            1209008
         5            1209008
         6            1209008
6 rows selected.

 

alert日誌中"RBA [0x2b.2.10]"的0x2b表示當前的線上日誌序列號，將0x2b轉換為十進位制：
session1>select to_number('2b','xxxx') from dual;
TO_NUMBER('2B','XXXX')
----------------------
                    43

 

檢查當前日誌狀態：
session1>select GROUP#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS from v$log;
    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1         43          1 NO  CURRENT
         2         41          1 YES INACTIVE
         3         42          1 YES INACTIVE
如上所示，SEQUENCE#43為當前的線上日誌。

 

轉儲日誌，找到對應的rba:
session1>alter system dump logfile '/u01/app/oracle/oradata/orcl/redo01.log';
System altered.

session1>select value from v$diag_info where name = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6691.trc

 

將SCN號轉化為十六進位制：
session2>select to_char(1209008,'xxxxxxxx') from dual
TO_CHAR(1
---------
   1272b0

 

session>!
$view /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6691.trc

(RBA相關內容如下，顯示髒塊資訊)
REDO RECORD - Thread:1 RBA: 0x00002b.000000ea.0010 LEN: 0x05f0 VLD: 0x06
SCN: 0x0000.001272b0 SUBSCN:  1 11/28/2012 01:41:58   (注：SCN: 0x0000.001272b0就是由1209008轉換來的)
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
 Block Written - afn: 6 rdba: 0x018000e8 BFT:(1024,25166056) non-BFT:(6,232)
                   scn: 0x0000.00127296 seq: 0x02 flg:0x04
 Block Written - afn: 6 rdba: 0x018000e9 BFT:(1024,25166057) non-BFT:(6,233)
                   scn: 0x0000.00127292 seq: 0x02 flg:0x04
 Block Written - afn: 6 rdba: 0x018000ea BFT:(1024,25166058) non-BFT:(6,234)
                   scn: 0x0000.00127298 seq: 0x01 flg:0x04
 Block Written - afn: 6 rdba: 0x01800002 BFT:(1024,25165826) non-BFT:(6,2)
                   scn: 0x0000.00127292 seq: 0x02 flg:0x04
 Block Written - afn: 6 rdba: 0x01800003 BFT:(1024,25165827) non-BFT:(6,3)
                   scn: 0x0000.00127292 seq: 0x01 flg:0x04
 Block Written - afn: 3 rdba: 0x00c029ec BFT:(1024,12593644) non-BFT:(3,10732)
                   scn: 0x0000.001272a6 seq: 0x03 flg:0x04
 Block Written - afn: 3 rdba: 0x00c00d0c BFT:(1024,12586252) non-BFT:(3,3340)
                   scn: 0x0000.0012727b seq: 0x1e flg:0x04
 Block Written - afn: 3 rdba: 0x00c00bf0 BFT:(1024,12585968) non-BFT:(3,3056)
                   scn: 0x0000.001272a4 seq: 0x01 flg:0x04
.....省略.....
Block Written - afn: 1 rdba: 0x004078d5 BFT:(1024,4225237) non-BFT:(1,30933)
                   scn: 0x0000.0012729c seq: 0x01 flg:0x06
 Block Written - afn: 1 rdba: 0x00406c77 BFT:(1024,4222071) non-BFT:(1,27767)
                   scn: 0x0000.0012729c seq: 0x01 flg:0x06
 Block Written - afn: 1 rdba: 0x004007e9 BFT:(1024,4196329) non-BFT:(1,2025)
                   scn: 0x0000.001272a8 seq: 0x03 flg:0x06
 Block Written - afn: 1 rdba: 0x004007d9 BFT:(1024,4196313) non-BFT:(1,2009)
                   scn: 0x0000.001272a8 seq: 0x02 flg:0x06
 Block Written - afn: 1 rdba: 0x00400121 BFT:(1024,4194593) non-BFT:(1,289)
                   scn: 0x0000.0012728f seq: 0x01 flg:0x06
 Block Written - afn: 1 rdba: 0x00400090 BFT:(1024,4194448) non-BFT:(1,144)
                   scn: 0x0000.00127298 seq: 0x01 flg:0x04

REDO RECORD - Thread:1 RBA: 0x00002b.000000ed.0030 LEN: 0x0214 VLD: 0x01
SCN: 0x0000.001272b0 SUBSCN:  1 11/28/2012 01:41:58

總結：ckpt事件會觸發資料庫寫程式DBWR，將資料緩衝區(data buffer cache)中的髒資料塊寫入到資料檔案中。當alter system checkpoint 命令發出，完全檢查點會立刻執行。如果是在生產庫，由於髒塊數量比較多，完全檢查點的時間會很長，並佔用一定的系統資源，這時作業系統的IO會變忙。

 

 

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