Zabbix data monitoring in Oracle database without unixODBC

The task was set: to implement monitoring of the Oracle database using Zabbix tools, namely, to monitor the parameters of table spaces on a specific instance. Once the task is set, then we are doing. As is known, Zabbix provides an opportunity through a predefined data type to query database data and get the result of a query. On the official Zabbix developer site there is very good documentation for configuring ODBC monitoring .


We have a Zabbix 3.0.4 server running Centos 7. Previously, ODBC monitoring was not configured, therefore, you need to open the instructions and begin installation and configuration.

According to the instructions from the official Zabbix website, the unixODBC package has been installed. Since the UnixODBC driver for Oracle is a trial one, and the budget was not allocated for this task, it was decided to look for another way. Shooting a bunch of sites with instructions for installing an Oracle client, the following packages were downloaded from oracle.com :

oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm
oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm
oracle-instantclient11.2-devel-11.2.0.3.0-1.x86_64.rpm
oracle-instantclient11.2-odbc-11.2.0.3.0-1.x86_64.rpm

After all these packages have been installed, it remains only to configure the client and unixODBC. As a result, all settings were made and it remains only to test that everything works.
')
We log in as the zabbix user and execute the isql command, according to the instructions.

[user@serverZabbix]$ isql -v CMSAHI username/password 

All OK. We are trying to get the same result from Zabbix web. After setting up the “Monitoring DB” data item, we are waiting for the result. But it was not there. The item has moved to the status "Not supported." We get the error: Cannot connect to ODBC DSN: [SQL_ERROR]: [01000] [0] [[unixODBC] [Driver Manager] Can't open lib '/usr/lib/oracle/11.2/client64/lib/libsqora.so. 11.1 ': file not found] | .

A strange error, especially since the specified file is at the specified path.


Began the process of finding the causes and attempts to correct the error. After several days of searching, having tried a bunch of tips found on the Internet, I picked up a ticket in the Zabbix bug tracker. According to the description of the problem - this is our problem. What to do? The ticket is open, the bug is found in subsequent versions of Zabbix, and therefore, if the cause of our error is this bug, we can’t configure monitoring. Well, do not put the same fresh version of Zabbix for the sake of solving one problem.

The thought came to mind: what if to connect to the database use not unixODBC but SqlPlus, which was installed together with the oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm package. Based on this idea, you need to configure the oracle client to connect.

The Oracle client was installed at /usr/lib/oracle/11.2/client64 . The first thing to do is to create a tnsnames.ora file and fill it with data to connect to the oracle database. To do this, create a folder to store this file:

 sudo mkdir /usr/lib/oracle/11.2/client64/network/admin –p 

In the created directory you need to create a file called tnsnames.ora and fill it. It is imperative to check that all the created directories and the file itself have read permissions for the zabbix user.

Next, you need to create a script to connect to the database and run queries. Below is an example script for executing simple selects:

 #!/bin/sh ##         export ORACLE_HOME=/usr/lib/oracle/11.2/client64 export PATH=$PATH:$ORACLE_HOME/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib64:/usr/lib:$ORACLE_HOME/bin export TNS_ADMIN=$ORACLE_HOME/network/admin ##    sql – .  . ##  ,   zabbix        scriptLocation=/etc/zabbix/SqlScripts ##            ##        -  ## ,      sqlFile=$scriptLocation/sqlScript_"$1".sql ##      echo "$2" > sqlFile; ## ,           . ##    ,  . username="$3" password="$4" tnsname="$5" var=$($ORACLE_HOME/bin/sqlplus -s $username/$password@$tnsname < $sqlFile) ##          . echo $var | cut -f3 -d " " 

The script must be placed in the / etc / zabbix / externalscripts folder - the folder for storing the external verification scripts (see the settings of zabbix_server.conf string ExternalScripts = / etc / zabbix / externalscripts). Also, the script needs to grant permissions to read and execute to the zabbix user. The script is ready. Configuring the item "External check" in the Zabbix web interface as in the screenshot below.


The script created earlier accepts sl. options:

1. Request File Id (String)
2. Simple query (string)
3. login to connect to the database (string)
4. Password to connect to the database (string)
5. TNS database to which we want to connect (string)

In the screenshot above, the “Key” field is filled in as follows:

 getOracleSelect.sh["TestSelect","select count(*) from testTable;","username","password","CMSAHI"] 

where “TestSelect” is a string identifier to form the sql file;
"Select count (*) from testTable;" - the request itself.
"Username" and "password" - connection data to the database
"Tnsname" - the name of the database (see tnsnames.ora)

! IMPORTANT! The query should return the value of only one column of the resulting table if it is expected to receive a numerical or text value of the result of the sample.

After the element is created, if everything is configured correctly, the Zabbix web interface will display the result of the query.

This method, of course, has several disadvantages, but, as a temporary solution, it works. Such a disadvantage, for example, is that it is very inconvenient to enter a large and complex query as a parameter.

To get the parameters of the occupied table space, use the script below:

 SELECT round((totalspace — freespace) * 100 / decode(maxspace, 0, totalspace, maxspace), 2) "%USED" FROM (SELECT tablespace_name, SUM (bytes) / (1024*1024*1024) totalspace, Sum(maxbytes)/ 1024/1024/1024 maxspace          FROM dba_data_files        GROUP BY tablespace_name) a,      (SELECT tablespace_name, SUM (Bytes) / (1024*1024*1024) freespace         FROM dba_free_space        GROUP BY tablespace_name) b WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)  AND A.TABLESPACE_NAME = 'TAB_SPACE1'; 

Since only a few table spaces are required to be monitored, each has its own sql file created. To solve the problem, the script for receiving data from the database has been changed. Below is the script itself:

 #!/bin/sh export ORACLE_HOME=/usr/lib/oracle/11.2/client64 export PATH=$PATH:$ORACLE_HOME/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib64:/usr/lib:$ORACLE_HOME/bin export TNS_ADMIN=$ORACLE_HOME/network/admin scriptLocation=/etc/zabbix/SqlScripts sqlFile=$scriptLocation/getPUsedTableSpace_"$1".sql username="$2" password="$3" tnsname="$4" var=$($ORACLE_HOME/bin/sqlplus -s $username/$password@$tnsname < $sqlFile) echo $var | cut -f3 -d " " 

The key for the data element, respectively, looks like this:

 getOracleSelect.sh["TAB_SPACE1","username","password","CMSAHI"] 

where 'TAB_SPACE1' is the name of the tablespace.


As can be seen from the screenshot above, Zabbix web interface receives the results of the query and displays the percentage of the used table space.

It remains only to configure the triggers and action alerts.

If this method helps someone, I will be glad. If someone has ideas on upgrading scripts and the approach itself, bypassing unixODBC, write.