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
