Question:
:
I am seeing very slow import utility because of redo.log file errors
"Checkpoint cannot complete". I'm assuming that I need to add
redo.log files to accommodate the checkpoint not complete message?
What else can I do to reduce the checkpoint not complete messages in
my alert log?
Answer: The "checkpoint not complete" messages are generated because
the logs are switching so fast that the checkpoint associated with
the log switch isn't complete.
During that time, Oracle's LGWR process has filled up the other redo
log groups and is now waiting for the first checkpoint to
successfully execute.
Remember, Oracle will stop processing until the checkpoint completes
successfully!
Usually you see this "checkpoint not complete" error in your alert
log:
/u01/app/oracle/mydb/redo16.log
Thu Jan 14 22:12:55 2015
Thread 1 cannot allocate new log, sequence 46352
Checkpoint not
complete
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.
