Check error and gap in active / standby data guard in Oracle

Useful SQL script for Data guard environment

Check the data guard information on primary and Standby

SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE

Check the error or messages in Data guard the environment.

SELECT MESSAGE FROM V$DATAGUARD_STATUS;

Check the background processes in the Oracle Data guard (Run on physical Standby)

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;

Check the received on physical standby in Oracle (Run the query on physical Standby)

select registrar, creator, thread#, sequence#, first_change#,
next_change# from v$archived_log;

Check the log status received and applied on standby

select 'Last Log applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log where
applied='YES')
union
select 'Last Log received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS')
Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log);

Monitor efficient recovery operations and estimate the time required to complete the current operation.

select to_char(start_time, 'DD-MON-RR HH24:MI:SS') start_time,
item, round(sofar/1024,2) "MB/Sec"
from v$recovery_progress
where (item='Active Apply Rate' or item='Average Apply Rate')

Find the last applied log time

select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM
V$ARCHIVED_LOG where applied='YES';

Check the last sequence received and applied on the data guard standby

SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq
Applied" FROM (select thread# thrd, MAX(sequence#) almax FROM v$archived_log
WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY
thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history
WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY
thread#) lh WHERE al.thrd = lh.thrd;