ORA-16014: Log %s sequence# %s not archived, no available destinations

I got ” ORA-16014: Log %s sequence# %s not archived, no available destinations ”  error in Oracle database.

 

ORA-16014: Log %s sequence# %s not archived, no available destinations

 

Details of error are as follows.

ORA-16014: Log %s sequence# %s not archived, no available destinations

Cause: An attempt was made to archive the named log, but the archive was unsuccessful. 
The archive failed because there were no archive log destinations specified or all 
destinations experienced debilitating errors.

Action: Verify that archive log destinations are being specified and/or take the necessary
 step to correct any errors that may have ocurred.

 

 

Log %s sequence# %s not archived, no available destinations

This ORA-16014 errors are related with the attempt was made to archive the named log, but the archive was unsuccessful.

The archive failed because there were no archive log destinations specified or all destinations experienced debilitating errors.

 

To solve this error, Verify that archive log destinations are being specified and/or take the necessary step to correct any errors that may have occurred.

 

Two local archival destinations were defined on the standby to handle archival of standby redo logs but one was incorrect:

LOG_ARCHIVE_DEST_3 = 'location=+<diskgroup3>/<standby_db_name>/standbyarchive valid_for=(standby_logfile, standby_role)'

LOG_ARCHIVE_DEST_4 = 'location=+<diskgroup3>/<standby_db_name>/standbyarchive valid_for=(standby_logfile, standby_role)'

 

 

The diskgroup <diskgroup3> defined in LOG_ARCHIVE_DEST_3 didn’t actually exist on the ASM instance.   This resulted in an ‘earlier’ error when archival was attempted on the physical standby:

Errors in file /<oracle_home>/diag/rdbms/<standby_db_name>/<oracle_sid>/trace/<oracle_sid>_arc8_17891.trc:
ORA-19504: failed to create file "+<diskgroup3>/<standby_db_name>/standbyarchive/4_15236_729104864.dbf"
ORA-17502: ksfdcre:3 Failed to create file +<diskgroup3>/<standby_db_name>/standbyarchive/4_15236_729104864.dbf
ORA-15173: entry '4_15236_729104864.dbf' does not exist in directory 'standbyarchive'
ORA-15001: diskgroup "+<diskgroup3>" does not exist or is not mounted                  <<<<
ORA-15001: diskgroup "+<diskgroup3>" does not exist or is not mounted                  <<<<

After this, no process can write to either local archival destination due to the following bug:

Bug 12565162 DESTROYING ASM DISK GROUP STOPS TO ARCHIVE TO IRRELEVANT ANOTHER DESTINATION

Fixed 11.2.0.3 Patchset and future 12g release

REDISCOVERY INFORMATION:
If ASM destination 1 directory dies, database will not able  to create archivelog file on ASM destination 2 directory.

 

 

To solve this error, Defer the incorrect destination.

For example:

SQL> alter system set LOG_ARCHIVE_DEST_STATE_3=DEFER;

To ensure the errors for local archival clear up, bounce the standby instance if possible. Otherwise it may be several minutes for the problem to clear on it’s own.

To avoid the problem in the future upgrade to 11.2.0.3 or apply an interim patch for BUG 12565162.

 

Sometimes If Mount point/Disk group is full, inaccessible or insufficient privilige, you will get this error.,

You can change the log_archive_dest_1 if the disk group or mount point, then change it as follows.

SQL> alter system set log_archive_dest_1='location=+DATAC1';
SQL> alter system switch logfile;

 

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP

Sometimes you can clear unarchived logfile related group as follows, then you can open database again.

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

ALTER DATABASE OPEN;

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

See more