ORACLE switch logfile更新ckpt研究

夏莹然

v$datafile信息来自controlfile，v$datafile_header信息来自数据文件头，从理论上来讲v$datafile和v$datafile_header的checkpoint_change#字段更新是同步的。需要指出的是logfile switch，并没有进行全量的ckpt触发。(full checkpoint),v$datafile和v$datafile_header的ckpt只是注明了哪个scn开始recover（假如现在数据库宕掉的话）。
下面观察一下日志切换更新controlfile和datafile的过程：
1。当看当前logfile状态
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
---------- ----------------
24941 CURRENT
24939 INACTIVE
24940 INACTIVE
2.查看目前ckpt
SQL>  select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------------
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
0
9359600426926
CHECKPOINT_CHANGE#
------------------------
9359600426926
9359600426926
13 rows selected.
SQL>  select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------------
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9359604423730
9359600426926
CHECKPOINT_CHANGE#
------------------------
9359600426926
9359600426926
13 rows selected.

3.切换一个日志，并观察v$datafile_header和v$datafile的ckpt变化
SQL>  select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------------
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
0
9359600426926
CHECKPOINT_CHANGE#
------------------------
9359600426926
9359600426926
13 rows selected.
SQL>  select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------------
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9744661109769
9359604423730
9359600426926
CHECKPOINT_CHANGE#
------------------------
9359600426926
9359600426926
13 rows selected.
可以看到ckpt并没有发生变化。那什么时候日志切换会造成ckpt发生变化呢
4.再次查看logfile状态，可以看到状态为active
SQL> select sequence#,status from v$log;
SEQUENCE# STATUS
---------- ----------------
24944 CURRENT
24942 ACTIVE
24943 ACTIVE
5。查看ckpt值
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------------
9744661117946
9744661117946
9744661117946
9744661117946
9744661117946
9744661117946
9744661117946
9744661117946
9744661117946
0
9359600426926
CHECKPOINT_CHANGE#
------------------------
9359600426926
9359600426926
13 rows selected.
SQL>  select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------------
9744661117946
9744661117946
9744661117946
9744661117946
9744661117946
9744661117946
9744661117946
9744661117946
9744661117946
9359604423730
9359600426926
CHECKPOINT_CHANGE#
------------------------
9359600426926
9359600426926
6。switch logfile，并观察ckpt变化
SQL> alter system switch logfile;
System altered.
SQL>  select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------------
9744661118208
9744661118208
9744661118208
9744661118208
9744661118208
9744661118208
9744661118208
9744661118208
9744661118208
0
9359600426926
CHECKPOINT_CHANGE#
------------------------
9359600426926
9359600426926
13 rows selected.
SQL>  select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------------
9744661118208
9744661118208
9744661118208
9744661118208
9744661118208
9744661118208
9744661118208
9744661118208
9744661118208
9359604423730
9359600426926
CHECKPOINT_CHANGE#
------------------------
9359600426926
9359600426926
13 rows selected.

可以看到ckpt发生了变化。
总结：
ORACLE切换到下一个日志之前，下一个日志状态为inactive时，switch logfile是不会进行更新ckpt的，只有当状态为active时，switch logfile才会进行更新ckpt。因为当状态为active的日志，ORACLE需要重用它时（即lgwr写logfile），lgwr必须触发dbwr进程去刷新该日志相关的脏块，当这些脏块刷新到数据文件时，触发ckpt进程更新数据文件头和controlfile