Mir Sayeed Hassan – Oracle Blog

How to set the alternative archive destination in Oracle database

In case of the archivelog destination space is filled/exausted., the database will be hang until the space release from server.

Error log:

0RA-00257: Archiver error, connect internal only until freed
ORA-16014: Log 1 sequence# 3981 not archived, no available destinations

Solution:

To overcome this issue you need to set the alternate archivelog destination as shown below.

This alternate destination will be used when the 1st archivelog destination is filled or when the transmission of an online redo log from the primary site to the standby site fails.

Check the default recovery area set in DB

SQL> show parameter recovery

NAME                            TYPE              VALUE
----------------------------------------------------------
db_recovery_file_dest          string            +ORADATA
db_recovery_file_dest_size     big integer        800G

Check the 1st archivelog set in DB.

SQL> show parameter log_archive_dest_1

NAME                   TYPE                                     VALUE
------------------------------------------------------------------------------------------------------
log_archive_dest_1    string    LOCATION=+ORADATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=swstdsem

Check the current status

SQL> show parameter log_archive_dest_state_1

NAME                         TYPE               VALUE
--------------------------------------------------------
log_archive_dest_state_1    string              enable

Now assign the 2nd Alternate archivelog destination as shown below.

SQL> alter system set log_archive_dest_3='LOCATION=/u01/archivelog/' scope=both;
System altered.

SQL> alter system set log_archive_dest_state_3='ALTERNATE' scope=both;
System altered.

Verify the above set parameters

SQL> show parameter log_archive_dest_3

NAME                      TYPE                       VALUE
-----------------------------------------------------------------------
log_archive_dest_3      string          LOCATION=/backup14/archivelog/

SQL> show parameter log_archive_dest_state_3

NAME                         TYPE                   VALUE
------------------------------------------------------------------------
log_archive_dest_state_3    string                ALTERNATE

Configure the ALTERNATE Destination

SQL> alter system set log_archive_dest_1='LOCATION=+ORADATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=swstdsem reopen=90 max_failure=5 alternate=log_archive_dest_3' scope=both;
System altered.

SQL> show parameter log_archive_dest_1

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=+ORADATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=swstdsem reopen=90 max_failure=5 alternate=log_archive_dest_3

Note:

Here we have used the REOPEN Option as it should be 0 or NOREOPEN then it will switch to ALTERNATE destination.
In case if REOPEN is non zero value then you specify the MAX_FAILURE is set therefore if it threshold reached then it will switch ALTERNATE 2nd destination.

=====Hence tested & Verified in our test env=====