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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9529380/viewspace-750381/