DR Configuration

The following are the list of tasks for DR configuration.

Primary Server

1. Enable Force Logging
2. Enable Archive log 
3. Set remote_login_passwordfile=EXCLUSIVE
4. Adding parameters in pfile
5. Adding parameters in tnsnames.ora file
6. TNSPING check
7. Create Password file
8. RMAN backup for stand-by Instance

Stand-by Server

1. OH Creation
2. Set remote_login_passwordfile=EXCLUSIVE
3. Adding parameters in pfile
4. Adding parameters in tnsnames.ora file
5. TNSPING check
6. Copy Password file
7. Create NFS mount point
8. Duplicate database for standby using RMAN

Primary Server

Below steps should be performed on Primary Instance.

Enable Force Logging

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> select force_logging from v$database;

FOR
---
YES

Enable Archive Log

You can follow the steps mentioned in this post to enable archive log in your database. Change the directories as per your environment. Click Here


Set remote_login_passwordfile=EXCLUSIVE
sqlplus '/as sysdba'

SQL> alter system set remote_login_passwordfile='EXCLUSIVE' scope = both;

Adding parameters in pfile

Edit your pfile and comment the below line
log_archive_dest_1 = 'LOCATION=/r12tst/R12TEST/ebs/db/datafiles/archive'

Once the above line is committed add the following at the end of the pfile
db_file_name_convert='/proddb/gnpoc/ebs/db/proddata','/r12tst/R12TEST/ebs/db/dat
afiles'
log_file_name_convert='/proddb/gnpoc/ebs/db/proddata','/r12tst/R12TEST/ebs/db/da
tafiles'
log_archive_dest_1='LOCATION=/r12tst/R12TEST/ebs/db/proddata/archive'
log_archive_dest_2='SERVICE=stdby LGWR'

Adding parameters in tnsnames.ora file

Copy the lines from tnsnames.ora file of the Stand-by instance, paste and save.

TEST=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=<hostname.domain>)(PORT=1571))
            (CONNECT_DATA=
                (SERVICE_NAME=TEST)
                (INSTANCE_NAME=TEST)
            )
        )

stdby = (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=<hostname.domain>)(PORT=1571))
                (CONNECT_DATA=(SID=TEST))
            )

TNSPING check

Now check ping to check the connectivity between our Primary and Stand-by servers.

$ tnsping stdby

TNS Ping Utility for HPUX: Version 11.2.0.4.0 - Production on 09-FEB-2018 12:53:03

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

/r12tst/R12TEST/ebs/db/tech_st/11.2.0/network/admin/TEST_erp12/sqlnet_ifile.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=<hostname.domain>)(PORT=1571)) (CONNECT_DATA=(SID=TEST)))

OK (50 msec)

$

Create Password file

$ pwd

/r12tst/R12TEST/ebs/db/tech_st/11.2.0/dbs

$

$ ls ora*

orapwGNPOCDB

$ mv orapwGNPOCDB orapwGNPOCDB_OLD

$ echo $ORACLE_SID

TEST

$

$ orapwd file=orapwTEST password=welcome 

RMAN backup for stand-by Instance

Configure RMAN and take a backup for Stand-by Instance from your Primary.

1. Configuring RMAN - Click Here

2. Take Backup using the below commands

RMAN > backup database include current controlfile for standby plus archivelog;

for compressed backup, use the below command

RMAN> backup as compressed backupset database include current controlfile for standby plus archivelog;

Stand-by Server

Below steps should be performed on Stand-by Instance.

OH Creation

Run pre-clone on Primary Instance and copy the OH to Stand-by server.

Run adcfgclone with dbTechStack option and create the OH with the same directory structure as the Primary instance.

cd $ORACLE_HOME/appsutil/scripts


Set remote_login_passwordfile=EXCLUSIVE

sqlplus '/as sysdba'

SQL> alter system set remote_login_passwordfile='EXCLUSIVE' scope = both;

Adding parameters in pfile

Edit your pfile and comment the below line
log_archive_dest_1='LOCATION=/DRDB/ebsdr/db/proddata/archive'

Once the above line is committed add the following at the end of the pfile
db_file_name_convert='/r12tst/R12TEST/ebs/db/proddata/','/DRDB/ebsdr/db/proddata
/'
log_file_name_convert='/r12tst/R12TEST/ebs/db/proddata/','/DRDB/ebsdr/db/proddat
a/'
standby_file_management=auto
fal_server=prdb
fal_client=stdby
log_archive_dest_1='LOCATION=/DRDB/ebsdr/db/proddata/archive'
log_archive_dest_2='SERVICE=stdby LGWR'

Adding parameters in tnsnames.ora file

Copy the lines from tnsnames.ora file of the Primary instance, paste and save.

TEST = (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=<hostname.domain>)(PORT=1571))
                (CONNECT_DATA=(SID=TEST))
            )

prdb =
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=<hostname.domain>)(PORT=1571))
            (CONNECT_DATA=
                (SERVICE_NAME=TEST)
                (INSTANCE_NAME=TEST)
            )
        )

TNSPING check

$ tnsping prdb

TNS Ping Utility for HPUX: Version 11.2.0.4.0 - Production on 09-FEB-2018 13:09:36

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=<hostname.domain>)(PORT=1571)) (CONNECT_DATA= (SERVICE_NAME=TEST) (INSTANCE_NAME=TEST)))

OK (10 msec)

$

Copy Password file

Copy password file from Primary to Stand-by

scp orapwTEST oradr@xxxxxxx:<OH Location/dbs>

Create NFS Mount Point

Make the RMAN Backup location of your Primary DB Instance as an NFS mount point and mount that in the Stand-by Instance. It is mandatory that the backup location should be same for both Primary and Stand-by Instance. So we need NFS mount point to proceed further.

Duplicate database for standby using RMAN

Now we can start duplicating the target database for stand-by instance using the below command. Here we can start the duplication in two ways, choose the one which suits you.

Method 1:

@Primary Instance

rman target / auxiliary=sys/<pwd you created for password file>@stdby

RMAN >duplicate target database for standby;

Method 2:

@Stand-by Instance

duplicate target database for standby backup location '<RMAN Backup location>' nofilenamecheck;

Once the duplication is finished, check and start the database at stand-by side.

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

NAME      OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
---------     --------------------          --------------------------- ------------------------------
SWITCHOVER_STATUS
--------------------------------
TEST      MOUNTED              PHYSICAL STANDBY MAXIMUM PERFORMANCE
TO PRIMARY


SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
---------------------------


SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2212392 bytes
Variable Size             671092184 bytes
Database Buffers         3590324224 bytes
Redo Buffers               12152832 bytes
Database mounted.
Database opened.

If faced any issue like "ORA-10458: standby database requires recovery" Click Here

SQL>
SQL> show parameter background;

SQL> show parameter diag;

NAME                                 TYPE        VALUE
-------------------------------- ----------- ------------------------------
diagnostic_dest                      string      /DRDB/ebsdr/db/tech_st/11.2.0/admin/TEST_erpdr                                         
SQL> select name,open_mode,database_role,protection_mode,switchover_status from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
---------     --------------------          --------------------------  -------------------------------
SWITCHOVER_STATUS
--------------------------------
TEST      READ ONLY            PHYSICAL STANDBY MAXIMUM PERFORMANCE
NOT ALLOWED


SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------------------
           267

SQL> recover managed standby database disconnect;
Media recovery complete.
SQL>
SQL> col GROUP# format a15
SQL> select process,status,group#,thread#,sequence# from v$managed_standby;

PROCESS   STATUS       GROUP#             THREAD#  SEQUENCE#
---------        ------------    --------------            -------------- ------------------
ARCH      CONNECTED    N/A                      0          0
ARCH      CONNECTED    N/A                      0          0
ARCH      CONNECTED    N/A                      0          0
ARCH      CONNECTED    N/A                      0          0
RFS          IDLE                   N/A                      0          0
RFS          IDLE                   N/A                      0          0
MRP0      WAIT_FOR_GAP N/A                    1        268
RFS          RECEIVING      N/A                       1        268

8 rows selected.

SQL> select sequence#,applied from v$archived_log;

 SEQUENCE# APPLIED
------------------ ------------
       266             YES
       267             YES

SQL> select sequence#,applied from v$archived_log;

 SEQUENCE# APPLIED
------------------ ------------
       266             YES
       267             YES
       268             YES

SQL> select sequence#,applied from v$archived_log;

 SEQUENCE# APPLIED
------------------ -------------
       266             YES
       267             YES
       268             YES
       269             YES
       270             YES

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------------------
           270

Useful Queries
===========

1. select name,open_mode,database_role,protection_mode,switchover_status from v$database; - on both

2. select max(sequence#) from v$archived_log; - on both

3. select process,status from v$managed_standby;

4. recover managed standby database disconnect;(standby) - To initiate the SYNC between Primary and Stand-by

5. col GROUP# format a15
   select process,status,group#,thread#,sequence# from v$managed_standby;
 
6.SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';

7. select sequence#,applied from v$archived_log;(standby)

8. recover managed standby database cancel; - to stop the SYNC between Primary and Stand-by

****************************நன்றி****************************