PURPOSE
To understand and use the 9i Data Guard database features of Recovery manager
1. Summary of test environment:
The production database is "PROD".
The standby database is "STANDBY"
Testing was done on SUN hardware and SUN Solaris 2.8
2. Standby Restrictions:
Note the following restrictions involved when using the DUPLICATE
command to create a standby database:
-- The standby instance must be started but not mounted.
-- RMAN must be connected to the target database and to the auxiliary
instance. If desired, you can connect to the recovery catalog.
-- At least one auxiliary instance channel must be allocated in the RUN
block. You can allocate multiple auxiliary channels if needed.
-- Backups and copies on disk are available at the standby host with the
same path names as in the target host.
-- Backups on tape are accessible from the standby host.
-- Backups on disk are available at the standby host with the same
pathnames as in the target host.
-- If archived logs have not been backed up, then archived logs must be
available at the standby host with the same path names as in the target
host.
-- If RMAN recovers the standby database, then the checkpoint SCN of the
control file must be included in an archived redo log that is either
available at the standby site or included in an RMAN backup. For example,
assume that you create the standby control file and then immediately
afterwards archive the current log, which has a sequence of 100. You must
recover the standby database up to at least log sequence 100, or Oracle
signals ORA-1152 because the standby control file backup or copy was
taken after the point in time.
-- You cannot specify the SKIP READONLY and LOGFILE options of the
DUPLICATE command. These options are legal for a duplicate database but
illegal for the creation of the standby database.
-- You cannot use SET NEWNAME or SET AUXNAME to transform the filenames for
the online redo logs on the standby database. You cannot use the DUPLICATE
command to activate a standby database.
3. RMAN Standby database commands:
CURRENT CONTROLFILE FOR STANDBY -
makes a backup of the current control file that can be used with a standby
database. A standby control file can also be used as an ordinary control
file backup, so you can restore it in the target database if necessary.
DUPLICATE FOR STANDBY -
creates a standby database rather than a duplicate database. Specify this
keyword only when creating a standby database.
DORECOVER -
specifies that RMAN should recover the database after creating it. If you
specify an untilClause, then RMAN recovers to the specified point and
opens the database. If you do not specify this keyword, then RMAN
creates the standby database and then leaves it mounted.
4. Primary INIT.ORA setup:
db_name = PROD
instance_name = PROD
fal_client = PROD
fal_server= STANDBY
log_archive_dest_1 = 'LOCATION=/p01/app/oracle/product/9.0.1/dbs/arch'
log_archive_dest_2 = 'SERVICE=STANDBY LGWR ASYNC NOAFFIRM'
log_archive_format = %t_%s.dbf
log_archive_start = true
remote_login_passwordfile = EXCLUSIVE
service_names = PROD
Additional information on these parameters can be found in the Oracle 9i
Reference Manual Available at http://tahiti.oracle.com.
5. Standby INIT.ORA setup:
# Copy the init.ora from production to standby.
# Edit the initSTANDBY.ora to make neccessary changes:
db_name = "PROD" -- Must match the production database.
instance_name = STANDBY -- Instance identifier
lock_name_space = STANDBY -- Used when the standby or clone have the same
name as the production database being copied.
service_names = STANDBY -- Specifies the service names supported by the
instance.
fal_client = STANDBY -- Specifies the service name resolved from the
remote host to fetch archive logs to local.
fal_server = PROD -- Specifies the service name resolved on the
local host to request archive logs from remote.
db_file_name_convert = ('/p01/oradata/PROD',
'/u03/STANDBY/oradata')
log_file_name_convert = ('/p01/oradata/PROD',
'/u03/STANDBY/oradata')
# Use these parameters if you have a different filesystem structure on the
# standby. Then edit all the dump_dest and controlfile locations for the
# standby site.
standby_archive_dest = /p01/app/oracle/product/9.0.1/dbs/arch
standby_file_management = auto
REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
- Create a password file for the standby database using orapwd command:
Create a password file 'orapwSTANDBY' file in the $ORACLE_HOME/dbs
directory and the password for internal "pwd".
orapwd file=$ORACLE_HOME/dbs/orapwSTANDBY password=pwd entries=5
6. Making a backup of the target for standby:
Connect to the target and the recovery catalog using Recovery manager:
setenv ORACLE_SID=PROD
rman nocatalog target /
Recovery Manager: Release 9.0.1.1.0 - 64bit Production
(c) Copyright 2001 Oracle Corporation. All rights reserved.
connected to target database: PROD (DBID=3261937922)
using target database controlfile instead of recovery catalog
Configure channels if you have not already. See the following Note for more
information:
Note 145178.1
RMAN 9i: Automatic Channel Allocation and Configuration
RMAN> Backup Database;
Starting backup at 09-APR-02
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=7 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
input datafile fno=00001 name=/p01/oradata/PROD/system01.dbf
input datafile fno=00002 name=/p01/oradata/PROD/undotbs01.dbf
input datafile fno=00005 name=/p01/oradata/PROD/example01.dbf
input datafile fno=00006 name=/p01/oradata/PROD/indx01.dbf
input datafile fno=00008 name=/p01/oradata/PROD/users01.dbf
input datafile fno=00003 name=/p01/oradata/PROD/cwmlite01.dbf
input datafile fno=00004 name=/p01/oradata/PROD/drsys01.dbf
input datafile fno=00007 name=/p01/oradata/PROD/tools01.dbf
channel ORA_DISK_1: starting piece 1 at 09-APR-02
channel ORA_DISK_1: finished piece 1 at 09-APR-02
piece handle=/p01/oradata/PROD/dbs/04dlfl6n_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:24:25
Finished backup at 09-APR-02
RMAN> Backup current controlfile for standby;
Starting backup at 09-APR-02
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby controlfile in backupset
channel ORA_DISK_1: starting piece 1 at 09-APR-02
channel ORA_DISK_1: finished piece 1 at 09-APR-02
piece handle=/p01/oradata/PROD/dbs/05dlfml6_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:22
Finished backup at 09-APR-02
RMAN> sql "Alter System Archive Log Current";
sql statement: Alter System Archive Log Current
RMAN> Backup filesperset 10 ArchiveLog all delete input;
Starting backup at 09-APR-02
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=3 recid=3 stamp=458742477
input archive log thread=1 sequence=4 recid=4 stamp=458742492
channel ORA_DISK_1: starting piece 1 at 09-APR-02
channel ORA_DISK_1: finished piece 1 at 09-APR-02
piece handle=/p01/oradata/PROD/dbs/06dlfmmt_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/p01/oradata/PROD/dbs/arch1_3.dbf recid=3 stamp=458742477
archive log filename=/p01/oradata/PROD/dbs/arch1_4.dbf recid=4 stamp=458742492
Finished backup at 09-APR-02
RMAN> exit;
Recovery Manager complete.
7. Restoring the database to the standby location:
Copy the backup pieces to the remote server (same directory).
rman target / auxiliary sys/change_on_install@STANDBY
Recovery Manager: Release 9.0.1.1.0 - 64bit Production
(c) Copyright 2001 Oracle Corporation. All rights reserved.
connected to target database: PROD (DBID=3261937922)
connected to auxiliary database: PROD (not mounted)
RMAN> duplicate target database for standby dorecover;
Starting Duplicate Db at 09-APR-02
using target database controlfile instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=17 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 09-APR-02
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring controlfile
output filename=/u03/STANDBY/oradata/control01.ctl
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/p01/oradata/PROD/dbs/05dlfml6_1_1 tag=null params=NULL
channel ORA_AUX_DISK_1: restore complete
Finished restore at 09-APR-02
replicating controlfile
input filename=/u03/STANDBY/oradata/control01.ctl
output filename=/u03/STANDBY/oradata/control02.ctl
sql statement: alter database mount standby database
printing stored script: Memory Script
{
set until scn 266683;
set newname for datafile 1 to
"/u03/STANDBY/oradata/system01.dbf";
set newname for datafile 2 to
"/u03/STANDBY/oradata/undotbs01.dbf";
set newname for datafile 3 to
"/u03/STANDBY/oradata/cwmlite01.dbf";
set newname for datafile 4 to
"/u03/STANDBY/oradata/drsys01.dbf";
set newname for datafile 5 to
"/u03/STANDBY/oradata/example01.dbf";
set newname for datafile 6 to
"/u03/STANDBY/oradata/indx01.dbf";
set newname for datafile 7 to
"/u03/STANDBY/oradata/tools01.dbf";
set newname for datafile 8 to
"/u03/STANDBY/oradata/users01.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
Starting restore at 09-APR-02
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u03/STANDBY/oradata/system01.dbf
restoring datafile 00002 to /u03/STANDBY/oradata/undotbs01.dbf
restoring datafile 00003 to /u03/STANDBY/oradata/cwmlite01.dbf
restoring datafile 00004 to /u03/STANDBY/oradata/drsys01.dbf
restoring datafile 00005 to /u03/STANDBY/oradata/example01.dbf
restoring datafile 00006 to /u03/STANDBY/oradata/indx01.dbf
restoring datafile 00007 to /u03/STANDBY/oradata/tools01.dbf
restoring datafile 00008 to /u03/STANDBY/oradata/users01.dbf
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/p01/oradata/PROD/dbs/04dlfl6n_1_1 tag=null params=NULL
channel ORA_AUX_DISK_1: restore complete
Finished restore at 09-APR-02
printing stored script: Memory Script
{
switch clone datafile all;
}
executing script: Memory Script
printing stored script: Memory Script
{
set until scn 266683;
recover
standby
clone database
;
}
executing script: Memory Script
executing command: SET until clause
Starting recover at 09-APR-02
using channel ORA_AUX_DISK_1
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=3
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=4
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/p01/oradata/PROD/06dlfmmt_1_1 tag=null params=NULL
channel ORA_AUX_DISK_1: restore complete
archive log filename=/u03/STANDBY/oradata/arch/1_3.dbf thread=1 sequence=3
archive log filename=/u03/STANDBY/oradata/arch/1_4.dbf thread=1 sequence=4
media recovery complete
Finished recover at 09-APR-02
Finished Duplicate Db at 09-APR-02
RMAN> exit;
8. Put the Standby in Managed Recovery Mode:
On the Standby:
SQL> recover standby database;
# Recover all available logs
SQL> alter database recover managed standby database disconnect;
9. References:
Recovery Manager and Data Guard Documentation at http://tahiti.oracle.com
Note 118409.1 Creating a Standby Database using RMAN (Recovery Manager)
Note 145178.1 RMAN 9i Automatic Channel Allocation and Configuration
Note.149286.1 How To Create a Standby Database in Oracle8i
