Troubleshooting Oracle 10g XE Installation on Ubuntu

You’ve followed the instructions, everythings working fine. You shutdown your machine and go for a celebratory pint / cup of tea / curry.
Then, when you next start your machine, Oracle doesn’t want to know. What’s going on ?
To be honest, I don’t know. What I do know is that there are some steps you can take to at least attempt to diagnose your problem.

Before I go any further, I’d like to dedicate this post to Wayne.
He found himself in this position. After much head scratching between the two of us, he managed to find and resolve his problem.

Whilst a hardened veteran in computing terms, Wayne was new to both Linux and Oracle. From the mails I’ve received regarding this post, there seem to be a number of people in a similar position to him. Therefore, this post is written with these interpid souls in mind and I’ve tried to take time out to explain why we’re doing stuff at each step.

Update

It’s now quite a while since I posted this and some people have contributed some really useful comments. These include solutions that they found to various problems, which are additional to the ones in this post. So, if you’re still having problems, check out the comments as well. MestreLion’s tip about sudo chmod -t /var/tmp/.oracle seems to be a particularly good example of this.

One other point worth mentioning is that Oracle 11gXE is now available. Many of the issues in this post are still relevant. However, if you’d like to know how to install the latest XE on Ubuntu or Mint, here are the instructions

So, what should be running ? There are two main components :

• The Oracle Database
• The TNS Listener

Is it plugged in ?

Let’s start with the obvious stuff. Can we actually run the shell script that starts everything – the one that’s behind the Start Database menu option ?
The file that actually runs is :
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/config/scripts/startdb.sh

At this point we need to go to the shell, so go to the Applications menu and select Accessories then Terminal

Probably the easiest way to do this is to go to the directory in question, and do a bit of checking:

cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/config/scripts

First off, we need to check the permissions on the file. We can do this using the ls command ( roughly equivalent to the DIR command in the Windows Command Prompt) :

ls -l startdb.sh

-rwxr-xr-x 1 oracle dba 958 2006-02-24 05:18 startdb.sh

The output should look like the listing above.

Just to pick out the parts of this listing that are of interest to us :
The permissions ( Read, Write eXecute) are the rwx combinations at the start of the line.
These are split into three groupings.
The owner of the file appears after the permissions and the number 1. In this case, the owner is oracle.
Next to the owner is the group associated with this file – i.e. dba

Looking at the permissions, the owner ( the first 3 letters) should have read, write and execute permissions; the group should have read and execute.
The final grouping of privileges are for all other users on the system i.e. not the user oracle or members of the dba group. In this case they should have read and execute permissions as well.
If you find that the permissions on this file are different then you will need to change them using the chmod command. Because you’re not connected as root at this point and are not the file owner, you’ll also need to use sudo to get the command to execute successfully :

sudo chmod 755 startdb.sh
[sudo] password for user :

Where user is your username
If you now list the file again, it should have the correct permissions :

ls -l startdb.sh

-rwxr-xr-x 1 oracle dba 958 2006-02-24 05:18 startdb.sh

Once you’ve checked that the correct permissions are in place on the file, you can have a go at running it :

. startdb.sh

If it returns an error, then we have a clue and can start to track it down. If it doesn’t return anything, then it’s probably worked. You can check by going to the Database Home Page from the menu :

Applications / Oracle Database 10g Express Edition / Go To Database Home Page

If it comes up, then we know that we’ve been able to start successfully and the problem is probably connected with environment variable settings being missing when we try and run from the menu.

If you have started successfully, then stop everything now and keep reading. If not, well, just keep reading.

Sorting out the database

Start Oracle again from the menu and see what is actually happening. So :

Applications / Oracle Database 10g Express Edition / Start Database

Now, let’s check the database. First off, we need to check out if it has in fact been started :

Go to Applications / Oracle Database 10g Express Edition / Run SQL Command Line

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 5 01:06:36 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.
SQL>


Connected to an idle instance means that the database has not been started.

Incidentally, we’re connecting here as the user sys in sysdba mode. This is the only way that you can connect to an Oracle database that isn’t up and running. If you connect with a different user or don’t specify sysdba mode, then you’ll get the error :

ORA-01034: ORACLE not available

If the database is not up, or you can’t get the Run SQL Command Line option to work, you need to go back into the shell.

Applications / Accessories / Terminal

Now we’re here, we need to check some stuff about your user to make sure that the problems we’re having aren’t down to some problem with permissions.
At the prompt :

$ echo $ORACLE_HOME 
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server 
$ echo $ORACLE_SID 
XE 
$ echo $NLS_LANG 
ENGLISH_UNITED KINGDOM.AL32UTF8 
$ 

The $ORACLE_HOME and $ORACLE_SID should be set to the same values as above. The $NLS_LANG should be whatever the character set is that you chose during installation.

If the $ORACLE environment variables are not set (i.e. the command just returns a blank), then your problem is that these are not being set when your user starts a session.
To fix this, you need to edit your .bashrc file which is in your home directory.
At this point, you’ll be pleased to know that you’ll be spared the joys of hacking around in vi.
Ubuntu has it’s own GUI text editor called gedit, which can be invoked from the command line.
OK, so technically gedit is part of the Gnome desktop rather than Ubuntu itself, but we really don’t need to get into that now.

Back up the file first and then edit it

cp .bashrc old_bashrc
sudo gedit .bashrc

You need to add lines that look something like this

ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server 
export ORACLE_HOME 
ORACLE_SID=XE 
export ORACLE_SID 
NLS_LANG=`. $ORACLE_HOME/bin/nls_lang.sh` 
export NLS_LANG 
PATH=$ORACLE_HOME/bin:$PATH 
export PATH 

Note – you need to set these variables above any other areas of the .bashrc in which they are referenced.
Another Note – keep the terminal session in which you’ve edited the .bashrc file open for the moment.

Once you’ve saved the changes, you can test the effects by starting a new terminal session and repeating the test for variable settings as described above – i.e.

$ echo $ORACLE_HOME 
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server 
$ echo $ORACLE_SID 
XE 
$ echo $NLS_LANG 
ENGLISH_UNITED KINGDOM.AL32UTF8 
$ 

This time, all of the variable values you’ve set in the .bashrc should be returned.
Incidentally, if you find that you’ve made a mistake with the changes to .bashrc, you can fix it in the session you’ve been using to edit the original .bashrc as follows :

cp oldbashrc .bashrc

This will overwrite your changes with the copy of .bashrc that you made prior to editing it.
The copy of the file will still be there, so you will be OK to try editing again.

Once you’ve successfully applied these changes, the next thing to do is to make sure that we’re a member of the dba group. At the prompt type :

groups

If dba isn’t included as one of the group names in the output of this command, then your user is not a member of this group. To rectify this, you need to add your user to the dba group. You can either do this through the Gui System/Administration/ Users and Groups ( as described in my original post) or you can just enter the following command :

sudo adduser username dba

where username is the name of your user on your machine.

Now we’re ready to try starting the database again ( Applications / Oracle Database 10g Express Edition / Start Database) and then re-test using the
Run SQL Command Line option ( give it a few seconds before trying to connect as the database might take a bit of time to start up).

TNS Listener

If the database is up and running ( or you’ve now managed to get it running), then congratulations, you’ve arrived at the fiddly bit.

The TNS Listener is the program that manages traffic between a client and an Oracle database. It’s analagous to a web server such as Apache ( or IIS). Instead of listening for HTTP requests like a web server, it will listen for TNS requests, which are what’s used for talking to Oracle.
In our case, the “client” and “server” are all on the same machine. Oracle provides a command line utility – lsnrctl – for managing the TNS Listener.
Before delving into the innards of the Listener, check to see if it is now up and running.
Applications / Oracle Database 10g Express Edition / Database Home Page
If the Home Page comes up, then everything is working and you can stop reading this and give thanks to the gods of computing. It’s working.
Still here ? OK, time to open up terminal again ( Applications / Accessories / Terminal)
At the prompt type :

lsnrctl status

If the Listener is down then you’ll get something like this :

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 05-APR-2010 01:36:56
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=yourmachine)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused

where yourmachine is the name of your machine.

The relevant thing here is ( apart from the fact we have a Linux Error) what HOST= is set to.
Make a note of this value because we may well come back to it in a moment.

The next step is to try starting the listener and see what we get :

lsnrctl start

When Wayne ran this, he got :

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on
23-MAR-2010 08:54:44
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/tnslsnr:
please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
NL-00280: error creating log stream
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log
NL-00278: cannot open log file
SNL-00016: snlfohd: error opening file
Linux Error: 13: Permission denied
Listener failed to start. See the error message(s) above..

What the Listener is telling us is that it can’t open the file /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log
We need to check the permissions on this file :

cd $ORACLE_HOME/network/log 
ls -l listener.log
-rw-rw-r-- 1 oracle dba 193139 2010-04-05 01:57 listener.log 

When Wayne ran this on his system, he got

-rw-r--r-- 1 oracle dba 193139 2010-04-05 01:57 listener.log 

For some reason, the dba group did not have write permissions to the file, which was causing the Listener to fail to start.
This was easily solved as follows :

sudo chmod g+w listener.log

Roughly translated, we’ve given write access to the group associated with the listener.log file ( dba).
This is how Wayne managed to solve his problem.
If you had the same issue, once you’ve applied Wayne’s fix (above) you could try to re-start the listener at this point by running

lsnrctl start

If it works, you’ll get something like :

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.ora
Log messages written to /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yourmachine)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=yourmachine)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 05-APR-2010 02:04:59
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.ora
Listener Log File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yourmachine)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

If this still does not bring you joy, there is still one thing left to check.
Remember that HOST= line I mentioned earlier ?
HOST is the machine the the listener is looking for the database on. In this case, all the action is happening on the same machine, but the listener still needs to be told that.

Check that the HOST is set correctly :

uname -n

This should return the same value as specified in the HOST=
We also need to make sure that there’s an entry for this machine in /etc/hosts

cat /etc/hosts

You’ll get a few lines from here, but the two that should definitely be there are :

127.0.0.1 localhost
127.0.1.1 yourmachine

If either of these checks produce different results on your machine, then we need to edit the listener.ora file, which is essentially the configuration file for the TNS Listener :

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

We need to make sure the LISTENER entry in this file looks like this :

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
# (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)

We then need to make sure that the tnsnames.ora entry for the XE database also has the correct host name specified. The tnsnames.ora file is in the same directory as listener.ora and is essentially the “client” configuration file:

sudo gedit tnsnames.ora


XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

At this point, it’s worth stopping everything, doing a re-boot, and then trying to start everything up again. Fingers crossed everything will now be working.

I’ve done my best to highlight possible issues with your XE setup and how to resolve them. Obviously, I am painfully aware that I don’t know of all the possible issues you may get with this, but hopefully, you’ll at least be able to use some of these steps to get a better idea of why it isn’t working.