[ZBX-11356] Zabbix don't connect Oracle Database with ODBC Created: 2016 Oct 13 Updated: 2017 Jun 02 Resolved: 2016 Nov 04 |
|
| Status: | Closed |
| Project: | ZABBIX BUGS AND ISSUES |
| Component/s: | Frontend (F), Server (S) |
| Affects Version/s: | 3.0.4, 3.0.5, 3.2.1 |
| Fix Version/s: | None |
| Type: | Incident report | Priority: | Critical |
| Reporter: | Leandro Moreira | Assignee: | Unassigned |
| Resolution: | Won't fix | Votes: | 0 |
| Labels: | database, odbc, oracle, zabbix_server | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
I I'm using debian 8.3, the zabbix-sever and all its components are in version 3.0.5, the zabbix this broken into 2 environment the database (postgresql version 9.4) with two quad-core processors and 48GB ram memory, and the zabbix-server and frontend on a server with two six-core processors and 48GB of memory. |
||
| Description |
|
My zabbix-server of this production in 3.0.4 have multiple monitors on it among them the company's ERP which perform various consulda in oracle database, I have installed on the server Oracle client 11.2.0.4, everything worked perfect until I had to make a restart the server (systemctl restart zabbix-server), after it was unable to monitor more the oracle databases by ODBC, the log returns the following message: 1462: 20,161,013: 110126,222 item "MOREIRA: db.odbc.select [database, {} $ DSN]" Became not supported: Can not connect to ODBC DSN: [SQL_ERROR]: [01000] [0] [[unixODBC] [Driver Manager ] Can not open lib '/usr/lib/oracle/11.2/client64/lib/libsqora.so': file not found] |
But in linux via isql command connect the oracle normalemente, created in the directory /etc/profile.d a oracleenv.sh file where seto for all the server users the variables of Oracle environment. root@debian:~# isql -v moreira +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select * from dual; +------+ | DUMMY| +------+ | X | +------+ SQLRowCount returns -1 1 rows fetched SQL> # VARIABLE ORACLE export ORACLE_HOME = / usr / lib / oracle / 11.2 / client64 export LD_LIBRARY_PATH = $ LD_LIBRARY_PATH: $ ORACLE_HOME / lib export TNS_ADMIN = / etc / oracle When I go on zabbix user the Oracle environment variables are set: root @ debian: ~ # su - zabbix In the directory, logging in with HOME = / root @ debian: ~ # vi / etc / passwd root @ debian: ~ # su - zabbix In the directory, logging in with HOME = / zabbix @ debian: / $ env XDG_SESSION_ID = 2 SHELL = / bin / bash TERM = xterm USER = zabbix LD_LIBRARY_PATH = / usr / lib / oracle / 11.2 / client64 / lib TNS_ADMIN = / etc / oracle MAIL = / var / mail / zabbix PATH = / usr / local / bin: / usr / bin: / bin: / usr / local / games / usr / games PWD = / LANG = en_US.UTF-8 SHLVL = 1 HOME = / LANGUAGE = en_US: en LOGNAME = zabbix ORACLE_HOME = / usr / lib / oracle / 11.2 / client64 _ = / Usr / bin / env zabbix @ debian: / $ ls -l /usr/lib/oracle/11.2/client64/lib/libsqora.so lrwxrwxrwx 1 root root 50 Oct 13 00:19 /usr/lib/oracle/11.2/client64/lib/libsqora.so -> /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1 Can help me, all the tests that did not resolve the problems, installed to a new environment that the 3.0.5 version and the problem still occurs. I thank all the attention. |
| Comments |
| Comment by Glebs Ivanovskis (Inactive) [ 2016 Oct 13 ] |
|
Are you sure that /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1 isn't missing? |
| Comment by Leandro Moreira [ 2016 Oct 13 ] |
|
I checked and this correrto in place: root @ debian: ~ # ls -l /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1 rw-r - r-- 1 root root 1003582 Aug 24 2013 /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1 root @ debian: ~ # ls -l /usr/lib/oracle/11.2/client64/lib/libsqora.so lrwxrwxrwx 1 root root 50 Oct 13 00:19 /usr/lib/oracle/11.2/client64/lib/libsqora.so -> /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1 |
| Comment by Leandro Moreira [ 2016 Oct 13 ] |
|
Under the configuration of odbcinst.ini, odbc.ini and tnsnames.ora root @ debian: ~ # cat /etc/odbcinst.ini
[oracle]
Description=ODBC for Oracle
Driver=/usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
FileUsage=1
Driver Logging=7
root @ debian: ~ # cat /etc/odbc.ini
[Moreira]
Driver = oracle
DSN = moreira
ServerName = moreira
Userid = zabbix
Password = monitors
root @ debian: ~ # cat /etc/oracle/tnsnames.ora
MOREIRA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.84) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = moreira)
)
)
|
| Comment by Leandro Moreira [ 2016 Oct 13 ] |
|
Access the database by Oracle Linux shell normally root@debian:~# !isql isql -v moreira +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> SELECT count (*),trunc(first_time) FROM v$loghist a where trunc( first_time)=trunc(sysdate) and THREAD# =1 group by trunc(first_time); +-----------------------------------------+--------------------+ | COUNT(*) | TRUNC(FIRST_TIME) | +-----------------------------------------+--------------------+ +-----------------------------------------+--------------------+ SQLRowCount returns -1 SQL> select * from dual; +------+ | DUMMY| +------+ | X | +------+ SQLRowCount returns -1 1 rows fetched |
| Comment by richlv [ 2016 Oct 13 ] |
|
you might want to use one of the available channels at http://zabbix.org/wiki/Getting_help for any further support requests |
| Comment by Glebs Ivanovskis (Inactive) [ 2016 Oct 13 ] |
|
Just guessing, what happens if you try to run isql as zabbix user? |
| Comment by Leandro Moreira [ 2016 Oct 13 ] |
|
With zabbix user can use the isql query and run the Oracle database normally root@debian:~# su - zabbix No directory, logging in with HOME=/ zabbix@debian:/$ isql -v moreira +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select * from dual; +------+ | DUMMY| +------+ | X | +------+ SQLRowCount returns -1 1 rows fetched SQL> |
| Comment by Leandro Moreira [ 2016 Oct 14 ] |
|
I changed the Debug Level for 5 to better detail what happens when the zabbix run the query in oracle. 2490:20161013:235232.820 In substitute_key_macros() data:'db.odbc.select[database,{$DSN}]'
2490:20161013:235232.820 In substitute_simple_macros() data:'{$DSN}'
2490:20161013:235232.820 In DCget_user_macro() macro:'{$DSN}'
2490:20161013:235232.820 End of DCget_user_macro()
2490:20161013:235232.820 End substitute_simple_macros() data:'moreira'
2490:20161013:235232.820 End of substitute_key_macros():SUCCEED data:'db.odbc.select[database,moreira]'
2490:20161013:235232.820 In substitute_simple_macros() data:'select * from dual;'
2490:20161013:235232.820 In substitute_simple_macros() data:'{$ORA_USER}'
2490:20161013:235232.820 In DCget_user_macro() macro:'{$ORA_USER}'
2490:20161013:235232.820 End of DCget_user_macro()
2490:20161013:235232.820 End substitute_simple_macros() data:'zabbix'
2490:20161013:235232.820 In substitute_simple_macros() data:'{$ORA_PASS}'
2490:20161013:235232.820 In DCget_user_macro() macro:'{$ORA_PASS}'
2490:20161013:235232.820 End of DCget_user_macro()
2490:20161013:235232.820 End substitute_simple_macros() data:'monitora'
2490:20161013:235232.820 In get_value() key:'db.odbc.select[database,{$DSN}]'
2490:20161013:235232.820 In get_value_db() key_orig:'db.odbc.select[database,{$DSN}]'
2490:20161013:235232.820 In db_odbc_select() query:'select * from dual;'
2490:20161013:235232.820 In odbc_DBconnect() db_dsn:'moreira' user:'zabbix'
2490:20161013:235232.821 odbc_Diag(): rc_msg:'SQL_ERROR' rec_nr:1 sql_state:'01000' native_err_code:0 err_msg:'[unixODBC][Driver Manager]Can't open lib '/usr/lib/oracle/11.2/client64/lib/libsqora.so' : file not found'
2490:20161013:235232.821 End of odbc_DBconnect():FAIL
2490:20161013:235232.821 End of db_odbc_select():NOTSUPPORTED
2490:20161013:235232.822 End of get_value_db():NOTSUPPORTED
2490:20161013:235232.822 Item [MOREIRA:db.odbc.select[database,{$DSN}]] 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' : file not found]|
|
| Comment by Anton Alekseev [ 2016 Oct 14 ] |
|
lmoreira, I'm pretty sure your zabbix_server doesn't know where to look for Oracle libs, especially libclntsh.so For example, add the following lines to /etc/sysconfig/zabbix-server and restart zabbix_server:
LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/usr/lib/oracle/11.2/client64/lib"
export LD_LIBRARY_PATH
|
| Comment by Leandro Moreira [ 2016 Oct 15 ] |
|
I modified the variable but the result was the same, he did not econontra the library path, which can not understand and that until about four weeks everything worked. root @ debian: ~ # cat /etc/init.d/zabbix-server #! / Bin / sh ### BEGIN INIT INFO # Provides: zabbix-server # Required-Start: $ remote_fs $ network # Required-Stop: $ remote_fs # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 # Should-Start: postgresql # Should-Stop: postgresql # Short-Description: Start zabbix-server daemon ### END INIT INFO Seven export ORACLE_HOME = / usr / lib / oracle / 11.2 / client64 / lib export LD_LIBRARY_PATH = "$ LD_LIBRARY_PATH: /usr/lib/oracle/11.2/client64/lib" export TNS_ADMIN = / etc / oracle NAME = zabbix_server DAEMON = / usr / sbin / $ NAME DESC = "Zabbix server" test -x $ DAEMON || exit 0 DIR = / var / run / zabbix PID = $ DIR / $ NAME.pid RETRY = 15 if test! -d "$ DIR"; Then mkdir -p "$ DIR" chown -R zabbix: zabbix "$ DIR" fi zabbix log output: 972: 20161015: 131734,637 item "MOREIRA: db.odbc.select [database, {} $ DSN]" Became not supported: Can not connect to ODBC DSN: [SQL_ERROR]: [01000] [0] [[unixODBC] [Driver Manager ] Can not open lib '/usr/lib/oracle/11.2/client64/lib/libsqora.so': file not found] |
|
| Comment by Leandro Moreira [ 2016 Oct 15 ] |
|
In Linux access to database oracle is normal isql -v moreira + --------------------------------------- + | Connected! | | | | sql-statement | | help [tablename] | | quit | | | + --------------------------------------- + SQL> select * from dual; + ------ + | DUMMY | + ------ + | X | + ------ + SQLRowCount returns -1 1 rows fetched SQL> And oracle variable is configured to zabbix user root@debian:~# su - zabbix No directory, logging in with HOME=/ zabbix@debian:/$ env XDG_SESSION_ID=2 SHELL=/bin/bash TERM=xterm USER=zabbix LD_LIBRARY_PATH=:/usr/lib/oracle/11.2/client64/lib TNS_ADMIN=/etc/oracle MAIL=/var/mail/zabbix PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games PWD=/ LANG=en_US.UTF-8 SHLVL=1 HOME=/ LANGUAGE=en_US:en LOGNAME=zabbix ORACLE_HOME=/usr/lib/oracle/11.2/client64/lib _=/usr/bin/env |
| Comment by Aleksandrs Saveljevs [ 2016 Oct 17 ] |
|
Do you have an idea why it tries to load "libsqora.so" instead of "libsqora.so.11.1", which is mentioned in /etc/odbcinst.ini ? Also, why /etc/odbc.ini has a password of "monitors" (note the "s"), but the DebugLevel=5 log has {$ORA_PASS} of "monitora" (note the "a")? |
| Comment by Leandro Moreira [ 2016 Oct 18 ] |
|
The libsqora.so is just a symbolic link to the libsqora.so.11.1 library, just to simplify, but have also tried to libsqora.so.11.1. About 2 months ago everything worked perfectly. Already the password wrong time to copy, I copy without the "a" and the rush to post in the forum I saw that was the missing "a" only typed the wrong letter, the right is the password that is in the log "monitors ". I do not really understand what I'm doing wrong, because everything worked perfectly, since reinstalled a zero environment and can not do funciobar, something that repeated several times successfully just stopped working, excuse me griping. |
| Comment by Aleksandrs Saveljevs [ 2016 Oct 18 ] |
|
Yes, "libsqora.so" is a symbolic link to "libsqora.so.11.1", but if you have "libsqora.so.11.1" in /etc/odbcinst.ini, why does it complain about "libsqora.so"? If it would have "libsqora.so" in /etc/odbcinst.ini, then it might have complained about either "libsqora.so" or "libsqora.so.11.1", depending on whether it prefers to print symbolic links themselves or its targets, but if there is "libsqora.so.11.1", then it should have only complained about that. Anyway, this currently looks like a support request. You might wish to find some more help at https://www.zabbix.org/wiki/Getting_help . |
| Comment by Leandro Moreira [ 2016 Nov 04 ] |
|
Dear good night! ORACLE_HOME = / usr / lib / oracle / 11.2 / client64 LD_LIBRARY_PATH = / usr / lib / oracle / 11.2 / client64 / lib: / usr / lib TNS_ADMIN = / etc / oracle PATH = / usr / local / sbin: / usr / local / bin: / usr / sbin: / usr / bin: / sbin: / bin: /usr/lib/oracle/11.2/client64/lib export ORACLE_HOME export LD_LIBRARY_PATH export TNS_ADMIN export PATH |
| Comment by Leandro Moreira [ 2016 Nov 04 ] |
|
Dear good night! # VARIABLE ENVIRONMENT ORACLE # ------------------------- ORACLE_HOME = / usr / lib / oracle / 11.2 / client64 LD_LIBRARY_PATH = / usr / lib / oracle / 11.2 / client64 / lib: / usr / lib TNS_ADMIN = / etc / oracle PATH = / usr / local / sbin: / usr / local / bin: / usr / sbin: / usr / bin: / sbin: / bin: /usr/lib/oracle/11.2/client64/lib export ORACLE_HOME export LD_LIBRARY_PATH export TNS_ADMIN export PATH |
| Comment by Glebs Ivanovskis (Inactive) [ 2016 Nov 04 ] |
|
Reopening to change resolution to Won't Fix. |
| Comment by ouzhanpei [ 2017 Jun 02 ] |
|
I have same problem.but my os is centos 6.8+zabbix 3.2.5，pls help how to set the oracle env,thank you! |
| Comment by Glebs Ivanovskis (Inactive) [ 2017 Jun 02 ] |
|
Dear ouzhanpei, it's not really a Zabbix question, and it really depends on configuration of both your system and Oracle database you want to monitor. Oracle environment variables documentation and consider ways of getting Zabbix related help. |
| Comment by ouzhanpei [ 2017 Jun 02 ] |
|
Dear Glebs Ivanovskis,but I test by isql is succeed.only on zabbix test is fail. |
| Comment by Glebs Ivanovskis (Inactive) [ 2017 Jun 02 ] |
|
Zabbix may be running as a different user and hence have different environment variables. |
| Comment by ouzhanpei [ 2017 Jun 02 ] |
|
Dear Glebs Ivanovskis, do you know the oracle environment variables in zabbix set which conf file on centos 6.8? |
| Comment by Glebs Ivanovskis (Inactive) [ 2017 Jun 02 ] |
|
Dear ouzhanpei, this sound more like a support request. Please consider available ways of getting help. |