Zabbix monitoring Oracle databases with Orabbix and custom scripts

As the company to the Oracle database, need to wake up to monitor this thing, so go to the online Amoy data, found that there is a suite Orabbix monitoring Oracle's effect is good, and then took to test a bit. Orabbix is a suite written by Java that needs to be installed on the Oracle server to run the Java environment. For simplicity, the Java environment can be installed using Yum-y install Java.

After installing the Java environment, you can use the following command to detect if the installation was successful:

[Email protected] ~]# Java-versionjava version "1.7.0_45" OpenJDK Runtime Environment (rhel-2.4.3.3.0.1.el6-x86_64 U45-B15) OpenJDK 64-bit Server VM (build 24.45-b08, Mixed mode)

As shown above, it proves that the Java environment has been installed successfully.

After that, we need to install the Orabbix suite on the Oracle server, can go to the official website to download, currently the latest version is 1.2.3, for: http://www.smartmarmot.com/downloads/orabbix-1.2.3.zip

After downloading it to the/opt directory, next, you need to configure the Orabbix configuration file. First copy the profile template and start configuring it:

cp orabbix/conf/config.props.sample orabbix/conf/config.propsvi !$ #comma  separed list  of Zabbix serversZabbixServerList=ZabbixServer1ZabbixServer1.Address=10.6.0.180                #zabbix服务器的IP地址ZabbixServer1. port= 10051                        #zabbix服务器的监听端口 #pidfileorabbixdaemon.pidfile=./logs/orabbix.pid#frequency  Of item ' S refreshorabbixdaemon.sleep=300#maxthreadnumber should be >= than  the number of your databasesOrabbixDaemon.MaxThreadNumber=100#put here  your databases in a comma separated listdatabaselist=db_qm                               #这个名字可以自行命名, but it needs to be consistent with the name below, and the hostname configured on Zabbixserver is aligned with this #configuration of connection  pool#if not specified Orabbis is going to use default  values  (hardcoded) #Maximum  number of active connection inside  pooldatabaselist.maxactive=10#the maximum number of milliseconds that the  pool will wait# (when there are no available connections)  for a  connection to be returned#before throwing an exception, or <=  0 to wait indefinitely. Databaselist.maxwait=100databaselist.maxidle=1#define here your connection string for &NBSP;EACH&NBSP;DATABASEDB_QM. url=jdbc:oracle:thin:@10.6.0.207:1521:test        # Orabbix is a JDBC-linked Oracle database, ORCL specifies the connection information for the monitored server (for example, a JDBC join, where the server address is 10.6.0.207,oracle port is 1521, test refers to the instance of the database) db_qm. user=zabbix                                          #链接oracle数据库的账号密码, this requires configuring DB_QM in the Oracle database. Password=123456#those values are optionals if not specified orabbix is &NBSP;GOING&NBSP;TO&NBSP;USE&NBSP;THE&NBSP;GENERAL&NBSP;VALUESDB_QM. maxactive=10                                         #删除用不到的多余的配置, just keep this. DB_QM. MAXWAIT=100DB_QM. MAXIDLE=1DB_QM. Querylistfile=./conf/query.props#db2. Url=jdbc:oracle:thin: @server2 .domain.example.com:<listener_port>:D B2

Then create the Zabbix user in the Oracle database:

CREATE USER ZABBIX identified by 123456 DEFAULT tablespace USERS temporary tablespace TEMP;     GRANT CONNECT to ZABBIX;     GRANT RESOURCE to ZABBIX;     ALTER USER ZABBIX DEFAULT ROLE all;     GRANT SELECT any TABLE to ZABBIX;     GRANT CREATE SESSION to ZABBIX;     GRANT SELECT any DICTIONARY to ZABBIX;     GRANT UNLIMITED tablespace to ZABBIX; GRANT SELECT any DICTIONARY to ZABBIX;

If it is Oracle 11g, you need to add the following command

Execdbms_network_acl_admin.create_acl (ACL = ' resolve.xml ', description = ' resolve ACL ', principal = ' ZABBIX ', Is_grant = true, privilege = ' resolve '); exec dbms_network_acl_admin.assign_acl (acl=> ' resolve.xml ', host = > ' * '); commit;

Copy the Orabbix boot file to the ETC directory:

Cp/opt/orabbix/init.d/orabbix/etc/init.d/orabbixchmod 755!$

Start Orabbix

Systemctl start Orabbixchkconfig--add orabbixchkconfig Orabbix on

Start Orabbix, no error, prove the success of the start, if there is an error, the general configuration file problems, carefully check should be no problem.

After starting, you also need to install the Zabbix AGENTD client, as to how to install the AGENTD client in my other blog has introduced, here will not repeat. The portal is here.

After the installation is complete, import the template into Zabbixserver:

Import template, template placed under/opt/orabbix/template

Orabbix_export_full.xml All import (Chart monitor item trigger)

Orabbix_export_graphs.xml Chart

Orabbix_export_items.xml Monitoring Items

Orabbix_export_triggers.xml Trigger

All templates are imported here, and after the template is imported, the Oracle host is added to the zabbixserver as shown in

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/70/E5/wKiom1XAjHPj0GI7AANFnohw7Rw359.jpg "title=" 1.png " alt= "Wkiom1xajhpj0gi7aanfnohw7rw359.jpg"/>

Once configured, it is normal to be able to monitor the Oracle database information.

Here is the effect after the plot:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/70/E4/wKioL1XAl9WwyB-EAAUg8S2lq7M100.jpg "title=" 2.png " alt= "Wkiol1xal9wwyb-eaaug8s2lq7m100.jpg"/>

Ben came here to the end, but the front is not the focus, the focus is on the back colleague needs to monitor the Oracle database tablespace usage, so, in order to achieve this requirement, I have in the original template modified. , using the Autodiscover feature to monitor tablespace usage.

First, you need to create a script to get the data into the Oracle database, and here's the script:

[[Email protected] opt]# cat /home/oracle/oracle_cron.sh #!/bin/bashsource /home /oracle/.bash_profilesqlplus -s zabbix/123456  > /tmp/tablespace.log<<eofset  linesize 140 pagesize 10000col  "Status"    for a10col  "Name"      for a25col  "Type"      for a10col  " Extent "   for a15col " size  (m) " for a20col " used  (m) "  for a20col  "used %"    for a20SELECT 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&NBSP;-&NBSP;NVL (f.bytes, 0),  0)/1024/1024, ' 999,999,999 ')   "used  (M)",  TO_ CHAR (NVL (A.bytes&nbsP;-&NBSP;&NBSP;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_filesgroup 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&NBSP;/&Nbsp;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

The

Executes this script to get a list of tablespace in the Moracle database and output it to/tmp/ Tablespace.log log, this execution script needs to modify the genus Oracle.oracle, and it needs to execute permissions, set every minute in Crontab, and the following is the result of the execution:

[[email protected] opt]# cat /tmp/tablespace.log status   name       TypeExtentSize  (M)      Used  (m)    used %---------- ------------------------- ---------- --------------- --------------- ----- -------------------- --------------------online   undotbs1      undolocal280       12     4.38online    QWERTY     PERMANENTLOCAL 101     10.00online   users     permanentlocal  51     20.00ONLINE   SYSTEM     PERMANENTLOCAL700       377    53.88ONLINE   TEMP       TEMPORARYLOCAL 20       11    55.00ONLINE   SYSAUX      PERMANENTLOCAL550      400    72.786  Rows selected.

The above is taken out of the data, since the data taken out, then the rest is to do is to get the data into the Zabbix.

The following two scripts are used to format and value the extracted data:

[Email protected] opt]# cat/usr/local/zabbix/scripts/oracle_discovery.sh #!/bin/bashtablespace= ' cat/tmp/ Tablespace.log |awk ' {print$2} ' |awk ' Nr>3{print} ' count= ' echo ' $TABLESPACE ' |wc-l ' Index=0echo ' {"Data": [' echo ' $ Tablespace "| while read line; Do echo-n ' {' {#TABLENAME} ': ' $LINE ' "} ' index= ' expr $INDEX + 1 ' If [$INDEX-lt $COUNT]; Then echo ', ' Fidoneecho ']} '

The function of this script is to remove the name column from the Tablespace.log file and perform the JSON formatted output (because the data type obtained by Zabbix's Autodiscover function is in JSON format), the following is the execution effect:

[[email protected] opt]# sh/usr/local/zabbix/scripts/oracle_discovery.sh {"Data": [{"{#TABLENAME}": "UNDOTBS1"},{"{# TABLENAME} ":" QWERTY "},{" {#TABLENAME} ":" USERS "},{" {#TABLENAME} ":" SYSTEM "},{" {#TABLENAME} ":" TEMP "},{" {#TABLENAME } ":" Sysaux "},{" {#TABLENAME} ":" "},{" {#TABLENAME} ":" Rows "}]}[[email protected] opt]#

Another script is to get the last three columns of data in Tablespace.log, with the following script:

[Email protected] opt]# cat/usr/local/zabbix/scripts/oracle_check.sh#!/bin/basheq_data= "$" zbx_req_data_tab= "$" Source_data=/tmp/tablespace.logcase $ in MAXMB) grep-e "$ZBX _req_data_tab" $SOURCE _data |awk ' {print $5*1024*102  4} ';;  Used) grep-e "$ZBX _req_data_tab" $SOURCE _data |awk ' {print $6*1024*1024} ';;  autopercent) grep-e "$ZBX _req_data_tab" $SOURCE _data |awk ' {print $7} ';; *) echo $ERROR _wrong_param; Exit 1;; Esacexit 0

Where MAXMB and used take out the value is M, so here to convert to a byte, convenient Zabbix value, the following is an example of the value taken out:

[Email protected] opt]# sh/usr/local/zabbix/scripts/oracle_check.sh sysaux maxmb576716800[[email protected] opt]#

After the configuration is complete, you need to add the monitoring key to the ZABBIXAGENTD configuration file:

[Email protected] opt]# tail-3/usr/local/zabbix/etc/zabbix_agentd.confuserparameter=ora.tab.discovery,/usr/local /zabbix/scripts/oracle_discovery.shuserparameter=tablespace[*],/usr/local/zabbix/scripts/oracle_check.sh $

The Zabbix Agentd service needs to be restarted after the key is added.

Then we need to add a monitoring template to the Zabbixserver, first creating a discovery rule:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/70/E4/wKioL1XAnCDAq3xWAAJNDgXounY591.jpg "title=" 3.png " alt= "Wkiol1xancdaq3xwaajndgxouny591.jpg"/>

The key value ora.tab.discovery is defined in the AGENTD configuration file, which is obtained by this discovery rule as a JSON-formatted return value. Be aware of the data update interval, which refers to the interval of time that your discovery rule was executed, which I temporarily set to 60s

Then we started to create the prototype of the project, the following is the details of the creation:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/70/E7/wKiom1XAmuihaLLPAAJGOuKStD4770.jpg "title=" 5.png " alt= "Wkiom1xamuihallpaajgoukstd4770.jpg"/>

Create {#TABLENAME}used and {#TABLENAME}autopercent according to the project prototype above, and the corresponding key values are tablespace[{#TABLENAME},used] and tablespace[{# Tablename},autopercent], after creating the discovery rule, you start to create a graphical prototype, the following is the details of the graphical prototype:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/70/E7/wKiom1XAm7WCLfwvAAPBZVfNV_k297.jpg "title=" 6.png " alt= "Wkiom1xam7wclfwvaapbzvfnv_k297.jpg"/>

After the save is complete, the discovery rule is done, then wait about one minute to see the collected data, which is consistent with the 60s data update interval set above. The following is the result of the obtained monitoring tablespace:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/70/E4/wKioL1XAnnKCDzfXAAQt59eAtrk089.jpg "title=" 7.png " alt= "Wkiol1xannkcdzfxaaqt59eatrk089.jpg"/>

Well, the target of monitoring tablespace has been implemented for the time being, and some details have yet to be improved. Attachment will be attached to my template according to the official Website templates modified, well, the end of the hope that the tutorial can help everyone!

This article is from the "Lemon" blog, be sure to keep this source http://xianglinhu.blog.51cto.com/5787032/1681687

Zabbix monitoring Oracle databases with Orabbix and custom scripts