Does log switching generate incremental checkpoints? Answer!

many Oracle beginners have headaches due to the checkpoint knowledge point. The vast majority of Oracle documents describe full checkpoints and incremental checkpoints; for example, if the switching of online logs results in a full checkpoint or an incremental checkpoint, there is a lot of debate. In fact, there is a significant difference between an incremental checkpoint and a full checkpoint: when a full checkpoint occurs, the checkpoint SCN in the control file and the data file header will be updated, when an incremental checkpoint occurs, only the checkpoint SCN in the control file is updated.
the following example shows the checkpoint triggered by log switching?:

SQL> select checkpoint_change # from V $ datafile_header where status = 'online'; checkpoint_change # ---------------- 1665476 1665476 1665476 1665476 1665476 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 rows selected. /* Manual Run the checkpoint command. The checkpoint SCN in the data file header immediately updates */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 rows selected. /* The flush buffer cache is flushed to the database cache and does not update the checkpoint SCN of the data file header */SQL> alter system set log_checkpoints_to_alert = true; System altered. SQL> Alter system set log_checkpoint_timeout = 20; System altered. /* Set log_checkpoint_timeout to 20 s, which frequently triggers incremental checkpoints */Alert Log: Wed Nov 3 20:24:49 2010 incremental checkpoint up to RBA [0x3d. dff1.0], current log tail at RBA [0x3d. dff6.0] wed Nov 3 20:25:07 2010 incremental checkpoint up to RBA [0x3d. dff7.0], current log tail at RBA [0x3d. dffc.0] wed Nov 3 20:25:25 2010 incremental checkpoint up to RBA [0x3d. dffd.0], c Urrent log tail at RBA [0x3d. e002.0] wed Nov 3 20:25:43 2010 incremental checkpoint up to RBA [0x3d. e003.0], current log tail at RBA [0x3d. e008.0] wed Nov 3 20:26:01 2010 incremental 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 1 697131 1697131 1697131 1697131 16971316 rows selected. /* you can see that the incremental checkpoint does not cause the checkpoint SCN of the data file header to be updated */20:26:43 SQL> alter system set log_checkpoint_timeout = 1800; System altered. /* Will the log file switch cause the checkpoint SCN in the data file header to be updated? */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 rows selected. /* logfile switch log file switching causes a complete low slow checkpoint, which is different from alter system checkpoint (ASC ), the dirty block writing and control file and Data File Header update required by ASC should be completed immediately, that is, when the alter system checkpoint statement returns "system altered. "The above work has been completed, and alter system switch logfile or natural log switching leads to a slow full checkpoint, when it returns "system altered", it does not require that dirty block writing and other work be completed * // * We can use the method of flushing the cache to ensure that the dirty block writing work is pushed to completion * /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 rows selected. /* although the slow checkpoint (slow checkpoint) caused by log switching does not need to be completed immediately, it is not completely unrestricted; when a log group is switched from 1 to 2, an slow checkpoint is triggered, after the continuous switching of logs to log group 1, the previous slow checkpoint must be completed */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 ------------ ---------- --- hour --------------- hour 1 1 67 52428800 2 Yes inactive 1698288 20:41:19 2 68 52428800 2 Yes inactive 1698292 20:41:21 3 1 69 52428800 2 no current 1698302 20: 41: 35 elapsed: 00:00:00. 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 alarm log Content: Wed Nov 3 20:42:40 2010 beginning log switch checkpoint up to RBA [0x46. 2.10], SCN: 1700686 ........................... Wed Nov 3 20:42:45 2010 thread 1 cannot allocate new log, sequence 72 checkpoint not complete .................... completed checkpoint up to RBA [0x46. 2.10], SCN: 1700686/* The last log switch took 2 S. You can see the slow checkpoint record in the alarm log */