Installing Oracle Instant Client

This document contains instructions for setting up Oracle Instant Client and connecting to an Oracle Database from Mac OSX. It shows how to install Oracle Instant Client Basic and SDK, and SQL*Plus for and how to use SQL*Plus to connect to a remote Oracle Database.

N.B. If you want to use the python module cx_Oracle to connect using this setup you will need to make sure you install a 10.x version of Instant Client. A description for setting cx_Oracle up is here

1. Download Oracle's Instant Client Basic Package binary for mac (this link is an Intel build). The file should look something like oracle-instantclient-basic-macosx-10.2.0.4.0.zip.
2. Download Oracle's Instant Client SDK Package binary for mac (this link is an Intel build). The file should look something like oracle-instantclient-sdk-macosx-10.2.0.4.0.zip.
3. Download Oracle's Instant SQL*Plus Package binary for mac (this link is an Intel build). The file should look something like oracle-instantclient-sqlplus-macosx-10.2.0.4.0.zip.
4. Unzip these to some location, e.g., your Desktop

   cd ~/Desktop
   unzip oracle-instantclient-basic-macosx-10.2.0.4.0.zip
   unzip oracle-instantclient-sdk-macosx-10.2.0.4.0.zip
   unzip oracle-instantclient-sqlplus-macosx-10.2.0.4.0.zip

5. Enter the directory:

   cd instantclient10_2

6. Set up symbolic links to the libraries

   ln -s libclntsh.dylib.10.1 libclntsh.dylib
   ln -s libocci.dylib.10.1 libocci.dylib

7. Move the directory to somewhere more appropriate, e.g., /usr/local/oracle (the rest of these instructions assume you used this directory):

   sudo mkdir /usr/local/oracle
   sudo mv instantclient_10_2 /usr/local/oracle/

8. Set the following environment variables using commands like these. These commands should be added to your ~/.bash_profile file to use this software in the future:

   export ORACLE_HOME=/usr/local/oracle/instantclient_10_2
   export DYLD_LIBRARY_PATH=$ORACLE_HOME
   export SQLPATH=$ORACLE_HOME
   export PATH=$PATH:$ORACLE_HOME

9. Now load the new profile document:

   source ~/.bash_profile

You should now be able to run sqlplus

In order to make connecting to a database easier you can follow these instructions to store your connection settings in a tnsnames.ora file.

1. Set up a new tnsnames.ora file:

   cd /usr/local/oracle/instantclient_10_2/
   mkdir network
   touch network/tnsnames.ora
     - Open ''network/tnsnames.ora'' and enter the following information, replacing <connection_name> with your connection name, <server_name> with the name of the server that is hosting the database, <port_number> with the port the database connection is on, and <service_name> with the service name:<code>
   <connection_name> = 
    (DESCRIPTION = 
     (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = <server_name>)(PORT = <port_number>))
     )
    (CONNECT_DATA = 
     (SERVICE_NAME = <service_name>)
    )
   )

This document contains instructions for connecting to an Oracle Database from Ubuntu (these instructions probably work just as well under other variants of linux). It shows how to install Oracle Instant Client and SQL*Plus for Ubuntu and how to use SQL*Plus to connect to a remote Oracle Database.

N.B. If you want to use the python module cx_Oracle to connect using this setup you will need to make sure you install a 10.x version of Instant Client. A description for setting cx_Oracle up is here

1. Download the Oracle's Instant Client Basic Package rpm for linux (this should look something like oracle-instantclient-basic-10.2.0.3-1.i386.rpm).
2. Install the rpm using the following (where <oracleInstantClient.rpm> is replaced with the filename of your downloaded rpm):

   sudo rpm -i <oracleInstantClient.rpm>

   This should install Oracle Instant Client somewhere like /usr/lib/oracle/10.2.0.3/client. Find the install location and remember it for the next step.

3. Set the following environment variables using commands like these (replacing <install directory> with the install location you remembered from the previous step). These commands should be added to your .bash_profile file to use this software in the future:

   export ORACLE_HOME=<install directory>
   export LD_LIBRARY_PATH=$ORACLE_HOME/lib

4. Download the optional package Instant Client Package - SQL*Plus linux rpm. (this should look something like oracle-instantclient-sqlplus-10.2.0.3-1.i386.rpm)
5. Install the rpm using the following (where <oracleInstantSQLPlus.rpm> is replaced with the filename of your downloaded rpm):

   sudo rpm -i <oracleInstantSQLPlus.rpm>

   This should install Oracle SQL*Plus with the Instant Client installed earlier.

6. You should now be able to use SQL*Plus to connect to a remote database using the following command:

   sqlplus dbusername/dbpassword@//your.domain.com:portnum/dbname

   replacing dbusername with your database username, dbpassword, with your database password, your.domain.com with the computer name, portnum with the port number, and dbname with the database name.

The following are some troubleshooting problems that I encountered with the fixes that sorted them out. Contact me if they don't work for you.

If you receive the following error when running SQL*Plus:

sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

Check your environmental variables (ORACLE_HOME and LD_LIBRARY_PATH) and make they are set correctly.

If you receive the following error when running SQL*Plus:

sqlplus: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

you are missing the required libaio-dev package. Install it using the following command:

sudo apt-get install libaio-dev