Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 11 August 2014

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

1 comments:

  1. BlueHost is definitely one of the best hosting provider with plans for all of your hosting requirements.

    ReplyDelete