Error: Checkpoint not complete, Cannot Allocate New Log Warning
Thread 1 cannot allocate new log, sequence 125487
Checkpoint not complete
Solution: Checkpoint not complete messages are generated due to the logs are switching so fast that the checkpoint associated with the log switch isn’t complete. You should increase redo log file size and amount to resolve. Also, If you use archive_lag_target parameter as near zero for example like 1 or 2 minutes, you should change this parameter zero (no lag) or more than 10-15 min. Oracle recommends that redo log switch operation interval should be between 15-30 minutes.
Show and change archive_lag_target parameter
SQL> show parameter archive_lag_target;
SQL> alter system set archive_lag_target=0 scope=both;
or
SQL> alter system set archive_lag_target=1800 scope=both;
1800 is 15 minutes. Parameter value as second.
If your archive_lag_target parameter is normal and you get checkpoint not complete error, you have to look your redo log file size and amount.
Show Redo Log Files
SQL> select * from v$log;
SQL> select * from v$logfile;
If all redo log files status are ACTIVE and CURRENT, you may get checkpoint not complete warning and that is normal. You should see redo log files status as ACTIVE, CURRENT and INACTIVE.
Redo Log File Status Descriptions
UNUSED – Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
CURRENT – Current redo log. This implies that the redo log is active. The redo log could be open or closed.
ACTIVE – Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
CLEARING – Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
CLEARING_CURRENT – Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
INACTIVE – Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
If your redo log file size is small or redo log file amount is too few for example two, you have to add new redo log file and increase size.
Add New Redo Log File
SQL> alter database add logfile size 200M;
You can drop small sized redo log files with group number while status is INACTIVE. You can check status with v$log;
Drop Redo Log File
SQL> alter database drop logfile group 3;
You have to check switch time of redo log files after add new redo logs. If switch interval is 15-30 minutes and do not see checkpoint not complete warning, you database redo log size and amount is suitable. You can set archive_lag_target to 15 minutes to force create archive log that is important for recovery operation. If you lose your redo logs, you have to use archived logs. Interval of create archive logs is important for RPO (Recover Point Objective) value.