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.

To view apply lag for your current environment :

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.

Stay tuned for More articles on Oracle DataGuard

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp