Sunday, February 7, 2010

ALTER SYSTEM ARCHIVE LOG CURRENT and CHECKPOINT

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

SQL> alter system archive log current;

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 INACTIVE
2010-FEB-07 10:41:46 2205034 44 ACTIVE
2010-FEB-07 10:50:38 2205435 45 CURRENT

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

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ --------------------
2205034 2010-FEB-07 10:41:46
2205034 2010-FEB-07 10:41:46
2205034 2010-FEB-07 10:41:46
2205034 2010-FEB-07 10:41:46
2205034 2010-FEB-07 10:41:46


SQL> alter system archive log current;

System altered.

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

FIRST_TIME FIRST_CHANGE# SEQUENCE# STATUS
-------------------- ------------- ---------- ----------------
2010-FEB-07 10:51:33 2205466 46 CURRENT
2010-FEB-07 10:41:46 2205034 44 ACTIVE
2010-FEB-07 10:50:38 2205435 45 ACTIVE


SQL> alter system archive log current;

System altered.

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

CHECKPOINT_CHANGE# CHECKPOINT_TIME
------------------ --------------------
2205435 2010-FEB-07 10:50:38
2205435 2010-FEB-07 10:50:38
2205435 2010-FEB-07 10:50:38
2205435 2010-FEB-07 10:50:38
2205435 2010-FEB-07 10:50:38