Oracle 12.2, 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 -i oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
If it shows:

libaio is needed by oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64

Install:
yum -y install libaio
'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 -i oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
Move to directory where precious oracle libraries are installed:
# cd /usr/lib/oracle/12.2/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/12.2/client64/lib/libsqlplusic.so
        libsqlplus.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libsqlplus.so
        liboramysql12.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/liboramysql12.so
        libons.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libons.so
        libocijdbc12.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libocijdbc12.so
        libociei.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libociei.so
        libocci.so.12.1 (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libocci.so.12.1
        libocci.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libocci.so
        libnnz12.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libnnz12.so
        libmql1.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libmql1.so
        libipc1.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libipc1.so
        libclntshcore.so.12.1 (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libclntshcore.so.12.1
        libclntsh.so.12.1 (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libclntsh.so.12.1
        libclntsh.so (libc6,x86-64) => /usr/lib/oracle/12.2/client64/lib/libclntsh.so
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)))'
If it shows:

sqlplus64: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

Then the previous step was not completed about setting up LD_PATH was not completed successfully.

Make sure unixODBC package is installed:
# yum -y install unixODBC
Install Oracle 12.2 ODBC driver:
# rpm -ivh oracle-instantclient12.2-odbc-12.2.0.1.0-2.x86_64.rpm
There is HTML documentation and script located how to install driver:
# cd /usr/share/oracle/12.2/client64
Observe content '/etc/odbcinst.ini':
# cat /etc/odbcinst.ini
Make sure 'libsqora.so.12.1' exists in '/usr/lib/oracle/12.2/client64/lib':
# ls -l /usr/lib/oracle/12.2/client64/lib
Install oracle definition:
# ./odbc_update_ini.sh / /usr/lib/oracle/12.2/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 12c ODBC driver]
Description     = Oracle ODBC driver for Oracle 12c
Driver          = /usr/lib/oracle/12.2/client64/lib/libsqora.so.12.1
Setup           =
FileUsage       =
CPTimeout       =
CPReuse         =
There is one DSN configured in ~/.odbc with title 'OracleODBC-12c': 
# cat ~/.odbc.ini
It will print:
[OracleODBC-12c]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle 12c ODBC driver
DSN = OracleODBC-12c
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MaxLargeData = 0
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName =
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID =
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F
SQLTranslateErrors=F
MaxTokenSize=8192
AggregateSQLType=FLOAT
This is great way to check if driver relation is installed:
# isql -v OracleODBC-12c
It will show:

[S1000][unixODBC][Oracle][ODBC][Ora]ORA-12162: TNS:net service name is incorrectly specified

This is success. The driver relation is working! Only the TNS service name has not been recognized.

Set correct IP address in:
# grep ServerName ~/.odbc.ini
It should print:
ServerName = 10.133.112.87
Test:
# isql -v OracleODBC-12c

[S1000][unixODBC][Oracle][ODBC][Ora]ORA-12541: TNS:no listener

Put the port in config:
# grep ServerName ~/.odbc.ini
It should look like:
ServerName = 10.133.112.87:49161
Test:
# isql -v OracleODBC-12c

[28000][unixODBC][Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied

Put password in config:
# grep Password ~/.odbc.ini
It should show:
Password = oracle
Connect:
# isql -v OracleODBC-12c
It will show:
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
Exit:
SQL> quit
We can now remove 'sqlplus' package. It's not required for ODBC monitoring. Only for debugging: 
yum remove oracle-instantclient12.2-sqlplus
Oracle packages at the end:
rpm -qa|grep oracle
It will print only:
oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64
oracle-instantclient12.2-odbc-12.2.0.1.0-1.x86_64
Byat

No comments:

Post a Comment