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

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

In this example oracle 11.2 (xe) 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-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
'sqlplus' really is not required but we can simulate the connection before moving to ODBC connfiguration that is the reason why we will install it:
# rpm -i oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm
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)))'
Move to direcotry where precious oracle libraries are installed:
# cd /usr/lib/oracle/11.2/client64/lib
Create some simbolic links:
# ln -s libclntsh.so.11.1 libclntsh.so
# ln -s libocci.so.11.1 libocci.so
A unique way how to configure libraries:
# echo "${PWD}" | sudo tee /etc/ld.so.conf.d/oracle-instantclient.conf
# sudo ldconfig
Check the libraries:
# ldconfig -p|grep oracle
It should print:
libsqora.so.11.1 (libc6,x86-64) => /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
libsqlplusic.so (libc6,x86-64) => /usr/lib/oracle/11.2/client64/lib/libsqlplusic.so
libsqlplus.so (libc6,x86-64) => /usr/lib/oracle/11.2/client64/lib/libsqlplus.so
libocijdbc11.so (libc6,x86-64) => /usr/lib/oracle/11.2/client64/lib/libocijdbc11.so
libociei.so (libc6,x86-64) => /usr/lib/oracle/11.2/client64/lib/libociei.so
libocci.so.11.1 (libc6,x86-64) => /usr/lib/oracle/11.2/client64/lib/libocci.so.11.1
libocci.so (libc6,x86-64) => /usr/lib/oracle/11.2/client64/lib/libocci.so
libnnz11.so (libc6,x86-64) => /usr/lib/oracle/11.2/client64/lib/libnnz11.so
libclntsh.so.11.1 (libc6,x86-64) => /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1
libclntsh.so (libc6,x86-64) => /usr/lib/oracle/11.2/client64/lib/libclntsh.so
Make sure unixODBC package is installed:
# yum -y install unixODBC
There is HTML documentation and script located how to install driver:
# cd /usr/share/oracle/11.2/client64
Observe content '/etc/odbcinst.ini':
# cat /etc/odbcinst.ini
Make sure 'libsqora.so.11.1' exists in '/usr/lib/oracle/11.2/client64/lib':
# ls -l /usr/lib/oracle/11.2/client64/lib
Install oracle definition:
# ./odbc_update_ini.sh / /usr/lib/oracle/11.2/client64/lib
Check /etc/odbcinst.ini:
# cat /etc/odbcinst.ini
It should end with:
[Oracle 11g ODBC driver]
Description     = Oracle ODBC driver for Oracle 11g
Driver          = /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
Setup           =
FileUsage       =
CPTimeout       =
CPReuse         =
Install LD_PATH for every user:
# cat << 'EOF' > /etc/profile.d/oracle.sh
ORACLE_HOME=/usr/lib/oracle/11.2/client64
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$PATH
TNS_ADMIN=$ORACLE_HOME/network/admin:$TNS_ADMIN

export ORACLE_HOME
export LD_LIBRARY_PATH
export PATH
export TNS_ADMIN
EOF
There is one DSN configured in ~/.odbc with title 'OracleODBC-11g': 
# cat ~/.odbc.ini
This is great way to check if driver relation is installed:
# isql -v OracleODBC-11g
If it prints:

[01000][unixODBC][Driver Manager]Can't open lib '/usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1' : file not found

Then driver relation is not working! Run:
# ldd  /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
No so good output:
ldd: warning: you do not have execution permission for `/usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1'
linux-vdso.so.1 =>  (0x00007ffe53bb2000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007fb9d011a000)
libm.so.6 => /lib64/libm.so.6 (0x00007fb9cfe18000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fb9cfbfc000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fb9cf9e2000)
libclntsh.so.11.1 => /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 (0x00007fb9cd073000)
libodbcinst.so.1 => not found
libc.so.6 => /lib64/libc.so.6 (0x00007fb9ccca5000)
/lib64/ld-linux-x86-64.so.2 (0x00007fb9d031e000)
libnnz11.so => /usr/lib/oracle/11.2/client64/lib/libnnz11.so (0x00007fb9cc8d8000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007fb9cc6d6000)
If lld reports:

'warning: you do not have execution permission'

Then run:
# chmod 755 /usr/lib/oracle/11.2/client64/lib/lib*
If lld reports:

libodbcinst.so.1 => not found

Run:
# cd /usr/lib64
# ln -s libodbcinst.so.2 libodbcinst.so.1
LD config again:
# sudo ldconfig
Check related libraries:
# ldd /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1
Good output:
linux-vdso.so.1 =>  (0x00007fff69db4000)
libdl.so.2 (0x00007f2124b7b000)
libm.so.6 (0x00007f2124879000)
libpthread.so.0 (0x00007f212465d000)
libnsl.so.1 (0x00007f2124443000)
libclntsh.so.11.1 => /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 (0x00007f2121ad4000)
libodbcinst.so.1 (0x00007f21218c2000)
libc.so.6 (0x00007f21214f4000)
/lib64/ld-linux-x86-64.so.2 (0x00007f2124d7f000)
libnnz11.so => /usr/lib/oracle/11.2/client64/lib/libnnz11.so (0x00007f2121127000)
libaio.so.1 (0x00007f2120f25000)
libltdl.so.7 (0x00007f2120d1b000)
Now after:
# isql -v OracleODBC-11g
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-11g

[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-11g

[28000][unixODBC][Oracle][ODBC][Ora]ORA-01005: null password given; logon denied

Put password in config:
# grep Password ~/.odbc.ini
It should show:
Password = oracle
Connect:
# isql -v OracleODBC-11g
It will show:
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
Exit:
SQL> quit
In case of running into errors, Make sure you are working with a global configuration file (/etc/odbc.ini) and there are no '.odbc.ini' installed at home:
find / -name .odbc.ini
Remove configuration for user:
rm ~/.odbc.ini
We can now remove 'sqlplus' package. It's not required for ODBC monitoring. Only for debugging:
yum remove oracle-instantclient11.2-sqlplus
Oracle packages at the end:
rpm -qa|grep oracle
It will print only:
oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64
oracle-instantclient11.2-odbc-11.2.0.4.0-1.x86_64
Byat

No comments:

Post a Comment