Archiving not possible: No primary destinations

Error Description:
Today I got a very interesting problem. The developer told me that they can't connect to database. It says ORA-00257: archiver error. Then I log on to the database and it was ok. I asked developer it is fine. After some time developers again asked me they are having the same problem. I then look for alert log and saw the following stack of message.

ARC0: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 1192 (4)

Cause of The Problem:
Unknown yet. Possibly hit oracle bug.

Solution of The Problem:
At first seems I guessed there is space issue and look for following thing as in described,
ORA-00257: archiver error. Connect internal only, until freed.

Step1: Look for archival destination.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1203
Next log sequence to archive 1205
Current log sequence 1205
SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata1
db_recovery_file_dest_size big integer 50G

I also checked for settings whether LOG_ARCHIVE_DEST_10 is implicitly set or not. Yet it was set.
SQL> select DEST_NAME,DESTINATION from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_10';

DEST_NAME
--------------------------------------------------------------------------------
DESTINATION
--------------------------------------------------------------------------------
LOG_ARCHIVE_DEST_10
USE_DB_RECOVERY_FILE_DEST

Step2: Let's check space in flash recovery area.
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/oradata1
5.3687E+10 32381952 0 2

So it was fine. I suddenly look for alert log and it was changed to

ORA-16038: log 2 sequence# 1160 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 2 thread 1: '/oradata1/arju/ARJU/redo02.log'

I immediately try to clear the logfile as it is described in ORA-16038,ORA-00354,ORA-00312 corrupt redo log block header
SQL> alter database clear unarchived logfile group 2;
Database altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
^C alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

But no hope. It stopped and in alert log again it shows error message Archiving not possible: No primary destinations.

Step3: I set LOG_ARCHIVE_DEST_9 explicitly to DB_RECOVERY_FILE_DEST

With the following statement whenever I explicitly set LOG_ARCHIVE_DEST_9 to use DB_RECOVERY_FILE_DEST then the error gone.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_9='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.

In the alert log I put tail -f and wanted to see that status. Immediately status becomes as follows,

Cleared LOG_ARCHIVE_DEST_10 parameter default value
Mon Jul 21 05:52:04 2008
ALTER SYSTEM SET log_archive_dest_9='LOCATION=USE_DB_RECOVERY_FILE_DEST' SCOPE=BOTH;
Mon Jul 21 05:52:04 2008
Archiver process freed from errors. No longer stopped

And error gone. I tested with alter system switch logfile and it went normal.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

I don't know what happened. Possibly this is oracle bug. Later I explicitly set log_archive_dest_9 to use LOCATION USE_DB_RECOVERY_FILE_DEST and unset log_archive_dest_9 and it went normal.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_9='';
System altered.

Related Documents

ORA-16038,ORA-00354,ORA-00312 corrupt redo log block header
ORA-00257: archiver error. Connect internal only, until freed.