Understanding Lag in an Active Data Guard Configuration

Understanding Lag in an Active Data Guard Configuration

• A standby database configured with real-time apply can lag behind the primary database as a result of:
– Insufficient CPU capacity
– High network latency
– Limited bandwidth

• Queries on the standby database need to return current results and/or be within an established service level.
• Ways to “manage” the standby database lag and take necessary action:
– Configure Data Guard configuration with a maximum data lag that will trigger an error when it is exceeded.
– Monitor the redo apply lag and take action when the lag is unacceptable

Monitoring Apply Lag: V$DATAGUARD_STATS

• Apply lag: This is the difference, in elapsed time, between when the last applied change became visible on the standby and when that same change was first visible on the primary.
• The apply lag row of the V$DATAGUARD_STATS view reflects statistics that are computed periodically and to the nearest second.

Standby database:
SQL> SELECT name, value, datum_time, time_computed FROM v$dataguard_stats WHERE name like 'apply lag';

Monitoring Apply Lag: V$STANDBY_EVENT_HISTOGRAM

• View histogram of apply lag on a physical standby database.
• Use to assess value for STANDBY_MAX_DATA_DELAY.
• Use to focus on periods of time when the apply lag exceeds desired levels so that issue can be resolved.
SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0;

Configuring Zero Lag Between the Primary and Standby Databases

• Certain applications have zero tolerance for any lag.
• Query on the standby database must return the same result as though it were executed on the primary database.
• Enforce by setting STANDBY_MAX_DATA_DELAY to 0.
• The standby database must have advanced to a value equal to that of the current SCN on the primary database at the time the query was issued.
• Results are guaranteed to be the same as the primary database, else ORA-3172 error is returned to the query.
• The primary database must operate in maximum availability or maximum protection mode.
• SYNC must be specified for redo transport.
• Real-time query must be enabled.

Setting STANDBY_MAX_DATA_DELAY by Using an AFTER LOGON Trigger

Create an AFTER LOGON trigger that:
• Is database role aware
– It uses DATABASE_ROLE, a new attribute in the USERENV context.
– SQL and PL/SQL clients can retrieve the database role programmatically using the SYS_CONTEXT function.
– It enables you to write role-specific triggers.
• Sets STANDBY_MAX_DATA_DELAY when the application logs on to a real-time query–enabled standby database
• Allows for configuration of a maximum data delay without changing the application source code

CREATE OR REPLACE TRIGGER sla_logon_trigger
AFTER LOGON
ON APP.SCHEMA
BEGIN
IF (SYS_CONTEXT('USERENV', 'DATABASE_ROLE')
IN ('PHYSICAL STANDBY'))
THEN execute immediate
'alter session set standby_max_data_delay=5';
ENDIF;
END;