Create Physical Standby Database using RMAN Backup Restore

In this article, we will see Physical Standby database creation and configuration using RMAN backup and restore. 

Step 1: Connect to the Primary database and check if recovery area

show parameter db_recovery

Step 2: Connect to RMAN and take backup

rman target /

backup database plus archivelog;

Step 3: Create standby control file from the primary database and create pfile from spfile.

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/DEVDRDB.ctl';

CREATE PFILE FROM SPFILE;

Step 4: Change following parameter in pfile.

CHANGE FOLLOWING PARAMETER IN PFILE

*.db_unique_name='DEVDRDB'

*.fal_server='DEVDB'

*.log_archive_dest_2='SERVICE=DEVDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DEVDB'

Step 5: Connect to Standby database server and create necessary directories.

mkdir -p /u01/app/oracle/oradata/DEVDRDB/datafile

mkdir -p /u01/app/oracle/oradata/DEVDRDB/controlfile

mkdir -p /u01/app/oracle/fast_recovery_area/DEVDRDB/controlfile

mkdir -p /u01/app/oracle/oradata/DEVDRDB/onlinelog

mkdir -p /u01/app/oracle/fast_recovery_area/DEVDRDB/onlinelog

Step 6: Transfer standby control file to standby database and rename it as defined in control_files initialization parameter.

Step 7: Transfer backup to Standby database server

Step 8: Transfer pfile to standby database

Step 9: Transfer password file to standby database.

Step 10: Connect to Standby database and create spfile from pfile.

 sqlplus / as sysdba 

create spfile from pfile;

Step 11: In standby database connect to RMAN and start the database in mount stage.

rman target /

startup mount

Step 12: Restore database using restore database command.

restore database;

Step 13: Connect to SQL prompt of standby database and create redo log files.

alter system set standby_file_management=manual;

alter database add logfile ('/u01/app/oracle/oradata/DEVDRDB/onlinelog/redo01.log') size 512m;

alter database add logfile ('/u01/app/oracle/oradata/DEVDRDB/onlinelog/redo02.log') size 512m;

alter database add logfile ('/u01/app/oracle/oradata/DEVDRDB/onlinelog/redo03.log') size 512m;

alter database add logfile ('/u01/app/oracle/fast_recovery_area/DEVDRDB/onlinelog/redo01.log') size 512m;

alter database add logfile ('/u01/app/oracle/fast_recovery_area/DEVDRDB/onlinelog/redo02.log') size 512m;

alter database add logfile ('/u01/app/oracle/fast_recovery_area/DEVDRDB/onlinelog/redo03.log') size 512m;

alter system set standby_file_management=AUTO;

Check Standby database synchronization with the Primary database

Step 14: Connect to the Primary database and check the role of the primary database.

select name,open_mode,database_role from v$database;

Step 15: Connect to Standby database and check the role of the database.

select name,open_mode,database_role from v$database;

Step 16: Check maximum archive log sequence from the primary.

select max(sequence#) from v$thread;

Step 17: Check maximum archive log sequence from standby database.

select max(sequence#) from v$thread;

Step 18: Start the MRP process at standby side.

alter database recover managed standby database disconnect from session;

alter database recover managed standby database cancel;

Step 19: Switch logfile at primary database

alter system switch logfile;

Step 20: Check again max archive log sequence at the standby database.

select max(sequence#) from v$thread;

 

Regards,

Mallik