Know more about checkpoint

Maclean Liu(刘相兵

checkpoint 分成很多种  full 、file、thread、parallel query、 object 、incremental 、logfile switch

每一种checkpoint 都有其自身的特性，例如Incremental Checkpoint会要求ckpt 每3s 更新一次controlfile 但是不更新datafile header， 而FULL CHECKPOINT要求立即完成(同步的) 且会同时更新 controlfile 和 datafile header。

各种checkpoint 的特点见下表：

Full Checkpoint

Writes block images to the database for all dirty buffers from all instances

Statistics updated:
DBWR checkpoints
DBWR checkpoint buffers written
DBWR thread checkpoint buffers written

Caused by:   
Alter system checkpoint [global]
Alter database begin backup
Alter database close
Shutdown

Controlfile and datafile headers are updated
CHECKPOINT_CHANGE#

Thread Checkpoint

Writes block images to the database for all dirty buffers from one instance

Statistics updated:
DBWR checkpoints
DBWR checkpoint buffers written
DBWR thread checkpoint buffers written

Caused by:   
Alter system checkpoint local

Controlfile and datafile headers are updated
CHECKPOINT_CHANGE#


File Checkpoint

Writes block images to the database for all dirty buffers for all files of a tablespace from all instances
Statistics updated:

DBWR tablespace checkpoint buffers written
DBWR checkpoint buffers written
DBWR checkpoints

Caused by:   

Alter tablespace XXX offline
Alter tablespace XXX begin backup
Alter tablespace XXX read only

Controlfile and datafile headers are updated
CHECKPOINT_CHANGE#


Parallel Query Checkpoint

Writes block images to the database for all dirty buffers belonging to objects accessed by the query from all instances

Statistics updated:
DBWR checkpoint buffers written
DBWR checkpoints

Caused by:   
Parallel Query
Parallel Query component of PDML or PDDL
Mandatory for consistency


Object “Checkpoint”

Writes block images to the database for all dirty buffers belonging to an object from all instances

Statistics updated:

DBWR object drop buffers written
DBWR checkpoints

Caused by:   
Drop table XXX
Drop table XXX purge
Truncate table XXX
Mandatory for media recovery purposes


Incremental Checkpoint

Writes the contents of “some” dirty buffers to the database from CKPT-Q  
Block images written in SCN order
Checkpoint RBA updated in SGA

Statistics updated:
DBWR checkpoint buffers written

Controlfile is updated every 3 seconds by CKPT
Checkpoint progress record


Log Switch Checkpoint(8i 以前 LOG switch checkpoint是FULL CHECKPOINT)

Writes the contents of “some” dirty buffers to the database

Statistics updated:

DBWR checkpoints
DBWR checkpoint buffers written
background checkpoints started
background checkpoints completed

Controlfile and datafile headers are updated
CHECKPOINT_CHANGE#

Maclean Liu(刘相兵

无论是什么类型的checkpoint 检查点 ，所有的本地检查点(CKPT)已类似的本地化方法处理。 每一个实例中所有本地的活跃检查点请求(active local checkpoint request)都保存在一个队列(queue)中，这个队列叫做 Active Checkpoint Queue。 在这个队列(queue)中的每一条记录代表一个本地检查点(local checkpoint request)。 当某一个进程( 可能是前台进程 foreground process --例如前台进程执行“alter tablespace users begin/end backup"， 也可能是CKPT 或者其他后台进程) ， 这个进程都会将新的 request 记录放到这个Active Checkpoint Queue中。 典型的一个checkpoint request 由 检查点类型checkpoint request type，优先级priority , 以及与该checkpoint request 关联的checkpoint structure， 等待进程 waiter process，  还有其余一些相关的属性如 FILE Checkpoint 的tablespace id、FILE NUMBER、 Object checkpoint的object id 等。

DBWR进程会不断地扫描这个Active Checkpoint Queue， 并服务于这个Queue上的checkpoint request 检查点请求。 一旦某个request 被完成了，DBWR 将这个request标记为completed 。 CKPT 进程也会不断监控这个  Active Checkpoint Queue 查看是否所有request都被完成了。 当CKPT发觉一个checkpoint request完成了， CKPT会将这个request从 Active Checkpoint Queue中移除。  取决于不同的检查点种类和目的， 当一个本地检查点(local checkpoint)完成，这意味着 某些特定的磁盘上的数据结构被更新，以反映这个检查点完成的物理表现。 这个操作 或者由 直接CKPT完成， 或者由提交checkpoint request的 等待进程直接完成， 或者由CKPT唤醒这个提交checkpoint request的等待进程间接地完成，以上具体由谁来完成操作 取决于检查点种类和目的。

Maclean Liu(刘相兵

图解Low RBA, Thread Checkpoint Queue, File Checkpoint Queue

wzhihua

学习了。先mark，一会儿再看下。

wzhihua

问下，
8i 后 LOG switch checkpoint，与FULL CHECKPOINT有啥区别？？
前面的看看还能明白一些，上面的图却看不懂是怎么回事了。刘大再给解释下？

[ 本帖最后由 wzhihua 于 2012-3-19 08:39 编辑 ]

wzhihua

这两天学习了下， LOG switch checkpoint虽说更新了数据文件头，但是并没有把切换时所有的脏块写入数据文件，还只是把日志切换时检查点相关参数确定的位置前的脏块写到数据文件。因此说LOG switch checkpoint不是FULL CHECKPOINT。不知道这样理解是否正确。

Maclean Liu(刘相兵

参考 这篇文档 logfile switch causes incremental checkpoint?
http://www.oracledatabase12g.com ... tal-checkpoint.html

willing66

谢谢maclean了

myownstars

direct path read也应属于 Object “Checkpoint”一类

semiter

图解Low RBA, Thread Checkpoint Queue, File Checkpoint Queue


刘大，这里的图怎么看不到？？？

dywjzh

网站出问题了。图都看不到了

yehc@epsoft.com

介绍的很详细，顶一个

wind

网站出问题了。图都看不到了

dianchou

顶一个 ，good

ybb896

图没有出来,刘大

xteitxu

好强大   收藏

lory

File Checkpoint
这里说到会修改controlfile 的checkpoint_change#
但是我在10.2.0.5 on windows 2008上做
alter tablespace XXX begin backup .
只是该表空间的checkpoint_change# 改变了，controlfile 的checkpoint_change# （v$database）没有改变

Maclean Liu(刘相兵

ODM FINDING:
Understand SCN movement during online user managed backup;
Before online backup, check current scn, system and datafile scn in controlfile and scn in datafile header.

As expected checkpoint scn are same in controlfile and datafile headers, and it is behind current scn.

SQL> select current_scn, checkpoint_change# from v$database;

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
  325011036          325009912

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

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
+DBA_DATA/dbaprd/datafile/system.270.675355175              325009912
+DBA_DATA/dbaprd/datafile/sysaux.269.675355177              325009912
+DBA_DATA/dbaprd/datafile/undotbs1.266.675355179            325009912
+DBA_DATA/dbaprd/datafile/undotbs2.264.675355187            325009912
+DBA_DATA/dbaprd/datafile/users.263.675355189               325009912
+DBA_DATA/dbaprd/datafile/xml_data.262.675355189            325009912


SQL> select name,checkpoint_change# from v$datafile_header where name like '%users%';

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
+DBA_DATA/dbaprd/datafile/users.263.675355189               325009912


Start online tablespace backup

Oracle did a checkpoint on USERS tablespace and datafile only, and freeze the checkpoint scn on datafile header.

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> select current_scn, checkpoint_change# from v$database;

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
  325011196          325009912


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

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
+DBA_DATA/dbaprd/datafile/system.270.675355175              325009912
+DBA_DATA/dbaprd/datafile/sysaux.269.675355177              325009912
+DBA_DATA/dbaprd/datafile/undotbs1.266.675355179            325009912
+DBA_DATA/dbaprd/datafile/undotbs2.264.675355187            325009912
+DBA_DATA/dbaprd/datafile/users.263.675355189               325011168
+DBA_DATA/dbaprd/datafile/xml_data.262.675355189            325009912


SQL> select name,checkpoint_change# from v$datafile_header where name like '%users%';

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
+DBA_DATA/dbaprd/datafile/users.263.675355189               325011168


during the online backup checkpoint scn is freeze on datafile belongs to USERS tablespace

SQL> alter system checkpoint;

System altered.

SQL> select current_scn, checkpoint_change# from v$database;

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
  325011272          325011243

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

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
+DBA_DATA/dbaprd/datafile/system.270.675355175              325011243
+DBA_DATA/dbaprd/datafile/sysaux.269.675355177              325011243
+DBA_DATA/dbaprd/datafile/undotbs1.266.675355179            325011243
+DBA_DATA/dbaprd/datafile/undotbs2.264.675355187            325011243
+DBA_DATA/dbaprd/datafile/users.263.675355189               325011168
+DBA_DATA/dbaprd/datafile/xml_data.262.675355189            325011243


SQL> select name,checkpoint_change# from v$datafile_header where name like '%users%';

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
+DBA_DATA/dbaprd/datafile/users.263.675355189               325011168


END online tablespace backup;

Oracle advanced checkpoint scn on USERS tablespace and datafile only to be same as system checkpoint scn


SQL> alter tablespace users end backup;

Tablespace altered.

SQL> select current_scn, checkpoint_change# from v$database;

CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
  325011488          325011243

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

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
+DBA_DATA/dbaprd/datafile/system.270.675355175              325011243
+DBA_DATA/dbaprd/datafile/sysaux.269.675355177              325011243
+DBA_DATA/dbaprd/datafile/undotbs1.266.675355179            325011243
+DBA_DATA/dbaprd/datafile/undotbs2.264.675355187            325011243
+DBA_DATA/dbaprd/datafile/users.263.675355189               325011243
+DBA_DATA/dbaprd/datafile/xml_data.262.675355189            325011243


9 rows selected.

SQL> select name,checkpoint_change# from v$datafile_header where name like '%users%';

NAME                                               CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
+DBA_DATA/dbaprd/datafile/users.263.675355189               325011243

Maclean Liu(刘相兵

Alter tablespace XXX begin/end  backup  ==>看来在10g/11g上确实不更新控制文件checkpoint_change#

lory

刘大这样一解释比以前清楚一些了

singlemice

学习了，不错

diyindo

谢谢刘老师  学习了

dba_sky

学习了，谢谢ML