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 :

TNSLSNR for Linux: Version 10.2.0.1.0 - Production on 17-MAY-2011 09:22:53

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

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
Trace information written to /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/trace/listener.trc
Trace level is currently 0

Started with pid=1906
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mikes-laptop)(PORT=1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
17-MAY-2011 09:22:53 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=mikes-laptop)(USER=mikes))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=8081))(Presentation=HTTP)(Session=RAW))
17-MAY-2011 09:23:40 * service_register * XE * 0

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.

The main portion of startdb.sh is quite interesting…

# Starting Oracle Database 10g Express Edition instance and Listener
	$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/startdb.sql > /dev/null 2>&1
	if [ ! `ps -ef | grep tns | cut -f1 -d" " | grep -q oracle` ]
	then
		$LSNR start > /dev/null 2>&1
	else
		echo ""
	fi

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

#!/bin/sh
iconPath='/usr/share/icons/gnome/32x32/status'
icon='weather-storm.png'
notify-send -t 500 -i "$iconPath/$icon" 'Mad Science Weather' 'Igor, ready the lightning rod !'
exit 0

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…

cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/config/scripts
sudo cp startdb.sh startdb.bak
sudo cp stopdb.sh stopdb.bak

We now need to change the scripts to look something like this ( startdb.sh first) :

#!/bin/bash
#
#       svaggu 09/28/05 -  Creation
#	svaggu 11/09/05 -  dba groupd check is added
#

xsetroot -cursor_name watch

case $PATH in
    "") PATH=/bin:/usr/bin:/sbin:/etc
        export PATH ;;
esac

SAVE_LLP=$LD_LIBRARY_PATH

ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
ORACLE_SID=XE
LSNR=$ORACLE_HOME/bin/lsnrctl
SQLPLUS=$ORACLE_HOME/bin/sqlplus
export ORACLE_HOME
export ORACLE_SID
#
# Variables for the desktop notification 
#
iconPath='/usr/share/icons/gnome/32x32/status/'
infoIcon='info.png'
errIcon='error.png'
icon=$infoIcon
msg='Oracle Database and TNS Listener now running'

LOG="$ORACLE_HOME_LISTNER/listener.log"
user=`/usr/bin/whoami`
group=`/usr/bin/groups $user | grep dba`
if test -z "$group"
then
	xterm -T "Warning" -n "Warning" -hold -e "echo Operation failed. $user is not a member of \'dba\' group." 
	# Added message setting here for the notify change - Mike 15/5/11
	msg="Database not started. $user is not a member of \'dba\' group."
	icon=$errIcon
else
# Starting Oracle Database 10g Express Edition instance and Listener
# NOTE - swapping the order to start the listener BEFORE the db - Mike
	if [ ! `ps -ef | grep tns | cut -f1 -d" " | grep -q oracle` ]
	then
		$LSNR start > /dev/null 2>&1
	else
		echo ""
	fi
	$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/startdb.sql > /dev/null 2>&1
fi
	xsetroot -cursor_name left_ptr
#
# Added desktop notification to advise when the database has started
# Mike 
#
notify-send -t 1000 -i "$iconPath/$icon" 'Oracle XE' "$msg" 

The changes to the stopdb.sh script are fairly similar :

#!/bin/bash
#
#       svaggu 09/28/05 -  Creation
#       svaggu 11/09/05 -  dba groupd check is added
#

xsetroot -cursor_name watch

case $PATH in
    "") PATH=/bin:/usr/bin:/sbin:/etc
        export PATH ;;
esac

SAVE_LLP=$LD_LIBRARY_PATH

ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
ORACLE_SID=XE
SQLPLUS=$ORACLE_HOME/bin/sqlplus
export ORACLE_HOME
export ORACLE_SID
#
# Variables for the desktop notification - Mike 
#
iconPath='/usr/share/icons/gnome/32x32/status/'
infoIcon='info.png'
errIcon='error.png'
icon=$infoIcon
msg='Database shutdown complete'

user=`/usr/bin/whoami`
group=`/usr/bin/groups $user | grep dba`
if test -z "$group"
then
        xterm -T "Warning" -n "Warning" -hold -e "echo Operation failed. $user is not a member of \'dba\' group." 
	# Added message setting here for the notify change - Mike 
	msg="Operation failed. $user is not a member of \'dba\' group."
	icon=$errIcon
else
# Stop Oracle Database 10g Express Edition instance
	$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/stopdb.sql > /dev/null 2>&1
fi
	
xsetroot -cursor_name left_ptr
notify-send -t 1000 -i "$iconPath/$icon" 'Oracle XE' "$msg" 

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.

About these ads