Guarantee a select will execute on a standby only within allowable lag

–set the SLA?
–Use a logon trigger to set the maximum delay
–whenever a user logs into the standby
— works on 11gR2 with real time apply on
— selects will succeed in this example if lag is less than 120 seconds / 2 minutes
— if lag is more that 120 seconds – the users SQL will throw the exception
— ora-03172
— if the lag
–SQL> connect sys/oracle@prod as sysdba
–Connected.
— ********** test this before you put in production ***********/
— this trigger was tested against oracle’s hr sample schema
— create this guy on the primary
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=120;’
END IF;
END;
/
Trigger created.
–orcl> oerr ora 3172
–03172, 00000, “STANDBY_MAX_DATA_DELAY of %s seconds exceeded”
–// *Cause:  Standby recovery fell behind the STANDBY_MAX_DATA_DELAY
–//          requirement.
–// *Action: Tune recovery and retry the query later, or switch to another
–//          standby database within the data delay requirement.

Leave a Comment

Scroll to Top