we know that, all our standby databases are in mount stage where MRP process are applied manually on standby databases . In 11g, Oracle Introduced very cool feature called Active dataguard, where shipped online redo applied on standby database in Open read only state. Active dataguard gives applications flexibility to access and query standby database. Anyway customer need extra licensing to configure active dataguard. We can implement Active Data Guard for High Scalability, Hight Availability and Real Time Data Changes. Block corruptions are automatic in Active dataguard and where application retries for data on the standby database if they encounter with ORA-01578. The corrupted blocks are repaired on standby automatically.
Procedure to convert physical standby to Active Dataguard
Stop the MRP
SQL> alter database recover managed standby database cancel;
OPEN THE STANDBY DATABASE IN READ ONLY
SQL> alter database open read only;
SQL> select open_mode, db_name,database_role from v$database;
APPLY THE MRP PROCESS;
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> select process,status,sequence# from v$managed_standby;
Monitoring Active Dataguard using following Query’s
SQL> SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like 'apply lag';
SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0;
we can set SLA( Service Level Agreement) for query’s accessing ACTIVE DATAGUARD
In 11g we have new session setting called “standby_max_data_delay”
if we set “ standby_max_data_delay=none “ queries will will be executed regardless of the apply lag on database by default
if the parameter is set to NON-ZERO value, query’s will be executed only after the apply_lag or equal to non - zero value
If the Query is waiting beyond the “standby_max_data_delay” applications have to access primary results in error “ORA-03172”
In order to set SLA # create trigger as follows #
CREATE OR REPLACE TRIGGER hr_logon_set_SLA_trigger
AFTER LOGON ON hr.schema
BEGIN
IF (SYS_CONTEXT('USERENV','DATABASE_ROLE')
IN ('PHYSICAL STANDBY'))
THEN
execute immediate ‘ALTER SESSION SET STANDBY_MAX_DATA_DELAY=2;’
END IF;
END;
/
Following trigger will not allow query’s to occur on primary until redo log applied on standby
CREATE OR REPLACE TRIGGER hr_logon_sync_trigger
AFTER LOGON ON hr.schema
BEGIN
IF (SYS_CONTEXT('USERENV','DATABASE_ROLE')
IN ('PHYSICAL STANDBY'))
THEN
execute immediate ‘ALTER SESSION SYNC WITH PRIMARY;’
END IF;
END;
If the MRP is down, where application encounters with ORA-03173. To avoid errors oracle recommends you to configure physical standby configuration in maximum availability mode
Reference #
—Nikhil Tatineni—
—Dataguard —
