1、建立监控脚本:
$cat check_tablespace_health_report.sh
#!/bin/sh source ~/.bash_profile #定义变量 v_datetime=`date +"%Y-%m-%d %H:%M:%S"` v_username='oracle' v_hostname=`hostname` v_dbname='DB01' #表空间大小报警阀值,单位:M v_free_space=5000 #表空间使用百分比报警阀值 v_used_rate=85 #发送人 v_sendmail='XXXXX@139.com' v_mailname='XXXXX@139.com' v_password='XXXXX' #smtp服务器IP或域名:smtp.qq.com v_smtpserver='XXX.XXX.XXX.XXX' #接收人,多个联系人用空格分开 v_receivemail='XXXXX@139.com XXXXX@163.com' #路径 v_path="/home/${v_username}/scripts" v_logfile_temp="${v_path}/tablespace_health_report.temp" sqlplus -S / as sysdba <<EOF spool $v_logfile_temp set heading off feedback off pagesize 0 verify off echo off time off timing off linesize 1000 SELECT '表空间' || D.TABLESPACE_NAME || '当前大小为:' || SPACE || 'M,可用空间为:' || FLOOR(F.FREE_SPACE) || 'M,空间使用率为:' || ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%,空间实际使用率为:' || ROUND((SPACE - NVL(FREE_SPACE, 0)) / DECODE(D.MAXBYTES / 1024 / 1024, 0, SPACE, D.MAXBYTES / 1024 / 1024) * 100, 2) || '%' FROM (SELECT TABLESPACE_NAME, SUM(DECODE(autoextensible, 'YES', MAXBYTES, USER_BYTES)) MAXBYTES, nvl(SUM(BYTES) / 1024 / 1024, SUM(BYTES)) SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) AND (ceil((1 - NVL(FREE_SPACE, 0) / SPACE) * 100) >= $v_used_rate and FLOOR(F.FREE_SPACE) <= $v_free_space); spool off set echo on set feedback on set heading on exit EOF if [ `cat ${v_logfile_temp}|wc -l` -gt 0 ] ; then echo "############################" > ${v_path}/tablespace_health_report.log echo "USER: ${v_username}" >> ${v_path}/tablespace_health_report.log echo "HOSTNAME: ${v_hostname}" >> ${v_path}/tablespace_health_report.log echo "DBNAME: ${v_dbname}" >> ${v_path}/tablespace_health_report.log echo "############################" >> ${v_path}/tablespace_health_report.log echo "数据库${v_dbname}表空间告警:" >> ${v_path}/tablespace_health_report.log cat ${v_logfile_temp} >> ${v_path}/tablespace_health_report.log ${v_path}/sendEmail -f ${v_sendmail} -t ${v_receivemail} -s ${v_smtpserver} -u "数据库${v_dbname}表空间报警邮件 - ${v_datetime}" -xu ${v_mailname} -xp ${v_password} -o message-content-type=text -o message-charset=GB2312 message-file=${v_path}/tablespace_health_report.log -l ${v_path}/check_tablespace_health_report.log rm ${v_logfile_temp} fi
2、配置crontab,每一分钟执行一次
$crontab -l
#oracle tablespace check */10 * * * * /home/oracle/scripts/check_tablespace_health_report.sh >/dev/null 2>&1
转载于:https://blog.51cto.com/8858975/1401985