How to Use ALTERNATE archive destination

How to Use ALTERNATE archive destination

We can define an archive destination with value ‘ALTERNATE’, which will take over if primary destination is full. As per Oracle documentation “An archiving destination can have a maximum of one alternate destination specified. An alternate destination is used when the transmission of an online redo log from the primary site to the standby site fails”. In fact this is the concept of data guard, but can also be applied on standalone system.

SQL> show parameter recovery

NAME TYPE VALUE

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

db_recovery_file_dest string +fradg

SQL> show parameter LOG_ARCHIVE_DEST_1

NAME TYPE VALUE

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

log_archive_dest_1 string location=use_db_recovery_file_dest

SQL> show parameter log_archive_dest_state_1

NAME TYPE VALUE

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

log_archive_dest_state_1 string enable

SQL> alter system set log_archive_dest_3=

'location=+testarch' scope=both;

SQL> alter system set log_archive_dest_state_3=

'ALTERNATE' scope=both;

Now change the primary location to reflect ‘ALTERNATE’ setting:

SQL> alter system set log_archive_dest_1=

'location=use_db_recovery_file_dest

noreopen alternate=log_archive_dest_3' scope=both;

Here we have to add ‘NOREOPEN’; otherwise it will not spill over to ‘ALTERNATE’ location. As per Oracle documentation – If archiving fails and the REOPEN attribute is specified with a value of zero (0), or NOREOPEN is specified, the Oracle database server attempts to archive online redo logs to the alternate destination on the next archival operation.
When archive logs are written to primary location

SQL>select dest_id, dest_name, status from

v$archive_dest_status where status <> 'INACTIVE';

DEST_ID DEST_NAME STATUS

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

 1 LOG_ARCHIVE_DEST_1 VALID

 2 LOG_ARCHIVE_DEST_3 UNKNOWN

When Primary location is full and archiver cannot write to it, first time it will throw following error stack but archiving request will to ‘ALTERNATE’ location. At this point of time LOG_ARCHIVE_DEST_1 will be ‘DISABLED’.

alter system archive log current

ERROR at line 1:

ORA-16038: log 2 sequence# 324 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 2 thread 1:

'+DG1/primary/onlinelog/group_2.384.684585247'

ORA-00312: online log 2 thread 1:

'+RECODG/primary/onlinelog/ group_2.384.684585247'

SQL>select dest_id, dest_name, status from

v$archive_dest_status where status <> 'INACTIVE';

 DEST_ID DEST_NAME STATUS

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

 1 LOG_ARCHIVE_DEST_1 DISABLED

 3 LOG_ARCHIVE_DEST_3 VALID

Once the space issue is resolved & we are ready to fallback to PRIMARY location

SQL> alter system set log_archive_dest_state_1=enable;

System altered.

SQL> alter system set log_archive_dest_state_3=alternate;

System altered.

SQL>select dest_id, dest_name, status from

v$archive_dest_status where status <> 'INACTIVE';

DEST_ID DEST_NAME STATUS

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

 1 LOG_ARCHIVE_DEST_1 VALID

 2 LOG_ARCHIVE_DEST_3 UNKNOWN