[原创] 如何使用 MRTG 监控数据库表空间利用率 - 1

一、思路 ：

分成3部分

    a）配置文件 ： Target 必须为一个脚本，该脚本位于本地 （get_cdr1_usage_info.sh），

                 其中 cdr1 为表空间的名称。该脚本的作用是执行位于另外一个服务器上的脚本

                （get_tablespace_info.sh），同时通过 wget 下载结果文件，并输出 MRTG 绘图需

                要的数据。

    b）get_tablespace_info.sh 通过 Sqlplus 执行一个 SQL 脚本，查询预先设定的表空间信息，

       得出表空间详细数据，并保存为指定文件，以便 get_cdr1_usage_info.sh 下载。

    c）SQL 脚本（tablespace_usage.sql）才是真正负责查询表空间信息的程序


二、配置文件部分 ：

[root@monitor db1]# cat cdr1.cfg

Refresh: 500
Target[cdr1]: `/etc/mrtg/db1/get_cdr1_usage_info.sh`
Directory[cdr1]:db1/cdr1
Options[cdr1]: growright,noinfo,gauge,nopercent,nolegend
#BodyTag[cdr1]:

SetEnv[cdr1]: MRTG_INT_IP="172.17.64.11" MRTG_INT_DESCR=""
MaxBytes1[cdr1]: 100
MaxBytes2[cdr1]: 90
Unscaled[cdr1]: d
Title[cdr1]: DB1:CDR_1
kmg[cdr1]:
YLegend[cdr1]: SS7_CDR_1 Usage
ShortLegend[cdr1]: %

LegendI[cdr1]: SS7_CDR_1 表空间利用率  

LegendO[cdr1]: SS7_CDR_1_INDEX 表空间利用率  

ThreshMaxI[cdr1]: 90

ThreshMaxO[cdr1]: 90
ThreshProgI[cdr1]: /etc/mrtg/send_input_alert.sh
ThreshProgO[cdr1]: /etc/mrtg/send_output_alert.sh
PageTop[cdr1]: <H1><p align=center><font color="blue" face="Trebuchet MS" size=6 >数据库服务器 DB1 的 SS7_CDR_1 表空间利用率
;

。。。（省略）

三、get_cdr1_usage_info.sh 脚本

[root@monitor db1]# cat get_cdr1_usage_info.sh

#!/bin/bash

cd /etc/mrtg/db1

rm -f cdr1_tablespace_usage_result >/dev/null 2>&1

su - n7css -c "ssh as1 /home/n7css/monitor/get_tablespace_info.sh" >/dev/null 2>&1

wget -q ftp://n7css:n7css@172.17.64.11/monitor/cdr1_tablespace_usage_result

while [ $? != 0 ]
do
        sleep 3
        wget ftp://n7css:n7css@172.17.64.11/monitor/cdr1_tablespace_usage_result
done
        cdr1_index_usage=0
        cdr1_usage=`cat cdr1_tablespace_usage_result |grep 'cdr1_used:' |cut -d ':' -f 2`
        cdr1_index_usage=`cat cdr1_tablespace_usage_result|grep 'cdr1_index_used:' |cut -d ':' -f 2`

printf "%0.0lf\n" $cdr1_usage
printf "%0.0lf\n" $cdr1_index_usage
[root@monitor db1]

四、as1 服务器上的 get_tablespace_info.sh 脚本

n7css@as1:~> cat /home/n7css/monitor/get_tablespace_info.sh
#!/bin/bash


cd ~n7css/monitor

rm -f *result *usage
 
sqlplus no7/no7@ss7 @/home/n7css/tablespace_usage.sql >/dev/null 2>&1
dos2unix -o ~n7css/cdr_tablespace_usage.lst >/dev/null 2>&1
tail -n +4 ~n7css/cdr_tablespace_usage.lst |grep "SS7_CDR_[1-5]" > cdr_tablespace_usage
tail -n +4 ~n7css/cdr_tablespace_usage.lst |grep "SS7_CDR_STATS" > cdr_stats_tablespace_usage
tail -n +4 ~n7css/cdr_tablespace_usage.lst |grep "SS7_ALARM_INDEX" > alarm_index_tablespace_usage
tail -n +4 ~n7css/cdr_tablespace_usage.lst |grep "SS7_ALARM_RECODE" > alarm_recode_tablespace_usage
cdr1_free=`cat cdr_tablespace_usage |grep '\'|awk '{print $5}' |tr -d '[:blank:]'`
cdr1_used=`echo "scale=2;100 - ${cdr1_free}" |bc`
cdr1_index_free=`cat cdr_tablespace_usage |grep '\'|awk '{print $5}' |tr -d '[:blank:]'`
cdr1_index_used=`echo "scale=2;100 - ${cdr1_index_free}" |bc`
cdr2_free=`cat cdr_tablespace_usage |grep '\'|awk '{print $5}' |tr -d '[:blank:]'`
cdr2_used=`echo "scale=2;100 - ${cdr2_free}" |bc`
cdr2_index_free=`cat cdr_tablespace_usage |grep '\'|awk '{print $5}' |tr -d '[:blank:]'`
cdr2_index_used=`echo "scale=2;100 - ${cdr2_index_free}" |bc`
cdr3_free=`cat cdr_tablespace_usage |grep '\'|awk '{print $5}' |tr -d '[:blank:]'`
cdr3_used=`echo "scale=2;100 - ${cdr3_free}" |bc`
cdr3_index_free=`cat cdr_tablespace_usage |grep '\'|awk '{print $5}' |tr -d '[:blank:]'`
cdr3_index_used=`echo "scale=2;100 - ${cdr3_index_free}" |bc`
cdr4_free=`cat cdr_tablespace_usage |grep '\'|awk '{print $5}' |tr -d '[:blank:]'`
cdr4_used=`echo "scale=2;100 - ${cdr4_free}" |bc`
cdr4_index_free=`cat cdr_tablespace_usage |grep '\'|awk '{print $5}' |tr -d '[:blank:]'`
cdr4_index_used=`echo "scale=2;100 - ${cdr4_index_free}" |bc`
cdr5_free=`cat cdr_tablespace_usage |grep '\'|awk '{print $5}' |tr -d '[:blank:]'`
cdr5_used=`echo "scale=2;100 - ${cdr5_free}" |bc`
cdr5_index_free=`cat cdr_tablespace_usage |grep '\'|awk '{print $5}' |tr -d '[:blank:]'`
cdr5_index_used=`echo "scale=2;100 - ${cdr5_index_free}" |bc`
cdr_stats_free=`cat cdr_stats_tablespace_usage | grep '\' |awk '{print $5}'|tr -d '[:blank:]'`
cdr_stats_used=`echo "scale=2;100 - ${cdr_stats_free}"|bc`
cdr_stats_index_free=`cat cdr_stats_tablespace_usage |grep '\' | awk '{print $5}' |tr -d '[:blank:]'`
cdr_stats_index_used=`echo "scale=2;100 - ${cdr_stats_index_free}" |bc`
alarm_index_free=`cat alarm_index_tablespace_usage |grep '\'|awk '{print $5}' |tr -d '[:blank:]'`
alarm_index_used=`echo "scale=2;100 - ${alarm_index_free}" |bc`
alarm_recode_free=`cat alarm_recode_tablespace_usage |grep '\'|awk '{print $5}' |tr -d '[:blank:]'`
alarm_recode_used=`echo "scale=2;100 - ${alarm_recode_free}" |bc`
echo "cdr1_used:$cdr1_used" > cdr1_tablespace_usage_result
echo "cdr1_index_used:$cdr1_index_used" >> cdr1_tablespace_usage_result
echo "cdr2_used:$cdr2_used" > cdr2_tablespace_usage_result
echo "cdr2_index_used:$cdr2_index_used" >> cdr2_tablespace_usage_result
echo "cdr3_used:$cdr3_used" > cdr3_tablespace_usage_result
echo "cdr3_index_used:$cdr3_index_used" >> cdr3_tablespace_usage_result
echo "cdr4_used:$cdr4_used" > cdr4_tablespace_usage_result
echo "cdr4_index_used:$cdr4_index_used" >> cdr4_tablespace_usage_result
echo "cdr5_used:$cdr5_used" > cdr5_tablespace_usage_result
echo "cdr5_index_used:$cdr5_index_used" >> cdr5_tablespace_usage_result
echo "cdr_stats_used:$cdr_stats_used" > cdr_stats_tablespace_usage_result
echo "cdr_stats_index_used:$cdr_stats_index_used" >>





cdr_stats_tablespace_usage_result
echo "alarm_index_used:$alarm_index_used" > alarm_index_tablespace_usage_result
echo "alarm_recode_used:$alarm_recode_used" > alarm_recode_tablespace_usage_result
n7css@as1:~>

五、tablespace_info.sql 脚本

n7css@as1:~> cat /home/n7css/tablespace_usage.sql
spool /home/n7css/cdr_tablespace_usage;
set linesize 200;
set pagesize 300;
SELECT d.status "״̬", d.tablespace_name "Ãû³Æ", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "´óС (M)", TO_CHAR(NVL(NVL(f.bytes, 0), 0)/1024/1024 ,'99G999G990D900') "Free (MB)", TO_CHAR(NVL((NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Free %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') UNION ALL SELECT d.status "״̬", d.tablespace_name "Ãû³Æ", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "´óС (M)", TO_CHAR(NVL((a.bytes-t.bytes), a.bytes)/1024/1024,'99G999G990D900') "Free (MB)", TO_CHAR(NVL((a.bytes-t.bytes) / a.bytes * 100, 100), '990D00') "Free %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';
spool off;
exit;


n7css@as1:~>

六、实际效果