Steps to create physical standby database using rman backup

by

Introduction: –

Here we are discussing ‘How to create physical standby database using rman backup restore’ in oracle database. We will follow the simple steps to create standby database.

Steps to create physical standby database.

Step 1:- Connect to primary database and check recovery area.

 
SQL> show parameter db_recovery;
 
NAMETYPEVALUE
———————————— ———– ——————————
db_recovery_file_deststring/data01/orale/recovery_area
db_recovery_file_dest_sizebig integer 10480M
 
If not, then we should configure it.
 
 

Step 2:- Connect Primary database and perform the consistent backup.

 
See another post for more details…
 
 

Step 3:- Create standby control file and pfile from primary database.

 
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/DATA02/ORACLE/STD_CTRL.ctl’;
SQL> CREATE PFILE FROM SPFILE;
 
 
 

Step 4:- Make changes in pfile.

 
 
Make the necessary changes to PRIMARY.
 
DB_NAME=PROD
DB_UNIQUE_NAME=PROD
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PROD,STDBY)’
LOG_ARCHIVE_DEST_2= ‘SERVICE= STDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= STDBY’
LOG_ARCHIVE_DEST_STATE_2=DEFER
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER= STDBY
FAL_CLIENT= PROD
DB_FILE_NAME_CONVERT=’STDBY ‘,’ PROD’ (ASMDISKGROUP NAME) –>in case of ASM
LOG_FILE_NAME_CONVERT=’ STDBY’,’ PROD’ (ASMDISKGROUP NAME)
STANDBY_FILE_MANAGEMENT=AUTO
 
 
Make the necessary changes to STANDBY database in oracle.
 
DB_NAME=PROD
DB_UNIQUE_NAME=STDBY
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PROD,STDBY)’
CONTROL_FILES=’+DATA’,’+FRA’
DB_FILE_NAME_CONVERT=’PROD’,’STDBY’  (ASMDISKGROUP NAME)
LOG_FILE_NAME_CONVERT=’PROD’,’STDBY’ (ASMDISKGROUP NAME)
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_2= ‘SERVICE=PROD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=PROD
FAL_CLIENT=STDBY
 
 

Step 5:- Copy the RMAN Backup from Primary to Standby.

 
    1.Copy the valid backup in backup location.
 
    2.Copy the pfile (init.ora) to standby location.
 
    3.Copy Recreate the password file using orapwd utility.
 
 

Step 6: – Make network connectivity between Primary and Standby database.

 
Do the changes on PRIMARY (TNSNAMES.ORA)
 
STDBY=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.11.15) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STDBY)
))
 
Same do the change on Standby instance.
 
PROD=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.11.14) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
))
 
 
After that we should check the connectivity using tnsping utility.
 
$ tnsping PROD/STDBY –à vice versa.
 
 
 

Step 7:- start the standby database in nomount using pfile.

 
$sqlplus / as sysdba
 
SQL> startup nomount;
 
Create spfile from pfile;
 
SQL> create spfile from pfile;
 
 

Step 8:- Connect Standby database in oracle using RMAN to restore and recovery.

Startup the standby database in nomount and restore the standby controlfile that was taken in step 3.

$rman target /

RMAN> restore standby controlfile from ‘/data01/STD_CTRL.ctl’;

RMAN> restore database;
 
See the more on restoration.

SQL> alter database mount;

Step 9:- Create redo log file on Standby database.

 
SQL> alter system set standby_file_management=manual;
 
System altered.
 
 
SQL> alter database add logfile (‘/data01/oracle/oradata/testdb/redo01.log’) size 512m;
 
Database altered.
 
SQL> alter database add logfile (‘/data01/oracle/oradata/testdb/redo02.log’) size 512m;
 
Database altered.
 
SQL> alter database add logfile (‘/data01/oracle/oradata/testdb/redo03.log’) size 512m;
 
Database altered.
 
 
SQL> alter system set standby_file_management=AUTO;
 
System altered.
 
 
 
Now we will check the synchronization of primary and standby database.
 
 
 

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

SQL> select name,open_mode,database_role from v$database;
 
NAMEOPEN_MODEDATABASE_ROLE
——— ——————– —————-
PRODREAD WRITEPRIMARY
 
SQL> select name,open_mode,database_role from v$database;
 
NAMEOPEN_MODEDATABASE_ROLE
——— ——————– —————-
PRODREAD WRITEPRIMARY
 
 
Check the maximum archive log on primary database.
 
SQL> select max(sequence#) from v$thread;
 
MAX(SEQUENCE#)
————–
153
 
 
Check maximum archive log sequence from standby database.
 
SQL> select max(sequence#) from v$thread;
 
MAX(SEQUENCE#)
————–
153
 
 

Step 11: – Start the MRP process.

 
 
In PRIMARY
 
 SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
 
 
In STANDBY
 
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
 
 

Switch logfile at primary database :

 
 
SQL> alter system switch logfile;
 
 
Verify the new redo data was archived on the standby database. On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:
 
 
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
 
 
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
 
 
Again, check the archive log sequence on standby database.
 
 
SQL> select max(sequence#) from v$thread;
 
MAX(SEQUENCE#)
————–
154
 

You may also check archive log gap in standby database.

SQL>select destination as Standby_DB,TYPE,ARCHIVED_THREAD#,APPLIED_SEQ#,ARCHIVED_SEQ#,GAP_STATUS from v$archive_dest_status where DEST_ID=2;

1 thought on “Steps to create physical standby database using rman backup”

Leave a Comment