Re: Monitoring the "Checkpoint not complete" event

From: Jared Still <jkstill_at_gmail.com>
Date: Tue, 27 Oct 2009 10:04:23 -0700
Message-ID: <bf46380910271004n56e292b3nd4f967921d848f7f_at_mail.gmail.com>

As with Niall, I've not had much trouble with 'checkpoint not complete'.

While investigating this I found that manual logfile switches seemed to be causing 'checkpoint not complete; messages in the alert.log of a couple databases, one 10.2.0.4 and the other 11.1.0.7.

The timing didn't explain it, as both archive processes were available at the time of error.

(see the attached script for querying v$archived_log)

ML note *435887.1 *offers an explanation - private redo strands must be flushed for the log switch.

The 'checkpoint not complete' is very easy to reproduce by leaving an uncommitted transaction in flight during the log switch. There's an example in the note.

This applies to 10.2.0.4+, so it would seem that this problem probably did not occur in previous versions.

The timing of the 'checkpoint not complete' in the 10.2.0.4 database corresponded to the normal backup window, approximately the time the backups normally finished. That would explain the manual log switches, and the 'checkpoint not complete' errors.

Check your alert.log for the sequence# of the log that could not be allocated, query v$archived_log and v$log_history and check the size of the archived log. If the size is less than the normal size, the 'checkpoint not complete' probably occurred as the result of a forced log switch during a backup while an uncommitted transaction still had active redo strands.

HTH Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com

--
http://www.freelists.org/webpage/oracle-l




text/x-sql attachment: loghistory_8.sql

Received on Tue Oct 27 2009 - 12:04:23 CDT