RE: Checkpoint not complete error

Your average log switch time is 30 seconds? And that's AFTER going from 100MB to 250MB??

Generally, I like to target around 15-20 minutes log switch frequency. Based on that, and the fact that you're at 30 seconds w/ a 250MB log file, you need to hugely increase the size of your logfile. You'd need around a 7.5GB log file, to maintain a log switch frequency of 15 minutes.

But, before you do that, it may be useful to try to understand where all those changes are coming from. Given your understanding of your system, do you have an idea whether that level of redo generation is reasonable, given the type and volume of work the database is doing? It seems high to me, but I know absolutely nothing about your system or what it does.

If you think that level of redo generation is "reasonable", or, at least, explainable, then you need to crank up the size of your redo log. If you think that level of redo generation is excessive, then you need to investigate that. Could be extraneous, unnecessary work that should be eliminated, could be an oracle bug, could be a really, really poor design and implementation, etc.....

Hope that helps,

-Mark

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Saad Khan Sent: Thursday, April 23, 2009 2:45 PM
To: oracle-l_at_freelists.org
Subject: Re: Checkpoint not complete error

I can do that but the original size of logfiles was 100MB which I already increased to 250MB.

One thing I forgot to mention is that on an average, the logswitch time is around 30 seconds and it goes even less during the peak database activity.

The three checkpoint parameters in the database are set as below:

log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE Do you think I should edit these values as well? On Thu, Apr 23, 2009 at 2:36 PM, Vamshi Damidi <vamshireddy_1_at_hotmail.com<mailto:vamshireddy_1_at_hotmail.com>> wrote: increase the size to 500mb and see

that should resolve the problem.

thanks,
Vamshi .D

From: Saad Khan<mailto:saad4u_at_gmail.com> Sent: Thursday, April 23, 2009 2:32 PM
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: Checkpoint not complete error

Hi DBAs,

I've the environment of Oracle 10g (10.2.0.3) on SUSE Linux 9 (32-bit).

I've got 5 redo logfiles of 250MB each, still I'm seeing this the following error messages in the alert log file:

Thread 1 cannot allocate new log, sequence 17640 Checkpoint not complete

Users are also complaining of the slow database. What else can I do to make it perform better and get rid of the above message?

Thanks for the help.

Saad

--

http://www.freelists.org/webpage/oracle-l Received on Thu Apr 23 2009 - 14:03:40 CDT