Measure transport/apply lag, apply rate and Data Guard processes on the standby

Command

-- Run on the standby:
SELECT name, value, unit, time_computed, datum_time
FROM   v$dataguard_stats
WHERE  name IN ('transport lag','apply lag','apply finish time','estimated startup time');

-- Redo apply rate (KB/s):
SELECT item, units, sofar, total, TO_CHAR(timestamp,'YYYY-MM-DD HH24:MI:SS') AS ts
FROM   v$recovery_progress
WHERE  item IN ('Active Apply Rate','Average Apply Rate','Maximum Apply Rate','Last Applied Redo')
ORDER  BY start_time DESC, item;

-- Transport and apply processes (12.2+; replaces v$managed_standby):
SELECT name, role, action, thread#, sequence#, block#, delay_mins
FROM   v$dataguard_process
ORDER  BY role;

What it does

Shows how far the standby is behind the primary (transport lag = redo not yet received, apply lag = redo not yet applied), when the backlog will be applied, the apply rate and which process is working on which sequence.

When to use it

For RPO/RTO monitoring, and on a lag alert to tell whether the problem is in the network (transport) or in standby I/O/CPU (apply).

Caution

Read-only. If datum_time is old (far behind time_computed), the standby is not receiving data from the primary and the lag value is unreliable. Check whether DelayMins is set; an intentional delay looks like apply lag.