DBA Tips Archive for Oracle

  


Placing Standby Database in Managed Recovery Mode

by Jeff Hunter, Sr. Database Administrator



Contents

  1. Introduction
  2. Configuring the Primary Database
  3. Configuring the Standby Database
  4. Standby Database Startup Script
  5. Standby Database Shutdown Script


Introduction

This article provides instructions and notes for putting an Oracle standby database in managed recovery mode. In this mode, the primary database is configured to write archived redo logs to not only a local file system, but to also send a copy of the log file to a remote standby database location (using an Oracle Net Service Name). The remote standby site is configured to automatically accept and apply the archived redo log from the primary database server. Once the archived redo log is applied to the standby database server, it goes back into wait mode to wait for the next archived redo log from the primary database server.

The Standby Database feature of Oracle (Oracle7 and higher) allows the DBA to maintain a duplicate, or standby, copy of a database at a remote site to provide continued primary database availability in the event of failure. The standby database is created with a special copy of the control file from the primary database. The standby database is kept in close synchronization with the primary database by applying the primary database's archived log files to the standby database. It is therefore necessary to operate the primary database in Archivelog Mode to avail the benefit of a standby database.

A standby database is generally used where high availability of data is required. A standby database is one way of quickly providing access to data if the primary database fails and recovery will take longer than the desired time.



Configuring the Primary Database

The log_archive_dest_[n] initialization parameters are used for defining archive destinations for the primary database. These parameters replace the log_archive_dest and log_archive_duplex_dest parameters used in previous releases of Oracle. (i.e. Oracle7 and Oracle8)

The log_archive_dest_[n] initialization parameters allow up to five archive log destinations, including 1 mandatory local destination and up to 4 local or remote destinations. A new log_archive_dest_state_[n] parameters were also introduced in Oracle8i to define an initial state for these destinations.

The primary database should set a minimum define two Archive Log Destinations. Their initial state should be 'enabled' as follows:

log_archive_dest_1        = 'location=/u06/app/oradata/ORA817/archive MANDATORY'
log_archive_dest_2        = 'service=ORA817_LINUX4 OPTIONAL REOPEN=30'

log_archive_dest_state_1  = 'enable'
log_archive_dest_state_2  = 'enable'

log_archive_format        = 'arch_t%t_s%s.dbf'
log_archive_start         = true

NOTE: The DBA should use the following convention when defining the Standby Database Service Name:
<ORACLESID>_<HOST>
For example: ORA817_LINUX4

NOTE: The DBA can query the view v$archive_dest to view the status of all five Archive Log Destinations.
SQL> select dest_name, status, destination from v$archive_dest

DEST_NAME            STATUS    DESTINATION
-------------------- --------- -----------------------------------
LOG_ARCHIVE_DEST_1   VALID     /u06/app/oradata/ORA817/archive
LOG_ARCHIVE_DEST_2   VALID     ORA817_LINUX4
LOG_ARCHIVE_DEST_3   INACTIVE
LOG_ARCHIVE_DEST_4   INACTIVE
LOG_ARCHIVE_DEST_5   INACTIVE


Configuring the Standby Database

The DBA will need to perform the following tasks on the Standby Database Server.

  1. Modify the init<SID>.ora file for the Standby Database and alter the standby_archive_dest parameter as follows:
    standby_archive_dest = /u06/app/oradata/ORA817/archive

    NOTE: The standby_archive_dest parameter should always coincide with the log_archive_dest_1 parameter in the standby database.

  2. Copy all required archived redo log files from the primary database to the standby database. Ensure that if you are using FTP, that you transfer these files in binary mode. In the following example, I will use rcp (from the standby host) to copy all archived redo logs from the primary database to the proper location:
    % rcp linux3:/u06/app/oradata/ORA817/archive/* /u06/app/oradata/ORA817/archive

  3. Startup the instance and then MOUNT the DB in Standby Mode
    % sqlplus "/ as sysdba"
    
    SQL> startup nomount
    ORACLE instance started.
    
    Total System Global Area  252777660 bytes
    Fixed Size                   451772 bytes
    Variable Size             218103808 bytes
    Database Buffers           33554432 bytes
    Redo Buffers                 667648 bytes
    
    
    SQL> alter database mount standby database;
    
    Database altered.

  4. Make sure to apply any logs already archived to the standby host:
    SQL> recover standby database until cancel;
    
    ORA-00279: change 1505990 generated at 09/05/2004 17:57:43 needed for thread 1
    ORA-00289: suggestion : /u06/app/oradata/ORA817/archive/arch_t1_s22.dbf
    ORA-00280: change 1505990 for thread 1 is in sequence #22
    
    
    Specify log: {=suggested | filename | AUTO | CANCEL}
    When prompted, type auto. All archived logs that have been copied over to the standby database will be applied. As soon as Oracle fails to find a log, the recovery session will be terminated, and the user will be returned to the SQL> prompt:
    ORA-00308: cannot open archived log
    '/u06/app/oradata/ORA817/archive/arch_t1_s24.dbf'
    ORA-27037: unable to obtain file status
    Linux Error: 2: No such file or directory
    Additional information: 3
    SQL>

    NOTE: Sustained recovery will NOT retrospectively apply archive logs, which have already been generated on the standby host. For example, the primary may have already archived a number of logs to the remote (standby) host. If the standby is put into sustained recovery mode after the logs have been archived, and the logs are required for roll-forward, the recovery session will sit there forever (unless the timeout parameter is specified).

    SOLUTION: Ensure all logs already archived to the standby host have been applied before starting a sustained recovery session. This can be achieved by first performing non-sustained recovery.

  5. At this stage, the standby database can be put into sustained recovery mode:
    SQL> recover managed standby database;

    NOTE: You will not get your SQL> prompt back. This is normal.

  6. As logs are archived from the primary database, they will be applied to the standby database automatically. There is no indication in the recovery session that logs are being applied. In order to monitor progress, check the standby alert.log. For example:
    % tail -f alert_ORA817.log
    Starting datafile 9 recovery in thread 1 sequence 24
    Datafile 9: '/u06/app/oradata/ORA817/xdb01.dbf'
    Starting datafile 10 recovery in thread 1 sequence 24
    Datafile 10: '/u06/app/oradata/ORA817/example01.dbf'
    Starting datafile 11 recovery in thread 1 sequence 24
    Datafile 11: '/u06/app/oradata/ORA817/perfstat01.dbf'
    Media Recovery Log
    Media Recovery Waiting for thread 1 seq# 24
    Sun Sep  5 18:12:07 2004
    Restarting dead background process QMN0
    QMN0 started with pid=12
    Sun Sep  5 18:15:30 2004
    Media Recovery Log /u06/app/oradata/ORA817/archive/arch_t1_s24.dbf
    Media Recovery Waiting for thread 1 seq# 25
    Media Recovery Log /u06/app/oradata/ORA817/archive/arch_t1_s25.dbf
    Media Recovery Waiting for thread 1 seq# 26

  7. To cancel the recovery session, open another terminal window, login to the database and perform the following command:
    SQL> alter database recover managed standby database cancel;

  8. The database can now be opened (read write or read-only). For example:
    SQL> alter database open read only;

    NOTE: At this stage the standby database can be used for reporting type queries if opened read-only.


Standby Database Startup Script

A call to the following script can be added to your database startup/shutdown script, (i.e. /etc/init.d/dbora), on the database server hosting the Oracle Standby Database.

start_db_recover_mode.ksh
#!/bin/ksh

# +--------------------------------------------------------------------------------
# | FILE    :  start_db_recover_mode.ksh
# | AUTHOR  :  Jeffrey Hunter, Sr. Database Administrator
# |
# | DESC.   :  This script is responsible for starting the Oracle standby database
# |            in managed recovery mode. The major steps in this script include 
# |            mounting the database in standby mode, copy all archived redo log
# |            files from the primary database server (using rcp), applying all
# |            archived redo logs from the primary database, and finally putting
# |            the database in managed recovery mode - automatically accepting and
# |            applying archived redo logs from the primary database.
# |
# | NOTE    :  Since this script uses one of the r* commands, namely rcp, it
# |            assumes that a valid .rhosts (or /etc/hosts.equiv) exists on the
# |            primary host to ensure that the standby host can login as the 
# |            "oracle" user account.
# |
# | SYNTAX  :  nohup start_db_recover_mode.ksh ORACLE_SID
# |                                            PRIMARY_DB_SERVER
# |                                            PRIMARY_ARCH_LOG_DEST
# |                                            STANDBY_ARCH_LOG_DEST
# |
# | EXAMPLE
# |    CALL :  nohup start_db_recover_mode.ksh -
# |                ORA817 -
# |                linux3 -
# |                /u06/app/oradata/ORA817/archive -
# |                /u06/app/oradata/ORA817/archive > -
# |                /u01/app/oracle/common/log/start_db_recover_mode.log 2>&1 &
# +--------------------------------------------------------------------------------

SHORT_NAME=`basename $0`

# +------------------------------+
# | VALIDATE INCOMING PARAMETERS |
# +------------------------------+

if (( $# != 4 )); then
    echo " "
    echo "Usage: $SHORT_NAME ORACLE_SID PRIMARY_DB_SERVER PRIMARY_ARCH_LOG_DEST STANDBY_ARCH_LOG_DEST"
    echo " "
    echo "  Invalid number of arguments."
    echo " "
    exit 1
fi

# +----------------------+
# | SET GLOBAL VARIABLES |
# +----------------------+

echo " "
echo "Setting Global Variables..."
echo " "

ORACLE_SID=$1
export ORACLE_SID

PRIMARY_DB_SERVER=$2
export PRIMARY_DB_SERVER

PRIMARY_ARCH_LOG_DEST=$3
export PRIMARY_ARCH_LOG_DEST

STANDBY_ARCH_LOG_DEST=$4
export STANDBY_ARCH_LOG_DEST

ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE

ORACLE_HOME=${ORACLE_BASE}/product/8.1.7
export ORACLE_HOME

LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
export LD_LIBRARY_PATH

ORA_NLS33=${ORACLE_HOME}/ocommon/nls/admin/data
echo ORA_NLS33

ORACLE_DOC=${ORACLE_HOME}/doc
echo ORACLE_DOC

# +-----------------------------------------+
# | DISPLAYING ORACLE ENVIRONMENT VARIABLES |
# +-----------------------------------------+

echo " "
echo "Displaying Oracle Environment Variables..."
echo " "

echo " "
echo " >>> set | grep ^ORA"
echo " "
set | grep ^ORA
echo " "

# +-------------------------------------+
# | CHECK FOR DATABASE INSTANCE OFFLINE |
# +-------------------------------------+

echo " "
echo "Checking for database instance offline..."
echo " "

STATUS=`ps -fu oracle | grep -v grep | grep $ORACLE_SID | grep ora_`
if [[ $? != 1 ]]; then
    echo "ERROR - Database Instance is up. Is this thing already in Managed Recovery Mode?"
    echo "        Process listing is to follow..."
    ps -fu oracle | grep -v grep | grep $ORACLE_SID | grep ora_
    echo "        Exiting script."
    echo " "
    exit
fi

# +---------------------------------------------------------+
# | TRY TO GET ANY ARCHIVED LOG FILES FROM PRIMARY DATABASE |
# +---------------------------------------------------------+

echo " "
echo "Attempting to get any archived log files from ${PRIMARY_DB_SERVER}:${LOG_ARCH_DEST} ..."
echo " "

rcp ${PRIMARY_DB_SERVER}:${PRIMARY_ARCH_LOG_DEST}/* $STANDBY_ARCH_LOG_DEST

# +---------------------------------------------+
# | STARTUP/MOUNT STANDBY DB & RECOVER ALL LOGS |
# +---------------------------------------------+

echo " "
echo "Startup/Mount Database and recover all archived redo logs..."
echo " "

sqlplus /nolog << END
connect / as sysdba
startup nomount
alter database mount standby database;
recover standby database until cancel;
auto
exit;
END

# +--------------------------------------------+
# | STARTUP DB IN MANGED STANDBY RECOVERY MODE |
# +--------------------------------------------+

echo " "
echo "Startup Database in Managed Standby Recovery Mode..."
echo " "

sqlplus /nolog << END
connect / as sysdba
recover managed standby database;
connect / as sysdba
shutdown immediate
exit;
END


Standby Database Shutdown Script

A call to the following script can be added to your database startup/shutdown script, (i.e. /etc/init.d/dbora), on the database server hosting the Oracle Standby Database.

stop_db_recover_mode.ksh
#!/bin/ksh

# +--------------------------------------------------------------------------------
# | FILE    :  stop_db_recover_mode.ksh
# | AUTHOR  :  Jeffrey Hunter, Sr. Database Administrator
# |
# | DESC.   :  This script is responsible for stopping an Oracle standby database
# |            that is in managed recovery mode. The major steps in this script
# |            include checking that the database instance is indeed running and
# |            then canceling managed recovery mode for the given database.
# |
# | SYNTAX  :  stop_db_recover_mode.ksh ORACLE_SID
# |
# | EXAMPLE
# |    CALL :  stop_db_recover_mode.ksh ORA817 > /u01/app/oracle/common/log/stop_db_recover_mode.log 2>&1
# +--------------------------------------------------------------------------------

SHORT_NAME=`basename $0`

# +------------------------------+
# | VALIDATE INCOMING PARAMETERS |
# +------------------------------+

if (( $# != 1 )); then
    echo " "
    echo "Usage: $SHORT_NAME ORACLE_SID"
    echo " "
    echo "  Invalid number of arguments."
    echo " "
    exit 1
fi

# +----------------------+
# | SET GLOBAL VARIABLES |
# +----------------------+

echo " "
echo "Setting Global Variables..."
echo " "

ORACLE_SID=$1
export ORACLE_SID

ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE

ORACLE_HOME=${ORACLE_BASE}/product/8.1.7
export ORACLE_HOME

LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
export LD_LIBRARY_PATH

ORA_NLS33=${ORACLE_HOME}/ocommon/nls/admin/data
echo ORA_NLS33

ORACLE_DOC=${ORACLE_HOME}/doc
echo ORACLE_DOC

# +-----------------------------------------+
# | DISPLAYING ORACLE ENVIRONMENT VARIABLES |
# +-----------------------------------------+

echo " "
echo "Displaying Oracle Environment Variables..."
echo " "

echo " "
echo " >>> set | grep ^ORA"
echo " "
set | grep ^ORA
echo " "

# +------------------------------------+
# | CHECK FOR DATABASE INSTANCE ONLINE |
# +------------------------------------+

echo " "
echo "Checking for database instance online..."
echo " "

STATUS=`ps -fu oracle | grep -v grep | grep $ORACLE_SID | grep ora_`
if [[ $? == 1 ]]; then
  echo "ERROR - database not in recovery mode. Did someone already shutdown the DB Instance?"
  echo "        Process listing is to follow..."
  ps -fu oracle | grep -v grep | grep $ORACLE_SID | grep ora_
  echo "        Exiting script."
  echo " "
  exit
fi

# +---------------------------------------------+
# | TAKE DB OUT OF MANAGED RECOVERY MODE.       |
# +---------------------------------------------+

echo " "
echo "Take Database out of Managed Standby Recovery Mode..."
echo " "

sqlplus /nolog << END
connect / as sysdba
recover managed standby database cancel;
exit;
END



Copyright (c) 1998-2014 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 21-Jun-2007 16:30:42 EDT
Page Count: 42921