incremental checkpoint advances the checkpoint position？

etl2007

根据文档，检查点的分为四种

“
CKPT进程在checkpoint发生时，将当时的SCN号写入数据文件头和控制文件，同时通知DBWR进程将数据块写到数据文件。
CKPT进程也会在控制文件中记录RBA(redo block address),以标志Recovery需要从日志中哪个地方开始。
与checkpoint相关的SCN号有四个，其中三个存在控制文件中，一个存放在数据文件头中。这四个分别是：
1.System Checkpoint SCN当checkpoint完成后，ORACLE将System Checkpoint SCN号存放在控制文件中。我们可以通过下面SQL语句查询：
select checkpoint_change# from v$database;
2.Datafile Checkpoint SCN当checkpoint完成后，ORACLE将Datafile Checkpoint SCN号存放在控制文件中。我们可以通过下面SQL语句查询所有数据文件的Datafile Checkpoinnt SCN号。
select name,checkpoint_change# from v$datafile;
3.Start SCN号
ORACLE将Start SCN号存放在数据文件头中。这个SCN用于检查数据库启动过程是否需要做media recovery.我们可以通过以下SQL语句查询：
select name,checkpoint_change# from v$datafile_header;
4.End SCN号
ORACLE将End SCN号存放在控制文件中。这个SCN号用于检查数据库启动过程是否需要做instance recovery.我们可以通过以下SQL语句查询：
select name,last_change# from v$datafile; 在数据库正常运行的情况下，对可读写的，online的数据文件，该SCN号为NULL.


但是 Oracle 文档上说
An incremental checkpoint is a type of thread checkpoint partly
intended to avoid writing large numbers of blocks at online redo log switches.
DBWn checks at least every three seconds to determine whether it has work to do.
When DBWn writes dirty buffers, it advances the checkpoint position,
causing CKPT to write the checkpoint position to the control file, but not to the data file headers.

我的疑问是 这个it advances the checkpoint position 是这个 checkpoint position 是什么呢？
好像和上面四种检查点都不靠啊？

Maclean Liu(刘相兵

首先 incremental checkpoint 增量检查点不会更新 data file header而 只更新 control file


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> alter system set log_checkpoint_timeout=600;

System altered.

设置600s发生一次 incremental checkpoint


SQL> alter system set log_checkpoints_to_alert=TRUE;

System altered.

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump controlf 3 ;
Statement processed.
SQL> oradebug tracefile_name;
/s01/admin/G10R21/udump/g10r21_ora_10774.trc

CPR1

***************************************************************************
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:0
low cache rba:(0xffffffff.ffffffff.ffff) on disk rba:(0x7.8d13.0)
on disk scn: 0x0000.010aa0a5 04/10/2012 10:54:27
resetlogs scn: 0x0000.0109a8d9 04/01/2012 07:39:01
heartbeat: 780241499 mount id: 2820988564
Flashback log tail log# 10 thread# 1 seq 14 block 337 byte 0


等待600s  告警日志中会出现 发生过incremental checkpoint的信息：

Tue Apr 10 11:04:12 2012
Incremental checkpoint up to RBA [0x7.8d66.0], current log tail at RBA [0x7.9286.0]


再次dump controlfile

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump controlf 3;
Statement processed.
SQL> oradebug tracefile_name;
/s01/admin/G10R21/udump/g10r21_ora_10831.trc

CPR2

***************************************************************************
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:282
low cache rba:(0x7.8d66.0) on disk rba:(0x7.928a.0)
on disk scn: 0x0000.010aa1cd 04/10/2012 11:04:33
resetlogs scn: 0x0000.0109a8d9 04/01/2012 07:39:01
heartbeat: 780241700 mount id: 2820988564
Flashback log tail log# 10 thread# 1 seq 14 block 604 byte 0

CPR1      

low cache rba:(0xffffffff.ffffffff.ffff) on disk rba:(0x7.8d13.0)               ==> 0xffffffff.ffffffff.ffff indicate last time FULL CHECKPOINT
on disk scn: 0x0000.010aa0a5 04/10/2012 10:54:27
heartbeat: 780241499 mount id: 2820988564

CPR2
low cache rba:(0x7.8d66.0) on disk rba:(0x7.928a.0)
on disk scn: 0x0000.010aa1cd 04/10/2012 11:04:33
heartbeat: 780241700 mount id: 2820988564

etl2007

1）你这个网站不能贴图啊,有些郁闷
2）我看了egyle大师的书，他里面写到
   “控制文件检查点SCN，增量检查点不断增进的内容之一”
   例如
  “
    < Controlfile Checkponited at scn 0x0000.00095a4c 07/07/200 11.27:56
    ................................
           Controlfile Checkponited at scn 0x0000.00095ae6 07/07/200 11.33:46
              >

   ”
我的疑问是 这个“    Controlfile Checkponited ”是什么东东？完全不在那四类检查点之中啊？
在你dump出来的结果中怎么看不到这个东东呢？

etl2007

我dumpcontrolfile看了一下
发现

**************************************************************************
DATABASE ENTRY
***************************************************************************
(blkno = 0x1, size = 192, max = 1, in-use = 1, last-recid= 0)
DF Version: creation=0x9200000 compatible=0x8000000, Date  12/06/2007 17:05:49
DB Name "ETLDB"
Database flags = 0x00404000
Controlfile Creation Timestamp  12/06/2007 17:05:50
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.00000001 Resetlogs Timestamp  12/06/2007 17:05:49
Prior resetlogs scn: 0x0000.00000000 Prior resetlogs Timestamp  01/01/1988 00:00:00
Redo Version: creation=0x9200000 compatable=0x9200000
#Data files = 8, #Online files = 8
Database checkpoint: Thread=1 scn: 0x0000.0010bd45
Threads: #Enabled=1, #Open=1, Head=1, Tail=1
enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000
Max log members = 3, Max data members = 1
Arch list: Head=0, Tail=0, Force scn: 0x0000.001002bcscn: 0x0000.00000000
Controlfile Checkpointed at scn:  0x0000.0010bd45 10/15/2011 10:43:25
thread:0 rba:(0x0.0.0)
enabled  threads:  00000000 00000000 00000000 00000000 00000000 00000000
  00000000 00000000

这个Controlfile Checkpointed at scn意味什么呢？

Maclean Liu(刘相兵

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


SQL> alter system set log_checkpoint_timeout=10;

System altered.

SQL> alter system checkpoint;

System altered.

SQL>  select CHECKPOINT_CHANGE# , CONTROLFILE_CHANGE# from v$database;

CHECKPOINT_CHANGE# CONTROLFILE_CHANGE#
------------------ -------------------
          17637178            17637178


SQL> delete tls ;

50675 rows deleted.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

SQL> select CHECKPOINT_CHANGE# , CONTROLFILE_CHANGE# from v$database;

CHECKPOINT_CHANGE# CONTROLFILE_CHANGE#
------------------ -------------------
          17637178            17639482

Sat Apr 14 08:29:41 2012
Incremental checkpoint up to RBA [0x10.c157.0], current log tail at RBA [0x10.c157.0]
Sat Apr 14 08:29:50 2012
Incremental checkpoint up to RBA [0x10.c158.0], current log tail at RBA [0x10.c158.0]

CONTROLFILE_CHANGE#   控制文件change#增加了， 这个CONTROLFILE_CHANGE# 会随着CKPT 每3s的heartbeat 更新， 而CHECKPOINT_CHANGE# 等于上一次FULL或 thread 或 log switch(比较特殊) checkpoint 检查点发生时的更新的SCN。





SQL> oradebug setmypid;
Statement processed.

SQL> oradebug dump controlf 4;
Statement processed.

SQL> oradebug tracefile_name;
/s01/admin/G10R21/udump/g10r21_ora_24875.trc

SQL> select CHECKPOINT_CHANGE# , CONTROLFILE_CHANGE# from v$database;

CHECKPOINT_CHANGE# CONTROLFILE_CHANGE#
------------------ -------------------
          17637178            17639482




***************************************************************************
DATABASE ENTRY
***************************************************************************
(size = 316, compat size = 316, section max = 1, section in-use = 1,
  last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 1, numrecs = 1)
03/09/2012 23:20:03
DB Name "G10R21"
Database flags = 0x40404001 0x00001200
Controlfile Creation Timestamp  03/09/2012 23:20:03
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.0109a8d9 Resetlogs Timestamp  04/01/2012 07:39:01
Prior resetlogs scn: 0x0000.00e7795c Prior resetlogs Timestamp  03/09/2012 23:22:15
Redo Version: compatible=0xa200100
#Data files = 16, #Online files = 15
Database checkpoint: Thread=1 scn: 0x0000.010d1f3a
Threads: #Enabled=1, #Open=1, Head=1, Tail=1
enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000
Max log members = 3, Max data members = 1
Arch list: Head=3, Tail=3, Force scn: 0x0000.010c30b4scn: 0x0000.010d1f0a
Activation ID: 2820193140
Controlfile Checkpointed at scn:  0x0000.010d283a 04/14/2012 08:28:59
thread:0 rba:(0x0.0.0)


  0x0000.010d283a  ==》　　17639482 ==  CONTROLFILE_CHANGE#


***************************************************************************
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:0
low cache rba:(0xffffffff.ffffffff.ffff) on disk rba:(0x10.c15e.0)
on disk scn: 0x0000.010d2859 04/14/2012 08:30:21
resetlogs scn: 0x0000.0109a8d9 04/01/2012 07:39:01
heartbeat: 780353738 mount id: 2820988564
Flashback log tail log# 20 thread# 1 seq 82 block 139 byte 0