Setup Oracle sql plus client on AWS Ubuntu/Linux

AWS is all about automating and command line. Therefore it's necessary access databases through terminal in many use-cases specially when dealing with AWS instances. For example, there can be a scenario where several complicated sql scripts need to be run on oracle database periodically during AWS test cycles. SQL Developer is not the ideal tool for this due to occasional hangs and unpredictability. But oracle has a great command line client ideal for these type of scenarios.

Dependencies :

AWS Instance with Ubuntu/Linux
Even this has setup on AWS instance, AWS instances are not required. These steps will be sufficient to install oracle sql instant client on any ubuntu/linux instance in foreseeable future.

Oracle Instant Client package
Oracle instant client package (Basic or lite). These packages are available on instant client downloads for Linux x86-64[1]. I'll be using  "oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm" for this setup. But there won't be  any different for lite version if you only need to run basic sql queries through the client.

Oracle SQL Plus package
Download SQL Plus package from oracle client download site[1]. This package contains additional libraries and executable for running SQL Plus with Instant Client. Make sure it's compatible to client package(same version eg: 11.2.0.3.0-1) "oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm" It's hard to mess this up as oracle clearly tabulated all the downloads[1] for each oracle version in their site.

Important : Make sure client packages are compatible with the version of the database instance. Otherwise client won't execute as expected. In this setup i'll be working with oracle 11g (11.2.0.3.0) instance. Following steps should work for any oracle database version in foreseeable future.


Steps to Install the Client : 


1. Install alien package

sudo apt-get install alien

2. Install downloaded instant client rpm package

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

3. Install downloaded sql plus package

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

4. Install libaio1,libaio-dev packages if not already installed. Usually in vanilla ubuntu 14 libaio1, libaio-dev packages are not pre-installed. These packages required to start oracle instant client.

sudo apt-get install libaio1 libaio-dev

5. Export oracle client lib directory path to LD_LIBRARY_PATH variable. Make sure to check the path in your system based on your versions before export.

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

6. Test the client.
Since i have set this up on a 64bit vm (all the aws instances are now 64bit) i'll be using "sqlplus64" if by any chance you are on 32bit vm replace  "sqlplus64" with "sqlplus".
Make sure to replace "username", "password", "rds_hostname". change the port if you have change the default port on oracle database instance.
Important : Change the "ORCL" to "XE" in connection url if you are using a oracle express edition as it's default SID is "XE".

sqlplus64 username/password@//rds_hostname:1521/ORCL

Following sql prompt will appear if your credentials are correct.

ubuntu@ip-272-300-203-1317:~/oracle_client$ sqlplus64 username/password@//rds_hostname:1521/ORCL

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 14 07:43:55 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL>

Now it's a matter of querying the database as your user permission permits.

Good Luck..!!


[1] http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html