problem in oracle DB monitoring using ODBC

[soumya]

Hi,

I'm trying to enable Oracle DB monitoring for my Prod Environment using ODBC. We're monitoring servers via Proxy. I've configured all necessary staffs (as per doc available https://habr.com/en/post/226365/) in proxy server & able to connect to configured DSN. Below are the details


[root@zbxproxy1 ~]# isql -v ORACLEQATMDB1V
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
[root@zbxproxy1 ~]#


odbc.ini

[root@zbxproxy1 ~]# cat /etc/odbc.ini
[ORACLEQATMDB1V]
Driver = OracleDriver
ServerName = //xxxxx:1521/HCCQATM
DSN = ORACLEQATMDB1V
UserID= xxxxx
Password= Xxxx
[root@zbxproxy1 ~]#


odbcinst.ini

[OracleDriver]
Description=Oracle ODBC driver
Driver=/u01/app/oracle/product/12.2.0/client_64/lib/libsqora.so.12.1


Loaded Modules for user ROOT

[root@zbxproxy1 ~]# ldd /u01/app/oracle/product/12.2.0/client_64/lib/libsqora.so.12.1
linux-vdso.so.1 => (0x00007ffd951e5000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f1b3703a000)
libm.so.6 => /lib64/libm.so.6 (0x00007f1b36d35000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f1b36b18000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f1b368fd000)
librt.so.1 => /lib64/librt.so.1 (0x00007f1b366f4000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007f1a37022000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f1b364da000)
libclntsh.so.12.1 => /u01/app/oracle/product/12.2.0/client_64/lib/libclntsh.so.12.1 (0x00007f1a3357b000)
libclntshcore.so.12.1 => /u01/app/oracle/product/12.2.0/client_64/lib/libclntshcore.so.12.1 (0x00007f1a32fac000)
libodbcinst.so.2 => /lib64/libodbcinst.so.2 (0x00007f1a32d99000)
libc.so.6 => /lib64/libc.so.6 (0x00007f1b3610a000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f1a32b81000)
/lib64/ld-linux-x86-64.so.2 (0x000055d31f4ad000)
libksplice_helper.so => /lib64/libksplice_helper.so (0x00007f1b35f05000)
libmql1.so => /u01/app/oracle/product/12.2.0/client_64/lib/libmql1.so (0x00007f1a32908000)
libipc1.so => /u01/app/oracle/product/12.2.0/client_64/lib/libipc1.so (0x00007f1a324d4000)
libnnz12.so => /u01/app/oracle/product/12.2.0/client_64/lib/libnnz12.so (0x00007f1a31d89000)
libons.so => /u01/app/oracle/product/12.2.0/client_64/lib/libons.so (0x00007f1a31b3a000)
libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f1a3192f000)
[root@zbxproxy1 ~]#


Env Variable for user ROOT

[root@zbxproxy1 ~]# env
XDG_SESSION_ID=11251
HOSTNAME=xxxxx
SHELL=/bin/bash
TERM=xterm
HISTSIZE=1000
PERL5LIB=/root/perl5/lib/perl5:
PERL_MB_OPT=--install_base /root/perl5
USER=root
LD_LIBRARY_PATH=:/u01/app/oracle/product/12.2.0/client_64/lib/
LS_COLORS=rs=0:di=01;34:ln=01;36:mh=00i=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01r=40;31;01:mi=01;05;37;41:su=37;41:sg=30;43:ca=30; 41:tw=30;42w=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:*.t xz=01;31:*.tzo=01;31:*.t7z=01;31:*.zip=01;31:*.z=0 1;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;3 1:*.sar=01;31:*.rar=01;31:*.alz=01;31:*.ace=01;31: *.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.c ab=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bm p=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga= 01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=0 1;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;3 5:*.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:*.fl i=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;3 5:*.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:*.o gg=01;36:*.ra=01;36:*.wav=01;36:*.axa=01;36:*.oga= 01;36:*.spx=01;36:*.xspf=01;36:
TNS_ADMIN=/u01/app/oracle/product/12.2.0/client_64/network/admin/
MAIL=/var/spool/mail/root
PATH=/u01/app/oracle/product/12.2.0/client_64//bin:/root/perl5/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
PWD=/root
LANG=en_US.UTF-8
HISTCONTROL=ignoredups
SHLVL=1
HOME=/root
PERL_LOCAL_LIB_ROOT=:/root/perl5
LOGNAME=root
XDG_DATA_DIRS=/root/.local/share/flatpak/exports/share:/var/lib/flatpak/exports/share:/usr/local/share:/usr/share
LESSOPEN=||/usr/bin/lesspipe.sh %s
XDG_RUNTIME_DIR=/run/user/0
ORACLE_HOME=/u01/app/oracle/product/12.2.0/client_64/
PERL_MM_OPT=INSTALL_BASE=/root/perl5
_=/bin/env
[root@zbxproxy1 ~]#




Loaded Modules for user ZABBIX


-bash-4.2$ ldd /u01/app/oracle/product/12.2.0/client_64/lib/libsqora.so.12.1
linux-vdso.so.1 => (0x00007fffcf6e7000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f7aa719e000)
libm.so.6 => /lib64/libm.so.6 (0x00007f7aa6e99000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f7aa6c7c000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f7aa6a61000)
librt.so.1 => /lib64/librt.so.1 (0x00007f7aa6858000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007f79a7186000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f7aa663e000)
libclntsh.so.12.1 => /u01/app/oracle/product/12.2.0/client_64/lib/libclntsh.so.12.1 (0x00007f79a36df000)
libclntshcore.so.12.1 => /u01/app/oracle/product/12.2.0/client_64/lib/libclntshcore.so.12.1 (0x00007f79a3110000)
libodbcinst.so.2 => /lib64/libodbcinst.so.2 (0x00007f79a2efd000)
libc.so.6 => /lib64/libc.so.6 (0x00007f7aa626e000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f79a2ce5000)
/lib64/ld-linux-x86-64.so.2 (0x0000560ab7923000)
libksplice_helper.so => /lib64/libksplice_helper.so (0x00007f7aa6069000)
libmql1.so => /u01/app/oracle/product/12.2.0/client_64/lib/libmql1.so (0x00007f79a2a6c000)
libipc1.so => /u01/app/oracle/product/12.2.0/client_64/lib/libipc1.so (0x00007f79a2638000)
libnnz12.so => /u01/app/oracle/product/12.2.0/client_64/lib/libnnz12.so (0x00007f79a1eed000)
libons.so => /u01/app/oracle/product/12.2.0/client_64/lib/libons.so (0x00007f79a1c9e000)
libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f79a1a93000)
-bash-4.2$


Env Variable for user ZABBIX

-bash-4.2$ env
XDG_SESSION_ID=11251
HOSTNAME=xxxxx
SHELL=/bin/bash
TERM=xterm
HISTSIZE=1000
PERL5LIB=/var/lib/zabbix/perl5/lib/perl5:
PERL_MB_OPT=--install_base /var/lib/zabbix/perl5
USER=zabbix
LD_LIBRARY_PATH=:/u01/app/oracle/product/12.2.0/client_64/lib/
LS_COLORS=rs=0:di=01;34:ln=01;36:mh=00i=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01r=40;31;01:mi=01;05;37;41:su=37;41:sg=30;43:ca=30; 41:tw=30;42w=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:*.t xz=01;31:*.tzo=01;31:*.t7z=01;31:*.zip=01;31:*.z=0 1;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;3 1:*.sar=01;31:*.rar=01;31:*.alz=01;31:*.ace=01;31: *.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.c ab=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bm p=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga= 01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=0 1;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;3 5:*.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:*.fl i=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;3 5:*.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:*.o gg=01;36:*.ra=01;36:*.wav=01;36:*.axa=01;36:*.oga= 01;36:*.spx=01;36:*.xspf=01;36:
TNS_ADMIN=/u01/app/oracle/product/12.2.0/client_64/network/admin/
MAIL=/var/spool/mail/zabbix
PATH=/u01/app/oracle/product/12.2.0/client_64//bin:/var/lib/zabbix/perl5/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin
PWD=/var/lib/zabbix
LANG=en_US.UTF-8
HISTCONTROL=ignoredups
SHLVL=1
HOME=/var/lib/zabbix
PERL_LOCAL_LIB_ROOT=:/var/lib/zabbix/perl5
LOGNAME=zabbix
XDG_DATA_DIRS=/var/lib/zabbix/.local/share/flatpak/exports/share:/var/lib/flatpak/exports/share:/usr/local/share:/usr/share
LESSOPEN=||/usr/bin/lesspipe.sh %s
ORACLE_HOME=/u01/app/oracle/product/12.2.0/client_64/
PERL_MM_OPT=INSTALL_BASE=/var/lib/zabbix/perl5
_=/bin/env
-bash-4.2$


-bash-4.2$ isql -v ORACLEQATMDB1V
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>


But all my Items are getting into not supported mode with error

Cannot connect to ODBC DSN: [SQL_ERROR]:[01000][0][[unixODBC][Driver Manager]Can't open lib '/u01/app/oracle/product/12.2.0/client_64/lib/libsqora.so.12.1' : file not found]

even LLD rules for jobs & tablespaces is giving error

Value should be a JSON object.

. though i can get valid json output while calling the external script. PFB for details.



External Script output

[root@zbxproxy1 ~]# /usr/lib/zabbix/externalscripts/oracle.odbc.discovery ORACLEQATMDB1V tablespaces

{"data":[{"{#TBSNAME}":"ABPPMOB_DATA"},{"{#TBSNAME}":"ABPPTM_DATA"},{"{#TBSNAME}":"ABPPUI_DATA"},{"{#TBSNAME}":"AC_DATA"},{"{#TBSNAME}":"AC_INDEX"},{"{#TBSNAME}":"API_ABPP_DATA"},{"{#TBSNAME}":"CEA_DATA"},{"{#TBSNAME}":"CEA_INDEX"},{"{#TBSNAME}":"COMMUSR"},{"{#TBSNAME}":"EDI_DATA"},{"{#TBSNAME}":"EDI_INDEX"},{"{#TBSNAME}":"FAUCT_DATA"},{"{#TBSNAME}":"FAUCT_INDEX"},{"{#TBSNAME}":"I2LP_DATA"},{"{#TBSNAME}":"I2LP_INDEX"},{"{#TBSNAME}":"I2TM_DATA"},{"{#TBSNAME}":"I2TM_INDEX"},{"{#TBSNAME}":"MOB_DATA"},{"{#TBSNAME}":"MOB_INDEX"},{"{#TBSNAME}":"RBS_UNDO"},{"{#TBSNAME}":"STATSPACK_DATA"},{"{#TBSNAME}":"SYSAUX"},{"{#TBSNAME}":"SYSTEM"},{"{#TBSNAME}":"TEMP"},{"{#TBSNAME}":"USERS"},{"{#TBSNAME}":"XDB"}]}

[root@zbxproxy1 ~]#
[root@zbxproxy1 ~]#
[root@zbxproxy1 ~]#
[root@zbxproxy1 ~]# /usr/lib/zabbix/externalscripts/oracle.odbc.discovery ORACLEQATMDB1V jobs

{"data":[{"{#JOBNAME}":"PURGE_LOG","{#JOBOWNER}":"SYS"},{"{#JOBNAME}":"PMO_DEFERRED_GIDX_MAINT_JOB","{#JOBOWNER}":"SYS"},{"{#JOBNAME}":"CLEANUP_NON_EXIST_OBJ","{#JOBOWNER}":"SYS"},{"{#JOBNAME}":"CLEANUP_ONLINE_IND_BUILD","{#JOBOWNER}":"SYS"},{"{#JOBNAME}":"CLEANUP_TAB_IOT_PMO","{#JOBOWNER}":"SYS"},{"{#JOBNAME}":"FILE_SIZE_UPD","{#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}":"MGMT_CONFIG_JOB","{#JOBOWNER}":"ORACLE_OCM"},{"{#JOBNAME}":"MGMT_STATS_CONFIG_JOB","{#JOBOWNER}":"ORACLE_OCM"},{"{#JOBNAME}":"CLEANUP_TRANSIENT_TYPE","{#JOBOWNER}":"SYS"},{"{#JOBNAME}":"CLEANUP_TRANSIENT_PKG","{#JOBOWNER}":"SYS"},{"{#JOBNAME}":"CLEANUP_ONLINE_PMO","{#JOBOWNER}":"SYS"}]}

[root@zbxproxy1 ~]#
[root@zbxproxy1 ~]#


error screen shots are attached. i don't understand whaere i'm doing mistake or anything get missed. Kindly help to fugure out & resolve the same. Thank you.

[soumya]

fixed this by setting environmental variable on /etc/sysconfig/zabbix-proxy & /etc/init.d/functions