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

2 comments:

  1. Hi ,
    Is 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

    ReplyDelete
  2. 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