Recently I was asked to check an issue which one of my client faced while connecting to a remote database. I will give a brief idea on the process that is followed and why for what reason we faced this issue and how was it resolved. Scenario is quiet simple. Oracle 9.2.0.8, Solaris 5.10. Its a report database which is daily restored from DR backup.

Checked alert log, didn’t find any error, but got some warning kind of message stating, “ORACLE Instance ORCL – Can not allocate log, archival required”. I asked the team about the process. Got some idea. With the details that I received, tried to identified few things.

alert log,

ORACLE Instance ORCL - Can not allocate log, archival required
Thu Dec 22 16:24:00 2011
ARCH: Connecting to console port...
Thread 1 cannot allocate new log, sequence 9
All online logs needed archiving
Current log# 1 seq# 8 mem# 0: /oradata/redo_01.log
Thu Dec 22 18:07:53 2011

1. Checked archive status,

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Disabled
Archive destination            /oracle/app/oracle/product/9.2/dbs
Oldest online log sequence     4
Next log sequence to archive   9
Current log sequence           9
SQL>

The report database is in no archive mode as per information what I reviewed. But with the above status found out something else. The Automatic archival is Disabled, but Database log mode is in Archive Mode.
Got some idea, then checked

2. log_archive_start status

SQL> sho parameter start

NAME                                 TYPE              VALUE
------------------------------------ ----------------- ------------------------------
log_archive_start                    boolean           FALSE
SQL>

Database is still in archive mode, log_archive_start is set to false. In short, the database was not brought into noarchive mode at mount stage. I was quite sure what will happen if I try to switch redo, but wanted to try. The output was what I expected. The switch command got hanged,

SQL> alter system switch logfile;

{hanged but still no error in alert log}.

There were no free online redo left.

With the help of MOS article, I was able to resolve the issue.
ARCHIVE PROCESS ERROR: CANNOT ALLOCATE LOG, ARCHIVAL REQUIRED. [ID 1030505.6]

SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

The above SQL statement force Oracle to archive all noncurrent redo logs. This statement is used to manually archive all online redo log file groups that are full but have not been archived. As soon as the statement was fired the redo started switching.

alert log,

ARCH: Evaluating archive   log 2 thread 1 sequence 3
ARCH: Beginning to archive log 2 thread 1 sequence 3
Creating archive destination LOG_ARCHIVE_DEST_1: '/archive/1_3.dbf'
ARCH: Completed archiving  log 2 thread 1 sequence 3
ARCH: Evaluating archive   log 3 thread 1 sequence 4
ARCH: Beginning to archive log 3 thread 1 sequence 4
Creating archive destination LOG_ARCHIVE_DEST_1: '/archive/1_4.dbf'
Thu Dec 22 18:12:42 2011
Thread 1 advanced to log sequence 9
Current log# 2 seq# 9 mem# 0: /s02/oradata/redo_02.log
Thu Dec 22 18:12:45 2011
ARCH: Completed archiving  log 3 thread 1 sequence 4
ARCH: Evaluating archive   log 4 thread 1 sequence 5
ARCH: Beginning to archive log 4 thread 1 sequence 5
Creating archive destination LOG_ARCHIVE_DEST_1: '/archive/1_5.dbf'
ARCH: Completed archiving  log 4 thread 1 sequence 5
ARCH: Evaluating archive   log 6 thread 1 sequence 6
ARCH: Beginning to archive log 6 thread 1 sequence 6
Creating archive destination LOG_ARCHIVE_DEST_1: '/archive/1_6.dbf'
ARCH: Completed archiving  log 6 thread 1 sequence 6
ARCH: Evaluating archive   log 5 thread 1 sequence 7
ARCH: Beginning to archive log 5 thread 1 sequence 7
Creating archive destination LOG_ARCHIVE_DEST_1: '/archive/1_7.dbf'
ARCH: Completed archiving  log 5 thread 1 sequence 7
ARCH: Evaluating archive   log 1 thread 1 sequence 8
ARCH: Beginning to archive log 1 thread 1 sequence 8
Creating archive destination LOG_ARCHIVE_DEST_1: '/archive/1_8.dbf'
ARCH: Completed archiving  log 1 thread 1 sequence 8

Wanted to check explicitly whether DB has come to normal state.

SQL> alter system switch logfile;

System altered.

SQL>

Great its smooth now. alert log,

Thu Dec 22 18:37:25 2011
Thread 1 advanced to log sequence 10
Current log# 3 seq# 10 mem# 0: /oradata/redo_03.log
About these ads