The Oracle (tm) Users' Co-Operative FAQ
I keep getting errors in the alert log 'Thread XXX cannot allocate new log - checkpoint not complete'. Is this serious ?
--------------------------------------------------------------------------------
Author's name: Jonathan Lewis
Author's Email: Jonathan@jlcomp.demon.co.uk
Date written: 26th July 2001
Oracle version(s): 7.3 - 8.1.7.0
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
Back to index of questions
--------------------------------------------------------------------------------
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 pre-8.1) 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 the next one in the cycle, 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. (Update for 8.1 - because dirty blocks are now kept on checkpoint queues in the order of being dirtied, Oracle can release a log file for reuse much sooner than it used to)
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, or by examining the timestamps in v$log_history). Furthermore, due to a change int he way that the various checkpoint parameters and checkpointing mechanism works in 8.1, the impact of this problem is even smaller than it used to be.
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.
--------------------------------------------------------------------------------
There are a couple of variations on dropping log files. The example above works if you are not using Oracle's log file duplexing to keep two copies of each log; however if you are using duplexing you have to use slightly different commands; there are a couple of possibilites listed here:
-- See what you have at present
select group#, member from v$logfile;
3 C:\ORACLE\ORADATA\O8I\REDO02A.LOG
3 D:\ORACLE\ORADATA\O8I\REDO02B.LOG
4 C:\ORACLE\ORADATA\O8I\REDO01A.LOG
4 D:\ORACLE\ORADATA\O8I\REDO01B.LOG
-- add two new files
alter database add logfile (
'c:\oracle\oradata\o8i\redo03a.log',
'd:\oracle\oradata\o8i\redo03b.log'
) size 4M reuse
;
Database altered.
alter database add logfile (
'c:\oracle\oradata\o8i\redo04a.log',
'd:\oracle\oradata\o8i\redo04b.log'
) size 4M reuse
;
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
-- We get rid of the first group in one show (2a and 2b)
-- then one file (member) from the next group
-- then the second group by specifying the name of the one remaining file.
alter database drop logfile group 3;
Database altered.
alter database drop logfile member 'c:\oracle\oradata\o8i\redo01a.log';
Database altered.
alter database drop logfile 'd:\oracle\oradata\o8i\redo01b.log';
Database altered.
--------------------------------------------------------------------------------
Note particularly that it is easiest to drop duplexed log files by group#, but if you drop them by file name, you need to drop member to identify files, unless there is only one member left, in which the member keyword causes an error. As I said, there are several variations on syntax, so it is inevitable that every now and again you use the wrong syntax for the actual situation.
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'
or
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of thread 1
ORA-00312: online log 2 thread 1: 'C:\ORACLE\ORADATA\O8I\REDO03.LOG'
Messages like these are NOT fatal, just try again a little later: the first message will not recur if you do a couple of 'switch logfiles' calls and wait a minute or two, the second just requires you to wait a bit.
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.
Back to index of questions
|
