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
Search This Blog
Total Pageviews
Thursday, 1 March 2012
Subscribe to:
Posts (Atom)
Oracle DBA
anuj blog Archive
- ► 2011 (362)