Query to check apply lag and transport lag
Apply lag # is difference in elapsed time between last applied change become visible on standby and when change was first visible on primary
Transport lag # Amount of data i.e standby has not received from primary
>>> >> view # v$dataguard_stats
select name,value,time_computed,datum_time from v$dataguard_stats where name=’%lag’;
Query to check errors triggered in alert log file and trace file
>>> >> view # v$dataguard_status
select * from v$dataguard_status where
severity in ('Error','Fatal') and
timestamp > (sysdate -1);
Query to check Media Recovery Process is currently running
>>> >> view # v$managed_standby
select * from v$managed_standby where process like 'MRP%';
Query to verify logs that are shipped and applied to standby database
>>> >> view # v$archived_log
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Query to check missing archive logs i.e not shipped to standby
SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);
Query to Verify that the last sequence# received and the last sequence# applied to standby database
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 first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;
Query to check log file gap between primary and standby and dest status
>> View v$Archive_Dest_status
select status, gap_status from v$Archive_Dest_status where dest_id = 2;
Query to check what are the archive logs missing on standby database
>> View v$Archive_gap
select * from v$Archive_gap;
( if this query result's output >
check missing exists on primary ,
manually copy it and
register the logs
and start MRP)
--Nikhil Tatineni----Dataguard--
--More links --https://jhdba.wordpress.com/2009/07/16/managing-dataguard-monitoring-scripts/
