Wednesday, November 18, 2009

cannot allocate new log

Thread 1 cannot allocate new log
Checkpoint not complete

If this problem is shown in alert log then increase the redo log group files.
Increase the size of redo log files.

It is batter to increase the redo log files.

This occurrs when Oracle attempts to reuse a log file but the checkpoint that would flush
the blocks that may have redo in this log file has not yet completed -- we must wait
until that checkpoint completes before we can reuse that file -- thats when this message
is printed. during this time (when we cannot allocate a new log) processing is suspended
in the database while the checkpoint is made to complete ASAP.

The major way to relieve this is to have sufficient log to carry you through peak times.
that way, we can complete the checkpoint while you are not busy.

also make sure your checkpoints happen as fast as they can (eg: enable ASYNC IO or
configure >1 DBWR if ansyc IO cannot be used, make sure disks are not contending with
other apps and so on)

Another way is to make the log files smaller, hence increasing the frequency with which
we checkpoint (log checkpoint interval and other init.ora parameters achieve the same
effect btw).

No comments: