nucleusv June 16, 2014 at 11:11

Monitoring Oracle Database through ODBC in Zabbix

The article will consider the possibility of monitoring the database using the built-in ODBC support in Zabbix, using auto-detection of objects.

First, let's look at the database monitoring methods available for Zabbix that were used before ODBC support.
Since the article is about monitoring Oracle, we will look in this context.

1. Using the zabora script

In principle, the script is good for everyone, but the main thing that didn’t suit me: the script is on each machine with a database, and when adding a request I had to go to this machine and edit the config.

It supports query parameters, that is, you can transfer the parameter to the key and based on it make a query to the database.
That is, the same query can be used to collect metrics of different objects.

2. Orabbix or DBforBIX

Also a good product, it is a java daemon, creates several connections and supports the automatic addition of new queries to the config without rebooting. It works like a Zabbix trapper, that is, it sends data to a Zabbix server with a certain frequency.

Disadvantages:
- it does not support parameters, that is, a separate request is created for each metric.
Imagine you have 10 tablespace and you need to remove from each 4 parameters - you get 40 queries in a file. The request interval receiving metrics is also set in the config, which is not very convenient.

After trying all these solutions, I decided to use ODBC support in Zabbix, and here's why:

query to the database is the standard Zabbix key, it follows that we configure such parameters as the polling frequency in the interface itself
editing requests in the Zabbix interface
allows you to use macros
the most important thing is to automate the process of adding new objects for monitoring

First, I will describe what is on the farm:

1. 6 Oracle databases - 1 database - 1 server + 1 backup server for the database total: 12 servers are obtained.
2. Servers for each database are combined into a cluster - a total of 6 clusters
3. Zabbix agent for AIX is installed
on each server 4. On each server according to the zabora script

Zabbix monitoring configuration:

Zabbix server on CentOS 6.5 + TokuDB - 20,000 elements - 380 nps (new values per second)
Especially for database monitoring, Zabbix Proxy was raised, since the requests can be executed for a long time, I would not want to suspend the data collection processes of the main Zabbix because of them - also CentOS 6.5 + TokuDB

In this article I will not touch on the configuration of TokuDB, as I plan another article why we switched from InnoDB to TokuDB, and what it gave us.

Install Oracle Instant Client

First you need to install Oracle Instant Client on a machine with Zabbix Proxy:

We use Oracle 11g, so download the RMP packages of the corresponding version from the Oracle website .
We need:

oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm - main libraries
oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm - drivers for java, are not needed for our task, but come in handy on the farm :)
oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm - SQLplus client
oracle-instantclient11.2-odbc-11.2.0.4.0-1.x86_64.rpm - library for working through ODBC
can be even before the heap: oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm :)

in the folder into which we downloaded all these files we do:

# rpm -i oracle-*.rpm

Configuring SQLplus to access an Oracle database.

In order for the client to work, it is necessary to set the necessary variables in the environment parameters; first, set them in your profile by writing to the file $HOME/.bash_profile:

ORACLE_HOME=/usr/lib/oracle/11.2/client64
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib64:$LD_LIBRARY_PATH
TNS_ADMIN=$ORACLE_HOME/network/admin>
PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin
export ORACLE_HOME
export LD_LIBRARY_PATH
export TNS_ADMIN
export PATH

We log in and see if our variables are in

# env

Let's pay attention to the variable. TNS_ADMIN=$ORACLE_HOME/network/admin
This path needs to be created, there we put the tnsnames.ora file which is used by the client libraries to connect to the database.

Let's create a connection to the database with the name TESTDB for example.

#cat $ORACLE_HOME/network/admin/tnsnames.ora

TESTDB =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oratestdb)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = testdb)
      )
)

You must substitute the desired values in HOST and SERVICE_NAME.
HOST - you can register an IP address or DNS name (just check that it resolves to IP)

Check the client settings, only create an account zabbix in Oracle first.

# sqlplus zabbix/zabbix@TESTDB

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 24 10:47:09 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

We see that the client issued an invitation, then the connection was successful, and to be sure, we will make a simple request:

SQL> select banner from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
SQL>

ODBC configuration.

The official Zabbix Server and Zabbix Proxy packages in KentoOS are compiled with unixODBC support, so after installing them you must have the unixODBC package installed, check:

# yum info *ODBC

The output should contain the following packages: unixODBCand oracle-instantclient11.2-odbc.

Edit files:

# cat /etc/odbcinst.ini

[OracleDriver]
Description=Oracle ODBC driver for Oracle 11g
Driver=/usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1

And immediately we do this check:

# ldd /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1

ldd: warning: you do not have execution permission for `/usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1`
      linux-vdso.so.1 =>  (0x00007fff1a58f000)
      libdl.so.2 => /lib64/libdl.so.2 (0x00007f89d6d4d000)
      libm.so.6 => /lib64/libm.so.6 (0x00007f89d6ac8000)
      libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f89d68ab000)
      libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f89d6692000)
      libclntsh.so.11.1 => /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 (0x00007f89d3d22000)
      libodbcinst.so.1 => /lib64/libodbcinst.so.1 (0x00007f89d3b11000)
      libc.so.6 => /lib64/libc.so.6 (0x00007f89d377d000)
      /lib64/ld-linux-x86-64.so.2 (0x00007f89d711c000)
      libnnz11.so => /usr/lib/oracle/11.2/client64/lib/libnnz11.so (0x00007f89d33af000)
      libaio.so.1 => /lib64/libaio.so.1 (0x00007f89d31ae000)
      libltdl.so.7 => /usr/lib64/libltdl.so.7 (0x00007f89d2fa5000)

With a high probability, you will receive libodbcinst.so.1 => not found, so you need to make a sim link:

# ls -lah /lib64 | grep odbc

lrwxrwxrwx.  1 root root    31 May 18 00:45 libodbcinst.so.1 -> /usr/lib64/libodbcinst.so.2.0.0 
lrwxrwxrwx.  1 root root    16 May 20 11:41 libodbcinst.so.2 -> libodbcinst.so.1

Next, edit the file:

# cat /etc/odbc.ini

[ORA_TESTDB]
Driver= OracleDriver
DSN= TESTDB
ServerName= TESTDB
UserID= zabbix
Password= zabbix

After that, we should be able to connect to the Oracle database through the ODBC client (always use the -v option if there is a connection error, it will tell you in detail what the problem is):

# isql -v ORA_TESTDB

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  
|                                       |
+---------------------------------------+
SQL>

Also, to clear our conscience that everything works for us, we make a request:

SQL> select banner from v$version where rownum=1;
+---------------------------------------------------------------------------------+
| BANNER                                                                          |
+---------------------------------------------------------------------------------+
| Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production    |
+---------------------------------------------------------------------------------+
SQLRowCount returns -1
1 rows fetched
SQL>

Congratulations, you have configured ODBC.

Now we need to ensure that Zabbix Proxy can also make requests through ODBC.

For this, it is necessary that the variables indicated above are available in the environment of the zabbix_proxy process, for this we add to the file:

# cat /etc/init.d/functions

# Set up a default search path.
PATH="/sbin:/usr/sbin:/bin:/usr/bin"
ORACLE_HOME=/usr/lib/oracle/11.2/client64
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib64:$LD_LIBRARY_PATH
TNS_ADMIN=$ORACLE_HOME/network/admin
PATH = $ PATH: $ ORACLE_HOME / bin: $ HOME / bin
export ORACLE_HOME
export LD_LIBRARY_PATH
export TNS_ADMIN
export PATH

After that, be sure to restart zabbix_proxy:

# service zabbix-proxy restart

Now we’ll go straight to setting up autodiscover rules in Zabbix terminology - this is a low level discovery rule.

What is LLD?
In principle, this is any element in Zabbix that can return data in JSON format.
So the built-in database monitoring in Zabbix always returns only 1 column and 1 row. For some reason, the Zabbix team has not yet written an LLD generator for the database.

Who needs this feature, please vote .

We have to write a script that will give us a list of objects in JSON format.

The template and script can be taken on GitHub.

The script is written in php, so please fans of bash to look away :)
I won’t comment on the script itself, I think everything is clear from the code, I’ll just say that it needs to be put in the folder specified in the zabbix_proxy.conf (or zabbix_server.conf) config:

 ExternalScripts=/usr/lib/zabbix/externalscripts

Oracle.odbc.discovery script

#!/usr/bin/php
array());
          while(odbc_fetch_row($result)){
               $tablespaces['data'][]=array('{#TBSNAME}'=>odbc_result($result,1));
          }
          echo json_encode($tablespaces);
          break;
      case "jobs":
          $result=odbc_exec($connected_dsn,"SELECT job_name, owner FROM dba_scheduler_jobs WHERE state != 'DISABLED';");
          $jobs = array("data"=>array());
          while(odbc_fetch_row($result)){
               $jobs['data'][]=array(
                                        '{#JOBNAME}'=>odbc_result($result,1),
                                        '{#JOBOWNER}'=>odbc_result($result,2));
          }
          echo json_encode($jobs);
          break;
  }
exit();
?>

Two parameters are passed to the script:
1. DSN - which you indicated in the /etc/odbc.ini file in square brackets, in the case of an example it is ORA_TESTDB
2. type of objects whose list needs to be returned: tablespaces or jobs

In case of jobs, the script will return the same and {#JOBOWNER}, that is, the owner of the job.

Add execution rights to the script and try to run it:, the
# /usr/lib/zabbix/externalscripts/oracle.odbc.discovery ORA_TESTDB tablespacesscript will return something like this array:

{
    "data": [
        {
            "{#TBSNAME}": "SYSTEM"
        },
        {
            "{#TBSNAME}": "SYSAUX"
        },
        {
            "{#TBSNAME}": "UNDOTBS1"
        },
        {
            "{#TBSNAME}": "TEMP"
        },
        {
            "{#TBSNAME}": "USERS"
        }
    ]
}

# /usr/lib/zabbix/externalscripts/oracle.odbc.discovery ORA_TESTDB jobs

{
    "data": [
        {
            "{#JOBNAME}": "PURGE_LOG",
            "{#JOBOWNER}": "SYS"
        },
        {
            "{#JOBNAME}": "ORA$AUTOTASK_CLEAN",
            "{#JOBOWNER}": "SYS"
        },
        {
            "{#JOBNAME}": "DRA_REEVALUATE_OPEN_FAILURES",
            "{#JOBOWNER}": "SYS"
        },
        {
            "{#JOBNAME}": "BSLN_MAINTAIN_STATS_JOB",
            "{#JOBOWNER}": "SYS"
        },
        {
            "{#JOBNAME}": "RSE$CLEAN_RECOVERABLE_SCRIPT",
            "{#JOBOWNER}": "SYS"
        },
        {
            "{#JOBNAME}": "SM$CLEAN_AUTO_SPLIT_MERGE",
            "{#JOBOWNER}": "SYS"
        },
        {
            "{#JOBNAME}": "RLM$EVTCLEANUP",
            "{#JOBOWNER}": "EXFSYS"
        },
        {
            "{#JOBNAME}": "RLM$SCHDNEGACTION",
            "{#JOBOWNER}": "EXFSYS"
        }
    ]
}

Finally, let's move on to adding Oracle database monitoring in Zabbix.

To begin with, a few words about the template:
1. common Oracle monitoring keys are taken from the zabora script
2. for the template to work, you need to add the macros of the host itself, add 3 user macros:
- {$ DSN1} - DSN which is written in the square brackets of the / etc / file odbc.ini (in the example ORA_TESTDB)
- {$ ORA_USER} - the user with the rights to connect to the Oracle database
- {$ ORA_PASSWORD} - password for connecting to the Oracle database

So the list of LLD rules:

For the rules to work for the first time, set them yourself rules interval, suppose 300 seconds, and after 5 minutes you have in the data elements dol new wives to be created.

Consider the rules themselves and start with Tablespaces.

In principle, everything is clear here, I want to pay attention to the "Filter" field.
Using this field, you can filter the list that returns the element itself; in our example, we do not need to add system tablespaces to the monitoring. Filtering is based on the regexp rule. As you can see in the image, the {#TBSNAME} field must comply with the regexp Oracle System Excluded Tablespaces rule. The general regexp rules are described in Администрирование > Общие > Регулярные выражения and are called in the filter through the @ symbol.

Everything is the same for jobs:

Go to the data

prototypes The prototype itself

The picture speaks for itself, but I want to draw on one nuance, namely on the "Unit of measurement" field, by default Zabbix uses a 10-decimal system of calculation, which is to be expected, therefore all the prefixes Kilo, Mega, Giga, etc. . this is a division by 1000, which from the point of view of calculating the amount of data is not entirely correct, therefore, in order to get adequate values in the "Recent Data" tab, Zabbix uses "special" units of measurement: B and Bps - byte and byte per second ( more ).
But there is a funny moment (bug), in the latest data the prefixes K (ilo), M (ega), G (iga) are translated into K, M, G, but the unit itself is not, therefore, in the case of gigabytes, Vas will have GB.

Prototype triggers for tablespaces:

The ranges are as follows:

when the size is less than 3TB, percentage limit
3TB to 10TB in gigabytes
from 10TB in gigabytes

Pay attention that the values in the conditions are used in bytes, and also pay attention to the order and values used.
At first glance, the condition “Maximum size> 0” may seem redundant.

But this was done in order to get a more informative letter for DBA.
In the actions you indicate:

1. {ITEM.NAME1} ({HOSTNAME1}:{TRIGGER.KEY1}): {ITEM.VALUE1}
2. {ITEM.NAME2} ({HOSTNAME1}:{TRIGGER.KEY2}): {ITEM.VALUE2}
3. {ITEM.NAME3} ({HOSTNAME1}:{TRIGGER.KEY3}): {ITEM.VALUE3}

In action, we cannot get the values of the key that was created automatically, not that we cannot get it, we just don’t know its name, for this we need to extract the name of the tablespace from the key, but there are no such Zabbix functions.

With these action settings, you will receive something like this:

1. Текущий размер tablespace BG_Z_LOB_TBS (ODB.odbc.select[tbs_size_BG_Z_LOB_TBS,ORA_ODB]): 2 GB 
2. Осталось свободного места в процентах в tablespace BG_Z_LOB_TBS (ODB.odbc.select[tbs_used_percent_BG_Z_LOB_TBS,ORA_ODB]): 99 %
3. Максимально возможный размер tablespace BG_Z_LOB_TBS (ODB.odbc.select[tbs_maxsize_BG_Z_LOB_TBS,ORA_ODB]): 32 GB

Data prototypes for jobs:

Trigger prototypes for jobs:

Triggers fire if:

job'a run time more than 720 minutes
if the job is completed with status not equal to "SUCCEEDED"

Enjoy your discoveries in the Oracle database :)

Tags:

Monitoring Oracle Database through ODBC in Zabbix

Install Oracle Instant Client

Configuring SQLplus to access an Oracle database.

ODBC configuration.

Also popular now: