Oracle – for when it was like that when you got there

Main menu

Post navigation

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 :

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 passwordserver is the name of the server that the database is running onport 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 :

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….

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 :

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 connectionsSERVICE_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 :