Oracle Client on Ubuntu – Installation and Configuration

Having obtained a sick laptop and nursed it back to health ( i.e. installed Ubuntu 10.04), I’ve decided to do something a bit more useful with it.
I want to be able to connect to the Oracle 11g database on my server. This means, installing an Oracle Client.
I’ll be using the machine mainly for SQL*Plus ( although I may well be installing SQLDeveloper shortly). Therefore, rather than mess about downloading the client directly from the Oracle site, I’m going to use the XE client, which is available in the Oracle supplied apt package repository.
NOTE – if you simply must have the full Oracle Instant Client, then you can find details of that installation here.

Getting the XE Client from the Repository

To add this to the repostories used by Synaptic :
Go to System/Administration and start Synaptic Package Manager.

In Synaptic, select the Settings menu and then Repositories.
Click the Other Software tab and then click the Add button.
The APT line you want to add is :

deb http://oss.oracle.com/debian unstable main non-free

Now all you need to do is type oracle-xe client in the Synaptic search bar and it should appear.
Apply this package and once Synaptic has done it’s magic, you should have your Oracle Client installed.
That’s the easy bit. Slightly more complex is getting it to work.

Initial Configuration

You should now have the directory /usr/lib/oracle/xe/app/oracle/product/10.2.0/client
This will be the ORACLE_HOME ( but not yet).
First thing to do is to make a minor change to the oracle_env.sh script, which is what you need to run to set the appropriate environment variables.
So, fire up terminal :

cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/client/bin
sudo gedit oracle_env.sh

The line :

NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh` 

Needs to change to :

NLS_LANG= `. $ORACLE_HOME/bin/nls_lang.sh` 

Save the changes and quit gedit.
Now execute the shell script and you should have some Oracle Environment Variables set :

. ./oracle_env.sh
echo $ORACLE_HOME
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client

echo $NLS_LANG
ENGLISH_UNITED KINGDOM.AL32UTF8

echo $SQLPATH
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/sqlplus

echo $PATH
/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/bin:...

Yep, Oracle has slapped it’s bin directory on the front of your existing $PATH. Oh, the arrogance.
All of which means we’re now ready to test a connection to the database.
By default, the client doesn’t have all that nice fluffy tnsnames stuff. Oh no, this is hardcore. OK, so maybe that’s a slight exaggeration. Anyway…
As part of the install, you should now have Oracle Client 10g Express Edition under the Ubuntu Applications Menu.
The SQL Command Line menu item run sqlplus.sh to start a terminal session with SQL running. Unfortunately, this script has the same problem as the oracle_env.sh, so we’ll need to edit that too.
In terminal once more …

cd $ORACLE_HOME/scripts
sudo gedit sqlplus.sh

The line :

NLS_LANG=` $ORACLE_HOME/bin/nls_lang.sh`

Should be changed to :

NLS_LANG=`. $ORACLE_HOME/bin/nls_lang.sh`

Save and quit.

Now, back in the menu select SQL Command Line.
To connect to my target database, which is on another machine, I need to use the command :

conn uid/pwd@server:port/tns service name

uid/pwd is your database username and password
server is the name of the server that the database is running on
port is the port that the TNS Listener is running on on the server ( NOTE – you only have to specify this if it’s not set to 1521 – the default)
tns service name is the name of the TNS service that is running on the server for that database.

If you’re not sure what your TNS server is, you can find this out by connecting to the server and doing the following :

sudo su – oracle

$ lsnrctl status 

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-JUL-2010 12:12:19 

Copyright (c) 1991, 2009, Oracle.  All rights reserved. 

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) 
STATUS of the LISTENER 
------------------------ 
Alias                     LISTENER 
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production 
Start Date                28-JUL-2010 12:09:26 
Uptime                    0 days 0 hr. 2 min. 52 sec 
Trace Level               off 
Security                  ON: Local OS Authentication 
SNMP                      OFF 
Listener Log File         /u01/app/oracle/diag/tnslsnr/myserver/listener/alert/log.xml 
Listening Endpoints Summary... 
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myserver)(PORT=1521))) 
Services Summary... 
Service "orakarmic.myserver" has 1 instance(s). 
  Instance "orakarmic", status READY, has 1 handler(s) for this service... 
Service "orakarmicXDB.myserver" has 1 instance(s). 
  Instance "orakarmic", status READY, has 1 handler(s) for this service... 
The command completed successfully

From this, we can see that the one we want is orakarmic.myserver.
Back in the client, on the SQL Command Line the connect string will be :

conn myuser/mypwd@myserver:1521/orakarmic.mikeserver

Adding TNS lookup

If you want to make your life a bit easier and have the client lookup all that mucky tns stuff so you just have to remember the name of the instance you’re connecting to, this is surprisingly straight forward.
All we need to do is to create a directory structure under the $ORACLE_HOME and then add a couple of files.
First off, the directories – we want them to be owned by oracle so …

cd $ORACLE_HOME
sudo su oracle
mkdir network
cd network
mkdir admin

Now, the oracle doesn’t have access to the display. If you try using gedit as oracle ( gedit filename) you’ll get Gtk-WARNING **: cannot open display 0:0.

I know the next bit is a cop-out. Yes I should really go and find out why this is happening. But I’ve got a hot date with a database so…. whilst you can use vi to create the following files if you so desire, if you want to use gedit then, as your own user….

cd $ORACLE_HOME/network/admin
sudo gedit sqlnet.ora

The new sqlnet.ora will look like this :

SQLNET.AUTHENTICATION_SERVICES=(NTS)
NAMES.DIRECTORY_PATH=(LDAP,TNSNAMES,ONAMES,HOSTNAME)
DEFAULT_SDU_SIZE=8761

I’m not sure if you need everything in here, but this one works fine for me ( I copied it from another client that I already had working…on Windows, since you ask).
Save the file and exit gedit.
Back at the prompt ( still in $ORACLE_HOME/network/admin) :

sudo gedit tnsnames.ora

This is where it gets interesting. The format of a TNS entry in this file is :

INSTANCE_ALIAS = 
	( DESCRIPTION =
		( ADDRESS = (PROTOCOL=TCP) (HOST=myserver) (PORT=1521))
		(CONNECT_DATA = 
			(SERVER = DEDICATED)
			(SERVICE_NAME=tns_listener_service_name.myserver)
		)
	)

By convention, the INSTANCE_ALIAS is the name of the oracle instance, but there’s nothing to stop you using any string you feel like.
The DESCRIPTION comprises two elements, each of which contain sub-elements.
The first element of the DESCRIPTION is ADDRESS.
This comprises a protocol ( usually TCP), the name of the server on which the instance is running, and the port on the server on which the TNSListener is listening ( 1521 by default).

CONNECT_DATA comprises :
SERVER – I’m not sure what this is but I’m guessing it’s something to do with MTS or DEDICATED connections
SERVICE_NAME is the tns listener service that’s running for the instance you’re trying to access.
This is the output from the lsnrctl status command above.
If you have used gedit for this bit, you need to make sure that the files you’ve created are owned by oracle and have a group of dba :

sudo chown oracle tnsnames.ora
sudo chown oracle sqlnet.ora
sudo chgrp dba tnsnames.ora
sudo chgrp dba sqlnet.ora

ls -l 
total 8 
-rw-r--r-- 1 oracle dba 225 2010-07-28 12:20 sqlnet.ora 
-rw-r--r-- 1 oracle dba 461 2010-07-31 13:02 tnsnames.ora

Now, you should be able to connect using the tns alias rather than all that mucking about with server names and ports and stuff.
Go back into the SQL Command Line from the menu and at the SQL prompt :

SQL> conn uid/pwd@instance_alias
Connected.

SQL> 

That’s quite enough messing around with all this TNS stuff for one night.