oracle查数据库积压任务,监控数据库表数据积压脚本（nagios）

监控数据库表数据积压脚本(nagios)

#!/bin/bash

STATE_OK=0

STATE_CRITICAL=2

SMS_BLOCK_COUNT=200

SMS_WAITSEND_COUNT=800

LOG_FILE='/tmp/check_sms.log'

export ORACLE_HOME='/u01/app/oracle/product/11.2.0.4/dbhome_1/'

rm -rf $LOG_FILE

$ORACLE_HOME/bin/sqlplus -S ***/*****@192.168.0.**:1521/**** >>/dev/null <

set echo off --是设置不把输出的结果显示在屏幕上

set feedback off --关闭结果反馈

spool $LOG_FILE --调用spool之后，所产生的所有终端显示，都会写入spool语句定义的文件中

set heading off --只显示数据，不显示字段名称

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

select sysdate from dual;

select 'sms1:'||count(*) from t_sms_send1;

select 'sms2:'||count(*) from t_sms_send2;

select 'sms3:'||count(*) from t_sms_send3;

select 'sms4:'||count(*) from t_sms_send4;

select 'waitsend:'||count(*) from t_sms_waitsend;

spool off

set echo on

set heading on

set feedback on

EOF

function check()

{

local SMS_COUNT=$(grep $1 $LOG_FILE | awk -F ':' '{print $2}')

if [ $SMS_COUNT -ge $3 ]; then

echo -n "ERROR- "

#    echo -n $(date '+%F_%T')

echo " $2 error!"

exit $STATE_CRITICAL

fi

}

if  [ -f $LOG_FILE  ];then

check 'sms1' 'sms_sender1' $SMS_BLOCK_COUNT

check 'sms2' 'sms_sender2' $SMS_BLOCK_COUNT

check 'sms3' 'sms_sender3' $SMS_BLOCK_COUNT

check 'sms4' 'sms_sender4' $SMS_BLOCK_COUNT

check 'waitsend' 'waitsend' $SMS_WAITSEND_COUNT

echo -n "OK- "

#    echo -n $(date '+%F_%T')

echo " sms_senders running..."

exit $STATE_OK

fi

echo "error."

exit $STATE_CRITICAL