oracle dg 检查,SHELL脚本检查Oracle DG备库是否已经应用归档

自动检查Oracle DG备库是否已经应用归档的shell脚本:

PS:本脚本需要先创建table：ARC_NOT_REPLY_LOG，这个表是用来存放记录的，如果不需要存放记录的话，可以将shell脚本的insert删掉。

ARC_NOT_REPLY_LOG 创建脚本:

CREATE TABLE ORAM.ARC_NOT_REPLY_LOG

(

HOSTNAME VARCHAR2(50 BYTE),

NAME VARCHAR2(100 BYTE),

SEQUENCE NUMBER,

APPLIED VARCHAR2(3 BYTE),

COMPLETION_TIME VARCHAR2(50 BYTE),

ADD_TIME DATE DEFAULT SYSDATE

)

shell脚本内容：

#!/bin/bash

#by Dbabc.net 2012/02/02

#User specific environment and startup programs

#

if [ -f ~/.bash_profile ]; then

. ~/.bash_profile

fi

export DBAEMAIL=dbabc@dbabc.net

export ORACLE_SID=dbabc

export IP='10.8.8.1'

export SMTP='smtp.dbabc.net'

export FRUSER='dba@dbabc.net'

export BCCUSER='dba@dbabc.net'

export USERPWD='pwd'

VALUE=`$ORACLE_HOME/bin/sqlplus -S /nolog ＜＜EOF

set heading off feedback off pagesize 0 verify off echo off numwidth 4

connect / as sysdba

select count(*) from v\\$archived_log where applied='NO';

exit

EOF`

if [ "$VALUE" -gt 0 ]; then

$ORACLE_HOME/bin/sqlplus -S /nolog > arc_not_reply.log ＜＜EOF

set heading off feedback off pagesize 0 verify off echo off

set lines 200

col name format a60

connect / as sysdba

alter session set nls_date_format='YYYY-MM-DDHH24:MI:SS';

select '$IP',name,sequence#,applied,completion_time from v\$archived_log where applied='NO';

exit

EOF

cat arc_not_reply.log|rev|awk {'print $1" "$2" "$3" "$4" "$5'} | rev |awk {'print "insert into ARC_NOT_REPLY_LOG(hostname,name,SEQUENCE,applied,COMPLETION_TIME) values(#"$1"#,#"$2"#,"$3",#"$4"#,#"$5"#);"'} |grep -v '##' | grep -v '#on#' | sed "s/#/'/g" |sed "s/%//g"| sed "s/ip/"${v_hostip}"/g" >ARC_NOT_REPLY_LOG.sql;

#echo "insert into ARC_NOT_REPLY_LOG(hostname,name,SEQUENCE#,applied,COMPLETION_TIME) values ( '${INTV1}', '${INTV2},'${INTV3}', '${INTV4}', '${INTV5}');" >ARC_NOT_REPLY_LOG.sql;

$ORACLE_HOME/bin/sqlplus /nolog ＜＜EOF

connect oram/oram@10.8.8.4:1521/yqpt

@ARC_NOT_REPLY_LOG.sql

commit;

exit;

EOF

/usr/local/bin/sendEmail -f $FRUSER -t ${DBAEMAIL} -bcc $BCCUSER -s $SMTP -u "$IP arclog not reply" -o message-file=arc_not_reply.log -xu "$FRUSER" -xp "$USERPWD"

exit

else

echo `date +'%F %T'`" All the archive log have replyed" >arc_replyok.txt

/usr/local/bin/sendEmail -f $FRUSER -t ${DBAEMAIL} -bcc $BCCUSER -s $SMTP -u "$IP arc_replyok" -o message-file=arc_replyok.txt -xu "$FRUSER" -xp "$USERPWD"

fi

exit

修改其中的变量即可，＜＜ 修改成半角格式，wordpress会自动转换半角的＜＜。

然后再通过crontab定时运行此脚本，实现Email预警。

实际上需要修改的地方有以下标红部分：

#!/bin/bash

#by Dbabc.net 2012/02/02

#User specific environment and startup programs

#

if [ -f ~/.bash_profile ]; then

. ~/.bash_profile

fi

export DBAEMAIL=dbabc@dbabc.net

export ORACLE_SID=dbabc

export IP='10.8.8.1'

export SMTP='smtp.dbabc.net'

export FRUSER='dba@dbabc.net'

export BCCUSER='dba@dbabc.net'

export USERPWD='pwd'

VALUE=`$ORACLE_HOME/bin/sqlplus -S /nolog ＜＜EOF

set heading off feedback off pagesize 0 verify off echo off numwidth 4

connect / as sysdba

select count(*) from v\\$archived_log where applied='NO';

exit

EOF`

if [ "$VALUE" -gt 0 ]; then

$ORACLE_HOME/bin/sqlplus -S /nolog > arc_not_reply.log ＜＜EOF

set heading off feedback off pagesize 0 verify off echo off

set lines 200

col name format a60

connect / as sysdba

alter session set nls_date_format='YYYY-MM-DDHH24:MI:SS';

select '$IP',name,sequence#,applied,completion_time from v\$archived_log where applied='NO';

exit

EOF

cat arc_not_reply.log|rev|awk {'print $1" "$2" "$3" "$4" "$5'} | rev |awk {'print "insert into ARC_NOT_REPLY_LOG(hostname,name,SEQUENCE,applied,COMPLETION_TIME) values(#"$1"#,#"$2"#,"$3",#"$4"#,#"$5"#);"'} |grep -v '##' | grep -v '#on#' | sed "s/#/'/g" |sed "s/%//g"| sed "s/ip/"${v_hostip}"/g" >ARC_NOT_REPLY_LOG.sql;

#echo "insert into ARC_NOT_REPLY_LOG(hostname,name,SEQUENCE#,applied,COMPLETION_TIME) values ( '${INTV1}', '${INTV2},'${INTV3}', '${INTV4}', '${INTV5}');" >ARC_NOT_REPLY_LOG.sql;

$ORACLE_HOME/bin/sqlplus /nolog ＜＜EOF

connect oram/oram@10.8.8.4:1521/yqpt

@ARC_NOT_REPLY_LOG.sql

commit;

exit;

EOF

/usr/local/bin/sendEmail -f $FRUSER -t ${DBAEMAIL} -bcc $BCCUSER -s $SMTP -u "$IP arclog not reply" -o message-file=arc_not_reply.log -xu "$FRUSER" -xp "$USERPWD"

exit

else

echo `date +'%F %T'`" All the archive log have replyed" >arc_replyok.txt

/usr/local/bin/sendEmail -f $FRUSER -t ${DBAEMAIL} -bcc $BCCUSER -s $SMTP -u "$IP arc_replyok" -o message-file=arc_replyok.txt -xu "$FRUSER" -xp "$USERPWD"

fi

exit

并且，/usr/local/bin/sendEmail是一个命令，希望执行成功需要先安装sendEmail

安装步骤如下：

sendEmail下载地址：

# wget //下载1.56版本

# tar -xzvf sendEmail-v1.56.tar.gz //解压后就可以使用了

# mv

sendEmail /usr/local/bin/