>I've been tuning an Oracle 7.3.4 instance running on Solaris 2.6 and have
>run into an error in the instance's alert log that I cannot eliminate.  The
>message is:
>
>Thread 1 cannot allocate new log, sequence 4331
>
>Since theer is no number at the beginning of this message, I haven't had a
>lot of luck finding it in the Oracle online documentation.  I did use the
>Java-based search function, but it continually crashed. A search of .htm
>files in the Docs directory using the criteria "cannot allocate new log"
>produced no hits.
>
>I'd appreciate any assistance anyone can provide.  Whatever is causing this
>problem is also cutting throughput to about 1/10th of normal on the INSERT
>batch where it occurs.
>

Answer 1:

the infamous "checkpoint not complete, cannot allocate new log" message.

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).

I myself prefer to use a modest sized log file (around 25meg) and will add
logfiles until these messages cease...
 

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   [email protected]
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Answer 2:

This error arises becos LGWR cannot write into the  RedoLog file either becos
the CheckPoint triggered earlier is not complete or ARCHiver did not finish
copying the filled Redo Log file to the LOG_ARCH_DEST.

You can alleviate this problem by
1) Add more Redo Log files as this will provide more time for the CHeckPoint to
Complete as well as more time for the Archiver to complete the File Copy
2) If this is a Archiver issue , Accelerate it by tuning LOG_ARCHIVE_BUFFERS
setting in init.ora
3) You may also need to Increase the Size of the Redo Log file, if this is a
'High Transaction Volume' issue . I have seen this error many times during High
Data Loads and Resizing the redo Log files definetely helped..

Hope this helps..

-Thiru