Tracking the Standby Lag from the Primary

 Here is a quick way of tracking the Standby Lag from the Primary.

This relies on the information in V$ARCHIVE_DEST on the Primary.

Note that this query will not work if the lag is so great that the SCN_TO_TIMESTAMP mapping fails (because the underlying table holds only a limited number of records) OR if the Standby instance is shutdown and the Primary cannot communicate with it.

Note : The lag based on "SCN_TO_TIMESTAMP" is always an approximation.  

SQL> l
  1  select scn_to_timestamp(current_scn) - scn_to_timestamp(applied_scn)   Time_Diff
  2  from v$database d,
  3* (select applied_scn from  v$archive_dest a where target = 'STANDBY')
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 00:41:09.000000000

SQL>
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 01:07:22.000000000

SQL>
SQL> l
  1  select scn_to_timestamp(current_scn) - scn_to_timestamp(applied_scn)   Time_Diff
  2  from v$database d,
  3* (select applied_scn from  v$archive_dest a where target = 'STANDBY')
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 01:07:58.000000000

SQL>
SQL> l
  1  select scn_to_timestamp(current_scn) - scn_to_timestamp(applied_scn)   Time_Diff
  2  from v$database d,
  3* (select applied_scn from  v$archive_dest a where target = 'STANDBY')
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 01:13:16.000000000

SQL>
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 01:13:37.000000000

SQL>
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000000 00:00:00.000000000

SQL>

Here, the lag was 4 days and it took some time for the Standby to catchup with the Primary.

(this is my Lab environment, not a real production environment at my work place, so don't ask how I managed to create a lag of 4 days or how long it took for the Standby to catch-up with the Pirmary)

Note : If the Standby database is down and/or the lag is very high, you will get error :

ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

for the "applied_scn" from v$archive_dest.  (If the Standby is down, the value for "applied_scn" in v$archive_dest on the Primary is "0").

If you have access to the Standby you can run this query :

select name, value from v$dataguard_stats where name like '%lag'

The demo above is only a quick way by querying the Primary without accessing the Standby