Using ALTERNATE archive destination to handle archive overflow

Scenario – Define a secondary archive location, which will be used when primary destination is full

Solution – 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.

This is in context of data guard, but also applies to a standalone database.

— FRA is defined as following

SQL> show parameter recovery
NAME TYPE VALUE
------------------------- ----------- ------
db_recovery_file_dest string +fradg

— Primary archive location is defined as

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

— How do we define the alternate location

Let’s say we want to use log_archive_dest_3 as alternate location.

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

alter system archive log current
*
ERROR at line 1:
ORA-16038: log 2 sequence# 194 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: 
'+DG1/primary/onlinelog/group_2.274.789415247'
ORA-00312: online log 2 thread 1: 
'+RECODG/primary/onlinelog/group_2.332.789415247'

But second archiving request will write to ‘ALTERNATE’ location. At this point of time LOG_ARCHIVE_DEST_1 will be ‘DISABLED’

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

Metalink Doc Ref –

NOTE 270069.1 – How to Automate Archive Log Overflow Using ‘Alternate’
NOTE 369120.1 – ALTERNATE Attribute of LOG_ARCHIVE_DEST_n Does Not Appear to Work

Advertisements
This entry was posted in Oracle Availability and tagged . Bookmark the permalink.

3 Responses to Using ALTERNATE archive destination to handle archive overflow

  1. Vpix says:

    Thanks ! This was useful !

  2. Sabina says:

    Thank you so much. Very helpful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s