Setting up active dataguard - Oracle 11g

Prerequsite:

Configuring Oracle 11gR2 Data Guard - Physical Standby (without DG Broker)

Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.

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:

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

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

STATUS	INSTANCE_NAME	DATABASE_ROLE
OPEN	dubai	PRIMARY

select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
119

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

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

STATUS	INSTANCE_NAME	DATABASE_ROLE
MOUNTED	riyadh	PHYSICAL STANDBY

  

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

MAX(SEQUENCE#)
119

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

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

PROCESS	STATUS	SEQUENCE#
ARCH	CLOSING	16
ARCH	CONNECTED	0
ARCH	CLOSING	13
ARCH	CLOSING	14
ARCH	CLOSING	15
MRP0	WAIT_FOR_LOG	17
RFS	IDLE	17
RFS	IDLE	0
RFS	IDLE	0
RFS	IDLE	0

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

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.

select name,open_mode,database_role,db_unique_name from v$database;

NAME	OPEN_MODE	DATABASE_ROLE	DB_UNIQUE_NAME
DUBAI	READ ONLY	PHYSICAL STANDBY	riyadh

5) Now start the MRP on the physical standby database.

SQL> alter database recover managed standby database disconnect from session;
Database altered.

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

PROCESS	STATUS	SEQUENCE#
ARCH	CLOSING	16
ARCH	CONNECTED	0
ARCH	CLOSING	17
ARCH	CLOSING	18
ARCH	CLOSING	15
MRP0	WAIT_FOR_LOG	19
RFS	IDLE	19
RFS	IDLE	0
RFS	IDLE	0
RFS	IDLE	0

You can see that the MRP is active and is waiting for the log sequence 19 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.

You can run the below query on standby also to verify
select name,open_mode,database_role,db_unique_name from v$database;

NAME	OPEN_MODE	DATABASE_ROLE	DB_UNIQUE_NAME
DUBAI	READ ONLY WITH APPLY	PHYSICAL STANDBY	riyadh

You can check on the primary also which logs have been applied to standby using below query.
select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from
V$ARCHIVED_LOG where name = 'riyadh' order by FIRST_TIME desc;

Note:
The SET TRANSACTION READ ONLY SQL statement must be executed before performing a distributed query on a physical standby database.