detecting gaps in logical standby environment

This is a cool script to detect gaps in the physical standby setup. Does anyone has something like this to detect gaps in the logical standby setup?

Thanks,

Roger Xu

On Tue, Jan 12, 2010 at 5:14 PM, Kellyn Pedersen <kjped1313_at_yahoo.com>wrote:

> When I worked with standby's, I always compared from v$log_history-
>
> select next_change# from v$log_history
> where recid =
> (
> select max(recid) from v$log_history);
>
> And compared it to the primary with a monitoring script that did this type
> of steps-
> sqlplus -s << EOF | read STDBY_CURRENT_ARCH
> connect / as sysdba
> set feedback off
> set pagesize 0
> select sequence# from v\$log_history
> where recid =
> (select max(recid) from v\$log_history);
> EOF
> echo Standby archive log: ${STDBY_CURRENT_ARCH}
> sqlplus -s << EOF | read PRIMARY_CURRENT_ARCH
> system/`crypt oracle < ${WORKING_DIR}/.pwd`_at_${REMOTE_SID}
> set feedback off
> set pagesize 0
> select sequence#
> from v\$archived_log
> where recid =
> (select max(recid) from v\$archived_log);
> EOF
> echo Primary archive log: ${PRIMARY_CURRENT_ARCH}
> let ARCH_GAP_ACTUAL=${PRIMARY_CURRENT_ARCH}-${STDBY_CURRENT_ARCH}
> echo Archive Log Gap: $ARCH_GAP_ACTUAL
> if (( ${ARCH_GAP_ACTUAL} > ${ARCH_GAP_TOLERANCE} ))
> then
> print "${HOST} standby archive log gap is > ${ARCH_GAP_TOLERANCE} logs
> behind ${REMOTE_SID} at ${DATE}" > ${OUT_FILE}
> print "${HOST} standby archive log gap is > ${ARCH_GAP_TOLERANCE} logs
> behind ${REMOTE_SID} at ${DATE}"
>
> then mail, etc., so forth and so on....
>
> Kellyn Pedersen
> Multi-Platform DBA
> I-Behavior Inc.
> http://www.linkedin.com/in/kellynpedersen
> www.dbakevlar.blogspot.com
>
> "Go away before I replace you with a very small and efficient shell
> script..."
>
>
> --- On *Tue, 1/12/10, Jiang, Lu <Lu.Jiang_at_umassmed.edu>* wrote:
>
>
> From: Jiang, Lu <Lu.Jiang_at_umassmed.edu>
> Subject: Find the latest SCN # in phisical standby
> To: "ORACLE-L" <oracle-l_at_freelists.org>
> Date: Tuesday, January 12, 2010, 3:43 PM
>
>
> Hi all,
>
>
>
> I have set up a real time apply physical standby and trying to find how
> Real Time it is. However it seems that it is hard to find the latest SCN# on
> physical standby database, v$database (current_scn) only gets updated to the
> last scn which has been archived. Here is what I got:
>
>
>
> SQL> select recovery_mode from v$archive_dest_status where dest_id=2;
>
> RECOVERY_MODE
>
> -----------------------
>
> MANAGED REAL TIME APPLY
>
>
>
> SQL> select name, value, time_computed from v$dataguard_stats where
> name='apply lag';
>
> NAME VALUE TIME_COMPUTED
>
> --------------- -------------------- ------------------------------
>
> apply lag +00 00:00:00 12-JAN-2010 15:26:02
>
>
>
>
>
> *Primary last change time: *
>
>
>
> SQL> select scn_to_timestamp(current_scn) from v$database;
>
> SCN_TO_TIMESTAMP(CURRENT_SCN)
>
> ---------------------------------------------------------------------------
>
> 12-JAN-10 03.05.41.000000000 PM
>
>
>
>
>
> *Standby last change time: - *not updated after last log archived
>
>
>
> SQL> select current_scn from v$database;
>
> CURRENT_SCN
>
> -----------
>
> 579664324
>
> SQL> select scn_to_timestamp(579664324) from v$database;
>
> SCN_TO_TIMESTAMP(579664324)
>
> ---------------------------------------------------------------------------
>
> 11-JAN-10 11.27.27.000000000 PM
>
>
>
> SQL> select max(FIRST_CHANGE#),max(NEXT_CHANGE#) from v$archived_log;
>
> MAX(FIRST_CHANGE#) MAX(NEXT_CHANGE#)
>
> ------------------ -----------------
>
> 579266831 579664325
>
>
>
> Could someone share some light on this?
>
>
>
> Thanks,
>
> Lu
>
>
>

--
http://www.freelists.org/webpage/oracle-l