How do I setup Oracle ODBC drivers on RHEL 6/Linux

Question

I need to setup Oracle ODBC drivers on RHEL 6 to connect to an Oracle data source residing on a remote Windows machine.

I have taken the following steps:
[1] Installed oracle-instanceclient-basic
[2] Set environment variables: ORACLE_HOME and LD path
[3] Created /etc/oracle/TNSnames.ora and configured but it did not set it correctly

Initially, I got the error, "Connect failed because target or object does not exist"

Then, I set: ORACLE_SID = DB_NAME in the TNSnames.ora file.

But, that did not fix the issue, I got a new error message now: "TNS: net service name is incorrectly specified"

Answer 1

yum install unixODBC
rpm -ivh oracle-instantclient-basic-10.2.0.3-1.i386.rpm #downloaded on http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linuxsoft.html
unzip instantclient-odbc-linux32-10.2.0.3-20061115.zip #downloaded on http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linuxsoft.html
cp ./instantclient_10_2/libsqora.so.10.1 /usr/lib/oracle/10.2.0.3/client/lib/
export ORACLE_HOME=/usr/lib/oracle/10.2.0.3/client
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

Also you need to set $TWO_TASK (default location where Oracle is looking to pickup the server) to point to where the Oracle server is running on Windows - - don't forget to add the listener at the end after the port number:

export TWO_TASK=//213.123.23.19:1521/listener

To chech for the name of listener, type the following commands on the windows prompt that is running the Oracle server:

lsnrctl
status

It will enlist listeners and their state (READY or UNKNOWN). Connect to the listener that is in the ready state: Instance "zelistener", status READY

mkdir /etc/oracle

vi /etc/oracle/tnsnames.ora

MY_SID =
 ( DESCRIPTION =
  (ADDRESS_LIST =
  (ADDRESS =
  (PROTOCOL = TCP)
  (Host = 127.0.0.1)
  (Port = 1521)
  )
 )
 (CONNECT_DATA = (SID = MY_SID)
 )
)

export TNS_ADMIN=/etc/oracle

vi /etc/odbcinst.ini

[OracleODBC-10g]
Description = Oracle ODBC driver for Oracle 10g
Driver = /usr/lib/oracle/10.2.0.3/client/lib/libsqora.so.10.1
FileUsage = 1
Driver Logging = 7

vi /etc/odbc.ini

[simple]
Driver = OracleODBC-10g
DSN = OracleODBC-10g
ServerName = MY_SID
UserID = USER
Password = PASSWORD

isql -v simple

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

Answer 2

I think that we should assign the version of unixODBC. if to connect the oracle12c, we should use the unixODBC-2.3.1