自动监控Oracle 表空间信息并发送邮件脚本

  服务器越来越多了，日常管理起来也越来越麻烦，也没找到特别称心如意的工具，如何自动的监控Oracle 告警日志和表空间信息呢？加入一台服务器有多个实例又如何监控呢？自己动手写了一个小shell，贴出来以备后续更新！若有更好的可以不防共享出来哈
#!/bin/ksh
# script name is check_tablespace.sh
# created date: 2015-01-05
# guijian


###################################  check information about oracle tablespace  ####################################
if [ -d "$HOME/.check_tbs" ];then
  echo "dir:$HOME/.check_tbs exists"
else
  mkdir $HOME/.check_tbs
  echo "dir:$HOME/.check_tbs  created!"
fi


host_name=`hostname`
host_ip=`grep Crsz6scsdb02 /etc/hosts | grep -v '127.0.1.1' | grep -v localhost`
mail_list="guijian5@crc.com.hk"


oper_version=`uname -a | awk '{print $1}'`
if [ $oper_version = "Linux" ];then
  ora_sid=`ps -ef | grep ora_pmon | grep -v grep |awk '{print $8}'|cut -d '_' -f3 | xargs`
  sh $HOME/.bash_profile
else
  ora_sid=`ps -ef | grep ora_pmon | grep -v grep |awk '{print $9}'|cut -d '_' -f3 | xargs`
  sh $HOME/.profile
fi  


echo "ora_sid:$ora_sid"
cat "#########################  check information about oracle tablespace  ##########################" > $HOME/.check_tbs/tbs.log
for i in $ora_sid
do
 export ORACLE_SID=$i
 echo $ORACLE_SID


 alert_logfile=`find $ORACLE_BASE/ -name alert_$ORACLE_SID.log`
 cd $logdir
 number1=`cat $HOME/.check_tbs/tmp_alert_number`
 sed -n "$number1,\$p"  $alert_logfile > $HOME/.check_tbs/tmp_alert.log
 wc -l $alert_logfile | awk '{print $1}' > $HOME/.check_tbs/tmp_alert_number


 cat $HOME/.check_tbs/tmp_alert.log | grep -E 'ORA-|RMAN-' > $HOME/.check_tbs/alertlog_check.log
  sqlplus -S /nolog<>$HOME/.check_tbs/tbs.log
 conn / as sysdba
 set line 140 pagesize 9999
 col tablespace_name for a30
 select instance_name,status from gv\$instance;
 select T.tablespace_name,T.TOTAL_SPACE_MB,(T.TOTAL_SPACE_MB-F.FREE_SPACE_MB) "USED_SPACE_MB",F.FREE_SPACE_MB,(100-ROUND((T.TOTAL_SPACE_MB-F.FREE_SPACE_MB)/T.TOTAL_SPACE_MB*100,2)) "FREE_RATE %",ROUND((T.TOTAL_SPACE_MB-F.FREE_SPACE_MB)/T.TOTAL_SPACE_MB*100,2) "USED_RATE %"   FROM (SELECT TABLESPACE_NAME,SUM(BYTES/1024/1024) "TOTAL_SPACE_MB" FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) T,(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES/1024/1024),2) "FREE_SPACE_MB" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F  WHERE T.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 6;
EOF
done
sed '/^$/d' $HOME/.check_tbs/tbs.log > $HOME/.check_tbs/tablespace.log
echo "#################################### alert log check #########################################" >> $HOME/.check_tbs/tablespace.log
cat $HOME/.check_tbs/alertlog_check.log >> $HOME/.check_tbs/tablespace.log
cat $HOME/.check_tbs/tablespace.log |mail -s "INSTANCE_NAME : $ora_sid HOST NAME AND IP ADDRESS : $host_ip" $mail_list

转载请注明出处

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