1. Overview
The following document describes the setup of an Oracle9i Data Guard (e.g. Standby) system. The database
runs on Oracle9i version 9.2.0.5 on a Sun Solaris platform. The task is to create an Oracle9i Data Guard
environment using a single physical standby database on a separate server. The Data Guard standby
database has been set up in "managed recovery" mode to automate the process of keeping the standby in
sync with the primary database. OEM is been installed on a separate server and has been configured to
monitor the DataGuard installation.
2. System Requirements
2.1 Kernel Parameters
Kernel parameter values for shared memory segments and semaphore settings are shown below. See the
Oracle9i Installation Guide for specific parameter guidelines. The following should be added to /etc/system:
# Settings for oracleset shmsys:shminfo_shmmax=1610612736 set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100 set shmsys:shminfo_shmseg=10 set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=160 <= Minimum recommendation is 256 set semsys:seminfo_semmns=470 <= Minimum recommendation is 1024 set semsys:seminfo_semopm=100
set semsys:seminfo_semvmx=32767 #
2.2 Disk Space
2.2.1 Oracle Software
Minimum of 3 GB for this installation.
2.2.2 Swap Space
Disk space equal to the system’s physical memory, or 1GB, whichever is greater.
2.2.3 Temporary Space
Up to 400 MB of space in the /tmp directory.
3. Standby Creation
3.1 Software Installation
3.1.1 Directory Setup
A new directory structure for the standby database's "admin" directories was created and the datafile
directories were located on the same NFS mounted file systems as the source database. The following is the
basic directory structure:
ORACLE_BASE = /u01/app/oracle ORACLE_HOME = /u01/app/oracle/product/9.2.0
Admin directories:
/u01/app/oracle/product/9.2.0/adump /bdump /cdump /pfile /udump3.1.1.1 Primary Database Backup
A new level 0 backup was taken of the primary database with a standby backup controlfile.
3.1.1.2 Standby Database
Datafile directories:
/u03/oradata/stbyControlfile directories:
/u03/oradata/stbyRedo log directories:
/u03/oradata/stbyStandby archive destination:
/u02/oradata/stby3.2 Primary Database Preparation
3.2.1 Enable Forced Logging
Place the primary database in FORCE LOGGING mode after database creation using the following SQL
statement:
SQL> ALTER DATABASE FORCE LOGGING;
This prevents the use of the "nologging" feature, which would not be replicated in the redo logs, and
therefore applied on the standby database.
3.2.2 Create A Password File
This is required to enable RMAN to create the standby database. After creating the password file with the
orapwd utility, the following init.ora parameter must be set:
remote_login_passwordfile='EXCLUSIVE'
3.2.3 Create A Standby Controlfile
A backup controlfile from the primary database is required with the standby option specified:
SQL> alter database create standby controlfile as '<file>';3.2.3.1 Backup With Standby Controlfile
The following option was added to the RMAN "backup database" command to insure that a controlfile was
available in standby format for RMAN to create the standby database. This is important to also allow for
future re-instantiation of the standby database through RMAN.
CONFIGURE CONTROLFILE AUTOBACKUP ON;
Appended to the backup command:
3.2.4 Additional init.ora Parameters
log_archive_dest_2='service=stby ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=300 register'
or
log_archive_dest_2='service=stby LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=300 register'
log_archive_dest_state_2='ENABLE' log_checkpoint_interval=10000 log_checkpoint_timeout=1800
3.2.5 Create An SPFILE For OEM Support
Oracle's Enterprise Manager requires spfile support for remote administration of DataGuard installations.
This can be created with the following command:
SQL> create spfile from pfile;
This will create an spfile in the default $ORACLE_HOME/dbs directory and will take affect on the next
database startup.
3.3 Standby Database Creation
To create the standby database the following basic steps were performed:
• Configure Oracle Server software
• Create standby database environment
• Configure Network Files
• Configure the Primary Database init.ora Parameters
• Configure the Standby Database init.ora Parameters
• Start the Standby Instance
• Create the Standby database with RMAN
• Place the Standby Database in recovery mode
The following sections will detail each of these steps.
3.3.1 Configure Oracle Server Software
The primary database is running Oracle 9i version 9.2.0.5. The ORACLE_HOME for the standby database
server was installed with version 9.2.0.5 as well.
3.3.2 Create Standby Database Environment
3.3.2.1 Standby Database Password File
A new password file was created for the standby database instance using the orapwd utility.
orapwd file=orapwDBSID password=xxxxxx entries=20
3.3.2.2 Configure the Standby Database init.ora Parameters
The source database init.ora parameters were copied and the following added or changed:
aq_tm_processes=0archive_lag_target=0 db_name = DBSID
instance_name = DBSID # control_files = ('/u01/oradata/DBSID/control01.ctl', '/u02/oradata/DBSID/control02.ctl', '/u03/oradata/DBSID/control03.ctl') # background_dump_dest = '/u01/app/oracle/admin/DBSID/bdump' core_dump_dest = '/u01/app/oracle/admin/DBSID/cdump' user_dump_dest = '/u01/app/oracle/admin/DBSID/udump' # compatible = '9.2.0.0.0' # job_queue_processes=0 #
# Standby specific parameters # db_file_name_convert = ('/u01/oradata/DBSID','/u03/oradata/stby', '/u01/oradata/DBSID','/u03/oradata/stby', '/u01/oradata/DBSID','/u03/oradata/stby') # log_file_name_convert = ('/u01/oradata/DBSID','/u03/oradata/stby', '/u01/oradata/DBSID','/u03/oradata/stby', '/u01/oradata/DBSID','/u03/oradata/stby') # standby_archive_dest = '/u01/app/oracle/admin/stby_arch/' standby_file_management=auto remote_archive_enable=true #
# Set to allow log apply services to automatically resolve archive gaps # fal_client='(DESCRIPTION=(ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=primary)(PORT=1521)))(CONNECT_DATA=(SID=DBSID)(ORACLE_HOME=/u 01/app/oracle/product/9.2.0)(SERVER=DEDICATED)))' fal_server='(DESCRIPTION=(ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=stby)(PORT=1521)))(CONNECT_DATA=(SID=DBSID)(ORACLE_HOME=/u01/ app/oracle/product/9.2.0)(SERVER=DEDICATED)))' #
# Set logging Parameters #
log_archive_dest_1 = 'LOCATION=/u01/app/oracle/admin/DBSID/arch/'
log_archive_dest_2='SERVICE=DBSID_stby ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=300 register' log_archive_dest_state_1='ENABLE' log_archive_dest_state_2='DEFER' log_archive_format = 'DBSID_%t_%s_%r.arc' log_archive_max_processes=2 log_archive_min_succeed_dest=1 log_archive_start=true log_archive_trace=0
Note: The standby_archive_dest parameter should match the log_archive_dest_n parameter.
3.3.3 Configure Network Files
On the primary database a network entry must be available that specifies how to SQL*Net to the standby
database. This is used to transfer archivelog files to the standby database. On the standby database itself
there should be an entry to register the database with the listener and optionally a network entry to specify
SQL*Net access to the standby database.
3.3.3.1 Primary Database
3.3.3.1.1 tnsnames.ora File
The tnsnames.ora file and/or Oracle Names/OID should be updated with information to allow access to the
standby database. The following was added to the tnsnames.ora file for the primary database:
DBSID_stby = (DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stby)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = DBSID) ) )
3.3.3.2 Standby Database
3.3.3.2.1 sqlnet.ora File
On the standby database the following entries should exist:
# SQLNET.ORA Network Configuration File:/u01/app/oracle/product/9.2.0/network/admin/sqlnet.ora # Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
3.3.3.3 listener.ora File
On the standby database the following entry should be added to the listener.ora file:
# LISTENER.ORA Network Configuration File:#/u01/app/oracle/product/9.2.0/network/admin/listener.ora # Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stby)(PORT = 1521)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/9.2.0) (SID_NAME = DBSID) ) )
3.3.3.3.1 tnsnames.ora File
The following example shows the standby database entry in the standby server's tnsnames.ora file:
DBSID_stby =(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stby)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = DBSID) ) ) DBSID = (DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521)) )
(CONNECT_DATA =
(SERVICE_NAME = DBSID) )
3.3.4 Create spfile
It is strongly recommended that the standby be run with an spfile rather than a standard init.ora. Both OEM
and DataGuard require an SPFILE in order to remotely manage the standby database.
An spfile was created from the init.ora which was used to initially start the standby database. This was
required for OEM support of it's DataGuard option. The following will create an spfile from an init.ora file
and put it in the default location (i.e. $ORACLE_HOME/dbs):
SQL> create spfile from pfile;
3.3.5 Create the Standby Database with RMAN
The standby database is created using RMAN and the latest online backup and archivelog files from the
primary database. The standby creation requires the use of a "standby controlfile" so the primary database
backup is made with the additional clause "include current controlfile for standby".
In order to create the standby database with RMAN the standby instance must be started in nomount mode.
RMAN will then create a "duplicate" database for "standby". RMAN expects to find the backups in the
same place that they were created in so the backup destination directory structure is duplicated and the
backup files are copied from the primary system to this destination on the standby database.
The following shows the commands used to create the standby database:
/u01/app/oracle/local/bkup/restore$ sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 6 11:39:45 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect / as sysdba Connected to an idle instance. SQL> startup nomount;
ORACLE instance started.
Total System Global Area 403412268 bytes Fixed Size 451884 bytes Variable Size 335544320 bytes Database Buffers 67108864 bytes Redo Buffers 307200 bytes SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production /u01/app/oracle/local/bkup/restore$
export NLS_LANG="AMERICAN_AMERICA.UTF8" export NLS_DATE_FORMAT="DD-MON-YY HH24:MI"
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DBSID (DBID=1260836409) connected to recovery catalog database
RMAN>
connected to auxiliary database: DBSID (not mounted)
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>
sql statement: ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YY HH24:MI"
Starting Duplicate Db at 06-APR-06 13:52 allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=12 devtype=DISK allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: sid=13 devtype=DISK
printing stored script: Memory Script {
restore clone standby controlfile to clone_cf; replicate clone controlfile from clone_cf;
sql clone 'alter database mount standby database'; }
executing script: Memory Script
Starting restore at 06-APR-06 13:52
using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2
channel ORA_AUX_DISK_1: starting datafile backupset restore channel ORA_AUX_DISK_1: restoring controlfile
output filename=/u01/oradata/DBSID/control01.ctl channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1phfr760_1_1 tag=INCR0_DISK_DBSID params=NULL
channel ORA_AUX_DISK_1: restore complete Finished restore at 06-APR-06 13:52 replicating controlfile input filename=/u01/oradata/DBSID/control01.ctl output filename=/u01/oradata/DBSID/control02.ctl output filename=/u01/oradata/DBSID/control03.ctl
sql statement: alter database mount standby database
printing stored script: Memory Script {
set until scn 7240492351118; set newname for datafile 1 to "/u01/oradata/DBSID/system01.dbf"; set newname for datafile 2 to "/u01/oradata/DBSID/undotbs01.dbf"; set newname for datafile 3 to "/u01/oradata/DBSID/cwmlite01.dbf"; set newname for datafile 4 to "/u01/oradata/DBSID/drsys01.dbf"; set newname for datafile 5 to "/u01/oradata/DBSID/nvdmrpt_tab01.dbf"; set newname for datafile 6 to "/u01/oradata/DBSID/nvdmrpt_tab02.dbf"; set newname for datafile 7 to "/u01/oradata/DBSID/odm01.dbf"; set newname for datafile 8 to "/u01/oradata/DBSID/tools01.dbf"; set newname for datafile 9 to "/u01/oradata/DBSID/users01.dbf"; set newname for datafile 10 to "/u01/oradata/DBSID/xdb01.dbf"; set newname for datafile 11 to "/u01/oradata/DBSID/srcdba_tab01.dbf"; set newname for datafile 12 to "/u01/oradata/DBSID/rhn_tab01.dbf"; set newname for datafile 13 to "/u01/oradata/DBSID/asdqa2_tab01.dbf"; set newname for datafile 14 to "/u01/oradata/DBSID/asdqa2_tab02.dbf"; set newname for datafile 15 to "/u01/oradata/DBSID/rhn_tab02.dbf"; set newname for datafile 16 to "/u01/oradata/DBSID/radia4_tab01.dbf"; set newname for datafile 17 to "/u01/oradata/DBSID/radia4_tab02.dbf"; set newname for datafile 18 to "/u01/oradata/DBSID/RADIA4DEV_TAB1.dbf"; set newname for datafile 19 to "/u01/oradata/DBSID/RADIA4DEV_TAB2.dbf"; set newname for datafile 20 to "/u01/oradata/DBSID/bigfix_tab01.dbf"; set newname for datafile 21 to "/u01/oradata/DBSID/ASDDEV_TAB1.dbf"; set newname for datafile 22 to "/u01/oradata/DBSID/ASDDEV_TAB2.dbf"; set newname for datafile 23 to
"/u01/oradata/DBSID/RADIA4QA_TAB1.dbf"; set newname for datafile 24 to "/u01/oradata/DBSID/RADIA4QA_TAB2.dbf"; restore check readonly clone database ; }
executing script: Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 06-APR-06 13:52
using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00005 to /u01/oradata/DBSID/nvdmrpt_tab01.dbf
restoring datafile 00007 to /u01/oradata/DBSID/odm01.dbf restoring datafile 00008 to /u01/oradata/DBSID/tools01.dbf restoring datafile 00011 to /u01/oradata/DBSID/srcdba_tab01.dbf restoring datafile 00012 to /u01/oradata/DBSID/rhn_tab01.dbf restoring datafile 00013 to /u01/oradata/DBSID/asdqa2_tab01.dbf restoring datafile 00016 to /u01/oradata/DBSID/radia4_tab01.dbf restoring datafile 00017 to /u01/oradata/DBSID/radia4_tab02.dbf restoring datafile 00018 to /u01/oradata/DBSID/RADIA4DEV_TAB1.dbf restoring datafile 00021 to /u01/oradata/DBSID/ASDDEV_TAB1.dbf
restoring datafile 00022 to /u01/oradata/DBSID/ASDDEV_TAB2.dbf restoring datafile 00024 to /u01/oradata/DBSID/RADIA4QA_TAB2.dbf channel ORA_AUX_DISK_2: starting datafile backupset restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/oradata/DBSID/system01.dbf
restoring datafile 00002 to /u01/oradata/DBSID/undotbs01.dbf restoring datafile 00003 to /u01/oradata/DBSID/cwmlite01.dbf restoring datafile 00004 to /u01/oradata/DBSID/drsys01.dbf restoring datafile 00006 to /u01/oradata/DBSID/nvdmrpt_tab02.dbf restoring datafile 00009 to /u01/oradata/DBSID/users01.dbf restoring datafile 00010 to /u01/oradata/DBSID/xdb01.dbf
restoring datafile 00014 to /u01/oradata/DBSID/asdqa2_tab02.dbf restoring datafile 00015 to /u01/oradata/DBSID/rhn_tab02.dbf restoring datafile 00019 to /u01/oradata/DBSID/RADIA4DEV_TAB2.dbf restoring datafile 00020 to /u01/oradata/DBSID/bigfix_tab01.dbf restoring datafile 00023 to /u01/oradata/DBSID/RADIA4QA_TAB1.dbf channel ORA_AUX_DISK_2: restored backup piece 1
piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1phfr760_1_1 tag=INCR0_DISK_DBSID params=NULL
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1qhfr760_1_1 tag=INCR0_DISK_DBSID params=NULL
channel ORA_AUX_DISK_2: restored backup piece 2
piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1phfr760_2_1 tag=INCR0_DISK_DBSID params=NULL
channel ORA_AUX_DISK_1: restored backup piece 2
piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1qhfr760_2_1 tag=INCR0_DISK_DBSID params=NULL
channel ORA_AUX_DISK_2: restored backup piece 3
piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1phfr760_3_1 tag=INCR0_DISK_DBSID params=NULL
channel ORA_AUX_DISK_1: restored backup piece 3
piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1qhfr760_3_1 tag=INCR0_DISK_DBSID params=NULL
channel ORA_AUX_DISK_2: restored backup piece 4
piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1phfr760_4_1 tag=INCR0_DISK_DBSID params=NULL
channel ORA_AUX_DISK_1: restored backup piece 4
piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1qhfr760_4_1 tag=INCR0_DISK_DBSID params=NULL
channel ORA_AUX_DISK_1: restored backup piece 5
piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1qhfr760_5_1 tag=INCR0_DISK_DBSID params=NULL
channel ORA_AUX_DISK_1: restore complete
channel ORA_AUX_DISK_2: restored backup piece 5
piece handle=/oracle/bkupdir/DBSID/DBSID.bkup.1phfr760_5_1 tag=INCR0_DISK_DBSID params=NULL
channel ORA_AUX_DISK_2: restore complete Finished restore at 06-APR-06 14:16
printing stored script: Memory Script {
switch clone datafile all; }
executing script: Memory Script
datafile 2 switched to datafile copy
input datafilecopy recid=79 stamp=587053019 filename=/u01/oradata/DBSID/undotbs01.dbf datafile 3 switched to datafile copy
input datafilecopy recid=80 stamp=587053020 filename=/u01/oradata/DBSID/cwmlite01.dbf datafile 4 switched to datafile copy
input datafilecopy recid=81 stamp=587053020 filename=/u01/oradata/DBSID/drsys01.dbf datafile 5 switched to datafile copy
input datafilecopy recid=82 stamp=587053020 filename=/u01/oradata/DBSID/nvdmrpt_tab01.dbf datafile 6 switched to datafile copy
input datafilecopy recid=83 stamp=587053020 filename=/u01/oradata/DBSID/nvdmrpt_tab02.dbf datafile 7 switched to datafile copy
input datafilecopy recid=84 stamp=587053020 filename=/u01/oradata/DBSID/odm01.dbf datafile 8 switched to datafile copy
input datafilecopy recid=85 stamp=587053020 filename=/u01/oradata/DBSID/tools01.dbf datafile 9 switched to datafile copy
input datafilecopy recid=86 stamp=587053020 filename=/u01/oradata/DBSID/users01.dbf datafile 10 switched to datafile copy
input datafilecopy recid=87 stamp=587053020 filename=/u01/oradata/DBSID/xdb01.dbf datafile 11 switched to datafile copy
datafile 12 switched to datafile copy
input datafilecopy recid=89 stamp=587053020 filename=/u01/oradata/DBSID/rhn_tab01.dbf datafile 13 switched to datafile copy
input datafilecopy recid=90 stamp=587053020 filename=/u01/oradata/DBSID/asdqa2_tab01.dbf datafile 14 switched to datafile copy
input datafilecopy recid=91 stamp=587053020 filename=/u01/oradata/DBSID/asdqa2_tab02.dbf datafile 15 switched to datafile copy
input datafilecopy recid=92 stamp=587053020 filename=/u01/oradata/DBSID/rhn_tab02.dbf datafile 16 switched to datafile copy
input datafilecopy recid=93 stamp=587053020 filename=/u01/oradata/DBSID/radia4_tab01.dbf datafile 17 switched to datafile copy
input datafilecopy recid=94 stamp=587053020 filename=/u01/oradata/DBSID/radia4_tab02.dbf datafile 18 switched to datafile copy
input datafilecopy recid=95 stamp=587053020 filename=/u01/oradata/DBSID/RADIA4DEV_TAB1.dbf datafile 19 switched to datafile copy
input datafilecopy recid=96 stamp=587053020 filename=/u01/oradata/DBSID/RADIA4DEV_TAB2.dbf datafile 20 switched to datafile copy
input datafilecopy recid=97 stamp=587053020 filename=/u01/oradata/DBSID/bigfix_tab01.dbf datafile 21 switched to datafile copy
input datafilecopy recid=98 stamp=587053021 filename=/u01/oradata/DBSID/ASDDEV_TAB1.dbf datafile 22 switched to datafile copy
input datafilecopy recid=99 stamp=587053021 filename=/u01/oradata/DBSID/ASDDEV_TAB2.dbf datafile 23 switched to datafile copy
input datafilecopy recid=100 stamp=587053021 filename=/u01/oradata/DBSID/RADIA4QA_TAB1.dbf datafile 24 switched to datafile copy
input datafilecopy recid=101 stamp=587053021 filename=/u01/oradata/DBSID/RADIA4QA_TAB2.dbf
printing stored script: Memory Script { set until scn 7240492351118; recover standby clone database delete archivelog ; }
executing script: Memory Script
executing command: SET until clause
Starting recover at 06-APR-06 14:17 using channel ORA_AUX_DISK_1 using channel ORA_AUX_DISK_2
starting media recovery
channel ORA_AUX_DISK_1: starting archive log restore to default destination channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=2216
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/bkupdir/DBSID/DBSID.arch.1rhfr941_1_1 tag=INCR0_DISK_DBSID params=NULL
channel ORA_AUX_DISK_1: restore complete
archive log filename=/u01/app/oracle/DBSID_arch/arch_1_2216.arc thread=1 sequence=2216 channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/DBSID_arch/arch_1_2216.arc recid=1 stamp=587053025 media recovery complete
Finished recover at 06-APR-06 14:17 Finished Duplicate Db at 06-APR-06 14:17
RMAN>
Recovery Manager complete.
3.3.5.1 RMAN Standby Database Creation Script
#$ORACLE_HOME/bin/rman nocatalog target sys/xxxxxx@DBSID log=dupdb.log <<-EOF connect auxiliary /
# run { #
#
sql 'ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YY HH24:MI"'; #set until time = '08-SEP-03 01:00';
#
duplicate target database for standby nofilenamecheck
dorecover; }
exit; EOF
3.4 Standby Redo Log Files
Standby redo log files are required for higher protection levels where LGWR is used to write redo records.
There is no downside to creating them for both the primary and standby and this allows a switchover back
to the primary. They must be created as the same size and number of groups and members as the primary
online redo log database.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
--- --- --- standby_file_management string auto
SQL> alter system set standby_file_management=manual;
System altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
--- --- --- standby_file_management string MANUAL
SQL> alter database add standby logfile '/u01/oradata/DBSID/srl01a.rdo' size 50m;
Database altered.
SQL> alter database add standby logfile '/u01/oradata/DBSID/srl02a.rdo' size 50m;
Database altered.
SQL> alter database add standby logfile '/u01/oradata/DBSID/srl03a.rdo' size 50m;
Database altered.
SQL> alter database add standby logfile '/u01/oradata/DBSID/srl04a.rdo' size 50m;
Database altered.
SQL>
Note: If standby log files are already defined (e.g. from the primary database), then they must be dropped
and recreated.
SQL> alter database drop standby logfile '/u01/oradata/DBSID/srl01a.rdo';
Database altered.
SQL> alter database add standby logfile '/u01/oradata/DBSID/srl01a.rdo' size 500m;
Database altered.
SQL>
It is recommended to always create one more standby redo log file than there is on the primary database.
SQL> alter system set standby_file_management=auto;
System altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
--- --- --- standby_file_management string AUTO
SQL>
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.
SQL>
To review the newly create standby log files:
SQL> select * from v$standby_log;GROUP# THREAD# SEQUENCE# BYTES USED ARC STATUS --- --- --- --- --- --- --- FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME
--- --- --- --- 4 0 0 52428800 1024 YES UNASSIGNED 0 0 5 0 0 52428800 1024 YES UNASSIGNED 0 0 6 0 0 52428800 1024 YES UNASSIGNED 0 0 7 0 0 52428800 1024 YES UNASSIGNED 0 0 SQL>
To enable LGWR sending of redo records on the primary database change the definition of
log_archive_dest_3:
SQL> l
1* select * from v$archive_dest where dest_id < 4; DEST_ID
--- DEST_NAME
--- STATUS BINDING NAME_SP TARGET ARCHIVER SCHEDULE
--- --- --- --- --- --- DESTINATION
--- LOG_SEQUENCE REOPEN_SECS DELAY_MINS NET_TIMEOUT PROCESS REG FAIL_DATE
--- --- --- --- --- --- --- FAIL_SEQUENCE FAIL_BLOCK FAILURE_COUNT MAX_FAILURE
--- --- --- --- ERROR --- ALTERNATE --- DEPENDENCY --- REMOTE_TEMPLATE --- QUOTA_SIZE QUOTA_USED MOUNTID TRANSMIT_MOD ASYNC_BLOCKS AFF TYPE
--- --- --- --- --- --- --- 1
LOG_ARCHIVE_DEST_1
VALID OPTIONAL SYSTEM PRIMARY ARCH ACTIVE /u01/app/oracle/admin/DBSID/arch
4071 300 0 0 ARCH YES 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC 2 LOG_ARCHIVE_DEST_2
VALID OPTIONAL SYSTEM STANDBY ARCH ACTIVE STBY_DBSID 4071 300 0 0 ARCH YES 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC 3 LOG_ARCHIVE_DEST_3
INACTIVE OPTIONAL SYSTEM PRIMARY ARCH INACTIVE 0 0 0 0 ARCH NO 0 0 0 0 NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC
SQL> alter system set log_archive_dest_3='service=DBSID_stby lgwr async noaffirm delay=0 optional max_failure=0 reopen=300 register' scope=both;
System altered.
SQL> alter system set log_archive_dest_state_3='ENABLE' scope=both;
System altered.
SQL> select * from v$archive_dest 2 where dest_id = 3; DEST_ID --- DEST_NAME --- STATUS BINDING NAME_SP TARGET ARCHIVER SCHEDULE
--- --- --- --- --- --- DESTINATION
--- LOG_SEQUENCE REOPEN_SECS DELAY_MINS NET_TIMEOUT PROCESS REG FAIL_DATE
--- --- --- --- --- --- --- FAIL_SEQUENCE FAIL_BLOCK FAILURE_COUNT MAX_FAILURE
--- --- --- --- ERROR --- ALTERNATE --- DEPENDENCY --- REMOTE_TEMPLATE --- QUOTA_SIZE QUOTA_USED MOUNTID TRANSMIT_MOD ASYNC_BLOCKS AFF TYPE
--- --- --- --- --- --- --- 3
LOG_ARCHIVE_DEST_3
VALID OPTIONAL SYSTEM STANDBY LGWR PENDING DBSID_stby 0 300 0 0 LGWR YES 0 0 0 0 NONE NONE NONE
0 0 0 ASYNCHRONOUS 2048 NO PUBLIC
SQL>
alter system set log_archive_dest_3='service= DBSID_stby LGWR ASYNC NOAFFIRM DELAY=0 OPTIONAL max_failure=0 reopen=300 register' scope=both;
alter system set log_archive_dest_state_3='ENABLE' scope=both;
Note that now two definitions have been created. The "log_archive_dest_2" definition defines an archiver
path to transfer archived redo log files and the "log_archive_dest_3" definition defines a log writer path.
Once the log writer definition is enabled it will become effective at the next log switch. If there is a failure
Oracle will fall back to the archiver definition if possible, and Oracle will attempt to use the log writer
definition at each log switch.
Oracle Documentation:
The minimum configuration should have one more standby redo log group than the
primary database.
It might be necessary to create additional standby log groups on the physical standby
database, so that the archival operation has time to complete before the standby redo log
is reused by the RFS process. If the primary database is operating in maximum
protection mode and a standby redo log cannot be allocated, the primary database
instance might shut down immediately. If the primary database is operating in maximum
protection mode or maximum availability mode, then the primary database might wait
for the standby redo log to become available. Therefore, be sure to allocate an adequate
number of standby redo logs.
Caution:
Whenever you add an online redo log to the primary database, you
must add a corresponding standby redo log to the standby
database. If you do not add a standby redo log to the standby
database, the primary database might shut down.
4. Standby Management
4.1 Start the Standby Instance
The standby instance must be started and placed in standby mount mode. The database should never be
"opened" because this will invalidate the "standby" status. In addition, the database must be mounted in
order to access the controlfile to allow file name changes and subsequent recovery. The following
commands are used:
1.
Use SQL*Plus to connect to the standby database instance. For example, enter:
SQL> CONNECT / AS SYSDBA
2.
Start the Oracle instance at the standby database without mounting the
database. For example, enter:
3.
Mount the standby database:
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
4.2 Modes and Recovery
Once the standby database has been recovered up to the last archived redo log file, it must be kept current
by the application of log files once they have been archived from the primary database. This can be done in
"manual recovery" mode or in "managed recovery" mode. The standby database can also be opened in
"read only" mode which allows query access to the standby database. No recovery can take place while the
database is open in read only mode though, and so time must be arranged for periodic recoveries to take
place in order to keep the standby in sync with the primary database.
For Oracle9i and above the standby database can be "caught up" and run in managed recovery mode with
the following commands:
SQL> alter database recover managed standby database disconnect;
It is still possible to perform manual recovery (this was required in 8i standby database) with the command:
SQL> recover standby database;
Note: dbcontrol supports the startup of a DataGuard or standby database in managed recovery mode.
4.3 Startup and Shutdown
In order to maintain managed recovery of the standby database, the following guidelines must be followed:
"To avoid creating gap sequences, follow these rules:
Start the standby databases and listeners before starting the primary database.
Shut down the primary database before shutting down the standby database.
If you violate either of these two rules, then the standby database is down while the
primary database is open and archiving. Consequently, Oracle can create a gap
sequence. When you restart the standby database later, you must synchronize the
standby database manually with the primary database before you can initiate
managed recovery."
1Once Data Guard is implemented this is typically not a problem since Data Guard will take care of
re-synchronization.
4.4 Events That Affect the Standby Database
The following events can cause standby database issues:
• Add datafile of create tablespace
• Drop or delete a tablespace or datafile
• Rename a datafile
• Alter the primary database control file (using the SQL ALTER DATABASE CREATE
CONTROLFILE statement)
• Perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause
• Change initialization parameter
See the Oracle Data Guard Concepts and Administration, Release 2 (9.2) manual for more details.
1
4.5 Managed Recovery Mode
Managed recovery mode allows for the automatic application of archived redo log files to the standby
database. Managed recovery mode should be initiated with a disconnected session. The following syntax
will accomplish this, and is used by dbcontrol:
SQL> alter database recover managed standby database disconnect;
4.6 Troubleshooting
4.6.1 Managed Recovery Errors
In order to initiate managed recovery all archived redo log files must be applied to the standby database.
Managed recovery will not retrospectively apply archived redo log files.
4.6.2 Archiver Errors
If archived redo log files are not being transferred to the standby database then check the "state" of the
archive destination in the v$archive_dest view on the primary database. If this state is in error then the
standby listener service may have failed. Once that is corrected, you must force the listener to begin
re-transmission with the command:
SQL> alter system set log_archive_dest_2='service=stby_DBSID ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=300 register';
If DataGuard is enabled then this should be issued automatically.
If the primary archive destination has stopped, possibly due to a full disk, fix the problem and issue:
archive log start4.6.3 Standby Recovery Commands
The following shows a case of "manually" recovering the standby database outside of "managed recovery
mode". This might be used when performing manual time lag recovery of a standby database. This is the
scenario used by the "stby_maint" script when implementing the "restore" option as part of a 10g AS high
availability environment using DataGuard.
SQL> connect / as sysdba Connected.
SQL> alter database recover automatic standby database until cancel; alter database recover automatic standby database until cancel *
ERROR at line 1:
ORA-00279: change 158556003 generated at 01/03/2006 11:42:22 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/DBSID/arch/DBSID_1_10886.arc ORA-00280: change 158556003 for thread 1 is in sequence #10886
ORA-00278: log file '/u01/app/oracle/admin/ DBSID/arch/DBSID_1_10886.arc' no longer needed for this recovery
ORA-16145: archival for thread# 1 sequence# 10886 in progress
SQL> alter database recover cancel;
Database altered.
SQL>
5. DataGuard Setup
DataGuard can be set up manually (e.g. the DataGuard Broker) or it can be set up through Enterprise
Manager (i.e. OEM). The following will briefly detail the OEM setup:
1. The 9.2 OEM console can be started from the OEM server. It is the Oracle Management Server
(OMS) and has the OEM software configured. The command is:
oemapp console
2. Once started, login to the management server using the SYSMAN userid.
3. Discover the standby node and verify the the standby database has been discovered as well.
4. Navigate to Tools -> Database Applications -> Data Guard Manager. A new window will pop up
for the Data Guard Manager.
5. Run the "Create Configuration Wizard" to create the Data Guard site.
6. System Management
6.1 oratab File
The oratab file should be updated so that the standby database entry has an "N" for the third field. This will
insure that the "dbstart" utility does not inadvertantly start and open the database and invalidate the standby
status. An example is:
DBSID:/u01/app/oracle/product/9.2.0:N
6.2 Utilities
In order to facilitate the management of standby databases the dbcontrol utility has been modified to be
standby database aware. The standby database's oratab entry must still be set with the third field set to "N"
in order to prevent the accidental opening of the database at boot time. A new comment is supported in the
oratab file to identify the standby database to the dbcontrol utility. The format is
#STANDBY:<SID>:<OPTION>
where SID is the standby database SID and option is NOSTART, MOUNT, MANAGED, or READ.
With this comment identifying a corresponding standby database entry, dbcontrol will not open the standby
database and will only perform operations using the "standby" option. The following has been added to the
oratab file for database "stby":
#STANDBY:DBSID:MANAGED