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;
