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
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
Comments
Post a Comment