Ad Widget

Collapse

ODBC monitoring Oracle DB

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Gabalino
    Senior Member
    • Mar 2013
    • 103
    #1

    ODBC monitoring Oracle DB

    Hello! Zabbix 3.0.1 is set from packets on the server with CentOS 7. I try to set up monitoring of Oracle DB and receive an error:

    Code:
    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]|
    In command line connection to the database is executed successfully

    Code:
    [root@zabbix ~]# isql -v ORA_TELECOM_NEW
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select banner from v$version where rownum=1;
+---------------------------------------------------------------------------------+
| BANNER                                                                          |
+---------------------------------------------------------------------------------+
| Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production    |
+---------------------------------------------------------------------------------+
SQLRowCount returns -1
1 rows fetched
SQL> quit;
    Code:
    [root@zabbix ~]# yum list installed | grep oracle
oracle-instantclient11.2-basic.x86_64   11.2.0.4.0-1       installed
oracle-instantclient11.2-devel.x86_64   11.2.0.4.0-1      installed
oracle-instantclient11.2-jdbc.x86_64    11.2.0.4.0-1       installed
oracle-instantclient11.2-odbc.x86_64    11.2.0.4.0-1       installed
oracle-instantclient11.2-sqlplus.x86_64 11.2.0.4.0-1        installed
    Code:
    [root@zabbix ~]# cat /etc/odbcinst.ini 
[OracleDriver]
Description	= Oracle ODBC driver for Oracle 11g
Driver		= /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
    Code:
    [root@zabbix ~]# cat /etc/odbc.ini 
[ORA_TELECOM_NEW]
Driver= OracleDriver
DSN= TELECOM_NEW
ServerName= TELECOM_NEW
UserID= zabbix
Password= zabbix
    Code:
    cat $ORACLE_HOME/network/admin/tnsnames.ora
TELECOM_NEW = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.1)(PORT = 1521))
    (CONNECT_DATA = 
      (SERVER = DEDICATED)
      (SERVICE_NAME = abc)
    )
)
    Code:
    [root@zabbix ~]# sudo -su zabbix env
HOSTNAME=zabbix.domain.loc
SHELL=/bin/bash
TERM=xterm
HISTSIZE=1000
USER=zabbix
LS_COLORS=rs=0:di=01;34:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:mi=01;05;37;41:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arc=01;31:*.arj=01;31:*.taz=01;31:*.lha=01;31:*.lz4=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.tzo=01;31:*.t7z=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lrz=01;31:*.lz=01;31:*.lzo=01;31:*.xz=01;31:*.bz2=01;31:*.bz=01;31:*.tbz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.war=01;31:*.ear=01;31:*.sar=01;31:*.rar=01;31:*.alz=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.cab=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.webm=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=01;36:*.au=01;36:*.flac=01;36:*.mid=01;36:*.midi=01;36:*.mka=01;36:*.mp3=01;36:*.mpc=01;36:*.ogg=01;36:*.ra=01;36:*.wav=01;36:*.axa=01;36:*.oga=01;36:*.spx=01;36:*.xspf=01;36:
LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib:/usr/lib64:
SUDO_USER=root
SUDO_UID=0
USERNAME=zabbix
PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/lib/oracle/11.2/client64/bin:
MAIL=/var/spool/mail/root
TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin
PWD=/root
LANG=en_US.UTF-8
SHLVL=1
SUDO_COMMAND=/bin/bash -c env
HOME=/var/lib/zabbix
LOGNAME=zabbix
SUDO_GID=0
ORACLE_HOME=/usr/lib/oracle/11.2/client64
_=/bin/env
    Code:
    [root@zabbix ~]# su - zabbix -c "ldd /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1"
	linux-vdso.so.1 =>  (0x00007ffdedb9f000)
	libdl.so.2 => /usr/lib64/libdl.so.2 (0x00007f19e08bd000)
	libm.so.6 => /usr/lib64/libm.so.6 (0x00007f19e05ba000)
	libpthread.so.0 => /usr/lib64/libpthread.so.0 (0x00007f19e039e000)
	libnsl.so.1 => /usr/lib64/libnsl.so.1 (0x00007f19e0185000)
	libclntsh.so.11.1 => /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 (0x00007f19dd815000)
	libodbcinst.so.1 => /usr/lib64/libodbcinst.so.1 (0x00007f19dd603000)
	libc.so.6 => /usr/lib64/libc.so.6 (0x00007f19dd242000)
	/lib64/ld-linux-x86-64.so.2 (0x00007f19e0c84000)
	libnnz11.so => /usr/lib/oracle/11.2/client64/lib/libnnz11.so (0x00007f19dce74000)
	libaio.so.1 => /usr/lib64/libaio.so.1 (0x00007f19dcc72000)
	libltdl.so.7 => /usr/lib64/libltdl.so.7 (0x00007f19dca68000)
    Prompt in what there can be a problem?
    Tags: None
    • Gabalino
      Senior Member
      • Mar 2013
      • 103
      #2
      Solution:

      Create file
      Code:
      [root@zabbix ~]# cat /etc/sysconfig/zabbix-server
ORACLE_HOME=/usr/lib/oracle/11.2/client64
LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib:/usr/lib64
TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/lib/oracle/11.2/client64/lib

export ORACLE_HOME
export LD_LIBRARY_PATH
export TNS_ADMIN
export PATH
      Restart zabbix
      Code:
      service zabbix-server restart
      Сheck environment variables
      Code:
      [root@zabbix ~]# strings -a /proc/<Zabbix Main PID>/environ 
LANG=en_US.UTF-8
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/lib/oracle/11.2/client64/lib
CONFFILE=/etc/zabbix/zabbix_server.conf
ORACLE_HOME=/usr/lib/oracle/11.2/client64
LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib:/usr/lib64
TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin

        Comment

        • mark.chan
          Member
          • Mar 2016
          • 35
          #3
          i am facing the same problem....
          after creating the file for zabbix-server, the problem is still here....

          Cannot connect to ODBC DSN:[SQL_ERROR]:[01000][0][[unixODBC][Driver Manager]Can't open lib '/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1' : file not found]|

          please help....

          FINALLY, i got it by disable the SELinux!!!
          Last edited by mark.chan; 08-04-2016, 12:07.

            Comment

            • GOID
              Member
              • Oct 2014
              • 35
              #4
              Gabalino thx for solution! It's work! Why zabbix-server not use global env vars...

                Comment

                • eduwutzl
                  Senior Member
                  Zabbix Certified Specialist
                  • Jul 2010
                  • 314
                  #5
                  In my scenario, I do not solve the problem, but one difference is what I do with a zabbix-proxy.

                  I have a Ticket in https://support.zabbix.com/browse/ZBX-12450

                  Look at my setup.

                  Code:
                  CentOS Linux release 7.3.1611 (Core) 
unixODBC-2.3.1-11.el7.x86_64
unixODBC-devel-2.3.1-11.el7.x86_64
oracle-instantclient11.2-odbc-11.2.0.4.0-1.x86_64
oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64
oracle-instantclient12.2-odbc-12.2.0.1.0-1.x86_64
oracle-instantclient11.2-tools-11.2.0.4.0-1.x86_64
oracle-instantclient12.2-jdbc-12.2.0.1.0-1.x86_64
oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64
oracle-instantclient12.2-tools-12.2.0.1.0-1.x86_64
oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64
oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64
                  /etc/odbcinst.ini
                  Code:
                  [OracleODBC-12.1]
Description = Oracle ODBC driver for Oracle 12c
Driver = /usr/lib/oracle/12.2/client64/lib/libsqora.so.12.1
FileUsage = 1
Driver Logging = 7
                  /etc/odbc.ini

                  Code:
                  [isp]
Driver = OracleODBC-12.1
ServerName = ISP
FileUsage = 1
Driver Logging = 7
UserID = zabbix 
Password = *********
                  /etc/oracle/tnsnames.ora

                  Code:
                  ISP =
 ( DESCRIPTION =
  (ADDRESS_LIST =
  (ADDRESS =
  (PROTOCOL = TCP)
  (Host = 10.1.1.223)
  (Port = 1521)
  )
 )
 (CONNECT_DATA = (SID = ISP)
 )
)

                  Today there are already some SQL SERVER databases and a monitoring is working perfectly.
                  An oracle configuration, this is also in perfect working order.
                  Look below.

                  Code:
                  isql -v isp
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from  GV$SESSION S where upper(S.EVENT) like 'enq:%' and s.SECONDS_IN_WAIT > 30
+-----------------------------------------+
| COUNT(*)                                |
+-----------------------------------------+
| 0                                       |
+-----------------------------------------+
SQLRowCount returns -1
1 rows fetched
SQL>
                  In my zabbix, I always have this kind of error
                  Code:
                  Cannot connect to ODBC DSN:[SQL_ERROR]:[01000][0][[unixODBC][Driver Manager]Can't open lib '/usr/lib/oracle/12.2/client64/lib/libsqora.so.12.1' : file not found]|
                  This error is in the item inside the HOST (in the zabbix web interface).
                  We have also already exported the oracle environment variables to the zabbix user and we realize that the binary recognizes the exported variables

                  Code:
                  #sudo su - zabbix
#env
XDG_SESSION_ID=727
HOSTNAME=SAOPLZABAPP03
SHELL=/bin/bash
TERM=xterm-256color
HISTSIZE=1000
USER=zabbix
LD_LIBRARY_PATH=:/usr/lib/oracle/12.2/client64//lib:/usr/lib/oracle/12.2/client64//lib:/usr/lib/oracle/12.2/client64//lib
TWO_TASK=//10.1.1.223:1521/GISP
LS_COLORS=rs=0:di=38;5;27:ln=38;5;51:mh=44;38;5;15:pi=40;38;5;11:so=38;5;13:do=38;5;5:bd=48;5;232;38;5;11:cd=48;5;232;38;5;3:or=48;5;232;38;5;9:mi=05;48;5;232;38;5;15:su=48;5;196;38;5;15:sg=48;5;11;38;5;16:ca=48;5;196;38;5;226:tw=48;5;10;38;5;16:ow=48;5;10;38;5;21:st=48;5;21;38;5;15:ex=38;5;34:*.tar=38;5;9:*.tgz=38;5;9:*.arc=38;5;9:*.arj=38;5;9:*.taz=38;5;9:*.lha=38;5;9:*.lz4=38;5;9:*.lzh=38;5;9:*.lzma=38;5;9:*.tlz=38;5;9:*.txz=38;5;9:*.tzo=38;5;9:*.t7z=38;5;9:*.zip=38;5;9:*.z=38;5;9:*.Z=38;5;9:*.dz=38;5;9:*.gz=38;5;9:*.lrz=38;5;9:*.lz=38;5;9:*.lzo=38;5;9:*.xz=38;5;9:*.bz2=38;5;9:*.bz=38;5;9:*.tbz=38;5;9:*.tbz2=38;5;9:*.tz=38;5;9:*.deb=38;5;9:*.rpm=38;5;9:*.jar=38;5;9:*.war=38;5;9:*.ear=38;5;9:*.sar=38;5;9:*.rar=38;5;9:*.alz=38;5;9:*.ace=38;5;9:*.zoo=38;5;9:*.cpio=38;5;9:*.7z=38;5;9:*.rz=38;5;9:*.cab=38;5;9:*.jpg=38;5;13:*.jpeg=38;5;13:*.gif=38;5;13:*.bmp=38;5;13:*.pbm=38;5;13:*.pgm=38;5;13:*.ppm=38;5;13:*.tga=38;5;13:*.xbm=38;5;13:*.xpm=38;5;13:*.tif=38;5;13:*.tiff=38;5;13:*.png=38;5;13:*.svg=38;5;13:*.svgz=38;5;13:*.mng=38;5;13:*.pcx=38;5;13:*.mov=38;5;13:*.mpg=38;5;13:*.mpeg=38;5;13:*.m2v=38;5;13:*.mkv=38;5;13:*.webm=38;5;13:*.ogm=38;5;13:*.mp4=38;5;13:*.m4v=38;5;13:*.mp4v=38;5;13:*.vob=38;5;13:*.qt=38;5;13:*.nuv=38;5;13:*.wmv=38;5;13:*.asf=38;5;13:*.rm=38;5;13:*.rmvb=38;5;13:*.flc=38;5;13:*.avi=38;5;13:*.fli=38;5;13:*.flv=38;5;13:*.gl=38;5;13:*.dl=38;5;13:*.xcf=38;5;13:*.xwd=38;5;13:*.yuv=38;5;13:*.cgm=38;5;13:*.emf=38;5;13:*.axv=38;5;13:*.anx=38;5;13:*.ogv=38;5;13:*.ogx=38;5;13:*.aac=38;5;45:*.au=38;5;45:*.flac=38;5;45:*.mid=38;5;45:*.midi=38;5;45:*.mka=38;5;45:*.mp3=38;5;45:*.mpc=38;5;45:*.ogg=38;5;45:*.ra=38;5;45:*.wav=38;5;45:*.axa=38;5;45:*.oga=38;5;45:*.spx=38;5;45:*.xspf=38;5;45::di=1;36:
TNS_ADMIN=/etc/oracle
MAIL=/var/spool/mail/zabbix
PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/var/lib/zabbix/.local/bin:/var/lib/zabbix/bin
PWD=/var/lib/zabbix
LANG=en_US.UTF-8
HISTCONTROL=ignoredups
SHLVL=1
HOME=/var/lib/zabbix
LOGNAME=zabbix
LESSOPEN=||/usr/bin/lesspipe.sh %s
ORACLE_HOME=/usr/lib/oracle/12.2/client64/
HISTTIMEFORMAT=%d/%m/%y %T 
_=/bin/env

                  Code:
                  # ps aux |grep zabbix_proxy  |grep "zabbix_proxy.conf"
root     20451  0.0  0.0 112668   976 pts/2    S+   13:45   0:00 grep --color zabbix_proxy.conf
zabbix   32309  0.0  0.1 182940  6100 ?        S    Jul31   0:00 /usr/sbin/zabbix_proxy -c /etc/zabbix/zabbix_proxy.conf

#strings -a /proc/32309/environ 
LANG=en_US.UTF-8
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin
CONFFILE=/etc/zabbix/zabbix_proxy.conf
TWO_TASK=//10.1.1.223:1521/ISP
ORACLE_HOME=/usr/lib/oracle/12.2/client64/
TNS_ADMIN=/etc/oracle
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
                  The zabbix user can do it, connect via ODBC to the base, including SQL SQL commands.
                  Please believe a problem with zabbix.

                  Code:
                  [root@SRV]:~ # sudo su - zabbix
[zabbix@SRV]:~ $ isql -v isp zabbix *******
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

                  The zabbix user can do it, connect via ODBC to the base, including SQL SQL commands.
                  Please believe a problem with zabbix.

                  Code:
                  [root@SAO]:~ # sudo su - zabbix
Último login:Ter Ago  1 13:27:09 -03 2017em pts/2



[zabbix@SAO]:~ $ isql -v isp zabbix *****
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
                  Many thanks for helping.
                  Last edited by eduwutzl; 03-08-2017, 15:55.
                  Eduardo Wutzl da Silva
                  SRE Engenharia de Monitoração e Observabilidade

                    Comment

                    • mark.chan
                      Member
                      • Mar 2016
                      • 35
                      #6
                      I am not sure why the LD_PATH contain double "/" ?

                      LD_LIBRARY_PATH=:/usr/lib/oracle/12.2/client64//lib:/usr/lib/oracle/12.2/client64//lib:/usr/lib/oracle/12.2/client64//lib

                        Comment

                        • DiViNe
                          Member
                          • Feb 2015
                          • 30
                          #7
                          Same problem here (more infos: https://www.zabbix.com/forum/showthr...655#post209655) --> Zabbix Server doesen't use global env vars.
                          I created this file /etc/sysconfig/zabbix-server and restaerted zabbix-server, but this didn't help. I'm using CentOS 7. Any ideas?

                          SELinux is disabled

                            Comment

                            Previous template Next
                            Working...