Subject: Creating a Standby Database using RMAN (Recovery Manager) Creation Date: 27-AUG-2000 Creating a Standby Database using RMAN (Recovery Manager) Oracle8i Release 3 8.1.7.0.0 Recovery Manager Oracle8i Release 3 (8.1.7) 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 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. 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: 97010.1 Configuring Managed Recovery for a Standby Database 91570.1 8i Standby Database presentation 76760.1 NT: Oracle8i - Creating s Standby Database on Windows NT 76451.1 Oracle8i Standby Database 76450.1 Graceful Switchover and Switchback of an Oracle Standby Database 70233.1 How to create a Standby Database Oracle8i Release 3 8.1.7 Recovery Manager Guide @ <Note to Author: DO NOT DELETE the following Disclaimer> ************************************************************* This article is being delivered in Draft form and may contain errors. Please use the MetaLink "Feedback" button to advise Oracle of any issues related to this article. ************************************************************* 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 Oracle8i release 3 8.1.7 Standby Database using Recovery manager. <TITLE FOR MAIN ARTICLE TEXT> ----------------------------- Creating a Standby Database using RMAN RELATED DOCUMENTS ----------------- See section #9 in note.