ORA-16014: log 2 sequence# 11 not archived, no available destinations

ALTER SYSTEM SWITCH LOGFILE hangs [Oracle 11gR2]

Today, while enabling my database in archivelog mode I faced ORA-16014 while switching logfile.

On digging further, I found out from my alert log file

ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance TRCDEV - Archival Error
ORA-16014: log 2 sequence# 11 not archived, no available destinations
ORA-00312: online log 2 thread 1: '/mnt/DEVdata/trcdev/redo02a.log'
ORA-00312: online log 2 thread 1: '/mnt/DEVdata/trcdev/redo02b.log'

If you read the alert log carefully it is evident that it is not able to archive the logs and the archival is hung

The possible reason could be my Archive destination got filled up, but in my case my archive destination has a free space of around 24G.

Then Try checking your spfile for the below archive log parameters and correct it if they are not pointing to the right destination where you want them to be.

log_archive_dest_1
 log_archive_format

In my case, both the parameter were already set.

But if you have to set these parameters manually use below commands:-

alter system set log_archive_dest_1='location=/mnt/DEVarchive/trcdev' scope=spfile;
alter system set log_archive_format='TRCDEV_%s_%t_%r.arc';

Bounce the DB once.

SQL> show parameter  log_archive_dest_1;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=/mnt/DEVarchive/trcdev
 

SQL> show parameter  log_archive_format
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      TRCDEV_%s_%t_%r.arc

 

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /mnt/DEVarchive/trcdev
Oldest online log sequence     9
Next log sequence to archive   11
Current log sequence           11

Now my database can archive the logs:

SQL>alter system archive log current;

session still hangs.

Hmm, I did a bit of diagnosis

SQL> select dest_name,status,error from v$archive_dest_status;

DEST_NAME         STATUS       ERROR
-------------------- --------------- --------------------
LOG_ARCHIVE_DEST_1   DEFERRED
LOG_ARCHIVE_DEST_2   DEFERRED
LOG_ARCHIVE_DEST_3   INACTIVE
LOG_ARCHIVE_DEST_4   INACTIVE

Perhaps it would be a good idea to enable the deferred destinations.

SQL> alter system set LOG_ARCHIVE_DEST_1=enable;
alter system set LOG_ARCHIVE_DEST_1=enable
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed

The correct command is:-
SQL> alter system set LOG_ARCHIVE_DEST_state_1=enable;
System altered.

SQL> select dest_name,status,error from v$archive_dest_status;

DEST_NAME

STATUS

ERROR

------------------------------ --------------- ---------------

LOG_ARCHIVE_DEST_1	VALID
LOG_ARCHIVE_DEST_2	DEFERRED

alter system switch logfile Session does not hang and we're good to go.

Checked the archive destination and all these archives were generated by switching logfile manually.

[oracle@abc.com]$ ls -lrt
total 247700
-rw-r----- 1 oraDEV dba 242587136 Sep  8 04:40 TRCDEV_11_1_824143175.arc
-rw-r----- 1 oraDEV dba      1024 Sep  8 04:40 TRCDEV_12_1_824143175.arc
-rw-r----- 1 oraDEV dba  11047424 Sep  8 04:40 TRCDEV_13_1_824143175.arc