Install Oracle ODBC Connector on Linux

  1. On the Oracle database/server, log in to the server where the Oracle database is installed as root, then su to oracle, and create your user (replace <user> by your name) as follows:
# su - oracle
Last login: Wed Sep  4 17:28:54 PDT 2019 from jurel.pbse.lab on pts/0
$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 5 15:21:24 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> create user mdavis identified by mdavis;
User created.
SQL> GRANT CONNECT,RESOURCE,DBA to mdavis;
Grant succeeded.
SQL> GRANT UNLIMITED TABLESPACE to mdavis;
Grant succeeded.
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
  1. Run the following steps on the log server, where only the Oracle ODBC connector needs to be installed. Steps shown below are on a CentOS 6 system:

    Oracle InstantClient (basic and ODBC) can be downloaded from https://www.oracle.com/database/technologies/instant-client/downloads.html.

    Download the file to a directory. For example, /tmp/Oracle.

    On RHEL 6, install oracle-instantclient<version>-basic… and oracle-instantclient<version>-odbc v18.5. Version 19.3 does not work on RHEL 6, but works on RHEL 7:

    # cd /tmp/Oracle
# yum install oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64.rpm oracle-instantclient18.5-odbc-18.5.0.0.0-3.x86_64.rpm

    Install unixODBC needed by Oracle ODBC:

    # yum install unixODBC

    unixODBC is installed in /usr/lib64.

  2. In pb.settings set:
    eventdestinations authevt=odbc=Oracle
odbcinidir  /opt/pbul/etc
  3. Create /etc/tnsnames.ora:
    # cat /etc/tnsnames.ora
ORCLCDB=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=moonfish)(PORT=1521)))(CONNECT_DATA=(SID=ORCLCDB)))

The tnsnames.ora file needs to be in /etc. Copying it elsewhere and setting environment variable TNS_ADMIN to the new directory does not work.

  1. Create /opt/pbul/etc/odbcinst.ini:
    [oracle]
Description = Oracle 18
Driver      = /usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1
ServerName  = ORCLCDB
  2. Create /opt/pbul/etc/odbc.ini:
    [oracle]
Description = Oracle
Driver      = oracle
DSN         = ORCLCDB
ServerName  = ORCLCDB
UserID      = mdavis
Password    = mdavis

    Use the user name created in step 1.

  3. The following library is required for pblighttpd to connect to the Oracle ODBC: /usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1. With Oracle ODBC, some of the paths of the dependent libraries are not set and setting LD_LIBRARY_PATH doesn’t work. The following error is displayed:
    Sep  6 09:08:18 [12974] 6339.32 Failed to connect to ODBC DSN 'Oracle' - [unixODBC][Driver Manager]Can't open lib '/usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1' : file not found

 

Solution 1

  1. Set the library path system wide in ld.so. For that, create a file oracle-instantclient.conf with the path in it, and do the following:
    # cat oracle-instantclient.conf
/usr/lib/oracle/18.5/client64/lib
# cp oracle-instantclient.conf /etc/ld.so.conf.d
# ldconfig
  2. Verify it’s loaded:
    # ldconfig -p|grep sqora
libsqora.so.18.1 (libc6,x86-64) => /usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1

Solution 2

Use patchelf to set the path for all the Oracle libraries.

If patchelf is not on your machine, download patchelf rpm from https://pkgs.org/search/?q=patchelf.

Do the following:

# service pblighttpd stop
# cd /usr/lib/oracle/18.5/client64/lib
# for i in *.so*
do
patchelf --set-rpath /usr/lib/oracle/18.5/client64/lib $i
done
# service pblighttpd start
Starting pblighttpd-svc service.

You might prefer the second solution because the lib path is set only for the Oracle libraries and is not system-wide.

  1. To use oracle SSL authentication, create sqlnet.ora file, update TNS_NAMES configurations for pblighttpd and restart pblighttpd.

    • Create /etc/sqlnet.ora.
      #cat /etc/sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES= (TCPS)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)
SSL_SERVER_DN_MATCH = no
WALLET_LOCATION = (SOURCE =(METHOD = FILE)(METHOD_DATA =(DIRECTORY = /home/oracle/app/oracle/wallet)) )            
      ls /etc/*.ora
/etc/sqlnet.ora  /etc/tnsnames.ora

    • If pblighttpd is running as deamon: edit the /etc/init.d/pblighttpd file.

    Add an entry to export the TNS_ADMIN environmental variable.

cat /etc/init.d/pblighttpd | grep TNS_ADMIN
TNS_ADMIN=/etc/
export PBLIGHTTPD_PRG PBLIGHTTPD_ROOT PBLIGHTTPD_PID PBLIGHTTPD_BIN PBLIGHTTPD_CONF RETVAL PATH TNS_ADMIN
    • If it is a service, edit /etc/systemd/system/pblighttpd.service and add the text below the [Service] tag.
cat /etc/systemd/system/pblighttpd.service
[Unit]
Description=BeyondTrust PowerBroker REST services
After=network.target
[Service]
ExecStart=/usr/lib/beyondtrust/pb/rest/sbin/pblighttpd-svc
ExecReload=/bin/kill -HUP $MAINPID
Environment=TNS_ADMIN=/etc/
[Install]
WantedBy=multi-user.target

TNS_ADMIN is the location from where odbc drivers will read sqlnet.ora and tnsnames.ora.

    • Restart pblighttpd.

  1. Run pbrun and verify using:

    # pblog --odbc -f Oracle

Export TNS_NAME=/etc/ to use oracle SSL connections.