当前位置：首页>数据库>正文

Zabbix的自定义键值和自动发现功能监控Oracle数据库的表空间

数据库2024-10-24 01:24:49

鍓嶉潰浠嬬粛浜嗗埄鐢∣rabbix鐩戞帶浜嗭紝鍙傝€儂abbix閫氳繃Orabbix鐩戞帶oracle鏁版嵁搴擄紝杩欓噷鎴戜滑鍘熷厛鐨勬ā鏉夸腑杩涜浜嗕慨鏀癸紝浣跨敤鑷姩鍙戠幇鍔熻兘瀹炵幇鐩戞帶tablespace鐨勪娇鐢ㄦ儏鍐点€?/p>

1. 鍦ㄨ鐩戞帶鐨勬暟鎹簱鏈哄櫒涓婂垱寤?涓猻hell鑴氭湰

1.1 寰楀埌琛ㄧ┖闂寸殑浣跨敤鎯呭喌

[oracle@oracledb shellScripts]$ ``cat` `/home/oracle/shellScripts/query_tablespace_usage``.sh ``source` `/home/oracle/``.bash_profile``sqlplus -s zabbix``/zabbix` `> ``/tmp/tablespace``.log<<EOF``set` `linesize 140 pagesize 10000``col ``"Status"`  `for` `a10``col ``"Name"`   `for` `a25``col ``"Type"`   `for` `a10``col ``"Extent"`  `for` `a15``col ``"Size (M)"` `for` `a15``col ``"Used (M)"` `for` `a15``col ``"Used %"`  `for` `a20``SELECT d.status ``"Status"``, d.tablespace_name ``"Name"``, d.contents ``"Type"``, d.extent_management ``"Extent"``, ``TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),``'99,999,990'``) ``"Size (M)"``, ``TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)``/1024/1024``,``'999,999,999'``) ``"Used (M)"``, ``TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), ``'990.00'``) ``"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 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"``, ``TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),``'99,999,999'``) ``"Size (M)"``, ``TO_CHAR(NVL(t.bytes,0)``/1024/1024``,``'999,999,999'``) ``"Used (M)"``, ``TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), ``'990.00'``) ``"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

閫氳繃杩欎釜鑴氭湰鎴戜滑鍙互寰楀埌琛ㄧ┖闂寸殑浣跨敤鎯呭喌锛屽苟璁板綍鍦?tmp/tablespace.log涓?/p>

1.2 閫氳繃鑴氭湰鍙栧緱琛ㄧ┖闂寸殑鍚嶅瓧锛屽苟杞崲鎴恓son鏍煎紡鐨勶紙鍥犱负zabbix鐨勮嚜鍔ㄥ彂鐜板姛鑳借幏鍙栫殑鏁版嵁绫诲瀷鏄疛SON鏍煎紡鐨勶級

[oracle@oracledb ~]$ ``cat` `/usr/local/zabbix-3``.6.2``/scripts/oracle_discovery``.sh ``#!/bin/bash``TABLESPACE=```cat` `/tmp/tablespace``.log |``awk` `'{print}'``|``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` `']}'

鎵ц鑴氭湰鍙互寰楀埌

[oracle@oracledb ~]$ ``/usr/local/zabbix-3``.6.2``/scripts/oracle_discovery``.sh ``{``"data"``:[``{``"{#TABLENAME}"``:``"COSTCTL_TBS_TEMP"``},``{``"{#TABLENAME}"``:``"PDMS_DATA_TEMP"``},``{``"{#TABLENAME}"``:``"UNDOTBS1"``},``     ``.....................``      ``....................``{``"{#TABLENAME}"``:``"TBLSPACE_PAYMENT"``},``{``"{#TABLENAME}"``:``"TBLSPACE_CLAIM"``},``{``"{#TABLENAME}"``:``"TBLSPACE_PAYMENTIDX"``},``{``"{#TABLENAME}"``:``"TEMP"``},``{``"{#TABLENAME}"``:``"TBLSPACE_TEMP02"``},``{``"{#TABLENAME}"``:``"SYSTEM"``}]}

1.3 鍒涘缓鑴氭湰鑾峰彇/tmp/tablespace.log涓殑鏈€鍚?鍒?/p>

[oracle@oracledb ~]$ ``cat` `/usr/local/zabbix-3``.6.2``/scripts/oracle_``oracle_check.sh   oracle_discovery.sh ``[oracle@oracledb ~]$ ``cat` `/usr/local/zabbix-3``.6.2``/scripts/oracle_check``.sh ``#!/bin/bash``EQ_DATA=``""``ZBX_REQ_DATA_TAB=``""``SOURCE_DATA=``/tmp/tablespace``.log``case` ` ``in`` ``maxmb)    ``  ``grep` `-w ``"$ZBX_REQ_DATA_TAB"` `$SOURCE_DATA |``awk` `'{print *1024*1024}'``;;`` ``used)  ``  ``grep` `-w ``"$ZBX_REQ_DATA_TAB"` `$SOURCE_DATA |``awk` `'{print *1024*1024}'``;;`` ``autopercent) ``  ``grep` `-w ``"$ZBX_REQ_DATA_TAB"` `$SOURCE_DATA |``awk` `'{print }'``;;`` ``*) ``  ``echo` `$ERROR_WRONG_PARAM; ``exit` `1;;``esac``exit` `0

娉ㄦ剰缁?usr/local/zabbix-3.6.2/scripts杩欎釜鐩綍鎵ц鏉冮檺

2. zabbixagent閰嶇疆鏂囦欢娣诲姞鑷畾涔夌洃鎺ey

鍦?usr/local/zabbix-3.6.2/etc/zabbix_agentd.conf鏂囦欢鏈熬娣诲姞锛?/p>

UserParameter=ora.tab.discovery,``/usr/local/zabbix-3``.6.2``/scripts/oracle_discovery``.sh``UserParameter=tablespace[*],``/usr/local/zabbix-3``.6.2``/scripts/oracle_check``.sh

3. 灏唓uery_tablespace_usage.sh鏀惧埌绯荤粺鑷姩浠诲姟涓紝姣忛殧涓€鍒嗛挓鎵ц銆?/h4>

*``/1` `* * * * ``/home/oracle/shellScripts/query_tablespace_usage``.sh > ``/home/oracle/shellScripts/oracle_cron``.sh.log 2>&1

4. 閲嶅惎zabbix_agentd,浣块厤缃敓鏁?/h4>

/etc/init``.d``/zabbix_agentd` `restart

鍙互鍦▃abbix鏈嶅姟绔湅鏄惁寰楀埌瀹㈡埛绔紶鏉ョ殑鏁版嵁

[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