logfile switch causes incremental checkpoint?

不少Oracle的初学者都会因为checkpoint这个知识点而头痛，绝大多数Oracle文档对完全检查点和增量检查点的描述又都略显朦胧；譬如在线日志的切换引起的是完全检查点还是增量检查点这个问题，就有不少的争论。实际上增量检查点与完全检查点有一个显著的区别:完全检查点发生时控制文件和数据文件头中的checkpoint scn都会被更新，而增量检查点发生时只有控制文件中的checkpoint scn更新;
我们可以通过以下演示证明日志切换引发的到底是何种检查点?:

SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';CHECKPOINT_CHANGE#------------------           1665476           1665476           1665476           1665476           1665476           16654766 rows selected.SQL> alter system checkpoint;System altered.SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';CHECKPOINT_CHANGE#------------------           1697131           1697131           1697131           1697131           1697131           16971316 rows selected./* 手动执行checkpoint,数据文件头的checkpoint scn立即更新了 */SQL> alter system flush buffer_cache;System altered.SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';CHECKPOINT_CHANGE#------------------           1697131           1697131           1697131           1697131           1697131           16971316 rows selected./* 单纯flush buffer cache冲刷数据库高速缓存不会更新数据文件头的checkpoint scn */SQL> alter system set log_checkpoints_to_alert=true;System altered.SQL> alter system set log_checkpoint_timeout=20;System altered./* 设置log_checkpoint_timeout为20s，频繁引发增量检查点 */alert log:Wed Nov  3 20:24:49 2010Incremental checkpoint up to RBA [0x3d.dff1.0], current log tail at RBA [0x3d.dff6.0]Wed Nov  3 20:25:07 2010Incremental checkpoint up to RBA [0x3d.dff7.0], current log tail at RBA [0x3d.dffc.0]Wed Nov  3 20:25:25 2010Incremental checkpoint up to RBA [0x3d.dffd.0], current log tail at RBA [0x3d.e002.0]Wed Nov  3 20:25:43 2010Incremental checkpoint up to RBA [0x3d.e003.0], current log tail at RBA [0x3d.e008.0]Wed Nov  3 20:26:01 2010Incremental checkpoint up to RBA [0x3d.e009.0], current log tail at RBA [0x3d.e00e.0]SQL> set time on;20:26:38 SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';CHECKPOINT_CHANGE#------------------           1697131           1697131           1697131           1697131           1697131           16971316 rows selected./* 可以看到增量检查点并不会引起数据文件头的checkpoint scn 被更新 */20:26:43 SQL>  alter system set log_checkpoint_timeout=1800;System altered./* 那么日志文件切换就会引起数据文件头的checkpoint scn被更新吗？*/20:28:10 SQL> alter system switch logfile;System altered.20:29:16 SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';CHECKPOINT_CHANGE#------------------           1697131           1697131           1697131           1697131           1697131           16971316 rows selected./* logfile switch 日志文件切换引起的是一种slow慢的完全检查点，它不同于alter system checkpoint(ASC)，   ASC要求的脏块写出和控制文件及数据文件头更新时要立即完成的，也就是说当alter system checkpoint语句返回"System altered."   后以上工作都已经完成了；而alter system switch logfile或者自然的日志切换引发的是一种慢的完全检查点，   它在返回"System altered"时不要求写脏块等工作必须已经完成*//* 我们可以用冲刷高速缓存的方式保证脏块写出的工作被督促完成 */20:33:39 SQL> alter system flush buffer_cache;System altered.20:33:45 SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';CHECKPOINT_CHANGE#------------------           1697544           1697544           1697544           1697544           1697544           16975446 rows selected./* 虽然日志切换所引发的slow checkpoint(慢的检查点)并无立即完成的要求，但也并非全无限制；   当某次日志切换由1号日志组切换到2号日志组时，   将引发一个slow checkpoint，之后日志连续切换又要切到1号日志组时要求之前的那个slow checkpoint在切换前必须完成*/20:41:35 SQL> set timing on;20:42:02 SQL>  select * from v$log;    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------         1          1         67   52428800          2 YES INACTIVE               1698288 2010-11-03 20:41:19         2          1         68   52428800          2 YES INACTIVE               1698292 2010-11-03 20:41:21         3          1         69   52428800          2 NO  CURRENT                1698302 2010-11-03 20:41:35Elapsed: 00:00:00.0020:42:17 SQL> delete tv;51134 rows deleted.Elapsed: 00:00:01.6820:42:34 SQL> commit;Commit complete.Elapsed: 00:00:00.0020:42:36 SQL> alter system switch logfile;System altered.Elapsed: 00:00:00.0120:42:40 SQL> alter system switch logfile;System altered.Elapsed: 00:00:00.0120:42:43 SQL> alter system switch logfile;System altered.Elapsed: 00:00:02.0020:45:28 SQL>  select checkpoint_change# from v$datafile_header where status='ONLINE';CHECKPOINT_CHANGE#------------------           1700686           1700686           1700686           1700686           1700686           17006866 rows selected.Elapsed: 00:00:00.00alter.log告警日志中的内容:Wed Nov  3 20:42:40 2010Beginning log switch checkpoint up to RBA [0x46.2.10], SCN: 1700686...........................Wed Nov  3 20:42:45 2010Thread 1 cannot allocate new log, sequence 72Checkpoint not complete....................Completed checkpoint up to RBA [0x46.2.10], SCN: 1700686/* 最近一次的日志切换耗费2s，在告警日志中可以看到此次slow checkpoint的相关记录 */