ORACLE Instance ORCL - Can not allocate log, archival required
Thread 1 cannot allocate new log, sequence xxx
Checkpoint not complete
These messages are written to alert.log when the database wants to reuse a redo log file but it can not do. This is related with
1- ) Not finished checkpointing (DBWR) or
2- ) ARCH process can not copy the redo log file to the archive destination.
Oracle wants to reuse log file but current checkpoint is in that log file. The database halts until checkpoint completion or archiving activity finishes. So Oracle waits until reusing redo log file safely.
As you see one reason is slow DBWR. OK, how can we make faster DBWR?
- You can use multiple DBWR processes
- Enable ASYNC I/O : For RHEL , you can verify if relink libaio with your Oracle binary using ldd and nm commands. Verifying Asynchronous I/O Usage.
- You can use DBWR slaves if ASYNC I/O is not supported.
Adding more redo log files can also solve this issue. This operation may create more room for DBWR as you guess.
Increasing size of redo log files can also help you. Like adding more redo log files, increasing size of them give you more time to reuse redo log file.