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
Hi ,
ReplyDeleteIs this happening because
LGWR will wait for the active redo logs to be archived before it will overwrite the same.
So to make sure that all the changes are written to the datafiles during this waiting
period ,oracle triggers a checkpoint .
I am not sure about my answer .
Please guide me with the appropriate answer ,if i am wrong
One more thing
I have checked the above scenario practially.But after every logswitch
"DBWR checkpoints" in v$sysstat is incrementing ,irrespective of the status
of the log group.
Why is this happening ?
Thanks
Amit
Thanks for taking the time to discuss this, I feel strongly about it and love learning more on this topic. If possible, as you gain expertise, would you mind updating your blog with extra information? It is extremely helpful for me. cable Tray manufacturers in Pakistan
ReplyDelete