ORA-16014: log # sequence# ### not archived, no available destinations (Standby Database)

Problem:

Following error in standby alert log:

ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance STDB4_DG - Archival Error
ORA-16014: log 7 sequence# 234 not archived, no available destinations

Solution:

For such error first we will check the following things:

1.      Archival mount point/Disk group is inaccessible or

2.      Mount point/Disk group space is full

In my case neither of above was the cause. In my standby database following destination was set:

 

Parameter

Value

log_archive_dest_1                  

LOCATION=/holding/stdb4_dg/archive_logs valid_for=(online_logfile, all_roles)

log_archive_dest_3                  

service="stdb3_dg", LGWR ASYNCNOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 net_timeout=30 db_unique_name="stdb3_dg", valid_for=(all_logfiles,primary_role)

 

So the problem was there was not archive log destination set for Physical standby server. After setup the archive destination for standby role the problem fixed.

 

alter system set log_archive_dest_2='LOCATION=/holding/stdb4_dg/standby_logs valid_for=(standby_logfile, standby_role)' scope=both;

 

VALID_FOR Definition

Primary Role

Physical Standby Role

Logical Standby Role

ONLINE_LOGFILE, PRIMARY_ROLE

Active

Inactive

Invalid

ONLINE_LOGFILE, STANDBY_ROLE

Inactive

Invalid

Active

ONLINE_LOGFILE, ALL_ROLES

Active

Invalid

Active

STANDBY_LOGFILE, PRIMARY_ROLE

Error

Error

Error

STANDBY_LOGFILE, STANDBY_ROLE

Invalid

Active

Active

STANDBY_LOGFILE ALL_ROLES

Invalid

Active

Active

ALL_LOGFILES, PRIMARY_ROLE

Active

Inactive

Invalid

ALL_LOGFILES, STANDBY_ROLE

Invalid

Active

Active

ALL_LOGFILES, ALL_ROLES

Active

Active

Active

 

Comments