Tuesday, February 10, 2015

Specifying Alternate Archive Destination


My implementation is on Oracle database 9i.

Check for the current archive log destination.
SQL> show parameter log_archive_dest_1

NAME                                 TYPE       VALUE
-------------------------------- ---------- ------------------------------
log_archive_dest_1              string     LOCATION=/jli6/arch


Check for the current archive log destination status.
 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_1='LOCATION=/jli7/arch MANDATORY NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/jli6/arch MANDATORY' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ALTERNATE SCOPE=BOTH;






SQL> ALTER SYSTEM SWITCH LOGFILE;

apcrss32:/jli7/arch [LIPLPDB] $ ls
1_72912.dbf


If failover to alternate happen, we might need to manual enable the original archive destination status.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ALTERNATE SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/jli7/arch NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2' SCOPE=BOTH;


Status for log destinations
i.   ENABLE indicates that the database can use the destination. 
ii.  DEFER indicates that the location is temporarily disabled. 
iii. ALTERNATE indicates that the destination is an alternate. The availability state of an alternate destination is DEFER. If its parent destination fails, the availability state of the alternate becomes ENABLE. 
* ALTERNATE cannot be specified for destinations LOG_ARCHIVE_DEST_11 to LOG_ARCHIVE_DEST_31.

Extra information (Oracle Database 11gR2)
Archive to only a single destination, refer initialization parameter
LOG_ARCHIVE_DEST
 

Archive to multiple destinations, refer initialization parameter 
LOG_ARCHIVE_DEST_n
 

Archive only to a primary and secondary destination, refer initialization parameter 
LOG_ARCHIVE_DEST
LOG_ARCHIVE_DUPLEX_DEST


References
http://docs.oracle.com/cd/B10501_01/server.920/a96653/log_arch_dest_param.htm#77201
https://docs.oracle.com/cd/E18283_01/server.112/e17120/archredo004.htm#

No comments:

Post a Comment