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:
  2. 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.

  1. In pb.settings set:
  2. eventdestinations authevt=odbc=Oracle
    odbcinidir  /opt/pbul/etc
  1. Create /etc/tnsnames.ora:
  2. # 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:
  2. [oracle]
    Description = Oracle 18
    Driver      = /usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1
    ServerName  = ORCLCDB
  1. Create /opt/pbul/etc/odbc.ini:
  2. [oracle]
    Description = Oracle
    Driver      = oracle
    DSN         = ORCLCDB
    ServerName  = ORCLCDB
    UserID      = mdavis
    Password    = mdavis

    Use the user name created in step 1.

  1. 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:
  2. 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
  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:
  2. # cat oracle-instantclient.conf
    /usr/lib/oracle/18.5/client64/lib
    # cp oracle-instantclient.conf /etc/ld.so.conf.d
    # ldconfig
  3. Verify it’s loaded:
  4. # ldconfig -p|grep sqora
    libsqora.so.18.1 (libc6,x86-64) => /usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1

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:

  2. # pblog --odbc -f Oracle

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