Active Data Guard SLA pursuit

When your business users runs read only report from ADG (physical standby database opened READ ONLY) there may unacceptable lag between primary and standby databases.Since 11.2 there is a nice view to see this lag.

SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0 and last_time_updated like '%12/29/2012%';

NAME         TIME UNIT          COUNT LAST_TIME_UPDATED

------------ ---- ---------- -------- --------------------

apply lag       0 seconds     3780291 12/29/2012 09:54:04

apply lag       1 seconds      319929 12/29/2012 09:52:32

apply lag       2 seconds        6509 12/29/2012 07:10:20

apply lag       3 seconds        3868 12/29/2012 05:52:06

apply lag       4 seconds        2956 12/29/2012 05:52:07

apply lag       5 seconds        2223 12/29/2012 05:46:56

apply lag       6 seconds        1344 12/29/2012 05:46:57

apply lag       7 seconds        1118 12/29/2012 05:46:58

apply lag       8 seconds        1144 12/29/2012 05:33:56

apply lag       9 seconds        1094 12/29/2012 05:33:57

apply lag      10 seconds        1098 12/29/2012 05:33:58

apply lag      11 seconds        1001 12/29/2012 05:33:59

apply lag       2 minutes        6363 12/29/2012 02:34:41

apply lag       3 minutes        1786 12/29/2012 02:35:54

61 rows selected

So, in my environment 2-3 minutes lag - unacceptable. Of course the best way - is tuning apply speed, but it's not always possible, for network limits for example. But we may guarantee users some SLA, via this kind of trigger,violation of which will lead to "ORA-03172: STANDBY_MAX_DATA_DELAY of 60 seconds exceeded". Run this trigger on primary, then it will by applied on standby. Here is simple Oracle example:

SQL> CREATE OR REPLACE TRIGGER LOGON_TO_STANDBY_TRIGGER

AFTER LOGON ON some.schema

BEGIN

IF (SYS_CONTEXT('USERENV','DATABASE_ROLE')

IN ('PHYSICAL STANDBY'))

THEN execute immediate 'ALTER SESSION SET STANDBY_MAX_DATA_DELAY=60;'

END IF;

END;