Don't panic!

  

Cry about...
Oracle Troubleshooting

ORACLE Instance NNNN - Cannot allocate log. Archival required

Symptom:

On the database server console, the message is displayed:

ORACLE Instance NNNN - Cannot allocate log. Archival required
Press <ENTER> to acknowledge message

Cause:

This error means that Oracle wanted to perform an archive log switch, but was unable to because the next online redo log has not yet been archived. The database will continue to function (i.e. no data will be lost), but all transactions will be blocked (i.e. the database may appear to hang) until the current archive log is written out and a redo log can be allocated.

Possible Causes and Remedies:

  • Automatic archiving has been disabled.

    In this case the database will also appear to hang. This can be verified by issuing the command:

    Archive log list;

    If this displays ‘Automatic archival Disabled’ then automatic archiving has been disabled. Archive the redo logs manually by issuing the command:

    Alter system archive log all;

    A better long term solution is to modify the init<SID>.ora file and set the parameter ‘LOG_ARCHIVE_START’ to ‘True’.

  • The archive log space is exhausted. Check that there is sufficient disk space in the directory receiving the archive logs.
  • There is very heavy database activity and this activity means that the database is generating redo logs faster than it can archive them. Database activity will still continue - once the archive process ‘catches up’.

    If this is the case then the following may help:

    • Ensure that redo logs and archive logs are being written to different disks.
    • Add extra redo log groups.

      This can be done without taking the database down, for example:

      alter database add logfile group 3 'C:\ORANT\DATABASE\LOG3ORCL_1.ORA' SIZE 1M;

    • Increase the size of the redo logs. Whilst Oracle does not provide a way of resizing the redo logs the same effect can be achieved by deleting a redo log file and then adding it again with the new size, for example:

      alter database drop logfile 'C:\ORANT\DATABASE\LOG3ORCL1.ORA';
      alter database add logfile 'C:\ORANT\DATABASE\LOG3ORCL1.ORA' SIZE 2M;

      Do ensure that there are always at least two available redo logs.

    • Consider increasing either or both of the initialisation parameters LOG_ARCHIVE_BUFFER_SIZE and LOG_ARCHIVE_BUFFERS.
    • If this error occurs during one-off loading of data into the database then either ignore the error or consider running the database in non-archive-log mode for the data loading.

These notes are believed to be correct for Oracle 8.0.5 and Oracle 8.1.5 and may also apply to other versions of Oracle.

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.