Sunday, February 7, 2010

ALTER SYSTEM SWITCH LOGFILE and CHECKPOINT

Question: Will "alter system switch logfile" trigger checkpoint?
Answer: It will only trigger a complete checkpoint when active redo log group to be overwritten due to circular fashion. In other words, "alter system switch logfile" may trigger complete checkpoint, but not always.



SQL> alter system switch logfile;

System altered.

SQL> select first_time,first_change#,sequence#,status from v$log;

FIRST_TIME FIRST_CHANGE# SEQUENCE# STATUS
-------------------- ------------- ---------- ----------------
2010-FEB-07 09:42:49 2180598 37 ACTIVE
2010-FEB-07 09:46:12 2180846 38 CURRENT
2010-FEB-04 20:51:32 2156580 36 INACTIVE

SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ --------------------
2180599 2010-FEB-07 09:42:49
2180599 2010-FEB-07 09:42:49
2180599 2010-FEB-07 09:42:49
2180599 2010-FEB-07 09:42:49
2180599 2010-FEB-07 09:42:49


SQL> alter system switch logfile;

System altered.

SQL> select first_time,first_change#,sequence#,status from v$log;

FIRST_TIME FIRST_CHANGE# SEQUENCE# STATUS
-------------------- ------------- ---------- ----------------
2010-FEB-07 09:42:49 2180598 37 ACTIVE
2010-FEB-07 09:46:12 2180846 38 ACTIVE
2010-FEB-07 09:46:40 2180856 39 CURRENT

SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ --------------------
2180599 2010-FEB-07 09:42:49
2180599 2010-FEB-07 09:42:49
2180599 2010-FEB-07 09:42:49
2180599 2010-FEB-07 09:42:49
2180599 2010-FEB-07 09:42:49


Check point triggered, due to active redo group being overwritten

SQL> alter system switch logfile;

System altered.

SQL> select first_time,first_change#,sequence#,status from v$log;

FIRST_TIME FIRST_CHANGE# SEQUENCE# STATUS
-------------------- ------------- ---------- ----------------
2010-FEB-07 09:46:55 2180863 40 CURRENT
2010-FEB-07 09:46:12 2180846 38 INACTIVE
2010-FEB-07 09:46:40 2180856 39 INACTIVE

SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ --------------------
2180863 2010-FEB-07 09:46:55
2180863 2010-FEB-07 09:46:55
2180863 2010-FEB-07 09:46:55
2180863 2010-FEB-07 09:46:55
2180863 2010-FEB-07 09:46:55


SQL> alter system switch logfile;

System altered.

SQL> select first_time,first_change#,sequence#,status from v$log;

FIRST_TIME FIRST_CHANGE# SEQUENCE# STATUS
-------------------- ------------- ---------- ----------------
2010-FEB-07 09:46:55 2180863 40 ACTIVE
2010-FEB-07 09:47:59 2181178 41 CURRENT
2010-FEB-07 09:46:40 2180856 39 INACTIVE

SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ --------------------
2180863 2010-FEB-07 09:46:55
2180863 2010-FEB-07 09:46:55
2180863 2010-FEB-07 09:46:55
2180863 2010-FEB-07 09:46:55
2180863 2010-FEB-07 09:46:55



SQL> alter system switch logfile;

System altered.

SQL> select first_time,first_change#,sequence#,status from v$log;

FIRST_TIME FIRST_CHANGE# SEQUENCE# STATUS
-------------------- ------------- ---------- ----------------
2010-FEB-07 09:46:55 2180863 40 ACTIVE
2010-FEB-07 09:47:59 2181178 41 ACTIVE
2010-FEB-07 09:48:29 2181977 42 CURRENT

SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ --------------------
2180863 2010-FEB-07 09:46:55
2180863 2010-FEB-07 09:46:55
2180863 2010-FEB-07 09:46:55
2180863 2010-FEB-07 09:46:55
2180863 2010-FEB-07 09:46:55

Check point triggered, due to active redo group being overwritten

SQL> alter system switch logfile;

System altered.

SQL> select first_time,first_change#,sequence#,status from v$log;

FIRST_TIME FIRST_CHANGE# SEQUENCE# STATUS
-------------------- ------------- ---------- ----------------
2010-FEB-07 09:49:28 2184453 43 CURRENT
2010-FEB-07 09:47:59 2181178 41 ACTIVE
2010-FEB-07 09:48:29 2181977 42 ACTIVE

SQL> select checkpoint_change#,checkpoint_time from v$datafile_header;

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ --------------------
2181178 2010-FEB-07 09:47:59
2181178 2010-FEB-07 09:47:59
2181178 2010-FEB-07 09:47:59
2181178 2010-FEB-07 09:47:59
2181178 2010-FEB-07 09:47:59



log switch checkpoint and complete checkpoint

2010-02-07 21:21:29.530000 +08:00
Beginning log switch checkpoint up to RBA [0x31.2.10], SCN: 2242997
Thread 1 advanced to log sequence 49
Current log# 1 seq# 49 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO01.LOG
2010-02-07 21:22:05.590000 +08:00
Beginning log switch checkpoint up to RBA [0x32.2.10], SCN: 2243020
Thread 1 advanced to log sequence 50
Current log# 2 seq# 50 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO02.LOG
2010-02-07 21:22:15.244000 +08:00
Thread 1 cannot allocate new log, sequence 51
Checkpoint not complete
Current log# 2 seq# 50 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO02.LOG
2010-02-07 21:22:16.766000 +08:00
Completed checkpoint up to RBA [0x31.2.10], SCN: 2242997
Beginning log switch checkpoint up to RBA [0x33.2.10], SCN: 2243027
Thread 1 advanced to log sequence 51
Current log# 3 seq# 51 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO03.LOG
2010-02-07 21:22:27.363000 +08:00
Thread 1 cannot allocate new log, sequence 52
Checkpoint not complete
Current log# 3 seq# 51 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO03.LOG
2010-02-07 21:22:28.647000 +08:00
Completed checkpoint up to RBA [0x32.2.10], SCN: 2243020
Beginning log switch checkpoint up to RBA [0x34.2.10], SCN: 2243034
2010-02-07 21:22:29.828000 +08:00
Thread 1 advanced to log sequence 52
Current log# 1 seq# 52 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO01.LOG