在linux/aix下用邮件对表空间报警

来源：互联网发布：material design软件编辑：程序博客网时间：2024/05/22 03:13

自己写了一个表空间报警程序。针对自动扩展的数据文件，有一个file limitation的问题，如8k的数据库，数据文件的限制是32g,脚本针对这个问题也可以报警。

1.setup service.txt -->将server 上需要监控的service 名写入一个文件：
$more service.txt
db1
db2
db3

2.在每个数据库创建用户oper并授予以下权限
grant select on SYS.SM$TS_AVAIL to oper;
grant select on SYS.SM$TS_USED to oper;
grant select on SM$TS_FREE to oper;
grant select on v_$database to oper;
grant select on v_$parameter to oper;
grant select on dba_data_files to oper;
grant select on dba_extents to oper;
grant select on dba_free_space to oper;
grant connect,resource to oper;

3.用oper创建以下存储过程：
CREATE OR REPLACE PROCEDURE Space_Monitor
IS
var_dbname v$database.NAME%TYPE;
var_blocksize v$parameter.VALUE%TYPE;
var_filelimit NUMBER;
CURSOR cur_tabalert IS
SELECT v1.file_name,v1.autoextensible,num1 total_space,
num3 free_space,
num1-num3 "USED_SPACE"
FROM
(SELECT file_name,file_id,autoextensible,SUM(bytes) num1 FROM DBA_DATA_FILES GROUP BY file_name,file_id,autoextensible) v1,
(SELECT file_id,SUM(BYTES) num3 FROM DBA_FREE_SPACE GROUP BY file_id) v3
WHERE v1.file_id=v3.file_id(+);

CURSOR cur_tabspacealt IS
SELECT TABLESPACE_NAME,TOTAL,USED,FREE,USED_CENT,FREE_CENT FROM (
SELECT A.TABLESPACE_NAME,TRUNC(A.BYTES/1024/1024) TOTAL,TRUNC(B.BYTES/1024/1024) USED, TRUNC(C.BYTES/1024/1024) FREE,
ROUND((B.BYTES*100)/A.BYTES,2) USED_CENT,ROUND((C.BYTES*100)/A.BYTES,2) FREE_CENT
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME) ;

BEGIN
SELECT NAME INTO var_dbname FROM v$database;
SELECT VALUE INTO var_blocksize FROM v$parameter WHERE NAME='db_block_size';
SELECT POWER(2,22)*var_blocksize INTO var_filelimit FROM dual;
FOR rec_tabalert IN cur_tabalert
    LOOP
   IF rec_tabalert.autoextensible='YES'
         THEN
   IF rec_tabalert.used_space/var_filelimit >0.9 THEN
       DBMS_OUTPUT.PUT_LINE('File: '||rec_tabalert.file_name||' in database '||var_dbname||' is autoextend, '||' Now Used% is >90%');
          END IF;
     END IF;
END LOOP;
FOR rec_tabspacealt IN cur_tabspacealt
    LOOP
   IF rec_tabspacealt.FREE_CENT<5 THEN
    DBMS_OUTPUT.PUT_LINE('Free space of Tablespace '||rec_tabspacealt.tablespace_name||' in database '||var_dbname||' is <5%');
    END IF;
    END LOOP;
END;
/

4.运行以下脚本：
￥more space_monitor.ksh
. /etc/profile
. $HOME/.profile

USER="user@126.com 139xxxxxx@139.com" --利用139邮箱的短信功能进行实时报警

for i in `cat /u80/scripts/service.txt | awk -F: ' {print $1}'`
do
sqlplus oper/oper@$i << EOF
set serveroutput on
set echo off
set term off
set verify off
set feedback off
spool /u80/scripts/space_monitor_$i
exec space_monitor;
spool off
EOF

#checking
#echo "FILE: space_monitor_${i}.lst"
if (( `cat /u80/scripts/space_monitor_${i}.lst|wc -l` > 2 ))
then
sed -e '1d;$d' /u80/scripts/space_monitor_${i}.lst|mail -s "${i} space is not enough" "${USER}"
fi
done