[Script]監控Oracle DataGuard 同步狀態

設定監控程式@standby db site

http://jhdba.wordpress.com/2009/07/16/managing-dataguard-monitoring-scripts/

export DB=SID

export ORACLE_SID=${DB}

export SPOOL_FILE=/home/oracle/script/odg_status.log;

sqlplus / as sysdba <<EOF

spool $SPOOL_FILE

set serveroutput on

set feedback off

DECLARE

last_app date;

last_recv date;

now       date;

begin

select next_time into last_app

from v\$archived_log

where sequence# = (select max(sequence#) from v\$archived_log where applied='YES');

select next_time into last_recv

from v\$archived_log

where sequence# = (select max(sequence#) from v\$archived_log );

select sysdate into now from dual;

 if ( last_recv-last_app > 1 ) or (now - last_recv > 1 ) then

 dbms_output.put_line ('Error in Oracle DataGuard Status!!! Gap exists !!!');

 dbms_output.put_line ('Last Received Archive log : '||to_char(last_recv,'YYYY/MM/DD HH24:MI:SS')||'');

 dbms_output.put_line ('Last applied Archive log : '||to_char(last_app,'YYYY/MM/DD HH24:MI:SS')||'');

 dbms_output.put_line ('Now : '||to_char(now,'YYYY/MM/DD HH24:MI:SS')||'');

 end if;

end;

/

spool off

EOF

RSTAT=`cat $SPOOL_FILE|grep "Error" |grep -v dbms| wc |awk '{print $1}'`

if [ "$RSTAT" = "0" ];

then

 res="Succeeded"

else

 res="Failed"

cat $SPOOL_FILE | mailx -s "`hostname` ODG sync status alert! " receiver@gmail.com

fi

收到的信 大概就會長這樣: