zabbix利用orabbix-1.2.3監(jiān)控oracle數(shù)據(jù)庫-及表空間

1、orabbix-1.2.3監(jiān)控oracle數(shù)據(jù)庫一、服務(wù)器端配置:1.1服務(wù)器版本說明操作系統(tǒng)版本號(hào)內(nèi)核版本Centos7.2.1511forx86643.10.0-327.el7zabbix版本3.2.0orabbix-1.2.3Java版本51.2安裝jdk軟件java-1.8.0-openjdk-5-3.b17.el7.x86_64java-1.8.0-openjdk-headless-5-3.b17.el7.x86_64java-1.8.0-openjdk-devel-5-3.b17.el7.x86_64java-atk-wrapper-0.30.4-5.el7.x86_64javap

2、ackages-tools-3.4.1-11.el7.noarchjavassist-3.16.1-10.el7.noarchpython-javapackages-3.4.1-11.el7.noarchtzdata-java-2016a-1.el7.noarch用centos光盤做成yum源直接安裝會(huì)缺少這個(gè)包需要去網(wǎng)站上下載/linux/rpm2html/search.php?query=/usr/share/javazi-1.8/tzdb.dat如果這個(gè)包不安裝jdbc就不會(huì)從客戶端獲取到數(shù)據(jù)。1.3安裝orabbix軟件下載地址：/product/orabbix/download/這個(gè)

3、下載地址和所有大神寫的文檔都一樣下載下來orabbix-1.2.3.zip這個(gè)包。（orabbix已經(jīng)很久沒更新了，從2013年的網(wǎng)上的文檔到現(xiàn)在用的都是這個(gè)包）rootzabbix-serveropt#mkdir/opt/orabbix上傳orabbixT.2.3.zip至/opt/orabbix目錄下rootzabbix-serverorabbix#unziporabbix-1.2.3.zip#解壓這個(gè)文件賦權(quán)rootzabbix-serveropt#chmoda+x/opt/orabbix-R#這步有很多網(wǎng)上的文檔省略了，但是很關(guān)鍵下面是解壓出來的文件rootiazabbix-serve

4、rorabbix#11total4992drwxr-xr-x2raotroot4696Oct1319:39confdrwxr-x廠2raotroot71Oct252011cdrwxr-xr-x2raotroot20act252911init.d-rwxr-xr-xraotroot809Oct252011install.resolve.xml,description=resolveacl,principal=ZABBIX,is_grant=true,privilege=resolve);execdbms_network_acl_admin.assign_acl(acl=resolve.xml,h

5、ost=*);commit;三、驗(yàn)證數(shù)據(jù)登陸zabbixweb端查看：這里監(jiān)控的內(nèi)容都是orabbix自帶的模板監(jiān)控項(xiàng)想要其他的監(jiān)控內(nèi)容還需要自己添加。數(shù)據(jù)信息正常獲取。四、安裝配置過程中產(chǎn)生的問題安裝完成后啟動(dòng)服務(wù)后正常無報(bào)錯(cuò)，但接收不到任何數(shù)據(jù)。查看日志rootzabbix-server#cat/opt/orabbix/logs/orabbix.log日志內(nèi)信息StartingOrabbixVersion1.2.3Orabbixstartedwithpid:18944PidFile-./logs/orabbix.pid日志信息不完整且沒有成功連接數(shù)據(jù)庫的信息下面為正常日志信息2016-10

6、-1310:04:22,655mainINFOOrabbix-StartingOrabbixVersion1.2.32016-10-1310:04:22,670mainINFOOrabbix-Orabbixstartedwithpid:210372016-10-1310:04:22,671mainINFOOrabbix-PidFile-./logs/orabbix.pid2016-10-1310:04:22,829mainINFOOrabbix-DBPoolcreated:mons.dbcp.datasources.SharedPoolDataSource1963006a2016-10-131

7、0:04:22,829mainINFOOrabbix-URL=jdbc:oracle:thin:88:1521:sbzytst2016-10-1310:04:22,829mainINFOOrabbix-maxPoolSize=102016-10-1310:04:22,829mainINFOOrabbix-maxIdleSize=12016-10-1310:04:22,829mainINFOOrabbix-maxIdleTime=1800000ms2016-10-1310:04:22,829mainINFOOrabbix-poolTimeout=1002016-10-1310:04:22,829

8、mainINFOOrabbix-timeBetweenEvictionRunsMillis=-12016-10-1310:04:22,829mainINFOOrabbix-numTestsPerEvictionRun=32016-10-1310:04:27,328mainINFOOrabbix-ConnectedasZABBIX2016-10-1310:04:27,332mainINFOOrabbix-onDatabase-sbzytst2016-10-1310:04:30,343pool-1-thread-1INFOOrabbix-DonewithdbJobondatabasesbzytst

9、188QueryListelapsedtime2972ms發(fā)現(xiàn)問題：使用rootzabbix-serverorabbix#sh/opt/orabbix/run.sh腳本運(yùn)行扌報(bào)錯(cuò)Causedby:java.lang.Error:java.io.FileNotFoundException:/usr/lib/jvm/java-1.8.0-openjdk-5-3.b17.el7.x86_64/jre/lib/tzdb.dat(Nosuchfileordirectory)去目錄下查看tzdb.dat文件發(fā)現(xiàn)鏈接tzdb.dat-/usr/share/javazi-1.8/tzdb.dat但是/usr/

10、share/javazi-1.8/tzdb.dat這個(gè)文件不存在網(wǎng)上搜索了一下發(fā)現(xiàn)需要安裝tzdata-java-2016a-1.el7.noarch.rpm就是本文開始中提到的。安裝該RPM包重啟orabbix后數(shù)據(jù)正常收取。這個(gè)問題不代表所有安裝過程中遇到的問題，如果在其他環(huán)境中部署遇到問題還是要多百度。五、監(jiān)控?cái)?shù)據(jù)庫表空間首先，需要?jiǎng)?chuàng)建腳本，獲取到oracle數(shù)據(jù)庫中的數(shù)據(jù)，下面是腳本：vi/etc/zabbix/zabbix_agentd.d/oracle/tablespace.shexportPATHexportORACLE_BASE=/oracle/appexportORACLE_

11、HOME=$ORACLE_BASE/product/11.2.0/db1exportLANG=en_USexportORACLE_SID=sbzytstexportNLS_LANG=AMERICAN_AMERICA.UTF8exportNLS_DATE_FORMAT=YYYY-MM-DDHH24:MI:SSexportPATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH/etc/zabbix/tablespace.logsqlplus/assysdbaEOF/etc/zabbix/zabbix_agentd.d/oracle/tablespace.sq

12、lEOFvi/etc/zabbix/zabbix_agentd.d/oracle/tablespace.sqlsetserveroutputonsetheadingoffsetpagesize300-setlinesize200setfeedbackoffcolumntablespace_namefora40columnFREE(G)fora10columnFREE_PCT(%)fora15columnSIZEformat99,999,999,999columnFREEformat99,999,999,999columnUSEDformat99,999,999,999columnFREE_PC

13、Tformat99,999,999,999setechooffspool/etc/zabbix/tablespace.logSELECTTABLESPACE_NAME,TO_CHAR(ROUND(BYTES/1024,2),99990.00)TOTAL(G),TO_CHAR(ROUND(FREE/1024,2),99990.00)FREE(G),TO_CHAR(ROUND(100*FREE/BYTES)/100,99990.00)FREE_PCT(%)FROM(SELECTA.TABLESPACE_NAMETABLESPACE_NAME,FLOOR(A.BYTES/(1024*1024)BYT

14、ES,FLOOR(B.FREE/(1024*1024)FREE,FLOOR(A.BYTES-B.FREE)/(1024*1024)USEDFROM(SELECTTABLESPACE_NAMETABLESPACE_NAME,SUM(BYTES)BYTESFROMDBA_DATA_FILESGROUPBYTABLESPACE_NAME)A,(SELECTTABLESPACE_NAMETABLESPACE_NAME,SUM(BYTES)FREEFROMDBA_FREE_SPACEGROUPBYTABLESPACE_NAME)BWHEREA.TABLESPACE_NAME=B.TABLESPACE_N

15、AME)ORDERBYFLOOR(FREE/BYTES);spooloffquit注意/etc/zabbix/tablespace.log文件的權(quán)限改為oracle。通過執(zhí)行這個(gè)腳本可以獲取到oracle數(shù)據(jù)庫中的tablespace列表，并輸出到/etc/zabbix/tablespace.log日志中去，這個(gè)執(zhí)行腳本需要修改屬主屬組為oracle.oracle,并且需要給于執(zhí)行權(quán)限，在crontab中設(shè)置每一分鐘執(zhí)行一次，下面是執(zhí)行得到的結(jié)果：rootsbzytst188zabbix#cat/etc/zabbix/tablespace.logGRID_APP_DATASYSAUX91.40

16、0.540.38SDH_INDEX390.00160.910.41SDE0.100.060.58UNDOTBS193.9793.691.00SDH_PT1150.0087.760.59USERS0.120.110.90GRID_IND0.100.100.99SYSTEM2.481.270.51SDH_DATA690.00173.170.25GRID_RP_DATA0.100.100.99SDH_PT2180.0097.470.54上面就是取出來的數(shù)據(jù)，既然數(shù)據(jù)取出來了，那么剩下的要做的就是把數(shù)據(jù)弄到zabbix中去咯。下面將使用下面兩個(gè)腳本來對(duì)取出的這些數(shù)據(jù)進(jìn)行格式化轉(zhuǎn)換和取值：rootsbz

17、ytst188/#cat/etc/zabbix/shell/oracle_discovery.sh#!/bin/bashTABLESPACE二cat/etc/zabbix/tablespace.log|awkprint$l|awkNR3printCOUNT=echo$TABLESPACE|wc-lINDEX=0echodata:echo$TABLESPACE|whilereadLINE;doecho-n#TABLENAME:$LINEINDEX=expr$INDEX+1if$INDEX-lt$COUNT;thenecho,fidoneecho這個(gè)腳本的功能是從tablespace.log文件中

18、取出Name那一列，并進(jìn)行JSON格式化輸出（因?yàn)閦abbix的自動(dòng)發(fā)現(xiàn)功能獲取的數(shù)據(jù)類型是JSON格式的），下面是執(zhí)行效果rootsbzytst188/#sh/etc/zabbix/shell/oracle_discovery.shdata:#TABLENAME:UNDOTBS1,#TABLENAME:SDH_INDEX,#TABLENAME:SDE,#TABLENAME:SDH_PT1,#TABLENAME:USERS,#TABLENAME:GRID_IND,#TABLENAME:SYSTEM,#TABLENAME:SDH_DATA,#TABLENAME:GRID_RP_DATA,#TAB

19、LENAME:SDH_PT2還有一個(gè)腳本是獲取tablespace.log中的最后三列數(shù)據(jù)，腳本內(nèi)容如下:rootsbzytst188/#cat/etc/zabbix/shell/oracle_check.sh#!/bin/bashEQ_DATA=$2ZBX_REQ_DATA_TAB=$1SOURCE_DATA=/etc/zabbix/tablespace.logcase$2inmaxgb)grep-E$ZBX_REQ_DATA_TAB$SOURCE_DATA|awkprint$2;used)grep-E$ZBX_REQ_DATA_TAB$SOURCE_DATA|awkprint$3;auto

20、percent)grep-E$ZBX_REQ_DATA_TAB$SOURCE_DATA|awkprint$4;*)echo$ERROR_WRONG_PARAM;exit1;esacexit0其中maxgb和used取出的值是GB,所以這里沒有轉(zhuǎn)換，方便zabbix取值，下面是舉例取出來的值：rootsbzytst188/#sh/etc/zabbix/shell/oracle_check.shGRID_INDused0.10這里配置完成之后就需要在zabbixagentd的配置文件中添加監(jiān)控key了：rootsbzytst188/#vi/etc/zabbix/zabbix_agentd.conf

21、#Option:UserParameterUser-definedparametertomonitor.Therecanbeseveraluser-definedparameters.Format:UserParameter=,Seezabbix_agentddirectoryforexamples.#Mandatory:noDefault:UserParameter=UserParameter=ora.tab.discovery,/etc/zabbix/shell/oracle_discovery.shUserParameter=tablespace*,/etc/zabbix/shell/o

22、racle_check.sh$1$2添加key之后需要重啟zabbixagentd服務(wù)。rootsbzytst188/#servicezabbix-agentrestartShuttingdownZabbixagent:OKStartingZabbixagent:OK六、zabbixserverweb頁面中添加監(jiān)控模板創(chuàng)建發(fā)現(xiàn)規(guī)則：添加名稱tablespacediscovery鍵值：ora.tab.discovery其中的鍵值ora.tab.discovery就是我們?cè)赼gentd的配置文件中定義的，由這個(gè)發(fā)現(xiàn)規(guī)則獲取到的是一個(gè)JSON格式的返回值。要注意其中的數(shù)據(jù)更新間隔，這個(gè)值指的是你的發(fā)現(xiàn)規(guī)則執(zhí)行的時(shí)間間隔，我這里暫時(shí)設(shè)置為60s笆稱Itablespacediscovery創(chuàng)建項(xiàng)目原型按照上面的項(xiàng)目原型依次創(chuàng)建名稱可以自定義但不要用中文#TABLENAMEmaxgb#TABLENAMEused#TABLENAMEautoperc