“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-126.96.36.199.0.i386.rpm
- instant client sdk – oracle-instantclient11.2-devel-188.8.131.52.0.i386.rpm
- instant client sqlplus – oracle-instantclient11.2-sqlplus-184.108.40.206.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-220.127.116.11.0.i386.rpm sudo alien -i oracle-instantclient11.2-devel-18.104.22.168.0.i386.rpm sudo alien -i oracle-instantclient11.2-sqlplus-22.214.171.124.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.
UPDATE : A Note if you’re installing the 64-bit client
There are a couple of differences between the respective installations of the client.
First of all, the Oracle Client home directory for the 64-bit version is :
Secondly, the 64-bit version insists on creating a symbolic link for sqlplus as sqlplus64.
This means that you’d have to invoke sqlplus using the command sqlplus64.
To revert to using the usual command, simply edit the symbolic link as follows :
cd /usr/bin sudo mv sqlplus64 sqlplus
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 :
Save the file. Now run :
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.
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
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 …
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 !