Dataguard

Oracle DataGuard

Run the following on the replica to see if there is Standby Lag

1

select * from v$recovery_progress;

Run the following on the replica to see lag times

1
2
3
4
5

set linesize 9000
column name format a25
column value format a20
column time_computed format a25
SELECT name, value, time_computed FROM v$dataguard_stats;

Run the following on the primary database to see redo logs applied

1

select max(sequence#),thread# from v$archived_log group by thread#;

Run the following on the replica to see the redo logs applied

1

select max(sequence#),thread#, applied from v$archived_log group by thread#, applied;

Show the latest archive produced on the primary and the last archive applied on the standby

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT a.resetlogs_id, DECODE (a.thread#, 1, 'node1', 2, 'node2') HOST, b.last_seq prmy_last_file,
      a.applied_seq stdby_last_file, CASE WHEN b.last_seq - a.applied_seq > 2 THEN '=>' ELSE to_char(b.last_seq - a.applied_seq) END archive_difference, TO_CHAR (a.latest_apply_time, 'dd/mm/yyyy hh24:mi:ss') stdby_latest_time
 FROM (SELECT   resetlogs_id, thread#, MAX (sequence#) applied_seq, MAX (next_time) latest_apply_time
           FROM v$archived_log
          WHERE applied = 'YES'
       GROUP BY resetlogs_id, thread#) a,
      (SELECT   resetlogs_id, thread#, MAX (sequence#) last_seq
           FROM v$archived_log
       GROUP BY resetlogs_id, thread#) b
WHERE a.thread# = b.thread#
ORDER BY a.thread#;

Check the status of the redo apply and redo transport services

1
2

column message format a66
SELECT timestamp, facility, message FROM v$dataguard_status ORDER by timestamp;