Troubleshoot "Can not allocate log, archival required" in alert log

-- Troubleshoot "Can not allocate log, archival required"
-- In Oracle 9i/10g/11g
-- Found from alert log

< ORACLE Instance PROD2 - Can not allocate log, archival required
< Thread 2 cannot allocate new log, sequence 12265
< All online logs needed archiving

-- Cause:
It seems that all the online redo logs are full and you need to empty them. This can happeen due to two reasons
1) archivelog destination is full
2)archiving is disabled although the database is in archive log mode

-- Diagnosis:


SQL> show parameter log_archive_max_process;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes            integer     4


SQL> show parameter LOG_ARCHIVE_START;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_start                    boolean     FALSE


-- solution:

If redo log size is too small to handle current redo log switch frequency then bigger redo log size will work in this case. Increase redo log size to 500M.

OTHERWISE

If automatic archival is disabled, run the following command

ALTER SYSTEM ARCHIVE LOG ALL;

it is better if you set as well LOG_ARCHIVE_START parameter into init.ora

LOG_ARCHIVE_START=TRUE

Consider increasing either or both of the initialisation parameters LOG_ARCHIVE_BUFFER_SIZE and LOG_ARCHIVE_BUFFERS.