Oracle Instant Client on Ubuntu...with added Aliens

“This is the voice of the Mysterons…have you got any Lemsip ?”
Yep, I’ve caught Deb’s cold and now sound like the alien menace from Captain Scarlet.
This provides a somewhat tenuous link to the subject at hand – namely installing Oracle Instant Client on Ubuntu.
I think I’d better explain. As you probably know, Ubuntu – being a Debian based Distro – uses the Debian packaging mechanism. Oracle, on the other hand, provides Instant Client for Linux in rpm ( RPM Package Manager) format. In order to bridge this divide, we’re going to need to use the alien utility. Look, I did say it was tenuous OK.

I’m doing this on a 32-bit Ubuntu installation ( 10.04, since you ask). If you’re running 64-bit, you’ll need to download the appropriate equivalent files.

Getting Instant Client

First – head over to the Oracle
Instant Client Download Page and download the following
- instant client basic – oracle-instantclient11.2-basic-11.2.0.2.0.i386.rpm
- instant client sdk – oracle-instantclient11.2-devel-11.2.0.2.0.i386.rpm
- instant client sqlplus – oracle-instantclient11.2-sqlplus-11.2.0.2.0.i386.rpm

libaio1…and other aliens

Now we need to install a couple of packages. The first is libaio1, which the Oracle client is going to need :

sudo apt-get install libaio1

The next is the alien package which will allow us to install the rpm packages :

sudo apt-get install alien

Now we can use alien to install the instant client rpms …

sudo alien -i oracle-instantclient11.2-basic-11.2.0.2.0.i386.rpm
sudo alien -i  oracle-instantclient11.2-devel-11.2.0.2.0.i386.rpm
sudo alien -i  oracle-instantclient11.2-sqlplus-11.2.0.2.0.i386.rpm

We should now have the oracle software installed under /usr/lib.
Time for a quick test …

export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client/lib
sqlplus uid/pwd@server:port/tns service name

In the above example, replace server with the server the database is running on, port with the TNS port ( usually 1521 or 1526), and tns service name with the name of the tns service setup for the database on the target server. If you find all of this a bit confusing, you can skip this test bit until we’ve got the local tns lookup sorted out, in which case it all gets a lot easier.

Setting up the Environment Variables

At this point, I think I should point out that there’s a really helpful how to on the Ubuntu site.

For the LD_LIBRARY_PATH :

cd /etc/ld.so.conf.d
sudo gedit oracle.conf

In the file, the first line should be the path we’ve just used to set LD_LIBRARY_PATH in the test. In this case :
/usr/lib/oracle/11.2/client/lib

Save the file. Now run :

sudo ldconfig

You should now have the LD_LIBRARY_PATH environment variable set each time you start a new terminal session.

Now for the other environment variables :

cd /etc/profile.d
sudo gedit oracle.sh

In this file, we’ll include the export commands for the ORACLE_HOME and TNS_ADMIN environemnt variables :

export ORACLE_HOME=/usr/lib/oracle/11.2/client 
export TNS_ADMIN=/usr/lib/oracle/11.2/client/network/admin

Yes, I know there’s no network/admin directory under the ORACLE_HOME…yet. We’ll come onto that in a mo.
First, we need to make sure that anything looking for the include directory can find it.

ls /usr/lib/oracle/11.2/client

If there isn’t a directory called include in the listing, you should be able to find it in the following command :

ls -d /usr/include/oracle/*/client

If so, then simply create a symbolic link in the ORACLE_HOME so we don’t trip anything up that’s looking for the directory in that location :

$ ln -s /usr/include/oracle/11.2/client /usr/lib/oracle/11.2/client/include

Adding TNSNAMES

Now, to make our lives considerably easier :

cd /usr/lib/oracle/11.2/client
sudo mkdir network
cd network
sudo mkdir admin
cd admin

In here, you need to add two files – sqlnet.ora and tnsnames.ora
This will enable you to connect to databases without having to remember ports and servers.
A typical sqlnet.ora looks something like this and works on any OS, so if you’ve got one lying around, you should be able to just copy it straight over…

SQLNET.AUTHENTICATION_SERVICES=(NTS)

NAMES.DIRECTORY_PATH=(LDAP,TNSNAMES,ONAMES,HOSTNAME)

DEFAULT_SDU_SIZE=8761

The tnsnames.ora needs to hold details of any oracle database you might want to access. For example, the tnsnames for an Oracle XE database would look something like this :

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mikes-laptop)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

Once again, if you have one on another machine / OS with Oracle client installed, you should just be able to copy it directly (assuming of course that you want to access the same database(s)).

Finally, make sure the files are writeable for everyone :

sudo chmod a+w *.ora

One final step – add the environment variables to your .bashrc

export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client/lib 
export ORACLE_HOME=/usr/lib/oracle/11.2/client 
export TNS_ADMIN=/usr/lib/oracle/11.2/client/network/admin 

Now, we should be ready to go.
Fire up a new terminal session and …

sqlplus hr/hr@XE

Note – in this example, I’m connecting as the hr user with a password of hr to the XE database.

If all is working as it should, you should now have a SQL prompt.
Now, back to the task of plotting the downfall of Spectrum…as soon as I can find those tissues. No wonder the Mysterons were always so grumpy !

About these ads