Oracle 10g standby Archive Gap

Oracle standby Archive Gap



On Primary
~~~~~~~~~~



SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;



On Physical Standby
~~~~~~~~~~~~~~~~~~



SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

You can also query ->

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;




alter session set nls_date_format='dd-mon-yy hh24:mi:ss'

/

select app_thread, seq_app, tm_applied,
nvl(seq_rcvd,seq_app) seq_rcvd, nvl(tm_rcvd,tm_applied) tm_rcvd
from
(select sequence# seq_app, FIRST_TIME tm_applied, thread# app_thread
from v$archived_log where applied = 'YES'
and (first_time, thread#) in (
select max(FIRST_TIME ), thread#
from v$archived_log where applied = 'YES'
group by thread# )
),
(select sequence# seq_rcvd, FIRST_TIME tm_rcvd, thread# rcvd_thread
from v$archived_log where applied = 'NO'
and (first_time, thread#) in (
select max(FIRST_TIME ), thread#
from v$archived_log where applied = 'NO'
group by thread# )
)
where rcvd_thread(+)= app_thread

/

output like :

APP_THREAD SEQ_APP TM_APPLIED SEQ_RCVD TM_RCVD

---------- ---------- ------------------ ---------- ------------------

1 3773 14-oct-08 10:19:59 3773 14-oct-08 10:19:59

2 2857 14-oct-08 04:18:21 2858 14-oct-08 11:11:51