Monitor Data Guard Script


SET LINESIZE 140
SET PAGESIZE 124
SET HEAD OFF
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL RECEIVED_TIME FORMAT A20
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A20

SELECT TO_CHAR (SYSDATE, 'mm-dd-yyyy hh24:mi:ss') "Current Time" FROM DUAL;

SELECT DB_NAME,
       HOSTNAME,
       APPLIED_TIME,
       RECEIVED_TIME,
       LOG_ARCHIVED,
       LOG_APPLIED,
       LOG_ARCHIVED - LOG_APPLIED LOG_GAP,
       (CASE
           WHEN (   (    APPLIED_TIME IS NOT NULL
                     AND (LOG_ARCHIVED - LOG_APPLIED) IS NULL)
                 OR (    APPLIED_TIME IS NULL
                     AND (LOG_ARCHIVED - LOG_APPLIED) IS NOT NULL)
                 OR ( (LOG_ARCHIVED - LOG_APPLIED) > 2))
           THEN
              'Error in Log Gap'
           ELSE
              'No Log GAP'
        END)
          Status
  FROM (SELECT INSTANCE_NAME DB_NAME
          FROM GV$INSTANCE
         WHERE INST_ID = 1),
       (SELECT MAX (SEQUENCE#) LOG_ARCHIVED
          FROM V$ARCHIVED_LOG
         WHERE DEST_ID = 1 AND ARCHIVED = 'YES' AND THREAD# = 1),
       (SELECT MAX (SEQUENCE#) LOG_APPLIED
          FROM V$ARCHIVED_LOG
         WHERE DEST_ID = 2 AND APPLIED = 'YES' AND THREAD# = 1),
       (SELECT TO_CHAR (MAX (COMPLETION_TIME), 'DD-MON-YY:HH24:MI:SS') APPLIED_TIME
                       FROM V$ARCHIVED_LOG
         WHERE DEST_ID = 2 AND APPLIED = 'YES' AND THREAD# = 1),
          (SELECT TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') RECEIVED_TIME
                  FROM v$archived_log
                 WHERE sequence# =
                          (SELECT MAX (sequence#) FROM v$archived_log) AND DEST_ID = 2 AND THREAD# = 1   )  RECEIVED_TIME,
            (SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,'.')-1))))) HOSTNAME

FROM V$INSTANCE)


Output looks like below

06-26-2014 13:59:10
1 row selected.

PEMSAPMS DEVDATABASE  26-JUN-14:11:02:09   26-JUN-14:13:26:07           1162        1161       1 No Log GAP      

1 row selected.

ref : http://www.oracle-ckpt.com/script-to-monitor-primary-and-standby-databases/