Know more about checkpoint

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#

===================================================================================================================================

=======================================================

无论是什么类型的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的等待进程间接地完成，以上具体由谁来完成操作 取决于检查点种类和目的。

==========================================================================================================================================================================================

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

 

 =============================================================================================================================================================================================

 

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

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