Connect to remote Oracle database server from RedHat Linux 7 using Oracle InstaClient

Hi friends,

I have come across a situation, where I need to connect to remote Oracle database server from RedHat Enterprise Linux (RHEL) 7. I found a way of using the Oracle InstaClient instead of installing the full Oracle DB which internally consists of Client.

Pre-requesites

1. Linux mahcine (I tried with RHEL 7) with following softwares
• Java (JAVA_HOME should be available)
• Development Tools (RHEL 7 tools like gcc etc.)
2. Another machine where Oracle server is installed with the following config
• Database schema should be available
• Users should be available instead of system/sysdba users
3. Both machines shall be reachable to each other via ping.

Note: You should know the database server SID/service name.

Procedure

Download

1. Download the insta client from Oracle Official Page
2. In my case, I have downloaded the "Instant Client for Linux x86-64"
3. Accept the license agreement
4. Click "oracle-instantclient18.3-basic-18.3.0.0.0-1.x86_64.rpm" to download
5. Optionally, you can download the "oracle-instantclient18.3-sqlplus-18.3.0.0.0-1.x86_64.rpm" to have sqlplus command line utility for testing
6. Alternatively, you can download the zip files and download the same to install.
7. In my case, I am going with RPMs.

Install

• Install the downloaded RPMs
• #> yum install oracle-instantclient18.3-basic-18.3.0.0.0-1.x86_64.rpm
• #> yum install oracle-instantclient18.3-sqlplus-18.3.0.0.0-1.x86_64.rpm
• While installing, it asks for locations, accept the defaults by pressing the "Enter" button

Note: If any dependencies are there, please install those.

Environment Variables

• Create a oracle_env.sh file and setup your variables
• #> gedit /etc/profile.d/oracle_env.sh

export ORACLE_HOME=/usr/lib/oracle//client/
export TNS_ADMIN=/usr/lib/oracle//client/
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

• Save your file
• Source your file with following command
• #> source /etc/profile.d/oracle_env.sh

tnsnames.ora

• Create tnsnames.ora file in your $ORACLE_HOME with following syntax

localsid = (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
    (CONNECT_DATA =
      (SID = )
    )
  )

• Instead of SID, you can use SERVICE_NAME also.

Connect

• You can test the connection
• #> sqlplus @

You should be able to connect to your remote database.

Hope, it is useful for you.

Please send your comments and feedback to psrdotcom@gmail.com