Setup ACTIVE DATA GUARD in 11G

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 http://shivanandarao.wordpress.com/2012/03/10/creating-physical-standby-database-on-oracle-11g/ 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.

1
2
3
4
5
6
7
8
9
10
11
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.

1
2
3
4
5
6
7
8
9
10
11
SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS   INSTANCE_NAME DATABASE_ROLE
-------- ------------- ---------------------
MOUNTED  stnd          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 physcial standby database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> alter database recover managed standby database disconnectfrom 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.