Question:
I have regular appearances of the following message in the alert log file - how big a threat is it, and what should I do ?
Thread 1 cannot allocate new log, sequence 2474 Checkpoint not complete Current log# 3 seq# 2473 mem# 0: C:\ORACLE\ORADATA\O8I\REDO05.LOG
Answer:
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.
How, then, do you increase the size of your log files ?
The simple answer is - you can't - you have to create new ones and drop the old ones, with an approach something like the following:
-- See what you have at present select group, member from v$logfile; 3 C:\ORACLE\ORADATA\O8I\REDO02.LOG 4 C:\ORACLE\ORADATA\O8I\REDO01.LOG -- add two new files alter database add logfile 'c:\oracle\oradata\o8i\redo03.log' size 4M ; Database altered. alter database add logfile 'c:\oracle\oradata\o8i\redo04.log' size 4M ; Database altered. -- Make sure that the current log file is one of the new ones alter system switch logfile; System altered. alter system switch logfile; System altered. -- Now drop the old log files alter database drop logfile 'c:\oracle\oradata\o8i\redo02.log'; Database altered. alter database drop logfile 'c:\oracle\oradata\o8i\redo01.log'; Database altered.
At this point you can delete the old files quite safely although, on NT systems at least, you may find that when you try to delete the files you get a 'sharing violation' as the operating system level. This may persist until the next time you stop the database services, it may clear if you do 'alter system checkpoint global;' it may simply clear spontaneously some time later.
If you have a number of log files to clear, and you start dropping them in the wrong order, don't worry - I don't think its possible to drop a log file that Oracle still needs; you may be stopped by messages like
SQL> alter database drop logfile 'c:\oracle\oradata\o8i\redo01.log'; alter database drop logfile 'c:\oracle\oradata\o8i\redo03.log' * ERROR at line 1: ORA-01623: log 2 is current log for thread 1 - cannot drop ORA-00312: online log 2 thread 1: 'C:\ORACLE\ORADATA\O8I\REDO03.LOG'
Don't worry, messages like these are NOT fatal, just try again a little later: this specific message will not recur if you do a couple of 'switch logfiles' calls and wait a minute or two.
The only time when you really need to be VERY careful is when you go to the operating system level and delete the old log files. Oracle does NOT delete the files for you, so you have to do it by hand. If you delete a log file that the database needs, then you may find yourself recovering from a backup database.