ODBC setup for Oracle Database

Before you begin

• Ensure that unixODBC is installed.
• Obtain the Oracle Database Client installation package (available at https://www.oracle.com/be/database/technologies/oracle19c-linux-downloads.html). This package is typically named LINUX.X64_193000_db_home.zip for 64-bit installations.
• Ensure that you are logged on with a user account with administrative privileges.

Installing Oracle Database Client

dpdbadmin only retrieves tables that are owned by the calling user. For this reason, you need to use either the table owner user to call dpdbadmin, or use consistently the same user for database schema creation, update, and/or validation.

To install Oracle Database Client 19c

1. Open a terminal window.

2. Ensure that all required packages are installed. Run the following command (depending on the distribution):

   • Red Hat Enterprise Linux 9

     dnf install -y bc binutils compat-openssl11 elfutils-libelf fontconfig glibc glibc-devel ksh libaio libasan liblsan libX11 libXau libXi libXrender libXtst libxcrypt-compat libgcc libibverbs libnsl libnsl2 libnsl2-devel librdmacm libstdc++ libxcb libvirt-libs make policycoreutils policycoreutils-python-utils smartmontools sysstat

   • Red Hat Enterprise Linux 8

     dnf install -y bc binutils elfutils-libelf elfutils-libelf-devel glibc glibc-devel ksh libaio libaio-devel libXrender libX11 libXau libXi libXtst libgcc libnsl libnsl2 libnsl2-devel librdmacm libstdc++ libstdc++-devel libxcb libibverbs make policycoreutils policycoreutils-python-utils smartmontools sysstat

   • Red Hat Enterprise Linux 7

     yum install -y bc binutils compat-libcap1 compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel fontconfig-devel glibc glibc-devel ksh libaio libaio-devel libX11 libXau libXi libXtst libXrender libXrender-devel libgcc libstdc++ libstdc++-devel libxcb make smartmontools sysstat

3. Disable X Window System access control to allow Oracle Universal Installer (OUI) to run:

   xhost +

4. Create the two user groups oinstall and dba:

   /usr/sbin/groupadd -g 54321 oinstall

   /usr/sbin/groupadd -g 54322 dba

5. Create a user account named oracle and assign it to the oinstall and dba groups:

   useradd -m -g oinstall -G dba oracle

6. Set a password for the user account oracle:

   passwd oracle

7. Create the installation folder for the Oracle Database Client:

   mkdir -p /u01/app/oracle

8. Change the owner of the installation folder to the oracleuser account:

   chown -R oracle:oinstall /u01/app/

9. Change the mode of the installation folder:

   chmod -R 775 /u01/app/

10. Download the Oracle Database Client installation package and extract it to a temporary folder, e.g. /tmp:

    unzip -oq oracle_installer_folder/LINUX.X64_193000_db_home.zip

11. Change the owner and mode of the folder where the Oracle Universal Installer files are located (if the location is different than /u01/app/):

    chown -R oracle:oinstall oracle_installer_folder

    chmod -R 775 oracle_installer_folder

    where oracle_installer_folder is the folder where the Oracle Universal Installer is located.

12. Log in as the oracle user account:

    su oracle

13. Set the following environment variables:

    export ORACLE_BASE=/u01/app/oracle

    export ORACLE_SID=database_sid

    where database_sid is the SID to be set for the Oracle database.

14. Navigate to the folder containing the Oracle Universal Installer files:

    cd oracle_installer_folder

15. Run Oracle Universal Installer:

    ./runInstaller

16. Follow the Oracle Universal Installer instructions and run any required scripts:

    1. Select the Administrator option on the Select Installation Type page and click Next.
    2. Skip the Specify Installation Location and the Create Inventory pages. Keep the default settings and click Next.
    3. Review the settings on the Summary page and click Install.

    4. When the Execute Configuration Scripts popup appears, execute the following command and click OK:

       /home/oracle/app/oraInventory/orainstRoot.sh

    5. When the Oracle Database Client installation has completed, exit Oracle Universal Installer.

17. Enable X Window System access control:

    xhost -

18. Add the following value to the Oracle profile configuration file sqlnet.ora:

    • DIAG_ADR_ENABLED=OFF

    For more information, refer to https://docs.oracle.com/en/database/oracle/oracle-database/19/netrf/parameters-for-the-sqlnet.ora.html#GUID-8689BCB1-3CEC-4233-B229-2E6653DF2533.

Configuring Oracle Database Client

To configure Oracle Database Client 19c

1. Create or modify the data source configuration file, i.e. /etc/odbc.ini:

   [dsn]

   Driver = Oracle 19c ODBC driver

   ServerName = database_sid

   DSN = database_sid

   where:

   • dsn is the data source name for the database.
   • database_sid is the SID set for the Oracle database.

   The database login details are provided when establishing the connection to the database using the Configuration Utility. The encrypted database credentials are stored in the OneSpan Authentication Server configuration file (identikeyconfig.xml).

   Do not specify the database user name and password in odbc.ini, where all information is stored in clear text!

2. Create a symbolic link to the data source configuration file:

   ln -s /etc/odbc.ini /root/.odbc.ini

3. Create or modify the ODBC driver configuration file, i.e. /etc/odbcinst.ini:

   [Oracle 19c ODBC driver]

   Description= Oracle ODBC driver for Oracle 19c

   Driver= oracle_base_directory/product/client_version/client_1/libsqora.so.19.1

   Setup=

   FileUsage=

   CPTimeout=

   CPReuse=

   Threading = 0

   where client_version is the version of the Oracle client, and oracle_base_directory is the base installation directory for the Oracle client (typically /u01/app/oracle, or as configured in the ORACLE_BASE environment variable).

4. Add the necessary environment variables to /etc/profile.d/oracle.sh:

   export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

   export ORACLE_BASE=oracle_base_directory

   export ORACLE_HOME=oracle_base_directory/product/client_version/client_1

   export ORACLE_SID=database_sid

   export TNS_ADMIN=$ORACLE_HOME/network/admin

   export TWO_TASK=$ORACLE_SID

   export PATH="$ORACLE_HOME/bin:$PATH"

   LD_LIBRARY_PATH="$ORACLE_HOME/lib:$LD_LIBRARY_PATH"

   export LD_LIBRARY_PATH

   where:

   • database_sid is the SID set for the Oracle database.
   • client_version is the version of the Oracle client, and oracle_base_directory is the base installation directory for the Oracle client (typically /u01/app/oracle, or as configured in the ORACLE_BASE environment variable).

5. Add the following line to /etc/ld.so.conf.d/oracle.conf to update the run-time link path:

   oracle_base_directory/product/client_version/client_1/lib

   where client_version is the version of the Oracle client, and oracle_base_directory is the base installation directory for the Oracle client (typically /u01/app/oracle, or as configured in the ORACLE_BASE environment variable).

6. Run the following command to update the library cache:

   ldconfig

7. Create the tns_admin/tnsnames.ora file with the following contents:

   dsn=

     (DESCRIPTION =

      (ADDRESS_LIST =

         (ADDRESS = (PROTOCOL = TCP)(HOST =oracle_server_ip_address)(PORT = oracle_server_port))

       )

       (CONNECT_DATA =

       (SERVICE_NAME = database_sid)

       )

     )

   where:

   • tns_admin is the path of the Oracle Net Services configuration files.
   • dsn is the data source name for the database.
   • database_sid is the SID set for the Oracle database.
   • oracle_server_port is the Oracle server port (typically 1521). oracle_server_ip_address can be an IP address or a fully-qualified domain name of the Oracle server.

Additional considerations

• The Oracle database SID is used to uniquely identify a particular database on the system, similar to the ODBC data source name (DSN). For simplicity, consider using the same value for both.

• You can test the database connection.

  Open a terminal window and type the following command to test the database connection:

  isql -v DSN db_username db_password

  If the connection is successful, exit the sql prompt.