Installing SQL*plus instant client on Linux


We back to the wonderful world of Oracle for a few hours. I have written a few articles on how to install APEX and Oracle XE on the same server, well now its time to run the APEX web server and Oracle server separate. I am again going to leverage the  power of Amazon AWS infrastructure as per usual, more about setting up the Oracle server over at my AWS tips n tricks blog.

I need to create a web server to access the APEX oracle back end server for this you need to get SQL*PLUS working to help you get things moving.

Well you can read the helpful documents from Oracle if you want, to be fare they get you most of the way there and in fact would get you working if the install did not do something a little strange, more about this at the end.

SQPLUS first this can be down loaded from http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

You will need :

oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm

 oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm

These were the latest at the time of writing, basically you need the basic and sqlplus rpm files for your environment.

Download them and transfer them  to the server, I have yet to workout how to download these with wget.

It is assumed that you have the files in the /opt/software directory

cd /opt/software

rpm -i oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm

rpm -i oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm

You then need to create a script or add the following lines to the .bashrc file.

I created a small script file to set all of the path statements.

mkdir /opt/oracle

cd /opt/oracle

vi set_env

Add these lines

LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib:${LD_LIBRARY_PATH}

export LD_LIBRARY_PATH

SQLPATH=/usr/lib/oracle/11.2/client64/lib:${SQLPATH}

export SQLPATH

ORACLE_HOME=/usr/lib/oracle/11.2/client64

export ORACLE_HOME

TNS_ADMIN=/u01/app/oracle/product/11.2.0/general/network/admin

export TNS_ADMIN

LIB_PATH=$ORACLE_HOME/lib
PATH=$ORACLE_HOME/bin:$PATH

 

To explain what these are variables are

LD_LIBRARY_PATH :

this is where the .so files are, that allows the connection to the database, to find your path

cd /

find . -name glogin.sql

SQLPATH :

this is the same location as the LD_LIBRARY_PATH set, not sure what it is used for, I just know it is needed

ORACLE_HOME :

This is where the directory prior to the oracle binaries and the libraries needed, this is standard practice in the Oracle world ???? beats me some of this does.

LIB_PATH:

again another Oracle thing, not going to argue as long as it works
PATH:

this is the important one, this indicates where the sqlplus binary can be found note the /bin after ORACLE_HOME

Every time that you want to use sqlplus you will need to run this script

TNS_ADMIN:

This will be explained below 🙂

To make it easier to use the sqlplus command line it is best to create a tnsnames.ora file, this can be stored anywhere, but based on the standards when installing Oracle locally it is best to store it in /u01/app/oracle/product/{VERSION}/{DATABASENAME}/network/admin/

e.g.

/u01/app/oracle/product/11.2.0/general/network/admin/

So

mkdir -p /u01/app/oracle/product/11.2.0/general/network/admin/

vi tnsnames.ora

Insert the following

DATABASE NAME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ENDPOINT FROM THE RDS CONSOLE)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DATABASE NAME WHEN CREATED)
)
)

e.g.

This is based on the database name of general and the Oracle instance being called companyname-general-orc

GENERAL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = companyname-general-orc.ckh5bdmhapmu.eu-west-1.rds.amazonaws.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = GENERAL)
)
)

SQLPLUS now need to know where the tnsnames file is

vi /opt/oracle/set_env

Add these lines

TNS_ADMIN=/u01/app/oracle/product/11.2.0/general/network/admin

export TNS_ADMIN

Now to try to connect to the database, make sure that you run the set_env script

cd /opt/oracle

. set_env

Then run the sqplus command, in this example the database is called general

sqlplus username/password@general

Easy I here you say, well if you get this dreaded error

sqlplus username@general
 
SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 17 15:25:04 2012
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
Enter password: 
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
 
 
Enter user-name: 
Its easy to fix he says, well the issue that I had was the symbolic link in /usr/bin, I deleted this and set the path to sqlplus in the set_env file to take care of it, so make sure you delete the offending entry
rm /usr/bin/sqlplus
rm /usr/bin/sqlplus64
Another error encountered when I was also trying to get this to work on another Linux version was: (It takes several attempts to get stuff working, this is why these documents are written 🙂 )

/usr/bin/sqlplus: /lib/ld-linux.so.2: bad ELF interpreter: No such file or directory

To fix this

yum install glibc.i686

One trick to make sure you can connect to the database is this,

/usr/lib/oracle/11.2/client64/bin/sqlplus username@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL = TCP)(HOST = database server name or ip address)(PORT = 1521)))(CONNECT_DATA=(SERVICE_NAME=database name)))’

So in our example

/usr/lib/oracle/11.2/client64/bin/sqlplus username@’GENERAL =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = companyname-general-orc.ckh5bdmhapmu.eu-west-1.rds.amazonaws.com)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = GENERAL)))’

I can not take credit for this it was a helpful DBA at Inoapps

On more just test connectivity, especially if have routers, firewalls, vlans or any other anti connection equipment it a simple telnet to the database

telnet Oracle server IP address 1521

If you are successful in the connection then you will get the following prompt

Escape character is ‘^]’.

If you don’t have telnet install on the server

yum install telnet

Advertisements

2 Comments on “Installing SQL*plus instant client on Linux”

  1. […] For the instruction of how to do this bit of the set-up head over to my other technical blog survivalguides and read the aptly named Installing SQL*plus instant client on Linux […]

  2. Maurice says:

    Excelent post, thank you very much!
    In my case the tnsnames.ora file required the line:
    (SERVER = DEDICATED)
    right after
    (CONNECT_DATA =
    in order to run
    Regards,
    Maurice


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s