Oracle Checkpoint Not Complete Tips

Oracle suggests that you might consider setting archive_lag_target to zero to reduce "checkpoint not complete" messages:

alter system set archive_lag_target=0 scope=both;

Overall Oracle performance can be dramatically improved by increasing the log sizes so that logs switch at the recommended interval of 15 to 30 minutes.  

Identify the current size of the redo log members from v$log, record the number of log switches per hour and increase the size of the log to allow Oracle to switch at the recommended rate of one switch per 15 to 30 minutes.  

See here, important notes on how to monitor online redo log switches.  

For example, if the database redo log size is 1 megabyte and you are switching logs every 1 minute, you will need to increase the log size to 30 megabytes in size to allow it to switch every 30 minutes.  

The problem is that many applications have workloads that vary dramatically throughout each 24-hour time-period. In addition, application-processing workloads may vary according to the days of the calendar month.  

Month-end and mid-month processing may increase the number of changes occurring in the database. This higher level of activity may cause the redo logs to be filled much more quickly than during off-peak times.  

You need to ensure that the on-line redo logs don’t switch too often during periods of high activity and switch often enough during times of low processing workloads.   

This should reduce the delays from the checkpoint not complete errors.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.