核心數(shù)據(jù)庫10.100.0.20和21表空間已經修改完成，可以正常監(jiān)控使用率和大小，并郵件告警通知。
修改Oracle數(shù)據(jù)庫表空間監(jiān)控
創(chuàng)建表空間信息收集腳本：
vim /home/oracle/scripts/check_tablespace.sh
腳本內容如下：
#!/bin/bash
# tablespace usagep check
source ~/.bash_profile
function check {
sqlplus -S "/ as sysdba" << EOF
set numwidth 20
set linesize 200
set pagesize 200
spool /tmp/ora_tablespace.txt
select aa.tablespace_name,
round(bb.maxs, 2) "MAX(G)",
round(aa.usd, 2) "USED(G)",
round((bb.maxs - aa.usd) , 2) "FREE(G)",
round(aa.usd / bb.maxs * 100, 2) "USED%"
from (Select b.Tablespace_Name, (b.Bytes - Nvl(a.Bytes, 0)) "USD"
From (select tablespace_name, sum(bytes) bytes
from Dba_Free_Space
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from Dba_Data_Files
group by tablespace_name) b
Where b.tablespace_name = a.tablespace_name(+)
-- and b.tablespace_name='MAXIMO_DATA'
) aa,
(select tablespace_name,
sum(case
when maxbytes = 0 then
user_bytes
else
maxbytes
end) "MAXS"
from dba_data_files
-- where tablespace_name='MAXIMO_DATA'
group by tablespace_name
order by tablespace_name) bb
where aa.tablespace_name = bb.tablespace_name and aa.tablespace_name not like '%UNDO%'
order by 5 desc, 1;
spool off
set linesize 100
set pagesize 100
spool /tmp/ora_autex.txt
select tablespace_name,autoextensible from dba_data_files;
spool off
quit
EOF
};check &>/dev/null
賦予執(zhí)行權限
chmod +x /home/oracle/scripts/check_tablespace.sh
配置計劃任務(oracle用戶)
*/5 * * * * /home/oracle/scripts/check_tablespace.sh
手動執(zhí)行腳本并查看結果
表空間腳本執(zhí)行結果
自動發(fā)現(xiàn)腳本內容如下：
# cat discovery_oracle_tablespace.sh
#!/bin/bash
#zabbix discovery oracle tablespace
table_spaces=(`cat /tmp/ora_tablespace.txt | sed -e "1,3d" -e "/^$/d" -e "/selected/d" | awk '{print $1}'`)
length=${#table_spaces[@]}
printf "{\n"
printf '\t'"\"data\":["
for ((i=0;i<$length;i++))
do
printf "\n\t\t{"
printf "\"{#TABLESPACE_NAME}\":\"${table_spaces[$i]}\"}"
if [ $i -lt $[$length-1] ];then
printf ","
fi
done
printf "\n\t]\n"
printf "}\n"
執(zhí)行結果
# ./discovery_oracle_tablespace.sh
{
"data":[
{"{#TABLESPACE_NAME}":"EMA"},
{"{#TABLESPACE_NAME}":"SYSAUX"},
{"{#TABLESPACE_NAME}":"MHWEB"},
{"{#TABLESPACE_NAME}":"SYSTEM"},
{"{#TABLESPACE_NAME}":"OGG"},
{"{#TABLESPACE_NAME}":"USERS"},
{"{#TABLESPACE_NAME}":"QDPTTBS"},
{"{#TABLESPACE_NAME}":"UYUN"},
{"{#TABLESPACE_NAME}":"LINKSCAPEH5DB"},
{"{#TABLESPACE_NAME}":"WX_HUANGHE_TBS"}
]
}
監(jiān)控項腳本內容如下：
# cat tablespace_check.sh
#!/bin/bash
# oracle tablespace check
CEHCK_TYPE=$1
TABLESPACE_NAME=$2
function usagepre {
grep "\b$TABLESPACE_NAME\b" /tmp/ora_tablespace.txt | awk '{printf "%.f\n",$5}'
# grep "\b$TABLESPACE_NAME\b" /tmp/ora_tablespace.txt | awk '{printf "%.f\n",$3/$2*100}'
}
function available {
grep "\b$TABLESPACE_NAME\b" /tmp/ora_tablespace.txt | awk '{printf $4}'
# grep "\b$TABLESPACE_NAME\b" /tmp/ora_tablespace.txt | awk '{printf ($2-$3)*1024*1024}'
}
function check {
if grep "\b$TABLESPACE_NAME\b" /tmp/ora_autex.txt | awk '{print $2}' | uniq | grep "YES" &>/dev/null;then
echo 1
else
echo 0
fi
}
case $CEHCK_TYPE in
pre)
usagepre ;;
fre)
available ;;
check)
check ;;
*)
echo -e "Usage: $0 [pre|fre|check] [TABLESPACE_NAME]"
esac
Agent參數(shù)配置：
# cat /etc/zabbix/zabbix_agentd.d/oracle_tablespace.conf
UserParameter=discovery.oracle.tablespace,/etc/zabbix/scripts/discovery_oracle_tablespace.sh
UserParameter=tablespace.check[*],/etc/zabbix/scripts/tablespace_check.sh $1 $2
重啟zabbix-agent
主機添加模板
添加自動發(fā)現(xiàn)模板
等待一會后查看監(jiān)控結果：
表空間監(jiān)控的數(shù)據(jù)
監(jiān)控指標圖形展示：
這個的告警我就不去模擬了，因為都是生產環(huán)境的，下圖為4月份的TEMP表空間告警郵件截圖：
郵件通知表空間告警
