自動監控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

轉載請註明出處