oracle的tablespaces使用情况监控

环境：oracle11G、linux

脚本作用：检测数据库的表空间使用量，大于设置法值通过短信告警通知相关人员。

思路：登陆数据库查询出所有表空间使用情况——通过shell脚本分析结果——找出大于设置值得表空间——短信预警（也可以改成邮件预警）

链接: https://pan.baidu.com/s/1SCOa1a0BMc_8qS2ppQ_Zxg 提取码: 36jw 

脚本：cat /u01/check-tablespaces.sh

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61

#! /bin/bash source /etc/profile source ~/.bash_profile sqlplus / as sysdba >/tmp/oracle.txt << EOF   set lines 800; set pages 800; col tablespace_name for a30; col used_pct for a10;   select /*+ parallel(8) */ total.TABLESPACE_NAME TABLESPACE_NAME, total.MAX_SIZE_MB MAX_SIZE_MB, total.SIZE_MB-free.USED_FREE_MB USED_SIZE_MB, to_char(round(((total.SIZE_MB-free.USED_FREE_MB)/total.MAX_SIZE_MB)*100,2),'fm99999999990.00')|| '%'  USED_PCT, total.MAX_SIZE_MB-(total.SIZE_MB-free.USED_FREE_MB) Free_MB from     (select TABLESPACE_NAME, sum(BYTES)/1024/1024 SIZE_MB, sum(decode(MAXBYTES, 0 , BYTES,MAXBYTES ))/1024/1024 MAX_SIZE_MB  from dba_data_files group by TABLESPACE_NAME) total,     (select TABLESPACE_NAME, sum(BYTES)/1024/1024 USED_FREE_MB from dba_free_space group by TABLESPACE_NAME) free     where total.TABLESPACE_NAME=free.TABLESPACE_NAME and total.TABLESPACE_NAME not like 'UNDO%'; quit; EOF sed -i 1,12d /tmp/oracle.txt sed -i -e '/selected/,$d' /tmp/oracle.txt sed -i '$d' /tmp/oracle.txt sed -i 1,2d /tmp/oracle.txt    #查询表空间大于90%的表空间 echo tablespaces-$(date +"%Y-%m-%d")>>/tmp/tbplesjg.txt cat /tmp/oracle.txt|while read line  do  m=`echo $line |awk '{print $1}'`  b=`echo $line |awk '{print $4}'|awk -F. '{print $1}'` if [ $b -gt 90 ]    then       echo "$m使用百分之$b" >> /tmp/tbplesjg.txt    else     echo "$m $b%"   fi   done   #发送短信   sb=`tac /tmp/tbplesjg.txt|sed '/tablespaces/q'|tac|grep '使用'`   if [ -z "$sb" ]   then     echo '文件为空'   else     a_phon=("1862***8189" "15283***025" "1801***5583" )     for element in ${a_phon[@]}         do           curl -X POST 'http://100.64.139.22/sjba/service/dxts/test' -d "phone=$element&content=`tac /tmp/tbplesjg.txt|sed '/tablespaces-2/q'|tac|tail -n 3`"         done fi

　配置定时任务 

?

1 2	[oracle@db]crontab -e 30 8 * * * /u01/check-tablespaces.sh