Monitoring Oracle Database via ODBC in Zabbix
The article will discuss the possibility of monitoring the database using the tools built into Zabbix to support ODBC using c Autodiscover objects.
To begin with, we will look at the database monitoring methods available for Zabbix, which were used before ODBC support.
Since the article is about monitoring Oracle, then we will look at this section.
1. Using the zabora script
In principle, the script is good for everyone, but the main thing is that it did not suit me: the script is on each machine from the database, and when adding a request, you had to go to this machine and edit the config.
')
It supports query parameters, that is, you can pass a parameter to the key and, on the basis of 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
It is also a good product, is a java daemon, creates several connections and supports automatically adding new requests to the config without rebooting. It works like a Zabbix trapper, that is, it sends data to a Zabbix server at regular intervals.
Disadvantages:
- does not support parameters, that is, a separate query is created for each metric.
Imagine you have 10 tablespaces and you need to remove from each 4 parameters - it turns out 40 requests in a file. The request interval receiving metrics is also set in the config, which is not very convenient.
Having tried 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 from this 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 to monitoring
First I will describe what is in the farm:
1. 6 Oracle databases - 1 DB - 1 server + 1 backup server under the database: total: 12 servers are obtained.
2. Servers for each database are combined in a cluster - a total of 6 clusters
3. Zabbix agent for AIX is installed on every server.
4. On each server according to the zabora script
Zabbix monitoring configuration:
- Zabbix server on CentOS 6.5 + TokuDB - 20,000 items - 380 nps (new values ​​per second)
- Especially for monitoring the database, Zabbix Proxy was raised, since requests can be executed long enough, I would not want to suspend the data collection processes of the main Zabbix, also CentOS 6.5 + TokuDB
In this article, I will not touch on the configuration of TokuDB, since I am planning another article on why we switched from InnoDB to TokuDB, and what it gave us.
Installing Oracle Instant Client
First you need to install Oracle Instant Client on the machine with Zabbix Proxy:
We use Oracle 11g, so we download the corresponding version of RMP packages 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, for our task are not needed, but it is useful 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 with ODBC
- You can still heap: oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm :)
in the folder where we downloaded all these files we do:
# rpm -i oracle-*.rpm Configure SQLplus to access Oracle database.
In order for the client to work, it is necessary to set the necessary variables in the environment parameters, to begin with, we set them in our profile by writing to the
$HOME/.bash_profile file: 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 Let us log and see if our variables are in
# env Note the variable
TNS_ADMIN=$ORACLE_HOME/network/adminThis path must be created, there we put the tnsnames.ora file which is used by the client libraries to connect to the database.
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) ) ) It is necessary to substitute the necessary values ​​in HOST and SERVICE_NAME.
HOST - you can register an IP address or DNS name (check only that it resolves to IP)
Check the client configuration, just pre-create a zabbix account in Oracle.
# 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:</code> 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, it means the connection was successful, and just 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 CentoOS are compiled with unixODBC support, so after installing them you should have the unixODBC package installed, check:
# yum info *ODBC The output should contain the following packages:
unixODBC and oracle-instantclient11.2-odbc .Rule 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 we immediately 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) libodbcinst.so.1 => not found likely, you will get 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 Then 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 parameter, if there is a connection error, tell in detail what the problem is):
# isql -v ORA_TESTDB +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | +---------------------------------------+ SQL> Just 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 via ODBC.
For this, it is necessary that the variables mentioned 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</code> PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin</code> export ORACLE_HOME export LD_LIBRARY_PATH export TNS_ADMIN export PATH After this, be sure to restart zabbix_proxy:
# service zabbix-proxy restart Now we will immediately move on to setting autodiscovery 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 will not write an LLD generator for the DB until then.
Who needs this feature, please vote .
We'll have to write a script that will give us a list of objects in JSON format.
Template and script can be taken on GitHub
The script is written in php, so bash lovers, please turn away to the side :)
I will not comment on the script itself, I think everything is clear from the code, I will only say that it should be placed in the folder that is specified in the zabbix_proxy.conf config (or zabbix_server.conf):
ExternalScripts=/usr/lib/zabbix/externalscripts Oracle.odbc.discovery script
#!/usr/bin/php <?php if(!isset($argv[1]) && !isset($argv[2])) exit("ZBX_NOTSUPPORTED"); $connected_dsn = odbc_connect($argv[1],"",""); if(!$connected_dsn) exit('SQL connection erorr | ZBX_NOTSUPPORTED'); switch ($argv[2]) { case "tablespaces": $result=odbc_exec($connected_dsn,"SELECT tablespace_name FROM dba_tablespaces;"); $tablespaces = array("data"=>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 specified in the /etc/odbc.ini file in square brackets, in the case of the example it is ORA_TESTDB
2. type of objects to return the list: tablespaces or jobs
In the case of jobs, the script will also return {#JOBOWNER}, that is, the job owner.
Add permissions to the script and try to run it:
# /usr/lib/zabbix/externalscripts/oracle.odbc.discovery ORA_TESTDB tablespaces , the script will return approximately such an 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, we proceed to the addition of monitoring the Oracle database in Zabbix.
First, a few words about the template:
1. Oracle shared monitoring keys are taken from the zabora script.
2. for the template to work it is necessary in the Macros tab of the host itself, add 3 user macros:
- {$ DSN1} - DSN which is registered in square brackets of the /etc/odbc.ini file (in the example of ORA_TESTDB)
- {$ ORA_USER} - the user with the rights of which will connect to the Oracle database
- {$ ORA_PASSWORD} - password to connect to Oracle database
So the list of LLD rules:
For the rules to work for the first time, set the interval in the rules themselves, suppose 300 seconds, and after 5 minutes new data elements should be created in your data elements.
Consider the rules themselves and start with Tablespaces.
In principle, everything is clear here, I want to draw 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 pictures, the {#TBSNAME} field must comply with the regexp Oracle System Excluded Tablespaces rule. General rules for regexp are described in
> > and are called in the filter using the @ symbol.
Similarly, all for job'ov:
Go to the prototype data
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 the 10th numeral system, which is to be expected, therefore, all prefixes are Kilo, Mega, Giga, etc. . this is a division by 1000, which is not entirely correct from the point of view of calculating the amount of data, so in Zabbix you get “special” units of measurement in the “Latest data” tab: B and Bps are bytes and bytes 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, you will have GB.
Trigger prototypes for tablespaces:
The ranges are as follows:
- with a size less than 3TB limit in percent
- from 3TB to 10TB in gigabytes
- from 10TB in gigabytes
Note that the values ​​in conditions are used in bytes, and also draw on the order and values ​​used.
At first glance, the condition “Maximum size> 0” may seem superfluous.
But this is done in order to come more informative letter for DBA.
In the actions you specify:
1. {ITEM.NAME1} ({HOSTNAME1}:{TRIGGER.KEY1}): {ITEM.VALUE1} 2. {ITEM.NAME2} ({HOSTNAME1}:{TRIGGER.KEY2}): {ITEM.VALUE2} 3. {ITEM.NAME3} ({HOSTNAME1}:{TRIGGER.KEY3}): {ITEM.VALUE3} We can’t get the key values ​​that were created automatically into action, it’s not that we can’t get it, we just don’t know its name, for this you need to extract the name of the tablespace from the key, but there are no such Zabbix functions.
With such 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 Prototypes of data for job:
Prototypes for job triggers:
Triggers work if:
- job'a over 720 minutes
- if job ended with status not equal to "SUCCEEDED"
Enjoy your discoveries in the Oracle database :)
Source: https://habr.com/ru/post/226365/
All Articles