How to setup active dataguard in oracle 11g

The main advantage of setting up dataguard in 11g is that the database can be opened in Read-Only mode allowing the Users to access the physical standby database for fetching reports and on the same time the physical standby database can be in recovery mode. In other words, the physical standby database would be in recovery mode and hand in hand the standby database can be used for reporting purposes.

The read-only physical standby database can be used to offload query from the primary database. Users can use select statements and complex queries against this this database and thereby decreasing the load on the primary database. While the standby is open read-only, the following operations are disallowed

• Any Data Manipulation Language (DML) except for select statements
• Any Data Definition Language (DDL)
• Access of local sequences
• DMLs on local temporary tables

Steps on how to setup the active dataguard:

Once you setup the physical standby database as described in

CREATING PHYSICAL STANDBY DATABASE

 Follow the below steps to setup the active dataguard.

Step 1: Check the status of the Primary database and the latest sequence generated in the primary database.

SQL> select status,instance_name,database_role from v$instance,v$database;

STATUS       INSTANCE_NAME    DATABASE_ROLE

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

OPEN         prim             PRIMARY

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

MAX (SEQUENCE#)

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

40

Step 2: Check the status of the physical standby database and the latest sequence applied on the physcial standby database.

SQL> select status,instance_name,database_role from v$database,v$instance;

 STATUS   INSTANCE_NAME DATABASE_ROLE

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

MOUNTED stand          PHYSICAL STANDBY

 SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX (SEQUENCE#)

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

40

Step 3: Check if the Managed Recovery Process (MRP) is active on the physical standby database.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#

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

ARCH      CONNECTED     0

ARCH      CONNECTED     0

ARCH      CONNECTED     0

ARCH      CONNECTED     0

RFS       IDLE          41

RFS       IDLE          0

RFS       IDLE          0

RFS       IDLE          0

MRP0      WAIT_FOR_LOG 41

 9 rows selected.

Here, MRP is active. The PROCESS Column above shows that MRP is active and is waiting for the log sequence 41.

Step 4: Cancel the MRP on the physical standby database and open the standby database. The standby database would be opened in the READ-ONLY Mode.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$instance;

STATUS INSTANCE_NAME DATABASE_ROLE    OPEN_MODE

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

OPEN   stnd           PHYSICAL STANDBY READ ONLY

Step 6: Now start the MRP on the physical standby database.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

 SQL> select process,status,sequence# from v$managed_standby;

 PROCESS   STATUS        SEQUENCE#

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

ARCH      CONNECTED     0

ARCH      CONNECTED     0

ARCH      CONNECTED     0

ARCH      CONNECTED     0

RFS       IDLE          41

RFS       IDLE          0

RFS       IDLE          0

RFS       IDLE          0

MRP0      WAIT_FOR_LOG 41

9 rows selected.

Here, you can see that the MRP is active and is waiting for the log sequence 41 and also the physical standby database is opened in READ-ONLY mode which would allow users to use the physical standby database for fetching reports.

 I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!