Oracle 19c, Primary and Active Dataguard standby database.
Has anyone had any luck to automatically change the session parameter STANDBY_MAX_DATA_DELAY when a user logs in to the database?
We want users connected in read only mode to the Standby DB to get an error when synchronization lag is greater than a certain threshold (5min).
Tried this logon trigger created on primary and replicated to standby:
CREATE OR REPLACE TRIGGER STANDBY_MAX_DATA_DELAY
AFTER LOGON ON database
WHEN
(SYS_CONTEXT('USERENV','DATABASE_ROLE') in ('PHYSICAL STANDBY') and
SYS_CONTEXT('USERENV','SESSION_USER')!='SYS' and
SYS_CONTEXT('USERENV','SERVICE_NAME')='serv_name.sn.vcn.oraclevcn.com')
BEGIN
execute immediate 'ALTER SESSION SET STANDBY_MAX_DATA_DELAY=300';
END;
However, whenever a non SYS user connects to the standby using the service specified, the trigger executes and fails with this error:
ORA-04088: error during execution of trigger 'SYS.STANDBY_MAX_DATA_DELAY'
ORA-00604: error occurred at recursive SQL level 1
ORA-03174: STANDBY_MAX_DATA_DELAY does not apply to SYS users
ORA-06512: at line 204088. 00000 - "error during execution of trigger '%s.%s'"
*Cause: A runtime error occurred during execution of a trigger.
*Action: Check the triggers which were involved in the operation.Vendor code 4088
By testing different ways and parameters to use the logon trigger, I concluded the trigger is always doing the execute immediate as SYS, no matter who is logging in. I also tried changing the CURRENTSCHEMA session parameter on the same ALTER SESSION statement but that does not work, the session needs to change or some variable needs to be set to target the SESSION_USER environment to change the parameter.
The solution was to create the trigger on a separate schema (not SYS), a user with the ADMINISTER DATABASE TRIGGER sys privilege. Still an AFTER LOGON ON DATABASE type trigger. It just works like that, as long as the trigger is not owned by SYS. Any other user logging into the database gets the session parameter STANDBY_MAX_DATA_DELAY set, we tested that by introducing a lag on the Standby by stopping the apply process and users start getting the ORA-03172 error, which is the desired outcome.