数据库监控脚本

# 定义日志文件存放的目录和日志文件名，将当前用户目录设置为LOG_PATH
LOG_PATH=/arch/check
LOG_FILE=/arch/check/logfile
CPU_VALUE=85
DISK_VALUE=85
TS_VALUE=85


# 检查 CPU的使用情况
echo "***************************************** Check CPU *****************************************" >$LOG_FILE
vmstat 1 10 | awk '{print $0;if($1 ~ /^[0-9].*/) (totalcpu+=$16);(avecpu=100-totalcpu/10)}; END {print "The average usage of cpu is :"avecpu}' >$LOG_PATH/cpu_info
 
cat $LOG_PATH/cpu_info >>$LOG_FILE
 
cpu_used_pct=$(cat $LOG_PATH/cpu_info | grep "The average usage of cpu is" |awk -F ":" '{print $2;}')
 if  [  "$cpu_used_pct"  -gt  "$CPU_VALUE" ] ; then
echo "LOG-Warnning:'date +%Y'-'%m'-'%d' '%H':'%M':'%S', The CPU usage is up to $cpu_used_pct%. Please check the system." >>$LOG_FILE
else
echo "  The CPU load is OK!!" >>$LOG_FILE
fi


# 内存使用监控，包括交换区的使用情况监控 
echo >>$LOG_FILE
echo >>$LOG_FILE
echo "***************************************** check memory useage *****************************************" >>$LOG_FILE
cat $LOG_PATH/cpu_info | awk '{print $0;if($1 ~ /^[0-9].*/) (totalpi+=$6)(totalpo+=$7)};
END {if(totalpi<10 && totalpo<10) print " The memory usage  is OK!!"; if(totalpi>10 || totalpo>10) print "The memory pagein  and pageout is to high,Please check the usage of the memory!"} ' >>$LOG_FILE


# 检查磁盘空间. 
echo >>$LOG_FILE
echo "***************************************** check disk space *****************************************">>$LOG_FILE
df -k >>$LOG_FILE
df -k |grep -v proc |grep -v Filesystem |awk '{x=1*$4}{print $1","$2","$3","$4","$5","$6","$7}'>$LOG_PATH/disk_info
 
cat $LOG_PATH/disk_info | grep -v '^#' | grep -v '-' | while read line
do
item1=$(echo $line | awk -F ',' '{print $1}')
item2=$(echo $line | awk -F ',' '{print $2}')
item3=$(echo $line | awk -F ',' '{print $3}')
item4=$(echo $line | awk -F ',' '{print $4}' |awk -F '%' '{print $1}')
item5=$(echo $line | awk -F ',' '{print $5}')
item6=$(echo $line | awk -F ',' '{print $6}')
item7=$(echo $line | awk -F ',' '{print $7}')
if [ "$item4" -gt "$DISK_VALUE" ]; then
echo "LOG-Warnning: `date +%Y'-'%m'-'%d' '%H':'%M':'%S`,$item7 is not have enough space ,please check." >>$LOG_FILE/logfile
echo " The space of disk $item7 is OK!!" >>$LOG_FILE
fi
done


# 检查磁盘I/O
echo >>$LOG_FILE
echo >>$LOG_FILE
echo "***************************************** check iostat *****************************************">>$LOG_FILE
iostat 1 3 >>$LOG_FILE


# 对网络流量进行监控
echo >>$LOG_FILE
echo >>$LOG_FILE
echo "***************************************** check netstat *****************************************">>$LOG_FILE
netstat -i >>$LOG_FILE
 
# Check the oracle background processes . 
echo >>$LOG_FILE
echo >>$LOG_FILE
echo "***************************************** check oracle process *****************************************">>$LOG_FILE
ps -ef | grep ora_ | grep -v grep | awk -F '-' '{print $2}' | awk '{print $2}' >$LOG_FILE/ora_process_info
ps -ef | grep ora_ | grep -v grep >>$LOG_FILE
 
# background process ckpt 
if [ `grep ora_ckpt_kwerpdb1 $LOG_PATH/ora_process_info` ]; then
COUNT=1
else
echo "LOG-Warnning: `date +%Y'-'%m'-'%d' '%H':'%M':'%S`,The Process ora_ckpt_kwerpdb1 was terminated!" >>$LOG_FILE
fi
 
# background process dbwr 
if [ `grep ora_dbw0_kwerpdb1 $LOG_PATH/ora_process_info` ]; then
COUNT=$((COUNT+1))
else
echo "LOG-Warnning: `date +%Y'-'%m'-'%d' '%H':'%M':'%S`,The Process ora_dbw0_kwerpdb1 was terminated !" >>$LOG_FILE
fi
 
# background process reco 
if [ `grep ora_reco_kwerpdb1 $LOG_PATH/ora_process_info` ]; then
COUNT=$((COUNT+1))
else
echo "LOG-Warnning: `date +%Y'-'%m'-'%d' '%H':'%M':'%S`,The Process ora_reco_kwerpdb1 was terminated !" >>$LOG_FILE
fi
 
# background process lgwr
if [ `grep ora_lgwr_kwerpdb1 $LOG_PATH/ora_process_info` ]; then
COUNT=$((COUNT+1))
else
echo "LOG-Warnning: `date +%Y'-'%m'-'%d' '%H':'%M':'%S`,The Process ora_lgwr_kwerpdb1 was terminated !" >>$LOG_FILE
fi
 
# background process pmon
if [ `grep ora_pmon_kwerpdb1 $LOG_PATH/ora_process_info` ]; then
COUNT=$((COUNT+1))
else
echo "LOG-Warnning: `date +%Y'-'%m'-'%d' '%H':'%M':'%S`,The Process ora_pmon_kwerpdb1 was terminated !" >>$LOG_FILE
fi
 
# background process smon 
if [ `grep ora_smon_kwerpdb1 $LOG_PATH/ora_process_info` ]; then
COUNT=$((COUNT+1))
else
echo "LOG-Warnning: `date +%Y'-'%m'-'%d' '%H':'%M':'%S`,The Process ora_smon_kwerpdb1 was terminated !" >>$LOG_FILE
fi
 
if [ "$COUNT" -eq 6 ];then
echo >>$LOG_FILE
echo " The main six Oracle processes is OK !!" >>$LOG_FILE
else
:
fi


# 
# Check the oracle tablespace.
# 
echo >>$LOG_FILE
echo >>$LOG_FILE
echo "***************************************** check oracle tablespace *****************************************">>$LOG_FILE
sqlplus -s must/must < $LOG_PATH/ts_info
set linesize 140 pagesize 10000
col "Status" for a10
col "Name" for a25
col "Type" for a10
col "Extent" for a15
col "Size (M)" for a20
col "Used (M)" for a20
col "Used %" for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990') "Size (M)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'999,999,999') "Used (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_data_files
group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,999') "Size (M)",
TO_CHAR(NVL(t.bytes,0)/1024/1024,'999,999,999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %" FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select
tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t WHERE
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND
d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY'
ORDER BY 7;
exit
!EOF
 
cat $LOG_PATH/ts_info>>$LOG_FILE
cat $LOG_PATH/ts_info |grep ONLINE |awk '{print $2":"$3":"$4":"$5}' |while read line
do
ts_name=$(echo $line |awk -F ':' '{print $1}')
ts_total=$(echo $line |awk -F ':' '{print $2}')
ts_used=$(echo $line |awk -F ':' '{print $3}')
ts_used_pct=$(echo $line |awk -F ':' '{print $4}' |awk -F '%' '{print $1}'|awk -F '.' '{print $1}')
if [ "$ts_used_pct" -gt "$TS_VALUE" -o "$ts_used_pct" -eq "$TS_VALUE" ]; then
echo "LOG-Warnning: `date +%Y'-'%m'-'%d' '%H':'%M':'%S`,表空间$ts_name 的剩余空间紧张,请尽快清理表空间!" >>$LOG_FILE 
else
echo " The tablespace of $ts_name is OK!!" >>$LOG_FILE 
fi
done


# 
# Check the oracle session.
# 
sqlplus -s must/must <>$LOG_FILE
select 'The Total sessions number is '||count(*)||'.' from v$session ; 
select 'table mt: ' ,count(*) from t_dxh_mt where msgresult='SUCCESS';
select 'table detect:' ,count(*) from t_dxh_opendetect where msgresult='SUCCESS';
exit
!

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30268819/viewspace-1680743/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30268819/viewspace-1680743/