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
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.
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.
Latest posts by Jeff Staten (see all)
- Configure Your HP Procurve Switch with SNTP - May 5, 2015
- Configuring HP Procurve 2920 Switches - May 1, 2015
- Troubleshooting Sendmail - November 28, 2014
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.
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
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 .
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
Uthra,
I would follow the error message. Ora-12514 is all about a service name issue. I would suggest following suggestings from Burleson at:
.
Good Luck,
Jeff
Abhinav,
Oracle’s requirement of having the TWO_TASK variable set in order to connect with ODBC is a real issue. It prevents one from being able to connect to two databases without some real trickeration. You can change the TWO_TASK variable back and forth using export as needed in your script in order to get to the one you need when you need. Or, you can utilize a different user or shell instance for each database. Whichever option you use, Oracle has made it difficult.
Jeff
Excelente trabajo hermano. Me funcionó perfecto. Gracias.
Hello Master,
i have configured all parameters as per your suggestion but i confusion over here is, i have IP address and port rather than DNS system.
[ORACLE]
Description = myoracledb database
Driver = OracleODBC-12.1
Trace = yes
TraceFile = /tmp/odbc_oracle.log
Database = orcl
UserID =system
Password =manager
Port = 1521
Servername=10.200.208.115
Protocol = 9.3
but i am not able to connect with me remote database.