Install and Test Oracle ODBC Drivers on CentOS

Immediately after installing the PostgreSQL ODBC drivers on a new CentOS system (see the earlier article:  Install PostgreSQL ODBC), I received a second request to install the Oracle ODBC drivers on the same CentOS system.  Everything has additional steps when doing them in Oracle and installing ODBC drivers is no exception, but once they are documented, they aren’t an issue any more.  This article will show you how to Install Oracle ODBC Driver on CentOS.


Install unixODBC with Yum

If you installed the PostgreSQL drivers with us in the last article, you probably already performed this task.  Installing unixODBC will give you the tools like isql and the framework for installing specific drivers for the various database types.

# yum install unixODBC

Download the Oracle ODBC Drivers from Oracle

Download the Oracle Instant Client Basic RPM

Download the Oracle Instant Client Basic RPM

I found the rpms to install located on the Oracle website (http://www.oracle.com) under the Downloads / Instant Client Download / Instant Client for Linux x86-64 section.

Download the Oracle Instant Client ODBC

Download the Oracle Instant Client ODBC

 The specific version when I downloaded was: 12.1.0.1.0-1.  It will likely be updated when you get there.  You will want to download both the basic instant client and the odbc instant client rpm packages:

oracle-instantclient12.1-basic-12.1.0.1.0-1.x86_64.rpm
oracle-instantclient12.1-odbc-12.1.0.1.0-1.x86_64.rpm


Install the Oracle Instant Client RPMs

Next, we need to upload the Oracle RPM packages to the CentOS box that we want to install the drivers on, then we use rpm to install them.  First the instant client basic rpm:

# rpm -Uvh ./oracle-instantclient12.1-basic-12.1.0.1.0-1.x86_64.rpm
Preparing...                ########################################### [100%]
   1:oracle-instantclient12.########################################### [100%]

then the instant client odbc rpm:

[root@michael jstaten]# rpm -Uvh ./oracle-instantclient12.1-odbc-12.1.0.1.0-1.x86_64.rpm
Preparing...                ########################################### [100%]
   1:oracle-instantclient12.########################################### [100%]

This installed the instant client files and odbc files in:  /usr/lib/oracle/12.1/client64/.

Configure the ODBC driver in the odbcinst.ini File

At this point, we edit the /etc/odbcinst.ini file to define the driver for unixODBC.  This will tell unixODBC where to find the libsqora.so driver file:

[OracleODBC-12.1]
Description = Oracle ODBC driver for Oracle
Driver = /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
FileUsage = 1
Driver Logging = 7

 

Configure the ODBC Datasources in the ODBC.ini File

Just as we discussed in installing the PostgreSQL ODBC datasources, we can configure the datasources in the

[myoracledb]
Description = myoracledb database
Driver = OracleODBC-12.1
Trace = yes
TraceFile = /tmp/odbc_oracle.log
Database = //oracle1.uptimemadeeasy.com:1521/myoracledb
UserID = myuserid
Password = mypassword
Port = 1521

Oracle ODBC Connection Error

When I installed this driver for the first time, I couldn’t connect initially.  Instead, I got this error below:

[01000][unixODBC][Driver Manager]Can't open lib '/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1' : file not found
[ISQL]ERROR: Could not SQLConnect

This error occurred because the LD_LIBRARY_PATH and other environment variables were not set.  You can set them using this syntax:

export ORACLE_HOME=/usr/lib/oracle/12.1/client64
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export TWO_TASK=//oracle1.uptimemadeeasy.com:1521/myoracledb

To make this more permanent, create a new script in the /etc/profile.d directory so that all users of the machine can use the script:

# cat >> /etc/profile.d/oracleenv.sh << EOF
export ORACLE_HOME=/usr/lib/oracle/12.1/client64
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export TWO_TASK=//oracle1.uptimemadeeasy.com:1521/myoracledb
cp oracleenv.sh /etc/profile.d
chmod 644 /etc/profile.d/oracleenv.sh
EOF
# chmod 644 /etc/profile.d/oracleenv.sh

Then, I can source this file to create my environment:

. /etc/profile.d/oracleenv.sh

This will setup those three important environment variables: ORACLE_HOME, LD_LIBRARY_PATH, TWO_TASK which should resolve the “Can’t open lib” error that we saw above.

Connect with Isql

We should now be able to connect to the database using the isql utility distirbuted with unixODBC:

# isql oraprd1
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

That’s it, we successfully connected to the database using our Oracle ODBC driver and datasource.

The following two tabs change content below.
Jeff has 20 years of professional IT experience, having done nearly everything in his roles of IT consultant, Systems Integrator, Systems Engineer, CNOC Engineer, Systems Administrator, Network Systems Administrator, and IT Director. If there is one thing he knows for sure, it is that there is always a simple answer to every IT problem and that downtime begins with complexity. Seasoned IT professional by day, Jeff hopes to help other IT professionals by blogging about his experiences at night on his blog: http://uptimemadeeasy.com. You can find Jeff on or LinkedIn at: LinkedIn or Twitter at: Twitter

Latest posts by Jeff Staten (see all)

8 comments for “Install and Test Oracle ODBC Drivers on CentOS

  1. September 8, 2014 at 6:42 am

    Hello Jeff,
    Great tips, I have followed this blog entry, unfortunately ended up getting Could Not SqlConnect.

    I am a bit confused,

    export TWO_TASK=//oraprd1.1on1.com:1521/oraprd1

    If I were to give my database host ip address, do I need to give the // at the beginning? What does that signify?

    Similarly, what does oraprd1 mean? is that the DB name or a Table name? I believe it should be the db name.

    Please advice.

  2. September 8, 2014 at 9:40 pm

    Arun,
    Sorry for the confusion. A bit of an editing issue there as I changed my hostname and db name through the article. Here’s what it means:

    export TWO_TASK=//myhostname.domain.com:portnumber/dbname

    Give that a try. It should work better for you.
    Jeff

  3. uthra
    October 2, 2014 at 9:45 am

    Hi Jeff,
    I have installed the unixODBC and oracle client as per your post . I have set the odbc.ini ,odbcinst.ini and bashrc as well .
    When i try to do isql ,I get the below error
    ora12514 tns:listener does not currently know of the services requested in connect descriptor.

    Please let me know how to solve the error .

  4. Abhinav Aggarwal
    October 7, 2014 at 2:40 am

    Hi.

    Thanks for a wonderful description. It worked out for me as well. But there is one problem i faced. I am working for my client and implementing the app in linux. They have multiple server and need to setup odbc for multiple server. i have accordingly configured the odbc.ini, but could not get how to export TWO_TASK variable. It seems that when i test with isql, all times i am getting connected to one database only.

    Regards.

    Abhinav

  5. October 13, 2014 at 8:41 am

    Uthra,
    I would follow the error message. Ora-12514 is all about a service name issue. I would suggest following suggestings from Burleson at:

  6. http://dba-oracle.com/t_ora_12514_tns_listener_does_not_currently_know_service_requested.htm
  7. .

    Good Luck,
    Jeff