Active Data Guard

Data Guard

Active Data Guard : While the standby is open read-only on the same time the physical standby database can be in recovery mode is called ACTIVE DATA GUARD.
Advantage
This allows you to use this standby as a real-time reporting database or even to backup the primary data, also as a result it does not have any impact on RTO or RPO.
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

Note :-However, this benefit is offset to a certain extent by the fact that Active Data Guard is available on Enterprise Edition only and is cost option which must be licensed on both the primary and standby database.

Steps To create Active DATA Guard .

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

Primary

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

STATUS INSTANCE_NAME DATABASE_ROLE


OPEN ORCL PRIMARY

select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

3333

Standby

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

STATUS INSTANCE_NAME DATABASE_ROLE


MOUNTED ORCL_STBY PHYSICAL STANDBY

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

MAX(SEQUENCE#)

3333

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

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

2) Cancel the MRP on the physical standby database and open the standby database in READ-ONLY mode

alter database recover managed standby database cancel;
alter database open read only;

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

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE


OPEN ORCL_STBY PHYSICAL STANDBY READ ONLY

3) start the MRP on the physical standby database.

alter database recover managed standby database disconnectfrom session;

4) Database is now Active Data Guard

SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE OPEN_MODE


PHYSICAL STANDBY READ ONLY WITH APPLY