gv$dataguard_stats is buggy even on most recent 11.2.0.3.x PSUs

Oracle introduced a new RAC-enabled view for DataGuard named gv$dataguard_stats probably starting with 11.2. However this view returns false information when queried on RAC standby node where MRP0 is not running, sample:

1st instance of standby RAC with MRP0 running:

SQL> select name,value from v$dataguard_stats;

NAME                           VALUE
------------------------------ --------------------
transport lag                  +00 00:00:00
apply lag                      +00 00:00:00
apply finish time              +00 00:00:00.000
estimated startup time         18

SQL> select name,value from gv$dataguard_stats;

NAME                           VALUE
------------------------------ --------------------
transport lag                  +00 00:00:00
apply lag                      +00 00:00:00
apply finish time              +00 00:00:00.000
estimated startup time         18
transport lag                  +00 00:00:00
apply lag                      +00 00:21:56
apply finish time              +00 00:00:41.526
estimated startup time         29

8 rows selected.

SQL>

According to this gv$dataguard_stats output there lag is 0s on 1st node (itself) and 21mins (time since when MRP0 has been relocated from 2nd node to 1st one) – this is wrong, because when you ask 2nd RAC instance for its local view of DataGuard apply lag you will see 0s.

SQL> select name,value from v$dataguard_stats;

NAME                             VALUE
-------------------------------- ------------------------------
transport lag                    +00 00:00:00
apply lag                        +00 00:00:00
apply finish time                +00 00:00:00.000
estimated startup time           29

SQL>

This inconsistency has been reported to Oracle in SR 3-6005743051: inconsistent gv$dataguard_stats on RAC standby. They did not register bug (even for docs) but returned with the following clarification:

the rows “apply lag time”, “apply finish time” and “estimated startup time” are relevant only for the applying instance. You are allowed to send log files to several instances – in such a case “transport lag” is relevant for all receiving instances.

This has certain repercussions as if you want to monitor standby lag properly – you will need to locate first the MRP0 process and then query this instance via dedicated connection for v$dataguard_stats. Doing shortcuts by using gv$dataguard_stats might return false results. Another question is why such (buggy in my opinion) view has been introduced at all if it represents no value.

-J.

Comments are closed.