Zabbix using Pyora to monitor Oracle databases

Using Orabbix to monitor the Oracle database over time, it was found that monitoring of the Oracel database was possible, but the recent deployment of Orabbix monitored the Oracle database and found that the contents of the database were not always available ... Since Orabbix is based on Java, using JDBC to connect to the Oracle database and get the value to be passed to the Zabbix trap to capture, this process I am not very familiar with ... You can never debug a drawing to ... Finally gave up, looking for Orabbix substitutes. Zabbix on the official website, found that the use of Python implementation of the Pyora this script is also good, the biggest advantage is to be able to customize the relevant parameters you want to monitor, only need to modify the script, add the relevant function inside the line. Pyora is obtained by Python using a component of Python cx_oracle, and the obtained data is passed to Zabbix agent to obtain the relevant monitoring data, which is simpler and easier to expand. Pyora's disadvantage is that it provides less monitoring item, and the template provided is a semi-finished template, you need to design the appropriate template according to your needs, so as to achieve the purpose of monitoring. Overall, Pyora is characterized by simple, open source, easy to expand, but the overall is not very complete.

Well, every blog begins with a routine crap and starts getting to the point.

First, we need to download Pyora, a link to GitHub found on the Zabbix website, linked here Https://github.com/bicofino/Pyora At the end of this article I will also put some of the tools used in this article in the annex for everyone to download.

However Python needs to install cx_oracle This component to connect to the database, install cx_oracle This component is more troublesome, I engaged in almost two hours before the fix, began to use the source code installation, the results are not installed ... have been unable to solve the error ... Then went to find the RPM package to install, Since I have a python version of the Oracle database here is 2.6, so download 2.6 corresponding version, and the latest Oracle supported here is only 11g, and I use 12c here, was also worried for a long time, afraid of unusable, and later confirmed that 11g RPM package is also available in the 12c Oracle The database used above, the link in this http://sourceforge.net/projects/cx-oracle/files/5.1.2/cx_Oracle-5.1.2-11g-py26-1.x86_64.rpm/ Download everyone can download the corresponding RPM package according to their needs.

Also need to download two Python components, one is argparse, there is a setuptools these two packages I have been to the Python website to download, you can also go to the official website to download, of course, I will be in the attachment of these two attachments are currently the latest version. First install the Setuptools, and then install the Argparse, installation method is to use the source code installation, decompression into the directory, using the Python setup.py install can be installed.

Well, the preparatory work is done, the next step is to install cx_oracle, first, the root user under the. BASHRC to add the environment variables of Oracle, otherwise it will not be possible to use the Cx_oracle module, the following is the environment variables added to the user:

tail -6 .bashrc 
# User specific aliases and functions
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/lib:/lib:/usr/lib:$ORACLE_HOME/rdbms/lib
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:$ORACLE_HOME/rdbms/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

After you add the environment variable, you need to make it effective immediately, using the command source. BASHRC to make it effective, note the export path= $PATH: $ORACLE _home/bin: $ORACLE _home/lib:/lib:/usr/lib:$ Oracle_home/rdbms/lib This command, at the beginning I did not add the following LIB environment variables, resulting in the installation of the Cx_oracle component or not import this component, the error message is as follows:

Error while loading shared Libraries:libclntsh.so.11.1:cannot open Shared object file:no such file or directory

Later, the error disappears when the Lib library is added to the environment variable. Next is the installation of cx_oracle, the use of RPM-IVH cx_oracle-5.1.2-11g-py26-1.x86_64.rpm can be installed, installed and use the following operation to see if the normal import, the following is my operation steps, If yours is the same as mine no abnormal output, then there is no problem:

python
Python 2.6.6 (r266:84292, Jan 22 2014, 01:49:05) 
[GCC 4.4.7 20120313 (Red Hat 4.4.7-4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>>

Once installed, you can use the Pyora script to get the value, (PS: Yes, before this, you need to create a corresponding monitoring user in the Oracle database) about adding the appropriate monitoring user rights to the Oracle database is a DBA, and I'm only going to post some reference permission configurations here, Related configurations You can negotiate with your company's DBA to configure:

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;

After configuring the environment, you can use the Pyora script to implement monitoring of Oracle, and then you need to execute the Pyora script to test it.

Include the pyora.py script in the downloaded Pyora package, upload it to the client server, and then use Python pyora.py--username zabbix--password 123456--address 127.0.0.1-- Database xxx show_users to test the ability to get normal data, here are the results I performed:

{"data": [
{"{#DBUSER}": "ANONYMOUS"}, 
{"{#DBUSER}": "APPQOSSYS"}, 
{"{#DBUSER}": "ASYNC"}, 
{"{#DBUSER}": "AUDSYS"}, 
{"{#DBUSER}": "DBSNMP"},
 {"{#DBUSER}": "DIP"}, 
{"{#DBUSER}": "GSMADMIN_INTERNAL"}, 
{"{#DBUSER}": "GSMCATUSER"},
 {"{#DBUSER}": "GSMUSER"}, 
{"{#DBUSER}": "OJVMSYS"}, 
{"{#DBUSER}": "ORACLE_OCM"},
 {"{#DBUSER}": "OUTLN"}, 
{"{#DBUSER}": "SYNC"},
 {"{#DBUSER}": "SYS"},
 {"{#DBUSER}": "SYSBACKUP"}, 
{"{#DBUSER}": "SYSDG"}, 
{"{#DBUSER}": "SYSKM"},
 {"{#DBUSER}": "SYSTEM"},
 {"{#DBUSER}": "WMSYS"}, 
{"{#DBUSER}": "XDB"},
 {"{#DBUSER}": "XS$NULL"}, 
{"{#DBUSER}": "ZABBIX"}]}

Get the data in JSON format, prove that the script is working properly, then we need to create Zabbix users, and then install agnetd (about this Part I do not introduce more, the previous blog wrote a lot. Then modify the Zabbix user's environment variables, edit the Zabbix user's home directory. BASHRC Add the above environment variable and source. BASHRC make the environment variable effective.

Then use the Zabbix user to repeat the above steps to get the data, the test can get the data, it is no problem, in the AGENTD configuration file, write the following configuration to execute this script to obtain the monitoring data:

tail -2 /home/zabbix/zabbix/etc/zabbix_agentd.conf
UserParameter=pyora[*],/usr/bin/python /home/zabbix/zabbix/scripts/pyora.py --username $1 --password $2 --address $3 --database $4 $5 $6 $7 $8

after saving, restart Agentd, and then use the Zabbix_get test on the server side to obtain the data, the test results are as follows:

/opt/software/zabbix/bin/zabbix_get -s xx.xx.xx.xx -k"pyora[zabbix,123456,127.0.0.1,xxx,show_users]"
{"data": [{
"{#DBUSER}": "ANONYMOUS"}, 
{"{#DBUSER}": "APPQOSSYS"}, 
{"{#DBUSER}": "ASYNC"}, 
{"{#DBUSER}": "AUDSYS"}, 
{"{#DBUSER}": "DBSNMP"}, 
{"{#DBUSER}": "DIP"},
 {"{#DBUSER}": "GSMADMIN_INTERNAL"},
 {"{#DBUSER}": "GSMCATUSER"},
 {"{#DBUSER}": "GSMUSER"}, 
{"{#DBUSER}": "OJVMSYS"}, 
{"{#DBUSER}": "ORACLE_OCM"},
 {"{#DBUSER}": "OUTLN"}, 
{"{DBUSER}": "SYNC"},
 {"{#DBUSER}": "SYS"}, 
{"{#DBUSER}": "SYSBACKUP"}, 
{"{#DBUSER}": "SYSDG"}, 
{"{#DBUSER}": "SYSKM"}, 
{"{#DBUSER}": "SYSTEM"}, 
{"{#DBUSER}": "WMSYS"}, 
{"{#DBUSER}": "XDB"}, 
{"{#DBUSER}": "XS$NULL"}, 
{"{#DBUSER}": "ZABBIX"}]}

Prove that there is no problem, then need to add a template on the web side of the Zabbix_server, this template in the download of the Pyora compression package has a semi-finished template, this template is an incomplete semi-finished, The rest of the configuration needs to be done by myself. I'll pack it up later on my side to make a partially modified template.

To add a host and add a template, you need to note that because the template uses macro variables, so we need to add the host to the corresponding macro variables, the following is the macro variable I set:

After setting the macro, wait for a period of time (PS: Normally 5-10 minutes will be able to produce the normal data), the following is the obtained monitoring data:

Well, so far, simple Oracle monitoring is over, and for more monitoring needs, you can make the appropriate modifications to your needs to meet your requirements.

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

Zabbix using Pyora to monitor Oracle databases