Old Guy New Trick

An old guys journey to learn how to code.

Oracle Instant Client - Mac OSX


Author: John on August 19, 2014

I recently had the joy of installing Oracle 11g Express Edition on my home server.  That server runs CentOS 6.x as that is the flavor of Linux we use at my day job.  The server install is another story I will cover in the future, but today I would like to share how I got my laptop and development environment setup to connect to the Oracle Database.

Why in the world would I want to use Oracle you ask?  Well I don't really want to.  However, at the day job, that is what we use. And I wanted a more flexible development environment.  Due to limited resources, our 'dev' database is also our disaster recovery database.  'Nuff said?

In order to get my ruby environment setup to work with this monster database, I need two gems:

gem 'activerecord-oracle_enhanced-adapter', '~> 1.5.0'
gem 'ruby-oci8', '~> 2.1.0'

[Oracle Enhanced]  

But I'm getting ahead of myself.  Lets take two steps back.

 

Download some files

On my server I have installed Oracle 11g Express, and my laptop is fairly current and run OS X Mavericks - a 64bit Operating System.  So let's get the appropriate client.

Download the required files from the Oracle Download site:
[Oracle Client]

Note:  The following was learned by reviewing the blog over at: [codiez](http://blog.codiez.co.za/2013/09/setup-oracle-instant-client-ruby-oci8-gem-mac/)

For me, I downloaded these files:

instantclient-basic-macos.x64-11.2.0.4.0.zip
instantclient-sqlplus-macos.x64-11.2.0.4.0.zip
instantclient-sdk-macos.x64-11.2.0.4.0.zip

Change to the directory where you downloaded the files
(e.g. cd Downloads - assuming you are in your home directory)

Unzip the three files:

unzip -qq instantclient-basic-macos.x64-11.2.0.4.0.zip
unzip -qq instantclient-sqlplus-macos.x64-11.2.0.4.0.zip
unzip -qq instantclient-sdk-macos.x64-11.2.0.4.0.zip

Change directories to the new one created by the unzipping:

cd instantclient_11_2

Perform the following steps:

mkdir -p /usr/local/oracle/product/instantclient_64/11.2.0.4.0/bin
mkdir -p /usr/local/oracle/product/instantclient_64/11.2.0.4.0/lib
mkdir -p /usr/local/oracle/product/instantclient_64/11.2.0.4.0/jdbc/lib
mkdir -p /usr/local/oracle/product/instantclient_64/11.2.0.4.0/rdbms/jlib
mkdir -p /usr/local/oracle/product/instantclient_64/11.2.0.4.0/sqlplus/admin
mv ojdbc* /usr/local/oracle/product/instantclient_64/11.2.0.4.0/jdbc/lib/
mv x*.jar /usr/local/oracle/product/instantclient_64/11.2.0.4.0/rdbms/jlib/
mv glogin.sql /usr/local/oracle/product/instantclient_64/11.2.0.4.0/sqlplus/admin/login.sql
mv *dylib* /usr/local/oracle/product/instantclient_64/11.2.0.4.0/lib/
mv sdk /usr/local/oracle/product/instantclient_64/11.2.0.4.0/lib/sdk
mv *README /usr/local/oracle/product/instantclient_64/11.2.0.4.0/
mv * /usr/local/oracle/product/instantclient_64/11.2.0.4.0/bin/

Next, we need to create and modify the tnsnames.org file:

mkdir -p /usr/local/oracle/admin/network
touch /usr/local/oracle/admin/network/tnsnames.ora
vim /usr/local/oracle/admin/network/tnsnames.ora
rubyrailssvr =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = XE))
  )

rubyrailssvr.homenet.com =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))     (CONNECT_DATA = (SERVICE_NAME = XE))   )

Now we need to get our environment setup. Create a .oracle_client file in your home directory:

touch ~/.oracle_client
vim ~/.oracle_client
export ORACLE_BASE=/usr/local/oracle
export ORACLE_HOME=$ORACLE_BASE/product/instantclient_64/11.2.0.4.0
export PATH=$ORACLE_HOME/bin:$PATH
export DYLD_LIBRARY_PATH=$ORACLE_HOME/lib:$DYLD_LIBRARY_PATH
export TNS_ADMIN=$ORACLE_BASE/admin/network
export SQLPATH=$ORACLE_HOME/sqlplus/admin

I use the bash shell at work, but have been working with zsh at home lately.  So below I show how to add the above oracle environment variables to both bash and zsh:

echo "source ~/.oracle_client" >> ~/.bash_profile
source ~/.bash_profile

echo "source ~/.oracle_client" >> ~/.zprofile
source ~/.zprofile

Optional step - if needed, you may need to update your /etc/hosts file so that you can resolve the IP address of your server to a fully qualified domain name.  See the following example:

192.168.1.14    rubyrailssvr.homenet.com

We are almost there - go take a break if you need to.  Before we update our Gemfile to add the two gems I listed earlier, let's first test and make sure we can connect.  We will use sqlplus to perform that test, which is a tool that was installed as part of the instant client.

➜ sqlplus system@192.168.1.14
SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 19 09:49:06 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>

You should be able to connect now, though I have made a huge assumption.  I have assumed that your Oracle DB is currently configured to accept remote connections.

Well that wraps up todays episode.  Get out there and ...

Learn Something New Every Day

Last Edited by: John on December 29, 2015