DG检测归档是否传输到备库

Oracle DG搭建好之后，每天的巡检都要查看归档是否传输到备库并应用，以下几步可以实现自动化检查归档是否传输到备库，
基本思路：
通过表来记录没有传输到备库的归档的信息，创建sp，记录未传输到备库的归档信息，通过crontab设定每天定时坚检查此表是否含有记录，若有记录就发mail预警。
通过以下sql检查归档是否传输到备库：

SELECT   '10.2.8.8' IP,
               LOCAL.THREAD#,
               LOCAL.SEQUENCE#,
               LOCAL.COMPLETION_TIME,
               SYSDATE
        FROM   (SELECT   THREAD#, SEQUENCE#, COMPLETION_TIME
                  FROM   V$ARCHIVED_LOG@dbabc1
                 WHERE   DEST_ID = 1 AND FIRST_TIME > SYSDATE - 1) LOCAL
       WHERE   LOCAL.SEQUENCE# NOT IN
                     (SELECT   SEQUENCE#
                        FROM   V$ARCHIVED_LOG@dbabc1
                       WHERE       DEST_ID = 2
                               AND THREAD# = LOCAL.THREAD#
                               AND FIRST_TIME > SYSDATE - 1)

1、创建table用来记录未传输的归档信息

CREATE TABLE ARC_NOT_TRANS
(
  HOSTNAME         VARCHAR2(20 BYTE),
  THREAD#          NUMBER,
  SEQUENCE#        NUMBER,
  COMPLETION_TIME  DATE,
  ADD_TIME         DATE                         DEFAULT sysdate
)

2、创建sp，将未传输的归档信息记录到表格中，这里是通过dblinkd实现的。

CREATE OR REPLACE PROCEDURE dbabc.SP_ARC_NOT_TRANS
AS
 /***************************************
 Author: dbabc.net
 Time:2011-12-07
功能：记录没有传输到备库的standby信息
**************************************/

BEGIN
   ---
   INSERT INTO dbabc.ARC_NOT_TRANS
      SELECT   '10.2.8.8' IP,
               LOCAL.THREAD#,
               LOCAL.SEQUENCE#,
               LOCAL.COMPLETION_TIME,
               SYSDATE
        FROM   (SELECT   THREAD#, SEQUENCE#, COMPLETION_TIME
                  FROM   V$ARCHIVED_LOG@dbabc1
                 WHERE   DEST_ID = 1 AND FIRST_TIME > SYSDATE - 1) LOCAL
       WHERE   LOCAL.SEQUENCE# NOT IN
                     (SELECT   SEQUENCE#
                        FROM   V$ARCHIVED_LOG@dbabc1
                       WHERE       DEST_ID = 2
                               AND THREAD# = LOCAL.THREAD#
                               AND FIRST_TIME > SYSDATE - 1);

   COMMIT;

   INSERT INTO dbabc.ARC_NOT_TRANS
      SELECT   '10.8.9.8' IP,
               LOCAL.THREAD#,
               LOCAL.SEQUENCE#,
               LOCAL.COMPLETION_TIME,
               SYSDATE
        FROM   (SELECT   THREAD#, SEQUENCE#, COMPLETION_TIME
                  FROM   V$ARCHIVED_LOG@dbabc2
                 WHERE   DEST_ID = 1 AND FIRST_TIME > SYSDATE - 1) LOCAL
       WHERE   LOCAL.SEQUENCE# NOT IN
                     (SELECT   SEQUENCE#
                        FROM   V$ARCHIVED_LOG@dbabc2
                       WHERE       DEST_ID = 2
                               AND THREAD# = LOCAL.THREAD#
                               AND FIRST_TIME > SYSDATE - 1);
   COMMIT;
END;
/

3、创建job，每天定时执行上面的sp
4、通过crontab每天定时检查table中是否有记录

#!/bin/bash
#Check archive logs if have trances to standby SCRIPTS arc_not_tracs.sh
#by dbabc.net 2011/12/07
#User specific environment and startup programs
if [ -f ~/.bash_profile ]; then
. ~/.bash_profile
fi

DBAEMAIL=dba@dbabc.net;export DBAEMAIL
VALUE=`$ORACLE_HOME/bin/sqlplus -S /nolog ＜＜EOF
set heading off feedback off pagesize 0 verify off echo off numwidth 4
conn oram/oram
SELECT count(*) FROM ARC_NOT_TRANS;
exit
EOF`
if [ "$VALUE" -gt 0 ]; then

$ORACLE_HOME/bin/sqlplus -S /nolog > arc_not_trans.log ＜＜EOF
set heading off feedback off pagesize 0 verify off echo off
set lines 200
conn oram/oram
alter session set nls_date_format='YYYY-MM-DDHH24:MI:SS';
SELECT * FROM ARC_NOT_TRANS;
insert into ARC_NOT_TRANS_MV select * from ARC_NOT_TRANS;
commit;
TRUNCATE TABLE ARC_NOT_TRANS DROP STORAGE;
exit
EOF
/usr/local/bin/sendEmail -f dbabc@dbabc.net -t ${DBAEMAIL} -s smtp.dbabc.net -u "archive log not trans" -o message-file=arc_not_trans.log -xu "dbabc@dbabc.net" -xp "pwddbabc"
exit
else
echo `date +'%F %T'`" All the archive log have trans" >arc_transok.txt
/usr/local/bin/sendEmail -f dbabc@dbabc.net -t ${DBAEMAIL}  -s smtp.dbabc.net -u "arc_transok" -o message-file=arc_transok.txt -xu "dbabc@dbabc.net" -xp "pwddbabc"
fi
exit