Zabbix data monitoring in Oracle database without unixODBC / AT Consulting blog

The task was set: to implement monitoring of an Oracle database using Zabbix, namely, to monitor the parameters of table spaces on a specific instance. Once the task is set, then we do. As you know, Zabbix provides an opportunity through a predefined data type to query the databases and get the result of the query. The official Zabbix developer site has very good documentation on setting up ODBC monitoring .

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

According to the instructions from the official Zabbix website, the unixODBC package was installed . Since the UnixODBC driver for Oracle is trial, and no budget was allocated for this task, it was decided to look for another way. Having shoveled a bunch of sites with Oracle client installation instructions, the 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 the settings were made and it remains only to test that everything works.

We log in as the user zabbix and execute the isql command according to the instructions.

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

All OK. Trying to get the same result from the Zabbix web. After setting up the “Database Monitoring” data item, we are waiting for the result. But it was not there. The data item has moved to the "Not Supported" status. 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 there is a specified file on the specified path.


The process of finding the cause and trying to correct the error has begun. After several days of searching, having tried a bunch of tips found on the Internet, I came across a ticket in the Zabbix bug tracker. According to the description of the problem - this is just our problem. What to do? The ticket is open, a bug was found in subsequent versions of Zabbix, and therefore, if the cause of our error is this bug, it will not work to configure monitoring. Well, do not put the freshly released version of Zabbix for the sake of solving one problem.

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

Oracle client has been installed in/usr/lib/oracle/11.2/client64 . The first thing to do is 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 in. Be sure to check that all created directories and the file itself have read access rights for the zabbix user.

Next, you need to create a script to connect to the database and execute queries. Below is an example script for performing 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 external test scripts (see the zabbix_server.conf settings line ExternalScripts = / etc / zabbix / externalscripts). Also, the script must be granted read and execute permissions to the zabbix user. The script is ready. Configure the External Check item in the Zabbix web interface as in the screenshot below.


The script created earlier takes parameters:

1. Request file identifier (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 as follows:

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

where "TestSelect" is a string identifier for generating the sql file;
      "Select count (*) from testTable;" - the request itself.
      "Username" and "password" - data connection 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 get a numerical or text value of the selection result.

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 it works as a temporary solution. 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, a separate sql file was created for each. To solve the problem, the script for obtaining data from the database was 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 item, respectively, looks like this:

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

where 'TAB_SPACE1' is the name of the table space.


As can be seen from the screenshot above, the 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 actions of alerts.

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