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

Main menu

Post navigation

Oracle XE – Speeding up Startup and getting Nofications on the Desktop in Ubuntu

Oh the tension. 2-0 up at half-time. Visions of last-day drama and an eleventh-hour escape floating tantilisingly before my eyes. Then, grim reality. A second-half collapse and the prize is snatched away once more. So, this is what it must be like to be an Arsenal fan.
I know that they say misery loves company, but it’s not really any consolation. Yes, my beloved West Ham have been relegated from the Premiership.

In an effort to rouse myself from the resultant depression, I decided to have a look at addressing one of those minor annoyances that I’m always intending to get around to but somehow never quite do. In this case, it’s how to get confirmation that my Oracle XE database has started before trying to connect to it ( and that it’s shut down before I turn off my computer).

It’s probably useful at this point to have a quick overview of the setup.
I’m running Oracle 10g XE on an Ubuntu 10.04 desktop.
I’m using the menu items in Applications/Oracle Database 10g Express Edition to start and stop the database. The database was installed and configured using these instructions.

Of course, being a traditionalist, I can tell when the database is up by simply tailing the database’s alert.log file and waiting to see that it’s open…

tail -f /usr/lib/oracle/xe/app/oracle/admin/XE/bdump/alert_XE.log

The problem with this is that the startup script doesn’t just start the database. It starts the TNS listener as well. It’s not until both are up an running that you can connect via TNS.
Even then, you still have to wait whilst the listener appears to have a moment of existential crisis – “why am I here ? What is my purpose?” before it remembers that it’s supposed to be routing connections to your database.
In the interim, if you try to connect to your database, you will be rebuffed with the ever helpful

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Why does this happen ? You try again a few seconds later and everything works fine. Fortunately, your listener is not in need of therapy…

What No Listener ?

So exactly how does the listener know when your database is available ?
Essentially, when the database starts up, it kicks off a number of processes. One of these – PMON – has a look around for a TNS Listener and if it finds one running, registers the database with it.
If no Listener is running, then PMON will poll every so often until it finds one.
There’s a full explanation of the ins and outs of this process in the Oracle Database 10g Release 2 documentation.
Is this what’s happening with XE ? Well, let’s do a test.

We’re going to check the listener’s log file to see when it can start handling connections after a startup.
So, in Terminal…

cd $ORACLE_HOME/network/log
tail -f listener.log

On the desktop, now run the Start Database Menu Item.

In my case, the output from the listener.log looks something like this :

So, the listener does not register the database until 47 seconds after startup. Hmmm, maybe we should have a look at the startup script that Oracle supply with XE.

The scripts behind the Start Database and Stop Database menu items can be found in /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/config/scripts. The startup script is called startdb.sh. Incidentally, the shutdown script is called stopdb.sh.

Yep, it starts the database before it starts the listener. So reversing the order – i.e. starting the listener first – should resolve our problem.
There’s more though. We don’t really want to have to start a terminal session and tail the alert.log if we can just get the script to tell us, via the Gnome desktop, that everything is up and running.
What we’d like to do is to get these scripts to display a desktop notification once they have completed.
Now, I recently came across a handy little utility to do this.

The libnotify package

Once more into the Terminal, dear friends…

sudo apt-get install libnotify-bin

Once we’ve got this package we can do stuff like this in a shell script to get a pop-up message on the desktop

The switches that I’m using for notify send are -t ( timeout in milliseconds) and -i (display this icon). The next string in the command – “Mad Science Weather” – is the heading and the last string is the message you want to display.
This being a shell script, we can of course substitute the literal strings for the header and message with variables.

When you run this, you should get a notification, complete with the icon, showing up in the top right-hand corner of your desktop.

All the best mad scientists use Linux...and here's why

Now all we need to do is to amend the database scripts to pop-up the appropriate notification.
Of course, it’s always a good idea to back up your scripts before making any changes, just in case…

A point of interest here – the stop script only does a shutdown of the database, it doesn’t stop the listener. Of course, this being XE, the listener is probably going to be used solely for the single XE instance. Anyway, if you do want to stop the listener when the stop script runs, you simply need to add the following line (preferably after the database shutdown command) :

$ORACLE_HOME/bin/lsnrctl stop

Now we just have to wait for the notification to know that everything is up and running and we can connect with impunity ( or at least, free from the tyranny of ORA-12514)…

Lightning has struck, time to experiment !

Thankfully, the football season is nearly over. All that’s left for me now is to help Simon through the ordeal of Luton’s Play-Off final against the mighty AFC Wimbledon on Saturday.

2 thoughts on “Oracle XE – Speeding up Startup and getting Nofications on the Desktop in Ubuntu”

Thanks! Easily added this on Linux Mint to the slightly different 11g scripts. Works like a charm. My scripts were in a different location, however, namely:
/u01/app/oracle/product/11.2.0/xe/config/scripts/