You can use the Recovery Manager DUPLICATE TARGET DATABASE FOR STANDBY command to create a standby database.
RMAN automates the following steps of the creation procedure:
Restores the standby control file.
Restores the primary datafile backups and copies.
Optionally, RMAN recovers the standby database (after the control file has been mounted) up to the specified time or to the latest archived redo log generated.
RMAN leaves the database mounted so that the user can activate it, place it in manual or managed recovery mode, or open it in read-only mode.
After the standby database is created, RMAN can back up the standby database and archived redo logs as part of your backup strategy. These standby backups are fully interchangeable with primary backups.
In other words, you can restore a backup of a standby datafile to the primary database, and you can restore a backup of a primary datafile to the standby database.
Step-by-Step Stand by Database Configuration:
Step1: Configure Listener in Production Server and Standby Server.
TIPS: You should try to Create Listener (Standby) by using Net Configuration Assistant on Standby Server.
TIPS: assume Listener already configure with PROD name on Primary Node. If Listener not configured on Primery Node , You Should Create Listener by using Net Configuration Assistant on Primary Server.
Step2: Configure TNSNAMES.ORA in Production Server and Standby Server. following TNSNAMES.ORA entry on Production Database and Standby Database
# Connection string for Primary Instance.
PROD =
(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Production IP)(PORT = 1521)) )
(CONNECT_DATA =
(SERVICE_NAME = PROD) )
)
# Connecting string for Standby Instance STANDBY =
(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY IP)(PORT = 1521)) )
(CONNECT_DATA =
(SERVICE_NAME = PROD) )
)
Step3: Put your production database in Archive Log mode if your database not running in Archive log mode add following entries in init.ora file in Production Server.
LOG_ARCHIVE_START=TRUE
LOG_ARCHIVE_DEST_1='LOCATION=C:\oracle\database\archive MANDATORY REOPEN=30' LOG_ARCHIVE_DEST_2='SERVICE=STANDBY REOPEN=300'
LOG_ARCHIVE_DEST_STATE_1=enable LOG_ARCHIVE_DEST_STATE_2=enable LOG_ARCHIVE_FORMAT=ARC%S.arc REMOTE_ARCHIVE_ENABLE=true STANDBY_FILE_MANAGEMENT=AUTO
STANDBY_ARCHIVE_DEST = 'C:\standby\archive '
# db_file_name_convert: do not need; same directory structure
# log_file_name_convert: do not need; same directory structure
Step4 : Configure RMAN in Production Instance if not configured earlier.
Example:
1. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oraprod/rmanbkp/%F';
2. CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/home/oraprod/rmanbkp/%U.bak' MAX PIECESIZE 4 G;
3. CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/home/oraprod/rmanbkp/%U.bak' MAX PIECESIZE 4 G;
Step5 : Take a full valied backup of Production instance RMAN> backup database plus archivelog;
Step6 : go to Standby machine and Create Service for standby instance Step7 : create a standby controlfile on production Machine
RMAN> backup current controlfile for standby format='c:\rman_backup\stby_cfile.%U';
Step8 : Record last log sequance SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#) ---
100
Step8 : Backup new archive logs RMAN>backup archivelog all;
Step9 : Make the RMAN Backups Available to Standby Server
Step10 : On the both system, the same directory structure was set-up Step11 : Create init.ora file for standby database.
Copy init.ora file from Production Server to Stand by Server in Database folder in oracle home directory and add following entries:
LOG_ARCHIVE_START = TRUE
LOG_ARCHIVE_DEST_1 = 'LOCATION=c:\oracle\database\archive MANDATORY' LOG_ARCHIVE_FORMAT = arch%s.arc
REMOTE_ARCHIVE_ENABLE = true STANDBY_FILE_MANAGEMENT = AUTO LOG_ARCHIVE_MIN_SUCCEED_DEST=1
STANDBY_ARCHIVE_DEST = 'C:\standby\archive '
fal_server = FAL fal_client = STANDBY
# db_file_name_convert: do not need; same directory structure
# log_file_name_convert: do not need; same directory structure
Note: Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the physical standby database, some
modifications need to be made.
Step: 12 Start Physical standby database
Start up the stand by database using following commands C:\>set oracle_sid=PROD
C:\>sqlplus /nolog
SQL> conn sys/prod as sysdba Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 135338868 bytes Fixed Size 453492 bytes
Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes
Step13 : Go to the Standby server and connect RMAN Run the following:
CMD> rman target sys/change_on_install@prod_conn_string RMAN > connect auxiliary sys/change_on_install
Step14 : The following RUN block can be used to fully duplicate the target database from the latest full backup. This will create the standby database:
run {
# Set the last log sequence number set until sequence = 100 thread = 1;
# Allocate the channel for the duplicate work allocate auxiliary channel ch1 type disk;
# Duplicate the database to ORA920
duplicate target database for standby dorecover nofilenamecheck ; }
RMAN> exit
Step15 : Put the Standby in Managed recovery Mode On the standby database, run the following:
SQL> sqlplus "/ as sysdba"
SQL> recover standby database;
SQL> alter database recover managed standby database disconnect;
Database altered.
Error and Solution:
Error 1: RMAN-06059: expected archived log not found, lost of archived log compromises recoverability Solution: RMAN> change archivelog all crosscheck;