Thinking Out Loud

January 10, 2016

How Reliable is v$archive_dest_status?

Filed under: 11g,Dataguard,oracle — mdinh @ 7:23 pm

v$archive_dest_status

Any DG Experts out there who knows more about the reliability for v$archive_dest_status as it has been many years since I have written scripts to monitor DG.

Don’t want to reinvent the wheel if I cannot make it better.

Note: gap_status is only available from 11gR2 +++

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 10 11:06:55 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@hawklas):PRIMARY> select
  2    arch.dest_id,
  3    arch.thread# "thread",
  4    arch.sequence# "last sequence received",
  5    appl.sequence# "last sequence applied",
  6   (arch.sequence#-appl.sequence#) "difference"
  7  from
  8    (
  9      select dest_id,thread#,sequence# from v$archived_log
 10      where (thread#,first_time)
 11      in (select thread#,max(first_time) from v$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#)
 12    ) arch,
 13    (
 14      select thread# ,sequence# from v$log_history
 15      where (thread#,first_time)
 16      in (select thread#,max(first_time) from v$log_history where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#)
 17    ) appl
 18  where arch.thread#=appl.thread#
 19  order by 1
 20  ;

DEST_ID thread last sequence received last sequence applied difference
------- ------ ---------------------- --------------------- ----------
      1      1                    234                   234          0
      2      1                    234                   234          0

Elapsed: 00:00:07.99
ARROW:(SYS@hawklas):PRIMARY> select
  2  DEST_ID,STATUS,TYPE,DATABASE_MODE,RECOVERY_MODE,STANDBY_LOGFILE_COUNT srl_ct,STANDBY_LOGFILE_ACTIVE srl_active,
  3  ARCHIVED_THREAD# thrd,ARCHIVED_SEQ# seq,APPLIED_SEQ# applied,SRL,
  4  GAP_STATUS, decode(ERROR,null,'NONE','ERROR') error
  5  from v$archive_dest_status
  6  where status!='INACTIVE'
  7  ;

DEST_ID STATUS    TYPE           DATABASE_MODE   RECOVERY_MODE           SRL_CT SRL_ACTIVE THRD  SEQ APPLIED SRL GAP_STATUS               ERROR
------- --------- -------------- --------------- ----------------------- ------ ---------- ---- ---- ------- --- ------------------------ -----
      1 VALID     LOCAL          OPEN            IDLE                         0          0    1  234       0 NO                           NONE
      2 VALID     PHYSICAL       MOUNTED-STANDBY MANAGED REAL TIME APPLY      4          0    1  234     233 YES NO GAP                   NONE

Elapsed: 00:00:00.01
ARROW:(SYS@hawklas):PRIMARY>
SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 10 11:06:36 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@hawksan):PHYSICAL STANDBY> select
  2    arch.dest_id,
  3    arch.thread# "thread",
  4    arch.sequence# "last sequence received",
  5    appl.sequence# "last sequence applied",
  6   (arch.sequence#-appl.sequence#) "difference"
  7  from
  8    (
  9      select dest_id,thread#,sequence# from v$archived_log
 10      where (thread#,first_time)
 11      in (select thread#,max(first_time) from v$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#)
 12    ) arch,
 13    (
 14      select thread# ,sequence# from v$log_history
 15      where (thread#,first_time)
 16      in (select thread#,max(first_time) from v$log_history where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#)
 17    ) appl
 18  where arch.thread#=appl.thread#
 19  order by 1
 20  ;

DEST_ID thread last sequence received last sequence applied difference
------- ------ ---------------------- --------------------- ----------
      1      1                    234                   234          0

Elapsed: 00:00:00.01
ARROW:(SYS@hawksan):PHYSICAL STANDBY> select
  2  DEST_ID,STATUS,TYPE,DATABASE_MODE,RECOVERY_MODE,STANDBY_LOGFILE_COUNT srl_ct,STANDBY_LOGFILE_ACTIVE srl_active,
  3  ARCHIVED_THREAD# thrd,ARCHIVED_SEQ# seq,APPLIED_SEQ# applied,SRL,
  4  GAP_STATUS, decode(ERROR,null,'NONE','ERROR') error
  5  from v$archive_dest_status
  6  where status!='INACTIVE'
  7  ;

DEST_ID STATUS    TYPE           DATABASE_MODE   RECOVERY_MODE           SRL_CT SRL_ACTIVE THRD  SEQ APPLIED SRL GAP_STATUS               ERROR
------- --------- -------------- --------------- ----------------------- ------ ---------- ---- ---- ------- --- ------------------------ -----
      1 VALID     LOCAL          MOUNTED-STANDBY MANAGED REAL TIME APPLY      0          0    1  234       0 NO                           NONE
      2 VALID     UNKNOWN        UNKNOWN         IDLE                         0          0    0    0       0 NO                           NONE
     32 VALID     UNKNOWN        UNKNOWN         IDLE                         0          0    1  234     234 NO                           NONE

Elapsed: 00:00:00.00
ARROW:(SYS@hawksan):PHYSICAL STANDBY>
Advertisements

1 Comment »

  1. Hello Chief,
    Indeed its great column to check from primary. Nice post.
    In my experience from standby database there is another view of course you know that “v$archive_gap”, but it is not all the times give updated information and some/most of the times misleads the actual. These might have fixed may be in 12cRxx, but am saying prior to 12cRxx
    So sometimes i prefer to check manually using v$archived_log.

    Comment by Nassyam Basha — January 11, 2016 @ 7:41 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: