DR Setup on Oracle using Data Guard Configuration

1.       Take a backup of the primary DB which will be restored on the standby server.

2.       Create a standby control file before you make any further changes to DB (like adding datafiles, renaming datafiles, etc.) for starting the standby db for recovery.

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oracle/PER/controlfile.dbf';

3.       Restore the standby DB server with the backup taken instep 1.

brrestore -b <backupfile_name>

The parameter changes in the section below, can be performed individually on primary and standby db, or only on primary db and then you can create a pfile and use it in the standby db for generating spfile. In the second approach, you may need to do a few additional changes to the standby system. I had done the changes individually on both systems.

4.       Check db_name and db_unique_name on both primary and standby databases. The db_name of standby should be the same as that of primary, but db_unique_name should be different.

On primary database:

On secondary database:

5.       The db_unique_name (of both primary and standby) should be used in dg_config setting of the parameter log_archive_config of both primary and standby databases.

    ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PER,PER_NRDR)'; 

6.       Create suitable archive destinations. Use db_unique_name in the service to properly reference the standby db.

In primary database:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='service="PER_NRDR", LGWR SYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PER_NRDR DELAY=0'; 

        In secondary database:

        ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='service="PER", LGWR SYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PER DELAY=0';

7.       Check the below parameters and set the values accordingly if not set by default in both primary and secondary databases.

a.       LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf'

b.       LOG_ARCHIVE_MAX_PROCESSES=30

c.       REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

8.       It is recommended to ensure that the primary DB is ready to switch the role of standby. Need to set the below parameters for this.

On primary database:

a.       ALTER SYSTEM SET FAL_SERVER=PER_NDR;

b.       ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

c.       ALTER SYSTEM SET FAL_CLIENT=PER;

        On standby database:

            a.       ALTER SYSTEM SET FAL_SERVER=PER;

            b.       ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

            c.       ALTER SYSTEM SET FAL_CLIENT=PER_NRDR;

9.       After all the changes are done to the parameters, generate fresh pfile from spfile.

CREATE PFILE='\oracle\PER\11204\database\initPER.ora' FROM SPFILE;

10.   In addition to the existing entries, make the entries for standby and standby data guard services in tnsnames.ora file on primary DB ($ORACLE_HOME\NETWORK\admin) in primary database. A sample of the entry would look like something as below:

        PER_NRDR.WORLD=

                        (DESCRIPTION =

                                        (ADDRESS_LIST =

                                        (ADDRESS =

                                        (COMMUNITY = SAP.WORLD)

                                        (PROTOCOL = TCP)

                                        (HOST = <standby_db_host>)

                                        (PORT = 1521)

                                        )

                                        )

                                        (CONNECT_DATA =

                                        (SID = PER)

                                        (GLOBAL_NAME = PER)

                                        (SERVICE_NAME = PER)

                                        )

                                        )

       

        PER_NRDR_DGMGRL.WORLD=

                        (DESCRIPTION =

                                        (ADDRESS_LIST =

                                        (ADDRESS =

                                        (COMMUNITY = SAP.WORLD)

                                        (PROTOCOL = TCP)

                                        (HOST = <standby_db_host>)

                                        (PORT = 1521)

                                        )

                                        )

                        (CONNECT_DATA =

                                        (SID = PER)

                                        (GLOBAL_NAME = PER)

                                        (SERVICE_NAME = PER_DGMGRL)

                                        )

                                        )

11.   Ensure that telnet from primary to standby DB and vice-versa using the ports defined in the file tnsnames.ora is successful.

12. Copy the file pwd<SID>.ora from primary DB to standby DB (Location: $ORACLE_HOME/database).

13.   If all the configurations above have been done properly, at this point, you should be able to see the oraarch directory on standby DB to be in synch with the primary DB.

14.   Copy the standby control file created in step 2 in all the control file locations on the standby db. Keep a copy of the existing control file as a backup. You can find the control file location as below:

15.   Now, you should run the recovery of the DB using backup control file.

16.   Mount the standby database.

            ALTER DATABASE MOUNT STANDBY DATABASE;

17.  Start applying the logs.

            RECOVER STANDBY DATABASE USING BACKUP CONTROLFILE;

18.   After all the logs have been applied in the foreground, enable the log application in the background.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

19.   This is the final step in DR setup. Now you can check if the MRP0 process in standby DB is waiting for logs from primary DB:

        select process, thread#, sequence#, status from v$managed_standby where process='MRP0';