Oracle 7.3.3. windows nt

[Paul H]

Sometimes I am getting a message coming up in the ALRT file for a particular database "Thread 1 cannot allocate new log sequence nnnnn - checkpoint not complete".

The database has two redo log files. Do I need extra log files?? It appears to be happening about twice a week.

[pipo]

This warning usually indicates that your redo log files are either too small, or that you have too few. Basically it means that all the available on-line redo log files have been used up whilst a single (possibly chained) checkpoint is running. At this point Oracle will wait until all the previously queued dirty blocks have been written from the buffer to disc before any of the redo log files can be considered redundant and available for re-use.

When the checkpoint is finally complete, Oracle will mark all the redo log files as available, select one of them, and allow redo to be written out again. Of course, since the redo buffer cannot be written to a redo log file whilst the checkpoint is completing, it is quite likely that the redo buffer will rapidly fill, and all activity on the database that needs to generate redo will be suspended.

Fortunately (for 'ordinary' databases) the time required for a checkpoint is quite small, so business will resume quite rapidly. If you only see a couple of these warnings each day in your alert log you need not take any action - the performance impact may be a couple of minutes in 24 hours. (You may be able to confirm this through the timestamps that appear with many messages in the alert log).

However, if you have numerous warnings about consecutive log sequences, you probably need to increase the size of your log files (although it may be sufficient just to add a few more log files). There is no very good rule of thumb that allows you to pick a good size of log file, but if you are checkpointing every one or two minutes in busy periods I think you need larger log files rather than more log files.