Hi Friends,

Welcome you back on exciting topic, today’s session is on

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;
File created.

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;



Please do follow me and support me on,

Blog: https://mallik034.blogspot.com/
LinkedIn: https://www.linkedin.com/in/mallikarjun-ramadurg-397a751a
twitter: https://twitter.com/mallik034
instagram: https://www.instagram.com/mallik034/

Regards,
Mallikarjun Ramadurg
Mobile: +966 543796525
WhatsApp: +91 9880616848
Blog: https://mallik034.blogspot.com/
LinkedIn: https://www.linkedin.com/in/mallikarjun-ramadurg-397a751a
twitter:https://twitter.com/mallik034
Instagram:https://www.instagram.com/mallik034/
YouTube: https://www.youtube.com/channel/UC-RwDqukbxZVwYoxN-E3zRA