Tuesday, July 30, 2019

How to find out if your standby database lags behind the primary database


Applicable to Oracle 12.2.

Use the view V$DATAGUARD_STATS, which will only return results when executed on a standby database:
set lines 200
col source_db_unique_name format a20
col value format a20
SELECT source_db_unique_name,name,value,unit,time_computed,datum_time
 FROM v$dataguard_stats
 WHERE name like '%lag'
 ;

The columns explained, as stated in the documentation:
* APPLY LAG - how much does the data in a standby database lag behind the data in the primary database, due to delays in propagating and applying redo to the standby database.
* TRANSPORT LAG - how much the transport of redo to the standby database lag behind the generation of redo on the primary database.
Note what will happen to the value in this column if there are more than one redo log gap: the transport lag is calculated "as if no redo has been received after the beginning of the earliest redo gap".
Both of these columns are of type INTERVAL DAY TO SECOND, so in my case I am facing a 7 days, 11 hours and 7 minutes lag.

* TIME_COMPUTED - Local time at the standby database when the metric was computed
* DATUM_TIME - Local time at the standby database when the datum used to compute the metric was received. Note that the APPLY LAG and TRANSPORT LAG metrics are computed based on data that is periodically received from the primary database. An unchanging value in this column across multiple queries indicates that the standby database is not receiving data from the primary database.

SOURCE_DB_UNIQUE_NAME NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
proddb01 transport lag +07 11:07:37 day(2) to second(0) interval 07/30/2019 08:39:01 07/30/2019 08:32:55
proddb01 apply lag +07 11:07:37 day(2) to second(0) interval 07/30/2019 08:39:01 07/30/2019 08:32:55

No comments:

Post a Comment