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
Posts
0 comments:
Post a Comment