监控agent自动发现mysql实例_Zabbix的自定义键值和自动发现功能监控Oracle数据库的表空间...

1. 在被监控的数据库机器上创建3个shell脚本

1.1 得到表空间的使用情况

oracle_cron.sh

#!/bin/bash

source /home/oracle/.bash_profile

sqlplus -s "/ as sysdba" > /tmp/tablespace.log<

set linesize 140 pagesize 10000

col "Status" for a10

col "Name" for a25

col "Type" for a10

col "Extent" for a15

SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent",

NVL(a.bytes, 0) "Size (M)",

NVL(a.bytes - NVL(f.bytes, 0), 0) "Used (M)",

round(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),2) "Used %"

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 d.tablespace_name not in ('SYSAUX','SYSTEM') AND NOT

(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')

UNION ALL

SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent",

NVL(a.bytes, 0) "Size (M)",

NVL(t.bytes,0) "Used (M)",

round(NVL(t.bytes / a.bytes * 100, 0),2) "Used %" 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'

ORDER BY 7;

EOF

#sed -i '$d' /tmp/tablespace.log

sed -i '$d' /tmp/tablespace.log

sed -i 's/,//g' /tmp/tablespace.log

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

#!/bin/bash

source/home/oracle/.bash_profile

sqlplus-s"/ as sysdba">/tmp/tablespace.log<

setlinesize140pagesize10000

col"Status"fora10

col"Name"fora25

col"Type"fora10

col"Extent"fora15

SELECTd.status"Status",d.tablespace_name"Name",d.contents"Type",d.extent_management"Extent",

NVL(a.bytes,0)"Size (M)",

NVL(a.bytes-NVL(f.bytes,0),0)"Used (M)",

round(NVL((a.bytes-NVL(f.bytes,0))/a.bytes *100,0),2)"Used %"

FROMsys.dba_tablespacesd,

(selecttablespace_name,sum(bytes)bytesfromdba_data_files

groupbytablespace_name)a,(selecttablespace_name,sum(bytes)bytesfromdba_free_spacegroupbytablespace_name)fWHERE

d.tablespace_name=a.tablespace_name(+)ANDd.tablespace_name=f.tablespace_name(+)ANDd.tablespace_namenotin('SYSAUX','SYSTEM')ANDNOT

(d.extent_managementlike'LOCAL'ANDd.contentslike'TEMPORARY')

UNIONALL

SELECTd.status"Status",d.tablespace_name"Name",d.contents"Type",d.extent_management"Extent",

NVL(a.bytes,0)"Size (M)",

NVL(t.bytes,0)"Used (M)",

round(NVL(t.bytes/a.bytes *100,0),2)"Used %"FROMsys.dba_tablespacesd,

(selecttablespace_name,sum(bytes)bytesfromdba_temp_filesgroupbytablespace_name)a,(select

tablespace_name,sum(bytes_cached)bytesfromv\$temp_extent_poolgroupbytablespace_name)tWHERE

d.tablespace_name=a.tablespace_name(+)ANDd.tablespace_name=t.tablespace_name(+)AND

d.extent_managementlike'LOCAL'ANDd.contentslike'TEMPORARY'

ORDERBY7;

EOF

#sed -i '$d' /tmp/tablespace.log

sed-i'$d'/tmp/tablespace.log

sed-i's/,//g'/tmp/tablespace.log

通过这个脚本我们可以得到表空间的使用情况，并记录在/tmp/tablespace.log中

[oracle@lingshan-oradb01 scripts]$ cat /tmp/tablespace.log

Status Name TypeExtent Size (M) Used (M) Used %

---------- ------------------------- ---------- --------------- ---------- ---------- ----------

ONLINE IDX_BZ_EBIZ PERMANENTLOCAL2147483648 129761280 6.04

ONLINE TEMP TEMPORARYLOCAL8589934592 1368391680 15.93

ONLINE USERS PERMANENTLOCAL 5242880 1376256 26.25

ONLINE UNDOTBS1 UNDOLOCAL8589934592 7702904832 89.67

ONLINE BZ_EBIZ PERMANENTLOCAL3951034368 3753639936 95

1

2

3

4

5

6

7

8

9

[oracle@lingshan-oradb01scripts]$cat/tmp/tablespace.log

StatusNameTypeExtentSize(M)Used(M)Used%

------------------------------------------------------------------------------------------

ONLINEIDX_BZ_EBIZPERMANENTLOCAL21474836481297612806.04

ONLINETEMPTEMPORARYLOCAL8589934592136839168015.93

ONLINEUSERSPERMANENTLOCAL5242880137625626.25

ONLINEUNDOTBS1UNDOLOCAL8589934592770290483289.67

ONLINEBZ_EBIZPERMANENTLOCAL3951034368375363993695

1.2 通过脚本取得表空间的名字，并转换成json格式的(因为zabbix的自动发现功能获取的数据类型是JSON格式的)

oracle_discovery.sh

#!/bin/bash

TABLESPACE=`cat /tmp/tablespace.log |awk '{print$2}'|awk 'NR>3{print}'`

COUNT=`echo "$TABLESPACE" |wc -l`

INDEX=0

echo '{"data":['

echo "$TABLESPACE" | while read LINE; do

echo -n '{"{#TABLENAME}":"'$LINE'"}'

INDEX=`expr $INDEX + 1`

if [ $INDEX -lt $COUNT ]; then

echo ','

fi

done

echo ']}'

1

2

3

4

5

6

7

8

9

10

11

12

13

#!/bin/bash

TABLESPACE=`cat/tmp/tablespace.log|awk'{print$2}'|awk'NR>3{print}'`

COUNT=`echo"$TABLESPACE"|wc-l`

INDEX=0

echo'{"data":['

echo"$TABLESPACE"|whilereadLINE;do

echo-n'{"{#TABLENAME}":"'$LINE'"}'

INDEX=`expr$INDEX+1`

if[$INDEX-lt$COUNT];then

echo','

fi

done

echo']}'

1.3 创建脚本获取/tmp/tablespace.log中的最后3列

oracle_check.sh

#!/bin/bash

EQ_DATA="$2"

ZBX_REQ_DATA_TAB="$1"

SOURCE_DATA=/tmp/tablespace.log

case $2 in

maxmb) grep -Ew "$ZBX_REQ_DATA_TAB" $SOURCE_DATA |awk '{print $5}';;

used) grep -Ew "$ZBX_REQ_DATA_TAB" $SOURCE_DATA |awk '{print $6}';;

autopercent) grep -Ew "$ZBX_REQ_DATA_TAB" $SOURCE_DATA |awk '{print $7}';;

*) echo $ERROR_WRONG_PARAM; exit 1;;

esac

exit 0

1

2

3

4

5

6

7

8

9

10

11

#!/bin/bash

EQ_DATA="$2"

ZBX_REQ_DATA_TAB="$1"

SOURCE_DATA=/tmp/tablespace.log

case$2in

maxmb)grep-Ew"$ZBX_REQ_DATA_TAB"$SOURCE_DATA|awk'{print $5}';;

used)grep-Ew"$ZBX_REQ_DATA_TAB"$SOURCE_DATA|awk'{print $6}';;

autopercent)grep-Ew"$ZBX_REQ_DATA_TAB"$SOURCE_DATA|awk'{print $7}';;

*)echo$ERROR_WRONG_PARAM;exit1;;

esac

exit0

注意给/etc/zabbix/scripts这个目录下的脚本oracle可执行权限

2. zabbixagent配置文件添加自定义监控key

PidFile=/tmp/zabbix_agentd.pid

LogFile=/tmp/zabbix_agentd.log

#LogRemoteCommands=1

#EnableRemoteCommands=1

Server=10.51.37.14

Hostname=10.117.36.219

UnsafeUserParameters=1

UserParameter=ora.tab.discovery,/etc/zabbix/scripts/oracle_discovery.sh

UserParameter=tablespace[*],/etc/zabbix/scripts/oracle_check.sh $1 $2

1

2

3

4

5

6

7

8

9

PidFile=/tmp/zabbix_agentd.pid

LogFile=/tmp/zabbix_agentd.log

#LogRemoteCommands=1

#EnableRemoteCommands=1

Server=10.51.37.14

Hostname=10.117.36.219

UnsafeUserParameters=1

UserParameter=ora.tab.discovery,/etc/zabbix/scripts/oracle_discovery.sh

UserParameter=tablespace[*],/etc/zabbix/scripts/oracle_check.sh$1$2

3.  将oracle_cron.sh放到系统自动任务中，每隔三分钟执行。

*/3 * * * * /etc/zabbix/scripts/oracle_cron.sh >/etc/zabbix/scripts/oracle_cron.log 2>&1

1

*/3****/etc/zabbix/scripts/oracle_cron.sh>/etc/zabbix/scripts/oracle_cron.log2>&1

4. 重启zabbix_agentd,使配置生效

/etc/init.d/zabbix_agentd restart

1

/etc/init.d/zabbix_agentdrestart

5.可以在zabbix服务端看是否得到客户端传来的数据

[root@lanmp bin]# ./zabbix_get -s 192.168.0.92 -k "tablespace[USERS autopercent]"

94.55

[root@lanmp bin]# ./zabbix_get -s 192.168.0.92 -k "tablespace[USERS maxmb]"

70

[root@lanmp bin]# ./zabbix_get -s 192.168.0.92 -k "tablespace[USERS used]"

66

[root@lanmp bin]# pwd

/usr/local/zabbix-3.2.6/bin

1

2

3

4

5

6

7

8

[root@lanmpbin]# ./zabbix_get -s 192.168.0.92 -k "tablespace[USERS autopercent]"

94.55

[root@lanmpbin]# ./zabbix_get -s 192.168.0.92 -k "tablespace[USERS maxmb]"

70

[root@lanmpbin]# ./zabbix_get -s 192.168.0.92 -k "tablespace[USERS used]"

66

[root@lanmpbin]# pwd

/usr/local/zabbix-3.2.6/bin

5.  添加发现规则

之前安装过orabbix,直接在Template_Oracle下添加发现规则

5.1 创建发现规则

模板–>Template_Oracle–>创建自动发现规则

名称：tablespacediscovery

键值：ora.tab.discovery

数据更新间隔：60