Lag in an Active Data Guard Configuration

Lag in an Active Data Guard Configuration

Oracle 11g comes up with a New Feature on Oracle Data Guard that is Active Data Guard. In Active Data Guard we can use a real-time query on Standby Database and can gain benefits by offloading read-only workload to Standby Database.

To know more about Active Data Guard Oracle Active Data Guard Overview and Architecture

But, a Standby Database Configured with Real-Time Apply can lag behind the Primary Database due to hardware or network issue.

There could be following reasons :

1.Insufficient CPU capacity: If the standby database is not capable enough to apply redo data as quickly as it receives from the primary database.

2.High Network Latency: When Standby and Primary database’s network link suffers from a high latency.

3.Limited bandwidth: It may prevent the primary database from shipping redo as quickly as it is generated, particularly during periods of peak workload.


Oracle Database 11g Release 2 (11.2) includes features to enable you to determine the lag time and take appropriate action.

We can enable a tolerance level for data staleness by configuring a maximum value for apply lag. Query results are returned to the application if the lag is within the acceptable tolerance level, otherwise, an error results.

SQL> SELECT NAME,VALUE,DATUM_TIME,TIME_COMPUTED FROM V$DATAGUARD_STATS WHERE UPPER(NAME)='APPLY LAG';

NAME VALUE   DATUM_TIME             TIME_COMPUTED
-------------------------------- ------------------------ ------------------------  ---------------------
apply lag +00 00:00:00   05/06/2018 22:46:06     05/06/2018 22:46:08

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.

In our case apply lag is 2 seconds.

Note: This lag should be less than 30 seconds.

We can monitor apply lag using a v$standby_event_histogram view. It shows a histogram of the apply lag on a physical standby database.

SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE UPPER(NAME)='APPLY LAG';

NAME        TIME UNIT   COUNT     LAST_TIME_UPDATED
----------------- ----------------------------   ---------- ----------------
apply lag   0 seconds    5874     05/06/2018 22:53:53
apply lag           1 seconds       1     05/06/2018 21:37:08
apply lag           2 seconds       1     05/06/2018 22:50:23

Use the histogram to focus on periods of time when the apply lag exceeds desired levels. Determine the cause of the lag during those time periods and take steps to resolve the excessive lag.