Creating a Standby Database using RMAN (Recovery Manager) [ID 118409.1]
***Checked for relevance on 29-Mar-2012***
PURPOSE
-------
To understand and use the standby database features of Recovery manager
SCOPE & APPLICATION
-------------------
All users of RMAN that have need of creating or refreshing an Standby Database using Recovery manager.
Creating a Standby Database using RMAN (Recovery Manager)
Topics discussed:
==============
1. Summary of test environment.
2. Duplicate for Standby restrictions.
3. RMAN Standby database commands.
4. Standby INIT.ORA setup
5. Example of making an RMAN backup for standby.
6. Restoring the database to the standby location.
7. RMAN scripts used for backup and restore.
8. Recovery Catalog and V$ view changes.
9. Notes and references on standby databases.
1. Summary of test environment:
=========================
The production database is "M817".
The Recovery Catalog is "RC817"
The standby database is "SB817"
Testing was done on SUN hardware and SUN Solaris 2.6
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:
==================================
VALIDATE - causes RMAN to scan the specified files and verify their contents.
This operation creates no output files. Use this command
periodically to check for physical and logical errors in
database files.
CURRENT CONTROLFILE FOR STANDBY -
makes a backup of the current control file that can be used with a standby
database. A standby control file is ONLY restored during the DUPLICTE FOR STANDBY
and cannot be used for a restore of a normal controlfile.
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. Standby INIT.ORA setup:
==========================
# Copy the init.ora from production to standby.
# Edit the initSB817.ora to make neccessary changes:
db_name = "M817" -- Must match the production database.
instance_name = SB817 -- Instance identifier
lock_name_space = SB817 -- Used when the standby or clone have the same
name as the production database being copied.
service_names = SB817 -- Specifies the service names supported by the
instance.
# The logfile parameter is not a part of "duplicate for standby" so we must
# specify the directory for the logfiles. They are created with the same name
# as the production using LOG_FILE_NAME_CONVERT parameter.
# Convert the datafile path in the controlfile to the new path.
# Only supports one file path. Use set newname for mutliple file paths.
# db_file_name_convert = -- (Used the set newname option in script.)
# Convert the online log file path to the standby path.
# Convert only support one path. Use "alter database rename file
# '/path/log' to ..., to change the online log file path after opening
# the standby.
log_file_name_convert = ("/beta/app/oracle/oradata/M817",
"/beta/app/oracle/oradata/SB817")
# Then edit all the dump_dest and controlfile locations for the standby site.
# Create a password file for the standby database using ORAPWD:
# Create a password file 'orapwSB817' file in the $ORACLE_HOME/dbs
# directory and the password for internal "pwd".
Orapwd file=?/dbs/orapwSB817 password=pwd entries=5
# See the Oracle Administrators Guide for more information about password
# files.
# For remote internal connections the init.ora should have:
REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
5. Making a backup of the target for standby:
=============================================
Connect to the target and the recovery catalog using Recovery manager:
Note: Use of the trace commands allows you to create an output file and
still have the server output displayed on the screen also.
setenv ORACLE_SID=M817
rman target / catalog rman/rman@RC817 trace backup.log
Run:
run {
allocate channel d1 type disk;
allocate channel d2 type disk;
setlimit channel d1 kbytes 2097150;
setlimit channel d2 kbytes 2097150;
backup incremental level 0 format '/beta/home/marrocha/backup/df_%U' database
include current controlfile for standby;
sql "alter system archive log current";
backup
archivelog all format '/beta/home/marrocha/backup/al_%U' delete input;
}
Note: This will create a normal level 0 backup of the production database
and will also create a backup of the controlfile as standby. If the
standby backup location is not given RMAN will use "$ORACLE_HOME/dbs"
to store the standby_cf.
6. Restoring the database to the standby location:
==================================================
Note: The output is included to give example of a complete successful
restore and recovery.
The standby database is not opened but recovery is as current as production.
All that is left is to setup for managed mode and set the archivelogs to be
transfered to the standby server using RAF via Net8.
% rman target / catalog rman/rman@RC817 auxiliary internal/pwd@SB817 trace
dup_sb.log
Recovery Manager: Release 8.1.7.0.0 - Production
RMAN-06005: connected to target database: M817 (DBID=2730131591)
RMAN-06008: connected to recovery catalog database
RMAN-06020: connected to auxiliary database
RMAN>
RMAN>
RMAN> run {
2> # set command id to 'DUPAUX';
3> # set until time "to_date('22-JUL-2000 15:34:38,'DD-MON-YYYY HH24:MI:SS')";
4> allocate auxiliary channel dup1 type disk;
5> allocate auxiliary channel dup2 type disk;
6> set newname for datafile 1 to '/beta/app/oracle/oradata/SB817/system01.dbf';
7> set newname for datafile 2 to '/beta/app/oracle/oradata/SB817/tools01.dbf';
8> set newname for datafile 3 to '/beta/app/oracle/oradata/SB817/rbs01.dbf';
9> set newname for datafile 4 to '/beta/app/oracle/oradata/SB817/temp01.dbf';
10> set newname for datafile 5 to '/beta/app/oracle/oradata/SB817/users01.dbf';
11> set newname for datafile 6 to '/beta/app/oracle/oradata/SB817/indx01.dbf';
12> duplicate target database for standby
13> dorecover;
14> }
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: dup1
RMAN-08500: channel dup1: sid=13 devtype=DISK
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: dup2
RMAN-08500: channel dup2: sid=14 devtype=DISK
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: Duplicate Db
RMAN-03025: performing implicit partial resync of recovery catalog
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-03027: 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';
}
RMAN-03021: executing script. Memory Script.
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel dup1: starting datafile backupset restore
RMAN-08502: set_count=27 set_stamp=403652844 creation_time=22-JUL-00
RMAN-08021: channel dup1: restoring controlfile
RMAN-08505: output filename=/beta/app/oracle/oradata/SB817/control01.ctl
RMAN-08023: channel dup1: restored backup piece 1
RMAN-08511: piece handle=
/beta/home/marrocha/backup/df_0rc0ug7c_1_1 tag=null params=NULL
RMAN-08024: channel dup1: restore complete
RMAN-03022: compiling command: replicate
RMAN-03023: executing command: replicate
RMAN-08058: replicating controlfile
RMAN-08506: input filename=/beta/app/oracle/oradata/SB817/control01.ctl
RMAN-08505: output filename=/beta/app/oracle/oradata/SB817/control02.ctl
RMAN-08505: output filename=/beta/app/oracle/oradata/SB817/control03.ctl
RMAN-03022: compiling command: sql
RMAN-06162: sql statement: alter database mount standby database
RMAN-03023: executing command: sql
RMAN-03027: printing stored script. Memory Script.
{
set until scn 230808;
set newname for datafile 1 to
'/beta/app/oracle/oradata/SB817/system01.dbf';
set newname for datafile 2 to
'/beta/app/oracle/oradata/SB817/tools01.dbf';
set newname for datafile 3 to
'/beta/app/oracle/oradata/SB817/rbs01.dbf';
set newname for datafile 4 to
'/beta/app/oracle/oradata/SB817/temp01.dbf';
set newname for datafile 5 to
'/beta/app/oracle/oradata/SB817/users01.dbf';
set newname for datafile 6 to
'/beta/app/oracle/oradata/SB817/indx01.dbf';
restore
check readonly
clone database
;
}
RMAN-03021: executing script. Memory Script.
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: set
RMAN-03022: compiling command: restore
RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel dup1: starting datafile backupset restore
RMAN-08502: set_count=27 set_stamp=403652844 creation_time=22-JUL-00
RMAN-08089: channel dup1: specifying datafile(s) to restore from backup set
RMAN-08523: restoring datafile 00001 to
/beta/app/oracle/oradata/SB817/system01.dbf
RMAN-08523: restoring datafile 00004 to
/beta/app/oracle/oradata/SB817/temp01.dbf
RMAN-08523: restoring datafile 00005 to
/beta/app/oracle/oradata/SB817/users01.dbf
RMAN-08016: channel dup2: starting datafile backupset restore
RMAN-08502: set_count=28 set_stamp=403652845 creation_time=22-JUL-00
RMAN-08089: channel dup2: specifying datafile(s) to restore from backup set
RMAN-08523: restoring datafile 00002 to
/beta/app/oracle/oradata/SB817/tools01.dbf
RMAN-08523: restoring datafile 00003 to
/beta/app/oracle/oradata/SB817/rbs01.dbf
RMAN-08523: restoring datafile 00006 to
/beta/app/oracle/oradata/SB817/indx01.dbf
RMAN-08023: channel dup1: restored backup piece 1
RMAN-08511: piece handle=
/beta/home/marrocha/backup/df_0rc0ug7c_1_1 tag=null params=NULL
RMAN-08024: channel dup1: restore complete
RMAN-08023: channel dup2: restored backup piece 1
RMAN-08511: piece handle=
/beta/home/marrocha/backup/df_0sc0ug7d_1_1 tag=null params=NULL
RMAN-08024: channel dup2: restore complete
RMAN-03027: printing stored script. Memory Script.
{
switch clone datafile all;
}
RMAN-03021: executing script. Memory Script.
RMAN-03022: compiling command: switch
RMAN-03023: executing command: switch
RMAN-08015: datafile 1 switched to datafile copy
RMAN-08507: input datafilecopy recid=7 stamp=403814852
filename=/beta/app/oracle/oradata/SB817/system01.dbf
RMAN-08015: datafile 2 switched to datafile copy
RMAN-08507: input datafilecopy recid=8 stamp=403814852
filename=/beta/app/oracle/oradata/SB817/tools01.dbf
RMAN-08015: datafile 3 switched to datafile copy
RMAN-08507: input datafilecopy recid=9 stamp=403814852
filename=/beta/app/oracle/oradata/SB817/rbs01.dbf
RMAN-08015: datafile 4 switched to datafile copy
RMAN-08507: input datafilecopy recid=10 stamp=403814852
filename=/beta/app/oracle/oradata/SB817/temp01.dbf
RMAN-08015: datafile 5 switched to datafile copy
RMAN-08507: input datafilecopy recid=11 stamp=403814852
filename=/beta/app/oracle/oradata/SB817/users01.dbf
RMAN-08015: datafile 6 switched to datafile copy
RMAN-08507: input datafilecopy recid=12 stamp=403814853
filename=/beta/app/oracle/oradata/SB817/indx01.dbf
RMAN-03027: printing stored script. Memory Script.
{
set until scn 230808;
recover
standby
clone database
check readonly
;
}
RMAN-03021: executing script. Memory Script.
RMAN-03022: compiling command: set
RMAN-03022: compiling command: recover
RMAN-03022: compiling command: recover(1)
RMAN-03022: compiling command: recover(2)
RMAN-03022: compiling command: recover(3)
RMAN-03023: executing command: recover(3)
RMAN-08054: starting media recovery
RMAN-03022: compiling command: recover(4)
RMAN-06050: archivelog thread 1 sequence 1796 is already on disk as file
/beta/app/oracle/admin/M817/arch/arch_1_1796.arc
RMAN-06050: archivelog thread 1 sequence 1797 is already on disk as file
/beta/app/oracle/admin/M817/arch/arch_1_1797.arc
RMAN-06050: archivelog thread 1 sequence 1798 is already on disk as file
/beta/app/oracle/admin/M817/arch/arch_1_1798.arc
RMAN-03023: executing command: recover(4)
RMAN-08017: channel dup1: starting archivelog restore to default destination
RMAN-08022: channel dup1: restoring archivelog
RMAN-08510: archivelog thread=1 sequence=1795
RMAN-08023: channel dup1: restored backup piece 1
RMAN-08511: piece handle=
/beta/home/marrocha/backup/al_0tc0ugc6_1_1 tag=null params=NULL
RMAN-08024: channel dup1: restore complete
RMAN-08515: archivelog filename=
/beta/app/oracle/product/817_B/dbs/arch1_1795.dbf thread=1 sequence=1795
RMAN-08515: archivelog filename=
/beta/app/oracle/admin/M817/arch/arch_1_1796.arc thread=1 sequence=1796
RMAN-08515: archivelog filename=
/beta/app/oracle/admin/M817/arch/arch_1_1797.arc thread=1 sequence=1797
RMAN-08515: archivelog filename=
/beta/app/oracle/admin/M817/arch/arch_1_1798.arc thread=1 sequence=1798
RMAN-08055: media recovery complete
RMAN-08031: released channel: dup1
RMAN-08031: released channel: dup2
RMAN> **end-of-file**
RMAN>
Recovery Manager complete.
Summary:
--------
RMAN connected to th recovery catalog, target, and standby could determine
the most recent archivelog and if it needs to get it from a backup set. In
this case only archivelog sequence arch_1_1795.arc was restored from backup
and all others required for recovery are already in the log_archive_dest of
the target database. Since both the target and the standby are on the same
server the logs were directly applied from the target M817 log_archive_dest.
7. RMAN scripts used for backup and restore:
============================================
B_ClosedDB.rcv - Backup Closed Database (cold backup):
------------------------------------------------------------------------------
shutdown immediate;
startup mount pfile=/beta/app/oracle/product/817_B/dbs/initM817.ora
run {
set command id to 'RMAN';
allocate channel d1 type disk;
allocate channel d2 type disk;
setlimit channel d1 kbytes 2097150;
setlimit channel d2 kbytes 2097150;
backup full format '/beta/home/marrocha/backup/df_%U' database;
backup
filesperset 10
format '/beta/home/marrocha/backup/arc_%U'
archivelog all delete input;
}
shutdown;
startup pfile=/beta/app/oracle/product/817_B/dbs/initM817.ora
B_M817_L0.rcv - Backup M817 database incremental level 0:
------------------------------------------------------------------------------
run {
allocate channel d1 type disk;
allocate channel d2 type disk;
setlimit channel d1 kbytes 2097150;
setlimit channel d2 kbytes 2097150;
backup incremental level 0 format '/beta/home/marrocha/backup/df_%U' database
include current controlfile for standby;
sql "alter system archive log current";
backup
archivelog all format '/beta/home/marrocha/backup/al_%U' delete input;
}
B_Validate.rcv - Backup Database Validation:
------------------------------------------------------------------------------
# Validates the datafiles to be backed up.
# Cannot use the "set maxcorrupt or proxy" options with validate.
run {
allocate channel d1 type disk;
backup validate database
archivelog all;
}
R_dup_sb.rcv - Restore Duplicate for Standby:
------------------------------------------------------------------------------
run {
# set command id to 'DUPAUX';
# set until time "to_date('22-JUL-2000 15:34:38,'DD-MON-YYYY HH24:MI:SS')";
allocate auxiliary channel sb1 type disk;
allocate auxiliary channel sb2 type disk;
set newname for datafile 1 to '/beta/app/oracle/oradata/SB817/system01.dbf';
set newname for datafile 2 to '/beta/app/oracle/oradata/SB817/tools01.dbf';
set newname for datafile 3 to '/beta/app/oracle/oradata/SB817/rbs01.dbf';
set newname for datafile 4 to '/beta/app/oracle/oradata/SB817/temp01.dbf';
set newname for datafile 5 to '/beta/app/oracle/oradata/SB817/users01.dbf';
set newname for datafile 6 to '/beta/app/oracle/oradata/SB817/indx01.dbf';
duplicate target database for standby
dorecover;
}
8. Recovery Catalog and V$ view changes:
========================================
View New Column Description
--------------------- -------------------- ---------------------------------
RC_ARCHIVED_LOG IS_STANDBY The location of archived log: Y
(located on the standby database
host) or N (located on the primary
database). A standby log cannot
be used for recovery of the primary
unless it is first backed up by
RMAN
RC_BACKUP_CONTROLFILE CONTROLFILE_TYPE The type of control file backup: B
(normal backup) or S (standby
backup).
RC_BACKUP_SET CONTROLFILE_ Possible values are NONE
INCLUDED (backup set does not include a
backup control file), BACKUP
(backup set includes a normal
backup control file), and STANDBY
(backup set includes a standby
control file).
RC_CONTROLFILE_COPY CONTROLFILE_TYPE The type of control file copy: B
(normal copy)or S (standby copy).
V$ARCHIVED_LOG CREATOR The database process that
generated the archived log:
ARCH, FGRD (foreground process),
LGWR, or RMAN.
9. Notes and references on standby databases:
=============================================
For more information and examples of setting up the network files to
configure a standby database for Managed Standby mode please refer to
WebIV notes:
Note:91570.1 8i Standby Database presentation
Note:70233.1 How to Create a Oracle 8i Standby Database
Note:76451.1 Oracle8i Standby Database
Note:76450.1 Graceful Switchover and Switchback of an Oracle Standby Database
Oracle Recovery Manager Guide
The Oracle documentation is available online at
http://www.oracle.com/technetwork/indexes/documentation/index.html
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-743287/，如需轉載，請註明出處，否則將追究法律責任。