Re: Incremental checkpoints and CHECKPOINT_CHANGE#

Thank you Tanel for clearing this out, well explained.
Thank you all for your inputs.

________________________________

From: Tanel Poder <tanel@xxxxxxxxxx> 
To: Jiang, Lu 
Cc: Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx>; oracle-l <oracle-l@xxxxxxxxxxxxx> 
Sent: Wed Jan 13 21:19:44 2010
Subject: Re: Incremental checkpoints and CHECKPOINT_CHANGE# 


Its because these are incremental checkpoints. Incremental checkpoint progress 
is recorded only in the controlfiles (and also DBWR occasionally dumps some of 
its progress details to redo, that's used for two pass recovery).

Datafile headers are updated only during the full or tablespace level 
checkpoints. If you have hundreds or thousands of datafiles you wouldn't want 
the CKPT to perform physical writes to each dafafile header every few seconds...

Log_checkpoint_timeout's behavior changed in 8i, it controls checkpoint lag 
now, not the frequency of checkpoints anymore (if I recall correctly, I haven't 
used this parameter for years as the fast_start* stuff is the way to go).

So, in an idle database (or a db with low enough activity) the full checkpoints 
may happen infrequently.

--
Tanel Poder
http://blog.tanelpoder.com



On Thu, Jan 14, 2010 at 7:45 AM, Jiang, Lu <Lu.Jiang@xxxxxxxxxxxx> wrote:


        I tried force a check point with 'alter system', this did update the 
scn # in datafile header. I don't understand why those check point showing in 
the alert log did not update the datafile header.

         

        From: Jiang, Lu 
        Sent: Wednesday, January 13, 2010 6:41 PM

        To: 'Bobak, Mark'; oracle-l@xxxxxxxxxxxxx
        Subject: RE: Incremental checkpoints and CHECKPOINT_CHANGE#

         

        Had checked the following and returned 'Not Active'. We only do backups 
at full db level with rman during evenings.

         

        From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxx] 
        Sent: Wednesday, January 13, 2010 6:39 PM

        To: Jiang, Lu; oracle-l@xxxxxxxxxxxxx
        Subject: RE: Incremental checkpoints and CHECKPOINT_CHANGE#

         

        Database being open and normal mode is fine, but datafiles could still 
be in backup mode.

         

        What do you get from:

        select * from v$backup where status=’ACTIVE’;

        ??

         

        If that returns any rows, those files are in backup mode.

         

        -Mark

         

        From: Jiang, Lu [mailto:Lu.Jiang@xxxxxxxxxxxx] 
        Sent: Wednesday, January 13, 2010 5:20 PM
        To: Bobak, Mark; oracle-l@xxxxxxxxxxxxx
        Subject: RE: Incremental checkpoints and CHECKPOINT_CHANGE#

         

        No. The database is open and normal mode.

         

        From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxx] 
        Sent: Wednesday, January 13, 2010 5:12 PM
        To: Jiang, Lu; oracle-l@xxxxxxxxxxxxx
        Subject: RE: Incremental checkpoints and CHECKPOINT_CHANGE#

         

        Is the tablespace in backup mode?

         

        From: oracle-l-bounce@xxxxxxxxxxxxx 
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jiang, Lu
        Sent: Wednesday, January 13, 2010 3:54 PM
        To: oracle-l@xxxxxxxxxxxxx
        Subject: Incremental checkpoints and CHECKPOINT_CHANGE#

         

        Hi DBA Gurus,

         

        Does anyone know why checkpoint scn does not write to datafile header 
in the following scenario?

         

        Parameter   log_checkpoint_timeout  = 1200

         

        -- Incremental checkpoints show in alert.log:

         

        Wed Jan 13 13:47:43 2010

        Incremental checkpoint up to RBA [0x79e.1cacf6.0], current log tail at 
RBA [0x79e.1cb16a.0]

        Wed Jan 13 14:07:43 2010

        Incremental checkpoint up to RBA [0x79e.1cc865.0], current log tail at 
RBA [0x79e.1ccd64.0]

        Wed Jan 13 14:27:47 2010

        Incremental checkpoint up to RBA [0x79e.1ceb74.0], current log tail at 
RBA [0x79e.1d0193.0]

        Wed Jan 13 14:47:47 2010

        Incremental checkpoint up to RBA [0x79e.1d101b.0], current log tail at 
RBA [0x79e.1d12e2.0]

         

        -- CHECKPOINT_CHANGE# has not been updated in v$datafile_header view, 
it stays with yesterdays check point info:

         

        SQL> select distinct CHECKPOINT_CHANGE#, CHECKPOINT_TIME from 
v$datafile_header;

         

        CHECKPOINT_CHANGE# CHECKPOINT_TIME

        ------------------ --------------------

                 580705781 12-JAN-10

         

        Thanks,

        Lu




-- 
Tanel Poder
http://blog.tanelpoder.com

Other related posts: