Home » RDBMS Server » Performance Tuning » Checkpoint not complete
Checkpoint not complete [message #137649] Thu, 15 September 2005 21:48 Go to next message
David King
Messages: 8
Registered: December 2004
Junior Member
Hi gang,

I am in need of some advice. First, I am running Oracle on Solaris 9. On just about a daily basis we perform sqlldr loads that load on the order of 300000 rows. I frequently see in my alert log:
Checkpoint not complete
Current log# 5 seq# 176431

I have 5 redo logs each of 10M in size. If I check what is going on in v$log and correlate to the alert log when it throws the checkpoint error I always notice that I have one current log (which is good) and the rest are in a status of 'ACTIVE'. It seems that when this happens I get the checkpoint error.

What can I do to get rid of this checkpoint error? Should I increase the size of my redo logs? Is there a good way to go about estimating what size redo logs I should have?

Thanks in advance for your help.

Re: Checkpoint not complete [message #137700 is a reply to message #137649] Fri, 16 September 2005 02:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
The logfiles are always re-used.
But first, checkpoint should be completed on those logfiles, so that oracle can use them. Until checkpoint can do its job / flush the logfiles , oracle cannot reuse the logfile.
So ideally, increase the logfile size.
Increase the frequency of checkpoint occurence
(reduce the interval between the checkpoints)
Re: Checkpoint not complete [message #137708 is a reply to message #137700] Fri, 16 September 2005 03:26 Go to previous messageGo to next message
Messages: 59
Registered: July 2005
Location: Glasgow
Not really sure if increasing the size of the redo log files are the solution I would go for.

As Mahesh says, the redo log files can only be reused if Oracle still requires them for instance recovery, that is, he checks the scn# of the oldest block in the checkpoint queue (a list of dirty blocks in the buffer cache) and works out if that number is higher or lower than the "highest scn#" in the redo log file that LGWR is trying to overwrite.. if the number is lower than the "highest scn#" then CKPT prevents the logfile being overwritten, as an instance crash right now will need that information for recovery.. (recovery starts at the point of the oldest dirty block not written to disk at the point of failure.)

The reason I mention all of this is because.. there are a number of solutions.

1. Make DBWR write more aggressively - as you are on 9i the parameter I would use is FAST_START_MTTR_TARGET=(how long you want recovery to take in seconds), the lower that number, the more aggressively DBWR has to write to keep up with the target, the advantage of this is that by the time LGWR comes to overwrite the redo log file, the chances are that DBWR has already written the "high scn#" (and beyond) from the checkpoint q.

-- This disadvantage is that you will get more I/O to your disks.

2. Create more redo log file groups - this will give DBWR more time to write before LGWR tries to overwrite a particular redolog file, again the chances are that the extra (6th) or (7th) group will give CKPT enough time to completely checkpoint beyond the "highest scn#" before that group is again required..

Which one to go for.. well that's up to you and your setup, if you have an I/O bound system then 2. would be better for you, as 1. will just increase your I/O problem, however if physical space is an issue and I/O isn't then 1. might be better (with the added advantage that instance recovery will also be faster).

Sorry for the training session, but as with everything to do with Oracle, there is rarely one solution that apply to everyone...


Re: Checkpoint not complete [message #562814 is a reply to message #137708] Mon, 06 August 2012 01:02 Go to previous messageGo to next message
Messages: 2
Registered: August 2012
Location: Dhaka, Bangladesh
Junior Member
This was a very helpful and informative one. Thanks!!
Re: Checkpoint not complete [message #571821 is a reply to message #562814] Fri, 30 November 2012 22:56 Go to previous messageGo to next message
Messages: 1
Registered: April 2012
Junior Member
alliejane Your advice on is really helpful !! But i have read that to reduce the I/O partial checkpoints like the ones occurring on the logs witches only guarantee the Ckpt that all the RBA entries in the log buffer will be written to the redologs so that if the instance crashes the instance recovery could be made by identifying the row,block etc just by pointing it to that particular block using pointers in redolog groups. Also what i understand is that checkpoint not complete occurs because it is protecting the data which is to be archived by ARCn process. As these are also the old pointers so they have to be protected. It doesnt care about DBWR has flushed its contents or not !! also DBWR writes its contents using a lazy algorithm so its not required that whenever log buffer flushes it has to write its contents ! Also consider one thing that the size of log buffer and DB buffer vary too much !! logbuffer is typically in MBs while DB buffer could be in GB's !! so it cant write all the data at once !! So in this scenario of Checkpointing not complete i recon that one should go for the increase in no of redolog groups and also dont create them huge ! ARCn process writes at a different pace then LGWR !! make them plenty so that frequency of reuse could be reduced and ARCn gets more time to flush the contents !! Cheers
Re: Checkpoint not complete [message #571826 is a reply to message #571821] Sat, 01 December 2012 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You mostly misundertood how it works.
Also it seems you are still on the version 7 of rdbms.
Please read Database Concepts.

Re: Checkpoint not complete [message #571847 is a reply to message #137649] Sat, 01 December 2012 19:51 Go to previous message
Messages: 59
Registered: July 2005
Location: Glasgow
Please note that I posted that information in 2005, oracle has come a long way since then and the processes have changed.

Kind regards

Previous Topic: Bloom filters
Next Topic: Parallel Feature
Goto Forum:

Current Time: Fri Mar 01 04:05:48 CST 2024