Steps to create physical standby database using rman backup

Here we are discussing ‘How to creating physical standby database using rman backup restore’ in oracle database.

See the following steps to create physical standby database.

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

SQL> show parameter db_recovery;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string        /data01/orale/recovery_area

db_recovery_file_dest_size           big integer 10480M

If not then we should configure it.

Step 2:- Connect Primary database and do 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.

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 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;

NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

PROD      READ WRITE           PRIMARY

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

PROD      READ WRITE           PRIMARY

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