ORA-16014: no available destinations On Standby

ORA-16014: no available destinations On Standby

Very recently one of my colleague 😉 reached me with the below errors on the standby database. The environment is 3-node RAC Primary and 3-node RAC standby of 11gR2, Below errors are very generic, Moreover they are not going to harm any how, But the errors and warnings can generate so many tickets/incidents depending on the tracking tools. Then better to clean up when we have solution, isn’t it?

Now let me share what the errors have received in the standby alert log file

ORACLE Instance ckpt1 - Archival Error
ORA-16014: log 209 sequence# 233 not archived, no available destinations
ORA-00312: online log 209 thread 3: '+REDO01/ckpt/onlinelog/group_209.280.802956425'
ORA-00312: online log 209 thread 3: '+REDO02/ckpt/onlinelog/group_209.280.802956427'
Fri Mar 01 07:19:12 2013
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance ckpt1 - Archival Error

At first look, i thought it might be either 1) Archival mount point/Disk group is inaccessible 2) Mount Point/Disk group space is full. But When i see there is enough space on the disk group. Please note, this issue only on standby alert log file, Now requested to get the details of “Log_archive_dest_n” of standby of local destination how it is configured, Will see now what the results i received.

Prod-SYS@ckpt1 SQL> show parameter log_archive_dest_1
NAME                   TYPE           VALUE
--------------------   -------------- -------------------------------- -----------------------------------
log_archive_dest_1     string         LOCATION=+FRA/ckpt/ArchiveLogs/ valid_for=(online_logfiles,all_roles)

From the above results, Here archiving takes place only for the redo/archive data for the standby redo log files from the primary and there will be no Online Redo log files will be used, Of course attribute “online_logfiles” applicable if in case of any role transition(switchover/failover), So this found to be wrong entry and must change from (online_logifles) to (standby_logfiles)

Prod-SYS@ckpt1 SQL> alter system set log_archive_dest_1='LOCATION=+FRA/ckpt/ArchiveLogs valid_for=(standby_logfile, standby_role)';

(or) you can completely remove the “VALID_FOR” attribute from the “log_archive_dest_1” as below.

Prod-SYS@ckpt1 SQL> alter system set log_archive_dest_1='LOCATION=+FRA/ckpt/ArchiveLogs';
System altered.
Prod-SYS@ckpt1 SQL> show parameter log_archive_dest_1
NAME                 TYPE            VALUE
------------------   --------------- -------------------------------- ------------------------------
log_archive_dest_1   string          LOCATION=+FRA/ckpt/ArchiveLogs

After changing the attributes of “log_archive_dest_1” of standby, There are no more errors/warnings found in the alert log file, Note that if you haven’t mentioned any “VALID_FOR” attribute in destination, Then archiving online redo log files and standby redo log files is enabled at the destination even if it is either primary or standby role. This is an equivalent keyword as (ALL_LOGFILES, ALL_ROLES).

Fri Mar 01 09:10:09 2013
Archiver process freed from errors. No longer stopped
Fri Mar 01 09:10:09 2013
ALTER SYSTEM SET log_archive_dest_1='LOCATION=+FRA/ckpt/ArchiveLogs' SCOPE=BOTH;

Apart from the above issues, I like to add more[below] stuff from docs.oracle which attributes should we use depending on the database roles.

To configure these factors for each LOG_ARCHIVE_DEST_n destination, you specify this attribute with a pair of keywords: VALID_FOR=(redo_log_type,database_role):

    The redo_log_type keyword identifies the destination as valid for archiving one of the following:
        ONLINE_LOGFILE—This destination is valid only when archiving online redo log files.
        STANDBY_LOGFILE—This destination is valid only when archiving standby redo log files.
        ALL_LOGFILES— This destination is valid when archiving either online redo log files or standby redo log files.
    The database_role keyword identifies the role in which this destination is valid for archiving:
        PRIMARY_ROLE—This destination is valid only when the database is running in the primary role.
        STANDBY_ROLE—This destination is valid only when the database is running in the standby role.
        ALL_ROLES—This destination is valid when the database is running in either the primary or the standby role.
Table 14-2 VALID_FOR Attribute Values

— Happy Reading —