0 Replies Latest reply: Jul 21, 2013 2:58 AM by user9056222 RSS

    detect archivelog gap through gv$archived_log or GV$ARCHIVE_DEST_STATUS

    user9056222

      I am trying to set up a physical standby.

      Version 11.2.0.1,Single instance.

      Platform - Linux     X86-64

       

      After setting up, when i execute the following queries, there is a difference in the output with reference to archivelog gap. Any inputs why these 2 views report different numbers.

       

      SQL> SELECT   a.thread#,  b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq   ARC_DIFF FROM (SELECT  thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,       (SELECT  thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;

       

         THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF

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

           1     59      59 21-JUL-2013 09:09:05      0

       

      SQL> select INST_ID,DEST_NAME,STATUS,RECOVERY_MODE,ARCHIVED_THREAD#,ARCHIVED_SEQ#,APPLIED_THREAD#,APPLIED_SEQ#,DB_UNIQUE_NAME

         from GV$ARCHIVE_DEST_STATUS

      where DEST_NAME in ('LOG_ARCHIVE_DEST_3');
        23
         INST_ID DEST_NAME             STATUSRECOVERY_MODE       ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# DB_UNIQUE_NAME

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

           1 LOG_ARCHIVE_DEST_3    VALID MANAGED                            1        59           1       57 db101pln