oracle数据库状态监控

1、功能介绍

    1、检查oracle数据库的运行状态

    2、第一次失败后，等待5秒钟再次检查一次

2、脚本

#!/bin/bash
source /etc/profile
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
w=$(cd $(dirname $0);pwd)

#检查数据库的方法
fun(){
result=$(sqlplus -s ${user}/${pass}@${database} <<EOF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
${sql_query}
EOF
)
new_result=$(echo "$result"|tr -d '[:space:]')

#判断结果情况
if [[ $new_result -eq 'OPEN' ]];
   then
     echo oracle数据库${hj}'连接成功'
   else
     echo oracle数据库${hj}'连接失败' >>$w/online-check-tmp.txt
fi

}
#配置参数调用函数
check(){
rm -rvf $w/online-check-tmp.txt
#生产库
user="system"
pass="pwssswd"
sql_query="select status from v\$instance;"
database="192.168.1.1:1521/scrk"
hj="prod-db1"
fun ${user} ${pass} ${database} ${sql_query} ${hj}

database="192.168.1.2:1521/scrk"
hj="prod-db2"
fun ${user} ${pass} ${database} ${sql_query} ${hj}

database="192.168.1.3:1521/scrk"
hj="prod-db3"
fun ${user} ${pass} ${database} ${sql_query} ${hj}

database="192.168.1.4:1521/scrk"
hj="prod-db4"
fun ${user} ${pass} ${database} ${sql_query} ${hj}

#查询库
user="system"
pass="pwssswd"
database="192.168.1.221:1521/rkqry"
hj="query-db221"
fun ${user} ${pass} ${database} ${sql_query} ${hj}

database="192.168.1.222:1521/rkqry"
hj="query-db222"
fun ${user} ${pass} ${database} ${sql_query} ${hj}

database="192.168.1.11:1521/rkqry"
hj="query-db11"
fun ${user} ${pass} ${database} ${sql_query} ${hj}
}

check
#判断是否有失败，失败重新检测一次，如果还失败将结果输出到文档
if [[ -e "$w/online-check-tmp.txt" ]];
   then
     sleep 5
     check
     if [[ -e "$w/online-check-tmp.txt" ]];
        then
         cat $w/online-check-tmp.txt >>/var/ftp/pub/$(date +"%Y-%m-%d")
        else
         echo 'all 2-ok!'
     fi  
   else
     echo 'all ok!'
fi