Subject:  Configuring Managed Recovery for Standby Database 
Creation Date:  21-JAN-2000 

PURPOSE
-------

This note shows how to place a standby database on the same machine as the 
primary database in managed recovery mode.  It also details opening the standby
database in read-only mode so that it can be used as a reporting database.

When the database is in managed recovery mode, archivelogs that are generated 
by the primary node are automatically applied to the standby node.  The logs 
can be copied manually using OS utilities and applied using the  
'recover standby database' command, but this automates the process.

This note assumes that the standby database is a minimum of Oracle 8.1.5 and has 
already been setup as per [NOTE:70233.1].


STEPS FOR PLACING A DATABASE IN MANAGED RECOVERY MODE
-----------------------------------------------------

This example assumes a primary database with an ORACLE_SID of V815 and a 
standby database on the same node with an ORACLE_SID called STBY.

Relevant "init.ora" parameters from "initSTBY.ora" are:

control_files = ("/oracle/standby/dbs/control01.ctl")
lock_name_space = V815STBY
standby_archive_dest = /oracle/standby/arch
log_archive_start=true
log_archive_dest = /oracle/standby/arch
log_archive_format = %t_%s.arc
db_file_name_convert="/oracle/OFA_base/app/oracle/product/8.1.5/oradata/V815"
, "/oracle/standby/dbs"
log_file_name_convert="/oracle/OFA_base/app/oracle/product/8.1.5/oradata/V815", 
"/oracle/standby/dbs"

CONTROL_FILES should be set to the location(s) where the standby database 
controlfiles will be placed

LOCK_NAME_SPACE should be set to something other than the db_name for the 
Standby Database's "init.ora".  This will allow us to have two databases on the 
same machine with the same db_name setting.  Without this setting, 
"ORA-1102: cannot mount database in exclusive mode" 
will be signalled when the second database is mounted.

STANDBY_ARCHIVE_DEST is used when logs are transmitted automatically from the 
primary database.  We use this parameter to determine where these logs should 
be placed.

LOG_ARCHIVE_DEST is the location where we look for logfiles by default when 
recovering the standby database.  It is recommended to set the values of 
STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_DEST to the same values in most cases.

DB_FILE_NAME_CONVERT is used to convert the filename of a datafile on the 
primary instance to the filename of the datafile on the standby instance.

LOG_FILE_NAME_CONVERT is used to convert the filename of a logfile on the 
primary instance to the filename of the logfile on the standby instance.

If the standby database is created and succesfully mounted as a standby 
database, then we can go about setting up managed recovery.

Archival of the redo logs generated by the primay node to a remote destination 
is setup as follows :

Edit "initV815.ora":

log_archive_dest_1 = "location=/oracle/.../admin/V815/arch"
log_archive_dest_2 = "service=STANDBY OPTIONAL REOPEN=30"


This says that we should archive to the service called STANDBY.  The 
destinations can either be OPTIONAL or MANDATORY.  When testing this it is 
probably best to set this to OPTIONAL as this will stop the primary instance 
from hanging if archival does not work.

The REOPEN means that we will retry after 30 seconds if an error occurs.

The service that we specify is taken from the "TNSNAMES.ORA" on the primary 
node.

This example uses an IPC entry as the standby database is on the same machine.
Another protocol (such as TCP) will have to be used if archiving to a remote 
node.


TNSNAMES.ORA entry for STANDBY service:

STANDBY=
  (DESCRIPTION=
   (ADDRESS=
    (PROTOCOL=IPC)(KEY=STBY)
   )
   (CONNECT_DATA=
    (SID=STBY)
    (SERVER=DEDICATED))
   )


When a log is archived, we will contact the listener on this IPC KEY.  The 
listener has been configured as follows :-

LISTENER.ORA entry

LISTENER=
  (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=IPC)(KEY=STBY))
  )

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (ORACLE_HOME=/oracle/OFA_base/app/oracle/product/8.1.5)
      (SID_NAME=STBY)
    )
  )



After editing the "LISTENER.ORA", we will need to perform a listener reload
lsnrctl reload

Stop/restart the primary instance.

perform 2 or 3 log switches

You can now query v$archived_log to see if the archival to the remote 
destination has been succesfull.

If the status of the remote destination is 'error', then you should use at the 
ERROR field to try and determine where the problem lies.

Assuming the logs have appeared at the remote destination, we can put the 
standby database in managed recovery mode.

SVRMGR> recover managed standby database;

-- This session will just sit there.  Logs will be applied as and when they 
   are transferred from the primary instance.

You can see the logs being applied by looking at the "alert.log" in another 
session :

tail -f alert_STBY.ora

ALTER DATABASE RECOVER   managed standby database
Fri Jan 21 15:06:28 2000
Media Recovery Start: Managed Standby Recovery
Media Recovery Log
Media Recovery Log /oracle/standby/arch/1_1382.arc
Media Recovery Waiting for thread 1 seq# 1383
Fri Jan 21 15:06:58 2000
Media Recovery Log /oracle/standby/arch/1_1383.arc
Media Recovery Waiting for thread 1 seq# 1384
Fri Jan 21 15:07:28 2000
Media Recovery Log /oracle/standby/arch/1_1384.arc
Media Recovery Waiting for thread 1 seq# 1385


We can open the database in read-only mode at any time.  If the database is in 
managed recovery mode, we will need to cancel this in order to open the 
database read-only.

recover managed standby database cancel

The database can now be opened read-only
alter database open read only

The database can be put back in managed recovery mode by using the command
recover managed standby database


RELATED DOCUMENTS
-----------------

8i Backup and Recovery Guide, Release 8.1.5