Re: Incremental checkpoints and CHECKPOINT_CHANGE#

From: Jiang, Lu <Lu.Jiang_at_umassmed.edu>
Date: Thu, 14 Jan 2010 09:20:52 -0500
Message-ID: <BBB2B987DB6E504D89FC45EA314CE98901BB0016_at_edmtpmail01.ad.umassmed.edu>

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

---

From: Tanel Poder <tanel_at_poderc.com> To: Jiang, Lu
Cc: Bobak, Mark <Mark.Bobak_at_proquest.com>; oracle-l <oracle-l_at_freelists.org> 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_at_umassmed.edu> 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_at_freelists.org
	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_at_proquest.com] 
	Sent: Wednesday, January 13, 2010 6:39 PM

	To: Jiang, Lu; oracle-l_at_freelists.org
	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_at_umassmed.edu] 
	Sent: Wednesday, January 13, 2010 5:20 PM
	To: Bobak, Mark; oracle-l_at_freelists.org
	Subject: RE: Incremental checkpoints and CHECKPOINT_CHANGE#

	 

	No. The database is open and normal mode.

	 

	From: Bobak, Mark [mailto:Mark.Bobak_at_proquest.com] 
	Sent: Wednesday, January 13, 2010 5:12 PM
	To: Jiang, Lu; oracle-l_at_freelists.org
	Subject: RE: Incremental checkpoints and CHECKPOINT_CHANGE#

	 

	Is the tablespace in backup mode?

	 

	From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jiang, Lu
	Sent: Wednesday, January 13, 2010 3:54 PM
	To: oracle-l_at_freelists.org
	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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jan 14 2010 - 08:20:52 CST