Checkpoints in modern versions: Discrepancy (or gap) in observations
Date: Tue, 11 Sep 2012 10:15:08 +0200
Message-ID: <504EF30C.4060502_at_usn-it.de>
Hi Oracle-List,
my question is based on the following statement in the 11gR2 documentation:
Thread checkpoints occur in the following situations: <...>
Online redo log switch
AND: An incremental checkpoint is a type of thread checkpoint partly intended to avoid writing large numbers of blocks at online redo log switches. DBWn checks at least every three seconds to determine whether it has work to do. When DBWn writes dirty buffers, it advances the checkpoint position, causing CKPT to write the checkpoint position to the control file, but not to the data file headers.
(http://docs.oracle.com/cd/E11882_01/server.112/e25789/startup.htm#CNCPT89043)
My observation in Redo Log confirms:
Tue Sep 11 10:05:26 2012
Beginning log switch checkpoint up to RBA [0x9d3c.2.10], SCN: 111597554694 Thread 1 advanced to log sequence 40252 (LGWR switch) Current log# 18 seq# 40252 mem# 0:
+ORADATA/devee05/onlinelog/group_18.289.776430937 Current log# 18 seq# 40252 mem# 1:
+ORAFRA/devee05/onlinelog/group_18.617.776430941 Tue Sep 11 10:05:27 2012
Archived Log entry 40166 added for thread 1 sequence 40251 ID 0x70a7206d dest 1:
<end of alert log>
This looks as if there is a checkpoint triggered at the very time of the redo log switch.
But if I check v$database, I can't see any checkpoint progress.
SYSDATE CURRENT_SCN CHECKPOINT_CHANGE#CONTROLFILE_CHANGE#
------------------------- ---------------------- ---------------------- ---------------------- 11.09.2012 10:07:40 111597621204 111597358540111597607427
After a while, I see in the alert log:
Tue Sep 11 10:10:19 2012
Completed checkpoint up to RBA [0x9d39.2.10], SCN: 111597545203 Completed checkpoint up to RBA [0x9d3a.2.10], SCN: 111597548331 Completed checkpoint up to RBA [0x9d3b.2.10], SCN: 111597551671 Completed checkpoint up to RBA [0x9d3c.2.10], SCN: 111597554694
======================
And my select confirms:
SYSDATE CURRENT_SCN CHECKPOINT_CHANGE#CONTROLFILE_CHANGE#
------------------------- ---------------------- ---------------------- ---------------------- 11.09.2012 10:12:03 111597713033 111597554694111597709213
So far, so good: Now I know that the CHECKPOINT_CHANGE# is identical to the alert.log entry.
But my final question is: How can I monitor the progress of the incremental checkpoint? Because my impression (not more) is, that CHECKPOINT_CHANGE# jumps as soon as the checkpoint is complete.
Thank you very much
Martin Klier
-- Usn's IT Blog for Linux, Oracle, Asterisk http://www.usn-it.de -- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 11 2012 - 03:15:08 CDT