ORACLE switch logfile更新ckpt研究-CSDN博客

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