Checkpoints in modern versions: Discrepancy (or gap) in observations

From: Martin Klier <usn_at_usn-it.de>
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           111597358540

111597607427

---

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           111597554694

111597709213

---

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-l

Received on Tue Sep 11 2012 - 03:15:08 CDT