Know more about checkpoint

Maclean Liu(刘相兵 发表于 2012-3-10 23:25:38

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
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(刘相兵 发表于 2012-3-10 23:28:04

无论是什么类型的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(刘相兵 发表于 2012-3-10 23:47:28

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

wzhihua 发表于 2012-3-17 08:58:15

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

wzhihua 发表于 2012-3-17 23:03:06

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

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

wzhihua 发表于 2012-3-20 10:10:23

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

Maclean Liu(刘相兵 发表于 2012-4-29 20:39:39

回复 6# 的帖子

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

willing66 发表于 2012-5-7 20:35:52

谢谢maclean了

myownstars 发表于 2012-5-18 13:23:02

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

semiter 发表于 2013-1-18 19:14:01

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

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

dywjzh 发表于 2013-1-21 21:06:32

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

yehc@epsoft.com 发表于 2013-1-24 11:55:05

介绍的很详细，顶一个

wind 发表于 2013-1-26 06:38:12

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

dianchou 发表于 2013-1-29 00:04:20

顶一个，good

ybb896 发表于 2013-1-29 09:21:20

图没有出来,刘大

xteitxu 发表于 2013-5-6 15:11:05

好强大收藏

lory 发表于 2013-11-4 17:33:19

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(刘相兵 发表于 2013-11-5 23:29:08

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(刘相兵 发表于 2013-11-5 23:30:46

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

lory 发表于 2013-11-6 11:16:23

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

singlemice 发表于 2013-11-18 11:02:08

学习了，不错

diyindo 发表于 2014-2-19 15:47:19

谢谢刘老师学习了

dba_sky 发表于 2014-2-20 17:21:18

学习了，谢谢ML

页: [1]

Oracle数据库数据恢复、性能优化's Archiver

Know more about checkpoint

回复 6# 的帖子