Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Wednesday, May 22, 2013

Setting up active dataguard - Oracle 11g

Prerequsite:


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.

No comments: