zabbix監(jiān)控oracle表空間的操作方法

更新時間：2024年04月22日 11:07:59 作者：hunao384

Zabbix是一款開源的網(wǎng)絡監(jiān)控和管理系統(tǒng),可以用于監(jiān)控各種網(wǎng)絡設備、服務器和應用程序等,本文給大家介紹了zabbix監(jiān)控oracle表空間的操作方法,并通過代碼示例和圖文講解的非常詳細,需要的朋友可以參考下

1、在agent上面準備腳本

查看oracle表空間的sql

set linesize 300
set tab off
set pagesize 1000
col TABLESPACENAME format a30
SELECT SUBSTR(a.TABLESPACE_NAME,1,30) TablespaceName,
round(SUM(a.bytes/1024/1024/1024),2) AS "Totle_size(G)",
round(SUM(NVL(b.free_space1/1024/1024/1024,0)),2) AS "Free_space(G)",
round(SUM(a.bytes/1024/1024/1024),2)-round(SUM(NVL(b.free_space1/1024/1024/1024,0)),2) AS "Used_space(G)",
ROUND((SUM(a.bytes/1024/1024/1024)-SUM(NVL(b.free_space1/1024/1024/1024,0))) *100/SUM(a.bytes/1024/1024/1024),2) AS "Used_percent%",
round(SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024/1024),2) AS "Max_size(G)",
ROUND((SUM(a.bytes/1024/1024/1024)-SUM(NVL(b.free_space1/1024/1024/1024,0)))*100/SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024/1024),2) AS "Max_percent%"
FROM dba_data_files a,
(SELECT SUM(NVL(bytes,0)) free_space1,
file_id
FROM dba_free_space
GROUP BY file_id
) b
WHERE a.file_id = b.file_id(+)
GROUP BY a.TABLESPACE_NAME
ORDER BY "Used_percent%" desc;

(1)定義查表空間使用率的腳本 /home/oracle/get_tablespace_usage.sh

#!/bin/bash
# get tablespace usage
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1/
export PATH=$ORACLE_HOME/bin:$PATH
source /etc/profile
source ~/.oracle_profile
#source ~/.bash_profile
function check {
sqlplus -S "/ as sysdba" <<  EOF
set linesize 300
set tab off
set pagesize 1000
col TABLESPACENAME format a30
spool /tmp/tablespace.log
SELECT SUBSTR(a.TABLESPACE_NAME,1,30) TablespaceName,
round(SUM(a.bytes/1024/1024/1024),2) AS "Totle_size(G)",
round(SUM(NVL(b.free_space1/1024/1024/1024,0)),2) AS "Free_space(G)",
round(SUM(a.bytes/1024/1024/1024),2)-round(SUM(NVL(b.free_space1/1024/1024/1024,0)),2) AS "Used_space(G)",
ROUND((SUM(a.bytes/1024/1024/1024)-SUM(NVL(b.free_space1/1024/1024/1024,0))) *100/SUM(a.bytes/1024/1024/1024),2) AS "Used_percent%",
round(SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024/1024),2) AS "Max_size(G)",
ROUND((SUM(a.bytes/1024/1024/1024)-SUM(NVL(b.free_space1/1024/1024/1024,0)))*100/SUM((case when a.MAXBYTES = 0 then a.bytes else a.MAXBYTES end)/1024/1024/1024),2) AS "Max_percent%"
FROM dba_data_files a,
(SELECT SUM(NVL(bytes,0)) free_space1,
file_id
FROM dba_free_space
GROUP BY file_id
) b
WHERE a.file_id = b.file_id(+)
GROUP BY a.TABLESPACE_NAME
ORDER BY "Used_percent%" desc;
set feedback off heading off
spool off
quit
EOF
};
check &> /dev/null
errors=`grep ERROR /tmp/tablespace.log | wc -l`
if [ "$errors" -gt 0 ]; then
    echo "" > /tmp/tablespace.log
fi

chown oracle: get_tablespace_usage.sh
chmod 755 get_tablespace_usage.sh

可以用Oracle用戶手動執(zhí)行一下腳本，查看/tmp/tablespace.log中有沒有內容輸入，必須要Oracle用戶。

(2)定義表空間自動發(fā)現(xiàn)的腳本

/etc/zabbix/scripts/discovery_tablespace.sh

#!/bin/bash
# zabbix auto discovery oracle tablespace
tablespaces=(`cat /tmp/tablespace.log | awk '{print $1}' | grep -v "^$"`)
length=${#tablespaces[@]}
printf "{\n"
printf '\t'"\"data\":["
for ((i=0;i<$length;i++))
do
    printf "\n\t\t{"
    printf "\"{#TABLESPACE_NAME}\":\"${tablespaces[$i]}\"}"
    if [ $i -lt $[$length-1] ];then
        printf ","
    fi
done
    printf "\n\t]\n"
printf "}\n"

chmod 755 /etc/zabbix/scripts/discovery_tablespace.sh

(3)定義表空間監(jiān)控項腳本

/etc/zabbix/scripts/tablespace_check.sh

#!/bin/bash
# oracle tablespace check
TABLESPACE_NAME=$1
grep "\b$TABLESPACE_NAME\b" /tmp/tablespace.log | awk '{print $2}'

chmod 755 /etc/zabbix/scripts/tablespace_check.sh

2、將腳本a放入crontab里面

su - oracle
crontab -e
	*/5 * * * * /home/oracle/get_tablespace_usage.sh

計劃任務里的環(huán)境變量會與外面的不一致，導致腳本沒有執(zhí)行，因為/tmp/tablespace.log的修改時間沒有變化。

注意：?。?！需要在腳本中添加兩行或者在腳本中添加ORACLE_HOME的完整路徑，例如第三行。

一般路徑及文件是/home/oracle/.bash_profile

source的路徑需要看Oracle的環(huán)境變量寫在哪個文件里

另一臺服務器我也是這樣寫的腳本，把Oracle的環(huán)境變量和環(huán)境變量文件都寫上了，但是計劃任務執(zhí)行了，/tmp/tablespace.log文件的修改時間還是沒有變化。

把第一個框框里的兩句話刪除就好了?。。?，可能是自己寫的Oracle路徑，和環(huán)境變量中的有沖突

修改時間已是最新時間（計劃任務執(zhí)行的時間）

3、編輯agent參數(shù)

vim /etc/zabbix/zabbix_agent.d/oracle.conf
# tablespace usage
UserParameter=discovery.tablespace,/etc/zabbix/scripts/discovery_tablespace.sh
UserParameter=tablespace.check.[*],/etc/zabbix/scripts/tablespace_check.sh $1

我用這個方法zabbix報錯找不到監(jiān)控項

我只能用笨的方法，把表空間一個一個從/tmp/tablespace.log中取出來。