详解oracle checkpoint

这篇文章，源于群中几位网友的疑问，故打算写这篇文章给其释疑。

1. 何为检查点？其存在的意义？
2. 检查点的有哪些分类？检查点的结构如何？什么是checkpoint queue？

通俗的将，检查点仅仅是一个机制而已，其作用是通知DBWR进程将cache buffer中的脏块写入
到disk中，当然这个通知的动作是通过检查点进程CKPT来完成的。那么检查点存在的意义是什
么呢？其实很简单，目的就是减少db crash后的recover time。

这里可能存在一个误区，那就是并不是只有检查点的情况下才会导致DBWR进程去写脏块到disk
中，在cache buffer 空间不足的情况下，由于Cache buffer LRU的机制就决定了不管是否发生
检查点，DBWR都会将脏块写入到disk中，以此来保证cache buffer能容纳更多的new block。

所谓的checkpoint queue，可以理解为由脏块组成的一个LRU链表。

检查点有哪些分类呢？大概有如下几种：

 
 

  
  
1. Full Checkpoint
2. Thread Checkpoint (local checkpoint, global checkpoint)
3. Datafile Checkpoint
4. Mini-checkpoint (object checkpoint,基于DDL）
5. Parallel Query Checkpoint
6. Incremental Checkpoint (Log Switch Checkpoint)

 
 

1. 首先我们来看下完全检查点，对于full checkpoint，在8i之前就存在了，很好理解，你目的是
   一旦触发完全检查点，DBWR进程会将cache buffer drity LRU list上的所有脏块写入到disk中。

那么在哪些情况下会触发完全检查点呢？如下：

(1) shutdown instance;
(2) 日志组切换
(3) log_checkpoint_timeout,log_checkpoint_interval,fast_start_io_target,fast_start_mttr_target
(4) 手工执行alter system switch logfile时
(5) 手工alter system checkpoint
(6) alter tablespace XXX begin/end backup;
(7) alter tablespace XXX datafile offline;

注意： 在oracle 8i以后，引入增量检查点以后，日志切换以及switch logfile都是增量检查点，
不过需要说明一下的是，虽然说其是增量检查点，不过同时触发时controlfile和datafile header
都会进行更新的，后面的实验可以说明。

完全检查点就说到这里，至于局部检查点以及全局检查点，注意是针对RAC而言，可以这样理解：

alter system checkpoint local;  就是触发单实例的完全检查点
alter system checkpoint global; 就是RAC中所有实例同时触发完全检查点

2. 文件检查点

文件检查点相对完全检查点而言，你可以理解为属于其子集，其针对范围是某个表空间或表空间
中的某个数据文件，当执行如下命令的时候，会触发文件检查点：

alter tablespace XXX begin/end backup;
alter tablespace ,datafile offline;

下面通过bbed来验证一下：

BBED> info all   File# Name Size(blks) ----- ---- ---------- 1 /home/ora10g/oradata/roger/system01.dbf 57600 2 /home/ora10g/oradata/roger/undotbs01.dbf 118400 3 /home/ora10g/oradata/roger/sysaux01.dbf 33280 4 /home/ora10g/oradata/roger/users01.dbf 640 5 /home/ora10g/oradata/roger/roger01.dbf 2560 6 /home/ora10g/oradata/roger/roger02.dbf 1280   BBED> set file 5 block 1   FILE# 5 BLOCK# 1   BBED> p kcvfhckp   struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000de088 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2db03de8 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000008 ub4 kcrbabno @504 0x00005c72 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00   BBED> set file 6 block 1   FILE# 6 BLOCK# 1   BBED> p kcvfhckp   struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000de088 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2db03de8 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000008 ub4 kcrbabno @504 0x00005c72 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00   BBED> set file 4 block 1 FILE# 4 BLOCK# 1   BBED> p kcvfhckp   struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000de088 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2db03de8 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000008 ub4 kcrbabno @504 0x00005c72 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00

将表空间ROGER offline，如下：

SQL> alter tablespace roger offline;   Tablespace altered.   SQL> !   [ora10g@killdb ~]$ bbed parfile=parfile.bbed Password:   BBED: Release 2.0.0.0.0 - Limited Production on Sun Nov 6 23:29:39 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. ************* !!! For Oracle Internal Use only !!! ***************   BBED> set file 4 block 1   FILE# 4 BLOCK# 1   BBED> p kcvfhckp   struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000de088 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2db03de8 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000008 ub4 kcrbabno @504 0x00005c72 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00   BBED> set file 5 block 1   FILE# 5 BLOCK# 1   BBED> p kcvfhckp   struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000df6b3 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2db074d1 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000008 ub4 kcrbabno @504 0x000075ab ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00   BBED> set file 5 block 1   FILE# 5 BLOCK# 1   BBED> p kcvfhckp   struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000df6b3 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2db074d1 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000008 ub4 kcrbabno @504 0x000075ab ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00   ++++++ 我们可以看到仅仅是表空间roger中的几个datafile的检查点SCN发生了改变。++++++

3. 对象检查点

那么object checkpoint是什么时候触发的呢？通常是如下两种情况：

drop table xxx/xxx purge;
drop index xxx;
truncate table xxx;

那么如何理解object checkpoint呢？我认为可以这样简单理解：

drop table killdb;  将触发一个object 检查点，将cache buffer中所有
                    跟killdb对象有关的脏块写入到disk中。

SQL> ALTER system FLUSH BUFFER_CACHE;   System altered.   SQL> SELECT b.name, a.VALUE 2 FROM v$sysstat a, v$statname b 3 WHERE a.statistic# >74 AND a.statistic# <86 4 AND a.statistic# = b.statistic#;   NAME VALUE ---------------------------------------------------------------- ---------- DBWR thread checkpoint buffers written 64 DBWR tablespace checkpoint buffers written 0 DBWR parallel query checkpoint buffers written 128 DBWR object DROP buffers written 33 DBWR TRANSACTION TABLE writes 76 DBWR undo block writes 169 DBWR revisited being-written buffer 0 DBWR make free requests 0 DBWR lru scans 0 DBWR checkpoints 15 DBWR fusion writes 0   11 ROWS selected.   SQL> CREATE TABLE t1(id NUMBER);   TABLE created.   SQL> INSERT INTO t1 VALUES(1);   1 ROW created.   SQL> commit;   Commit complete.   SQL> SELECT b.name, a.VALUE 2 FROM v$sysstat a, v$statname b 3 WHERE a.statistic# >74 AND a.statistic# <86 4 AND a.statistic# = b.statistic#;   NAME VALUE ---------------------------------------------------------------- ---------- DBWR thread checkpoint buffers written 64 DBWR tablespace checkpoint buffers written 0 DBWR parallel query checkpoint buffers written 128 DBWR object DROP buffers written 33 DBWR TRANSACTION TABLE writes 76 DBWR undo block writes 169 DBWR revisited being-written buffer 0 DBWR make free requests 0 DBWR lru scans 0 DBWR checkpoints 15 DBWR fusion writes 0   11 ROWS selected.   SQL> DROP TABLE t1;   TABLE dropped.   SQL> SELECT b.name, a.VALUE 2 FROM v$sysstat a, v$statname b 3 WHERE a.statistic# >74 AND a.statistic# <86 4 AND a.statistic# = b.statistic#;   NAME VALUE ---------------------------------------------------------------- ---------- DBWR thread checkpoint buffers written 64 DBWR tablespace checkpoint buffers written 0 DBWR parallel query checkpoint buffers written 128 DBWR object DROP buffers written 41 DBWR TRANSACTION TABLE writes 76 DBWR undo block writes 169 DBWR revisited being-written buffer 0 DBWR make free requests 0 DBWR lru scans 0 DBWR checkpoints 16 DBWR fusion writes 0   11 ROWS selected.

另外，dbsnake大牛以前也写过一篇相关的文章，详见如下链接：

详细解析truncate引发的object checkpoint

4. 并行查询检查点

对应并行查询，由于是走direct read，所以oracle会触发检查点将buffer中的脏块写入到disk中，

SQL> SELECT statistic#,name FROM v$statname WHERE name LIKE '%DBWR%';   STATISTIC# NAME ---------- ---------------------------------------------------------------- 74 DBWR checkpoint buffers written 75 DBWR thread checkpoint buffers written 76 DBWR tablespace checkpoint buffers written 77 DBWR parallel query checkpoint buffers written 78 DBWR object DROP buffers written 79 DBWR TRANSACTION TABLE writes 80 DBWR undo block writes 81 DBWR revisited being-written buffer 82 DBWR make free requests 83 DBWR lru scans 84 DBWR checkpoints 85 DBWR fusion writes   12 ROWS selected.   SQL> ALTER system FLUSH BUFFER_CACHE;   System altered.     SQL> SELECT b.name, a.VALUE 2 FROM v$sysstat a, v$statname b 3 WHERE a.statistic# IN (77, 84) 4 AND a.statistic# = b.statistic#;   NAME VALUE ---------------------------------------------------------------- ---------- DBWR parallel query checkpoint buffers written 123 DBWR checkpoints 9   SQL> DELETE FROM t1 WHERE rownum <2;   1 ROW deleted.   SQL> commit;   Commit complete.   SQL> SELECT b.name, a.VALUE 2 FROM v$sysstat a, v$statname b 3 WHERE a.statistic# IN (77, 84) 4 AND a.statistic# = b.statistic#;   NAME VALUE ---------------------------------------------------------------- ---------- DBWR parallel query checkpoint buffers written 123 DBWR checkpoints 9   SQL> SELECT/*+ parallel(t1,4)*/DISTINCT id FROM t1;   ID ---------- 1314   SQL> SELECT b.name, a.VALUE 2 FROM v$sysstat a, v$statname b 3 WHERE a.statistic# IN (77, 84) 4 AND a.statistic# = b.statistic#;   NAME VALUE ---------------------------------------------------------------- ---------- DBWR parallel query checkpoint buffers written 124 DBWR checkpoints 10   SQL> DELETE FROM t1 WHERE rownum <1000;   999 ROWS deleted.   SQL> commit;   Commit complete.   SQL> SELECT b.name, a.VALUE 2 FROM v$sysstat a, v$statname b 3 WHERE a.statistic# IN (77, 84) 4 AND a.statistic# = b.statistic#;   NAME VALUE ---------------------------------------------------------------- ---------- DBWR parallel query checkpoint buffers written 124 DBWR checkpoints 10   SQL> ALTER SESSION SET events '10046 trace name context forever,level 12';   SESSION altered.   SQL> SELECT/*+ parallel(t1,4)*/DISTINCT id FROM t1;   ID ---------- 1314   SQL> ALTER SESSION SET events '10046 trace name context off';   SESSION altered.   SQL> SELECT b.name, a.VALUE 2 FROM v$sysstat a, v$statname b 3 WHERE a.statistic# IN (77, 84) 4 AND a.statistic# = b.statistic#;   NAME VALUE ---------------------------------------------------------------- ---------- DBWR parallel query checkpoint buffers written 128 DBWR checkpoints 11

我们可以看到，在第2次执行parallel query时，DBWR写了4个block (128-124)，但是
此时的checkpoint只发生了1次，这也直接验证了parallel query checkpoint。
这里其实还有object checkpoint产生，我们通过10046 trace可以看出，如下：

===================== PARSING IN CURSOR #3 len=45 dep=0 uid=60 oct=3 lid=60 tim=1289872258867729 hv=4038107070 ad='25f2dd08' select/*+ parallel(t1,4)*/distinct id from t1 END OF STMT PARSE #3:c=57992,e=56729,p=460,cr=81,cu=0,mis=1,r=0,dep=0,og=1,tim=1289872258867724 BINDS #3: WAIT #3: nam='enq: KO - fast object checkpoint' ela= 6 name|mode=1263468550 2=65557 0=1 obj#=52229 tim=1289872258868398 WAIT #3: nam='enq: KO - fast object checkpoint' ela= 5018 name|mode=1263468550 2=65557 0=1 obj#=52229 tim=1289872258873457 WAIT #3: nam='PX Deq: Join ACK' ela= 1732 sleeptime/senderid=268500992 passes=1 p3=0 obj#=52229 tim=1289872258875411 WAIT #3: nam='PX Deq: Join ACK' ela= 10 sleeptime/senderid=268500996 passes=1 p3=0 obj#=52229 tim=1289872258875734 WAIT #3: nam='PX Deq: Join ACK' ela= 1926 sleeptime/senderid=268500996 passes=2 p3=0 obj#=52229 tim=1289872258877717 WAIT #3: nam='PX Deq: Parse Reply' ela= 7 sleeptime/senderid=200 passes=1 p3=0 obj#=52229 tim=1289872258877917 WAIT #3: nam='PX Deq: Parse Reply' ela= 91227 sleeptime/senderid=200 passes=2 p3=0 obj#=52229 tim=1289872258969183 WAIT #3: nam='PX Deq: Parse Reply' ela= 7 sleeptime/senderid=200 passes=1 p3=0 obj#=52229 tim=1289872258969352 WAIT #3: nam='PX Deq: Parse Reply' ela= 2059 sleeptime/senderid=200 passes=2 p3=0 obj#=52229 tim=1289872258971446

5. 增量检查点

关于增量检查点，是本文需要描述的重点，增量检查点其实是在oracle 8i就引入了，引入的目的
当然是为了更大程度的降低instance crash时的recover time。

说道增量检查点，我们就需要讲讲其相关的一个重要结构，那就是checkpoint queue。

当然，checkpoint queue并不仅仅是为增量检查点服务的，也包括其他类型的检查点。

SQL> SELECT CPLRBA_SEQ,CPLRBA_BNO,CPLRBA_BOF,CPODR_SEQ,CPODR_BNO,CPODR_BOF,CPODS FROM x$kcccp;   CPLRBA_SEQ CPLRBA_BNO CPLRBA_BOF CPODR_SEQ CPODR_BNO CPODR_BOF CPODS ---------- ---------- ---------- ---------- ---------- ---------- ---------------- 9 1303 0 9 1312 0 916350 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0   8 ROWS selected.     '++++++ 相关字段的解释如下：++++++'   low rba OF cache: CPLRBA_SEQ log seq # CPLRBA_BNO block # IN log file CPLRBA_BOF byte offset IN block   ON disk rba: CPODR_SEQ log seq # CPODR_BNO block # IN log file CPODR_BOF byte offset IN block CPODS ON disk scn     SQL> SELECT rtckp_rba_seq,rtckp_rba_bno,rtckp_rba_bof,rtckp_scn FROM x$kccrt;   RTCKP_RBA_SEQ RTCKP_RBA_BNO RTCKP_RBA_BOF RTCKP_SCN ------------- ------------- ------------- ---------------- 9 2 16 915739   '++++++ 相关字段解释如下：++++++'   RTCKP_RBA_SEQ log seq # RTCKP_RBA_BNO block # IN log file RTCKP_RBA_BOF byte offset IN block RTCKP_SCN checkpoint scn

BBED> set file 1 block 1   FILE# 1 BLOCK# 1   BBED> p kcvfhckp   struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000df91b ==> 转换为10进制为915739 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2db07b87 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x00000009 ==> rba_seq ub4 kcrbabno @504 0x00000002 ==> rba_bno ub2 kcrbabof @508 0x0010 ==> rba_bof ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00

下面switch logfile一下, 看看结果：

SQL> SELECT file#,CHECKPOINT_CHANGE# FROM v$datafile_header ORDER BY 1;   FILE# CHECKPOINT_CHANGE# ---------- ------------------ 1 915739 2 915739 3 915739 4 915739 5 915747 6 915747   6 ROWS selected.   SQL> ALTER system checkpoint;   System altered.   SQL> SELECT file#,CHECKPOINT_CHANGE# FROM v$datafile_header ORDER BY 1;   FILE# CHECKPOINT_CHANGE# ---------- ------------------ 1 916589 2 916589 3 916589 4 916589 5 916589 6 916589   6 ROWS selected.   SQL> SET LINES 200 SQL> SELECT * FROM v$log;   GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 7 52428800 1 NO INACTIVE 872908 03-NOV-11 2 1 8 52428800 1 NO INACTIVE 901680 05-NOV-11 3 1 9 52428800 1 NO CURRENT 915739 06-NOV-11   SQL> ALTER system switch logfile;   System altered.   SQL> SELECT * FROM v$log;   GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 10 52428800 1 NO CURRENT 916611 07-NOV-11 2 1 8 52428800 1 NO INACTIVE 901680 05-NOV-11 3 1 9 52428800 1 NO INACTIVE 915739 06-NOV-11   SQL> SELECT file#,CHECKPOINT_CHANGE# FROM v$datafile_header ORDER BY 1;   FILE# CHECKPOINT_CHANGE# ---------- ------------------ 1 916611 2 916611 3 916611 4 916611 5 916611 6 916611   6 ROWS selected.   SQL> SELECT * FROM v$log;   GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 10 52428800 1 NO CURRENT 916611 07-NOV-11 2 1 8 52428800 1 NO INACTIVE 901680 05-NOV-11 3 1 9 52428800 1 NO INACTIVE 915739 06-NOV-11   SQL> ALTER system switch logfile;   System altered.   SQL> SELECT file#,CHECKPOINT_CHANGE# FROM v$datafile_header ORDER BY 1;   FILE# CHECKPOINT_CHANGE# ---------- ------------------ 1 916663 2 916663 3 916663 4 916663 5 916663 6 916663   6 ROWS selected.   SQL> SELECT rtckp_rba_seq,rtckp_rba_bno,rtckp_rba_bof,rtckp_scn FROM x$kccrt;   RTCKP_RBA_SEQ RTCKP_RBA_BNO RTCKP_RBA_BOF RTCKP_SCN ------------- ------------- ------------- ---------------- 11 2 16 916663   '++++++ 我们可以看到虽然把logfile switch归为增量检查点，其实datafile header也是被更新了。++++++'   SQL> SELECT rtckp_rba_seq,rtckp_rba_bno,rtckp_rba_bof, rtckp_scn FROM x$kccrt;   RTCKP_RBA_SEQ RTCKP_RBA_BNO RTCKP_RBA_BOF RTCKP_SCN ------------- ------------- ------------- ---------------- 11 2 16 916663   SQL> ALTER tablespace roger offline;   Tablespace altered.   SQL> SELECT rtckp_rba_seq,rtckp_rba_bno,rtckp_rba_bof, rtckp_scn FROM x$kccrt;   RTCKP_RBA_SEQ RTCKP_RBA_BNO RTCKP_RBA_BOF RTCKP_SCN ------------- ------------- ------------- ---------------- 11 2 16 916663

我们可以看到，对于alter tablespace xxx offline操作，触发的是增量检查点。

BBED> set file 5 block 1   FILE# 5 BLOCK# 1   BBED> p kcvfhckp   struct kcvfhckp, 36 bytes @484 struct kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000dfd78 ==> 916663 ub2 kscnwrp @488 0x0000 ub4 kcvcptim @492 0x2db08628 ub2 kcvcpthr @496 0x0001 union u, 12 bytes @500 struct kcvcprba, 12 bytes @500 ub4 kcrbaseq @500 0x0000000b ==> 11 ub4 kcrbabno @504 0x00000015 ub2 kcrbabof @508 0x0010 ub1 kcvcpetb[0] @512 0x02 ub1 kcvcpetb[1] @513 0x00 ub1 kcvcpetb[2] @514 0x00 ub1 kcvcpetb[3] @515 0x00 ub1 kcvcpetb[4] @516 0x00 ub1 kcvcpetb[5] @517 0x00 ub1 kcvcpetb[6] @518 0x00 ub1 kcvcpetb[7] @519 0x00

如果想关闭增量检查点功功能，可以调整参数 _disable_incremental_checkpoints 为true。

最后回到网友的问题上来，如下：

 
 

  
  
获思(280770144) 10:40:34
checkpoint 的rba 排序是从大到小，还是从小到大呢？ 

东东堂(327356330) 10:41:28
只是说排序，好像没有说从大到小，还是从小到大

 
 

既然说道checkpoint rba，那么我们就需要先来了解下rba的结构，其实在上面的实验中
就已经给出了rba的结构：

Checkpoint RBA = RTCKP_RBA_SEQ + RTCKP_RBA_BNO + RTCKP_RBA_BOF

在10g中，checkpoint rba一个占据12 bytes，换句话说，将上面三部分组成在一起就构成
我们所需要的RBA地址，如下；

SQL> col low_rba FOR a30 SQL> col on_disk_rba FOR a30 SQL> SELECT cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 FROM x$kcccp;   CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 258 11.55.0 11.1050.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0   8 ROWS selected.

关于rba(redo block address)，有可以分为如下3种:

low checkpoint rba;  某个block第一次被修改时所对应的redo recode记录，同时也就意味着
                     low rba是redo apply的起点

high cechkpoint rba; 某个block被经过多次修改后，最后一次修改时所对应的redo recode记录

on disk rba;         是指disk中redo log中的最后一条redo recode记录，在实例crash后进行
                     实例恢复时on disk rba将是redo log apply的终点。

下面通过实验来进行说明：

SQL> CREATE TABLE t1 AS SELECT * FROM dba_objects WHERE rownum<10;   TABLE created.   SQL> SELECT dbms_rowid.rowid_relative_fno(rowid) file#, 2 dbms_rowid.rowid_block_number(rowid) blk# 3 FROM t1;   FILE# BLK# ---------- ---------- 1 56034 1 56034 1 56034 1 56034 1 56034 1 56034 1 56034 1 56034 1 56034   9 ROWS selected.   SQL> UPDATE t1 SET owner='www.killdb.com' WHERE object_id=20;   1 ROW updated.   SQL> UPDATE t1 SET owner='www.killdb.com' WHERE object_id=25;   1 ROW updated.   SQL> UPDATE t1 SET owner='www.killdb.com' WHERE object_id=29;   1 ROW updated.   SQL> SELECT cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 FROM x$kcccp;   CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 40 11.1153.0 11.1283.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0   8 ROWS selected.   SQL> SELECT cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 FROM x$kcccp;   CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 22 11.1432.0 11.1450.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0   8 ROWS selected.   SQL> ALTER system switch logfile;   System altered.   SQL> commit;   Commit complete.   SQL> SELECT cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 FROM x$kcccp;   CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 23 11.1432.0 12.3.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0   8 ROWS selected.   SQL> ALTER system switch logfile;   System altered.   SQL> SELECT cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 FROM x$kcccp;   CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 23 11.1432.0 13.2.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0   8 ROWS selected.   SQL> UPDATE t1 SET owner='www.killdb.com' WHERE object_id=44;   1 ROW updated.   SQL> UPDATE t1 SET owner='www.killdb.com' WHERE object_id=51;   1 ROW updated.   SQL> commit;   Commit complete.   SQL> SELECT cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 FROM x$kcccp;   CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 26 11.1432.0 13.4.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0   8 ROWS selected.   SQL> ALTER system switch logfile;   System altered.   SQL> SELECT cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 FROM x$kcccp;   CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 3 13.2.0 14.2.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0   8 ROWS selected.   SQL> SELECT * FROM v$log;   GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 13 52428800 1 NO ACTIVE 918247 07-NOV-11 2 1 14 52428800 1 NO CURRENT 918274 07-NOV-11 3 1 12 52428800 1 NO ACTIVE 918241 07-NOV-11   SQL> SQL> SELECT cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 FROM x$kcccp;   CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 3 13.2.0 14.2.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0   8 ROWS selected.   SQL> ALTER system switch logfile;   System altered.   SQL> SELECT cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 FROM x$kcccp;   CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 0 4294967295.4294967295.65535 15.2.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0   8 ROWS selected.   SQL> SELECT cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 FROM x$kcccp;   CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 0 4294967295.4294967295.65535 15.2.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0   8 ROWS selected.   SQL> UPDATE t1 SET owner='www.killdb.com' WHERE object_id=28;   1 ROW updated.   SQL> commit;   Commit complete.   SQL> SELECT cpdrt, 2 cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba", 3 cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba" 4 FROM x$kcccp;   CPDRT low_rba on_disk_rba ---------- ------------------------------ ------------------------------ 3 15.2.0 15.4.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0 0 0.0.0 0.0.0   8 ROWS selected.   SQL> SELECT * FROM v$log;   GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 13 52428800 1 NO INACTIVE 918247 07-NOV-11 2 1 14 52428800 1 NO INACTIVE 918274 07-NOV-11 3 1 15 52428800 1 NO CURRENT 918300 07-NOV-11

我们可以看到，对应由脏块组成的checkpoint queue队列，DBWR是按照seq为顺序将脏块写入到disk中的。
另外通过controlfile的dump，我们也能看到low rba和on disk scn的信息，如下：

SQL> ALTER SESSION SET events 'immediate trace name controlf level 8';   SESSION altered.

++++++ trace 部分信息如下：++++++   CHECKPOINT PROGRESS RECORDS *************************************************************************** (size = 8180, compat size = 8180, section max = 11, section in-use = 0, last-recid= 0, old-recno = 0, last-recno = 0) (extent = 1, blkno = 2, numrecs = 11) THREAD #1 - status:0x2 flags:0x0 dirty:3 low cache rba:(0xf.2.0) on disk rba:(0xf.4.0) on disk scn: 0x0000.000e0330 11/07/2011 01:43:51 resetlogs scn: 0x0000.000cf315 11/03/2011 02:05:04 heartbeat: 766575498 mount id: 2470768355 THREAD #2 - status:0x0 flags:0x0 dirty:0   low cache rba:(0xf.2.0) -- （f->15,2->2,0->0),跟上面查询x$kcccp的结果是一样的。

最后补充一下，10g oracle引入了增量检查点自动调节机制，其中相关的几个参数如下：

 
SQL> SHOW parameter checkpoint
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_disable_incremental_checkpoints     BOOLEAN     FALSE
_disable_selftune_checkpointing      BOOLEAN     FALSE
_log_checkpoint_recovery_check       INTEGER     0
_selftune_checkpoint_write_pct       INTEGER     3
_selftune_checkpointing_lag          INTEGER     300
log_checkpoint_interval              INTEGER     0
log_checkpoint_timeout               INTEGER     1800
log_checkpoints_to_alert             BOOLEAN     FALSE
 
_disable_incremental_checkpoints  决定是否启用增量检查点
_disable_selftune_checkpointing   决定是否启动增量检查点启动调节，默认是FALSE
_selftune_checkpoint_write_pct    是一个百分比
_selftune_checkpointing_lag       默认值是300s。