Oracle 18.5, CentOS 7, RPM, ODBC configuration, Zabbix

This solution is using official RPM's dedicated for RedHat7/CentOS7 system.

In this example oracle 12.2 (12c) server database is located or remote machine '10.133.112.87' and listening with port '49161':

# yum -y install nmap
# nmap -p 49161 10.133.112.87

Make sure no existing oracle rpm packages are installed:

# rpm -qa|grep oracle

It should print nothing for:

# ldconfig -p|grep oracle

Take a look at:

# sudo find / -name oracle

Possibly remove oracle ld config profile:

# cd /etc/ld.so.conf.d
# ls -l
# rm -rf oracle-instantclient.conf
# sudo ldconfig

Instant client is required:

# rpm -ivh oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64.rpm

'sqlplus' package really is not required but we can simulate the connection before moving to ODBC configuration that is the reason why we will install it:

# rpm -ivh oracle-instantclient18.5-sqlplus-18.5.0.0.0-3.x86_64.rpm

Move to directory where precious oracle libraries are installed:

# cd /usr/lib/oracle/18.5/client64/lib

Configure LD library path for system:

# echo "${PWD}" | sudo tee /etc/ld.so.conf.d/oracle-instantclient.conf
# sudo ldconfig

Check the libraries:

# ldconfig -p|grep oracle

It should print:

        libsqlplusic.so (libc6,x86-64) => /usr/lib/oracle/18.5/client64/lib/libsqlplusic.so
        libsqlplus.so (libc6,x86-64) => /usr/lib/oracle/18.5/client64/lib/libsqlplus.so
        liboramysql18.so (libc6,x86-64) => /usr/lib/oracle/18.5/client64/lib/liboramysql18.so
        libons.so (libc6,x86-64) => /usr/lib/oracle/18.5/client64/lib/libons.so
        libocijdbc18.so (libc6,x86-64) => /usr/lib/oracle/18.5/client64/lib/libocijdbc18.so
        libociei.so (libc6,x86-64) => /usr/lib/oracle/18.5/client64/lib/libociei.so
        libocci.so.18.1 (libc6,x86-64) => /usr/lib/oracle/18.5/client64/lib/libocci.so.18.1
        libnnz18.so (libc6,x86-64) => /usr/lib/oracle/18.5/client64/lib/libnnz18.so
        libmql1.so (libc6,x86-64) => /usr/lib/oracle/18.5/client64/lib/libmql1.so
        libipc1.so (libc6,x86-64) => /usr/lib/oracle/18.5/client64/lib/libipc1.so
        libclntshcore.so.18.1 (libc6,x86-64) => /usr/lib/oracle/18.5/client64/lib/libclntshcore.so.18.1
        libclntsh.so.18.1 (libc6,x86-64) => /usr/lib/oracle/18.5/client64/lib/libclntsh.so.18.1

Contact database on host '10.133.112.87' through port '49161' by using username 'system' with password 'oracle'. service name is 'xe':

# sqlplus64 'system/oracle@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.133.112.87)(PORT=49161)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xe)))'

Do not continue if it does not work.

Make sure unixODBC package is installed:

# yum -y install unixODBC

Install Oracle 18.5 ODBC driver:

# rpm -ivh oracle-instantclient18.5-odbc-18.5.0.0.0-3.x86_64.rpm

There is HTML documentation and script located how to install driver:

# cd /usr/share/oracle/18.5/client64

Observe content '/etc/odbcinst.ini':

# cat /etc/odbcinst.ini

Make sure 'libsqora.so.18.1' exists in '/usr/lib/oracle/18.5/client64/lib':

# ls -l /usr/lib/oracle/18.5/client64/lib/libsqora*

Make sure 'odbc.ini' file exist:

# cat /etc/odbc.ini
# create an empty file if it does not exist

Install oracle definition:

# ./odbc_update_ini.sh / /usr/lib/oracle/18.5/client64/lib

It should print:

 *** ODBCINI environment variable not set,defaulting it to HOME directory!

Check /etc/odbcinst.ini:

# cat /etc/odbcinst.ini

It should end with:

[Oracle 18 ODBC driver]
Description     = Oracle ODBC driver for Oracle 18
Driver          = /usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1
Setup           =
FileUsage       =
CPTimeout       =
CPReuse         =

Make sure no errors are produced while running:

ldd /usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1

It should print very similar output to:

        linux-vdso.so.1 =>  (0x00007fffc757f000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f410fa83000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f410f781000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f410f565000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f410f34b000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f410f143000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f410ef41000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f410ed27000)
        libclntsh.so.18.1 => /usr/lib/oracle/18.5/client64/lib/libclntsh.so.18.1 (0x00007f410ad69000)
        libclntshcore.so.18.1 => /usr/lib/oracle/18.5/client64/lib/libclntshcore.so.18.1 (0x00007f410a786000)
        libodbcinst.so.2 => /lib64/libodbcinst.so.2 (0x00007f410a574000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f410a1a6000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f4109f90000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f410ff4b000)
        libmql1.so => /usr/lib/oracle/18.5/client64/lib/libmql1.so (0x00007f4109d30000)
        libipc1.so => /usr/lib/oracle/18.5/client64/lib/libipc1.so (0x00007f41098be000)
        libnnz18.so => /usr/lib/oracle/18.5/client64/lib/libnnz18.so (0x00007f4109169000)
        libons.so => /usr/lib/oracle/18.5/client64/lib/libons.so (0x00007f4108f19000)
        libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f4108d0f000)

There is one DSN configured in ~/.odbc with title 'OracleODBC-18':

# cat ~/.odbc.ini

It will print:

[OracleODBC-18]
AggregateSQLType = FLOAT
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CacheBufferSize = 20
CloseCursor = F
DisableDPM = F
DisableMTS = T
DisableRULEHint = T
Driver = Oracle 18 ODBC driver
DSN = OracleODBC-18
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
LobPrefetchSize = 8192
Lobs = T
Longs = T
MaxLargeData = 0
MaxTokenSize = 8192
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName =
SQLGetData extensions = F
SQLTranslateErrors = F
StatementCache = F
Translation DLL =
Translation Option = 0
UseOCIDescribeAny = F
UserID =

Transfer ODBC configuration on global level only:

# cat ~/.odbc.ini >> /etc/odbc.ini
rm ~/.odbc.ini

Specify the credentials in '/etc/odbc.ini' file:

ServerName = //10.133.112.87:49161/xe
UserID = system
Password = oracle

This is great way to check if driver relation is installed:

# isql -v OracleODBC-18

Connect:

# isql -v OracleODBC-18

It will show:

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

Exit:

SQL> quit

Optionally you can remove package:

yum remove oracle-instantclient18.5-sqlplusoracle-instantclient12.2-odbc-12.2.0.1.0-1.x86_64

Oracle packages at the end:

rpm -qa|grep oracle