This remote location is known as an Archival Standby Database and is nothing more than an Oracle instance with a controlfile and no physical database associated with it. The controlfile can be a standby controlfile of the source database or a controlfile of another database.

This article describes the steps necessary to configure a database to archive its redo log files to a remote archival standby database.

Primary Database
Oracle Release:	Oracle9i Release 2 - (9.2.0.7.0)
Machine Name:	vmlinux3.idevelopment.info
Operating System:	Red Hat Linux 4 - (CentOS 4.2)
Oracle SID:	TESTDB
DB_NAME:	TESTDB
Archival Standby Database
Oracle Release:	Oracle9i Release 2 - (9.2.0.7.0)
Machine Name:	vmlinux4.idevelopment.info
Operating System:	Red Hat Linux 4 - (CentOS 4.2)
Oracle SID:	ARCHSTDBY
Instance Service Names:	ARCHSTDBY.IDEVELOPMENT.INFO
TNS Service Name:	ARCHSTDBY_VMLINUX4.IDEVELOPMENT.INFO

Create standby controlfile from primary database

The first step is to create a standby controlfile from the primary database (the database that will be archiving its redo data to the remote node). In this example, that would be TESTDB running on host vmlinux3:

SQL> alter database create standby controlfile as '/u01/app/oracle/control01.ctl';

Database altered.

Copy standby controlfile to remote database server

Next, transfer the controlfile from the primary database server (vmlinux3) to the remote server (vmlinux4):

$ scp /u01/app/oracle/control01.ctl oracle@vmlinux4:/u02/oradata/ARCHSTDBY
oracle@vmlinux4's password:
control01.ctl      ***************************   100% 7720KB   7.5MB/s   00:01

Create an Oracle instance on remote server

Now, create an Oracle instance on the remote server (vmlinux4) that will be used as the repository for the archive redo logs. For the purpose of this example, I will be creating an Oracle instance named ARCHSTDBY on the host vmlinux4.

1. From vmlinux4, create an instance parameter file for the ARCHSTDBY Oracle instance with the following entries:

   $ORACLE_HOME/dbs/initARCHSTDBY.ora

   audit_file_dest = /u01/app/oracle/admin/ARCHSTDBY/adump background_dump_dest = /u01/app/oracle/admin/ARCHSTDBY/bdump core_dump_dest = /u01/app/oracle/admin/ARCHSTDBY/cdump user_dump_dest = /u01/app/oracle/admin/ARCHSTDBY/udump control_files = /u02/oradata/ARCHSTDBY/control01.ctl db_block_size = 8192 db_name = TESTDB remote_login_passwordfile = EXCLUSIVE service_names = ARCHSTDBY.IDEVELOPMENT.INFO standby_archive_dest = /u02/oraarchive/ARCHSTDBY

   Note that db_block_size and db_name must match to that of the primary database which you created the controlfile from. The archived redo logs will be placed in the directory specified by the standby_archive_dest parameter.

2. Create a password file:

   $ ORACLE_SID=ARCHSTDBY; export ORACLE_SID
   $ orapwd file=$ORACLE_HOME/dbs/orapwARCHSTDBY password=change_on_install

3. Mount the new archival standby database:

   $ ORACLE_SID=ARCHSTDBY; export ORACLE_SID
   $ sqlplus "/ as sysdba"
   
   SQL*Plus: Release 9.2.0.7.0 - Production on Wed Jul 26 21:39:15 2006
   
   Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
   
   Connected to an idle instance.
   
   SQL> startup nomount
   ORACLE instance started.
   
   Total System Global Area   97588468 bytes
   Fixed Size                   451828 bytes
   Variable Size              46137344 bytes
   Database Buffers           50331648 bytes
   Redo Buffers                 667648 bytes
   
   SQL> alter database mount standby database;
   
   Database altered.

4. Register the new instance with the local listener:

   $ORACLE_HOME/network/admin/listener.ora

   LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = vmlinux4.idevelopment.info) (PORT = 1521) ) ) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/9.2.0) (PROGRAM = extproc) (ENVS="EXTPROC_DLLS=ANY") ) (SID_DESC = (GLOBAL_DBNAME = TESTDB.IDEVELOPMENT.INFO) (SID_NAME = ARCHSTDBY) (ORACLE_HOME = /u01/app/oracle/product/9.2.0) ) )

5. Start (or restart) the listener process:

   $ lsnrctl start

Configure primary database to start archiving to archival standby database

The last step is to configure the initialization parameters on the primary database (TESTDB on vmmlinux3) to starting archiving its redo log files to the new archival standby database:

1. Create a TNS Service Name in the tnsnames.ora for the primary database to communicate with the new archival standby database:

   $ORACLE_HOME/network/admin/tnsnames.ora

   ARCHSTDBY_VMLINUX4.IDEVELOPMENT.INFO = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = vmlinux4.idevelopment.info) (PORT = 1521) ) ) (CONNECT_DATA = (SERVICE_NAME = ARCHSTDBY.IDEVELOPMENT.INFO) ) )

2. Set the following initialization parameters on the primary database TESTDB:

   SQL> alter system set log_archive_dest_3='service=archstdby_vmlinux4.idevelopment.info noregister';
   SQL> alter system set log_archive_dest_state_3=enable;

   The following is a list of other optional parameters that can be used with LOG_ARCHIVE_DEST_n OPTIONAL/MANDATORY DELAY/NODELAY REOPEN/NOREOPEN ACTIVE_ONLINE/CURRENT_ONLINE ALTERNATE/NOALTERNATE MAXFAILURE/NOMAXFAILURE QUOTA_SIZE/NOQUOTA_SIZE QUOTA_USED/NOQUOTA_USED

3. Perform a log switch on the primary database TESTDB:

   SQL> alter system archive log current;

   The archived redo logs will be placed in the directory specified by the standby_archive_dest defined on the archival standby database!

purge_archived_redo_logs.ksh

#!/bin/ksh echo Purging archived redo logs older than 7 days... find /u02/oraarchive/ARCHSTDBY -type f -mtime +7 -name \*.dbf -print -exec rm {} \;