Oracle XE 11g – Getting APEX to start when your database does

They say patience is a virtue. It’s one that I often get to exercise, through no fault of my own.
Usually trains are involved. Well, I say involved, what I mean is…er…late.
I know, I do go on about trains. It’s a peculiarly British trait.
This may be because the highest train fares in Europe somehow don’t quite add up to the finest train service.
We can debate the benefits of British Trains later – let’s face it we’ll have plenty of time whilst we’re waiting for one to turn up. For now, I want to concentrate on avoiding any further drain on my badly tried patience by persuading APEX that it should be available as soon as my Oracle XE database is…

Oracle Express Edition – how it starts

There are three main components to Oracle XE :

  1. The Database
  2. The TNS Listener
  3. APEX

When you fire up Express Edition, it will start these components in this order :

  1. The Database
  2. The TNS Listener

APEX doesn’t get a look in at this point. Instead, when you first invoke it, it has to wait for the XDB database component to be initialized.

As I’ve observed previously, starting up the database before the listener can cause a lag if you’re trying to connect via TNS – i.e. from any machine other than the one the database is running on, or by specifying the database in the connect string.

The other problem is, of course, APEX will often refuse to play when you first call it after startup.

Often, your first attempt to get to the Database Home Page will be met with the rather unhelpful :

leaves on the line, or the wrong kind of snow ? Either way, APEX isn't talking to you

leaves on the line, or the wrong kind of snow ? Either way, APEX isn’t talking to you

It’s not until the TNS Listener is up and running that you’ll actually be able to connect to APEX.

In fact, it won’t be until you see the XEXDB service has been started by the Listener that you’ll be able to use APEX.
To check this :

lsnrctl status

The output should look something like this :

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 02-MAY-2015 19:10:19

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                02-MAY-2015 18:25:19
Uptime                    0 days 0 hr. 44 min. 59 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/mike-Monza-N2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mike-Monza-N2)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mike-Monza-N2)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully

You can see what happens when you first call APEX by looking in the database alert log. If you want to see it in real-time, open a terminal and type :

tail -f /u01/app/oracle/diag/rdbms/xe/XE/trace/alert_XE.log

With the terminal window open and visible, click on the Getting Started Desktop icon (or simply invoke APEX directly from your browser). You should see this in the alert.log…

XDB installed.
XDB initialized.

So, the solution is :

  1. Start the Listener before starting the Database
  2. Get “APEX” to start directly after starting the Database

I’ve put APEX in quotes here because what we actually want to do is initialize the XDB component within the database.

Step 1 – changing the starting order

To do this, we’ll need to edit the standard startdb.sh script, after first making a backup copy, just in case …

sudo su oracle
cd /u01/app/oracle/product/11.2.0/xe/config/scripts
cp startdb.sh startdb.sh.bak
gedit startdb.sh

… The edited script should look something like this :

#!/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

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_SID=XE
LSNR=$ORACLE_HOME/bin/lsnrctl
SQLPLUS=$ORACLE_HOME/bin/sqlplus
LOG="$ORACLE_HOME_LISTNER/listener.log"
user=`/usr/bin/whoami`
group=`/usr/bin/groups $user | grep -i dba`

if test -z "$group"
then
	if [ -f /usr/bin/zenity ]
	then
		/usr/bin/zenity --error --text="$user must be in the DBA OS group to start the database." 
		exit 1
	elif [ -f /usr/bin/kdialog ]
	then
		/usr/bin/kdialog --error "$user must be in the DBA OS group to start the database."
		exit 1
	elif [ -f /usr/bin/xterm ]
	then
		/usr/bin/xterm -T "Error" -n "Error" -hold -e "echo $user must be in the DBA OS group to start the database."
		exit 1
	fi
else
    # Listener start moved to before database start to avoid lag in db
    # registering with listener after db startup
    # Mike
	if [ ! `ps -ef | grep tns | cut -f1 -d" " | grep -q oracle` ]
	then
		$LSNR start > /dev/null 2>&1
	else
		echo ""
	fi
# now start the database
	$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/startdb.sql > /dev/null 2>&1
fi

xsetroot -cursor_name left_ptr

Now, when the database first starts and looks around for the Listener to register with, it’ll find it up and ready to go.

Step 2 – initialise XDB

Exactly how you do this properly has been a bit of a puzzle to me. I’m sure there is a proper way to do this, other than pointing your browser at APEX only for it to tell you to go away.
In lieu of this elusive “proper” XDB startup command, I’m going to use one that tells you what port the PL/SQL Gateway ( the default listener for APEX) is listening….

select dbms_xdb.gethttpport
from dual;

Something interesting happens when you run this command. The first time you execute it after database startup and when you haven’t invoked APEX, it takes quite a long time to return. If you look in the alert log you’ll see the reason for this…

XDB installed.
XDB initialized.

Yes, the same entries you see when you first try to invoke APEX.

So, we’re going to get this query to run as soon as the database is started. The easiest way to do this is to edit the startdb.sql script that’s called by the shell script we’ve just edited…

sudo su oracle
cd /u01/app/oracle/product/11.2.0/xe/config/scripts
cp startdb.sql startdb.sql.bak
gedit startdb.sql

Here, we’re simply adding this query directly the database is open…

connect / as sysdba
startup
-- added to start the PL/SQL Gateway so that APEX should be reachable
-- right after startup
select dbms_xdb.gethttpport from dual;
exit

Now, if we check the alert.log on startup of the database we’ll see something like…

QMNC started with pid=28, OS id=2469
Wed Apr 29 12:16:27 2015
Completed: ALTER DATABASE OPEN
Wed Apr 29 12:16:32 2015
db_recovery_file_dest_size of 10240 MB is 57.54% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Wed Apr 29 12:16:32 2015
Starting background process CJQ0
Wed Apr 29 12:16:32 2015
CJQ0 started with pid=29, OS id=2483
Wed Apr 29 12:16:43 2015
XDB installed.
XDB initialized.

As soon as that last message is there, APEX is up and ready to receive requests.
On the one hand, it’s nice to know for definite when APEX will finally deign to answer your call, as opposed to hiding behind the PAGE NOT FOUND error and pretending to be out.
On the other hand, having to tail the alert.log to figure out when this is seems a bit like hard work.

Of course, in Linux land, you can always just prompt the shell script to announce when it’s finished…

Desktop Notification

I’m running this on a Gnome based desktop ( Cinnamon, if you’re interested, but it should work on anything derived from Gnome). KDE adherents will have their own, equally useful methods.
As in my previous attempt at this sort of thing, I’m going to use notify-send.

If you want to test if it’s installed, you can simply invoke it from the command line :

notify-send "Where's that train ?"

If all is OK, you should get this message displayed in a notification on screen…

Is it a bird ? Is it a train...

Is it a bird ? Is it a train…

Now we simply use this utility to add a message at the end of the database startup script.
We can even add an icon if we’re feeling flash….

#!/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

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_SID=XE
LSNR=$ORACLE_HOME/bin/lsnrctl
SQLPLUS=$ORACLE_HOME/bin/sqlplus
LOG="$ORACLE_HOME_LISTNER/listener.log"
user=`/usr/bin/whoami`
group=`/usr/bin/groups $user | grep -i dba`

if test -z "$group"
then
	if [ -f /usr/bin/zenity ]
	then
		/usr/bin/zenity --error --text="$user must be in the DBA OS group to start the database." 
		exit 1
	elif [ -f /usr/bin/kdialog ]
	then
		/usr/bin/kdialog --error "$user must be in the DBA OS group to start the database."
		exit 1
	elif [ -f /usr/bin/xterm ]
	then
		/usr/bin/xterm -T "Error" -n "Error" -hold -e "echo $user must be in the DBA OS group to start the database."
		exit 1
	fi
else    
    # Listener start moved to before database start to avoid lag in db
    # registering with listener after db startup
    # Mike
	if [ ! `ps -ef | grep tns | cut -f1 -d" " | grep -q oracle` ]
	then
		$LSNR start > /dev/null 2>&1
	else
		echo ""
	fi
    # now start the database
	$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/startdb.sql > /dev/null 2>&1
fi
#
# Publish desktop notification that we're ready to go...
#
notify-send -i /usr/share/pixmaps/oraclexe-startdatabase.png "Database and APEX ready to play" 

xsetroot -cursor_name left_ptr

When the script hits the notify line, we’re rewarded with…

db_ready

So, even if your train has stopped randomly in-between stations or is simply proving once again, that the timetable is a work of fiction, at least you won’t have to wonder if your database is ready for action.

Advertisements

13 thoughts on “Oracle XE 11g – Getting APEX to start when your database does

  1. Seems to make sense, but… For me this apex will not start- no process is listening on declared port 8080, which drives me mad already… :(. For listener endpoints summary still does not include apex…

    • Problem solved.in this so excellent software, installer did not set up listener.ora correctly which missed the line with apex service. Hope this will help someone

  2. There is a typo on this page
    select dbms_xdb.gethtpport needs an extra “t” in http ie dbms_xdb.gethttpport

  3. hello ,
    actually am facing a problem , i installed apex in oracle 11g r2 and i have ebusiness too on oracle linux ..

    it finished successfully and i can open the apex without any problem , but i tried restart the listner and the database to check something , when i opened it again apex doesn’t want to start .. i did and checked everything i could without any hope ..

    i check the listener status and i can see 1s session only for the oracle database port and i can’t see the apex port

    so , how can i make apex startup , should i have to edit the listener to add soemthing for the apex !!

    NOTE : i previously installed apex in windows server and it doesn’t have any problem when i restart listner or the database itself this issue appears here only in linux

    • Peter,

      a couple of things spring to mind.
      First of all, is the database itself up and running ? If not, then it may be worth checking that all the appropriate Linux Environment Variables are set for the user who starts the database ( $ORACLE_HOME etc).
      Secondly, are you saying that you have ebusiness running on the same server ? I haven’t used it but if it has a web front-end does it use the same port as you installed APEX on ? If so, you may have a port conflict issue.

      HTH,

      Mike

  4. Mike ,
    thank you so much for your fast reply ..

    first , yes .. database is up and running cause i know apex will not work without the database running and the listener too and also the application is running .

    second , no there is no port conflict cause basically e-business works on ports ( 9000,9001… ) .

    if you have anything else in your mind you could help me with i’ll be really grateful .

    Thank you mike .

    -peter

    • Peter,
      are you trying to access APEX from the server on which it is running or are you trying to do this from a remote machine ?
      When you try to open the APEX url, what is the error message that you get ?

      • Mike ,
        i already solve the problem , the issue was related to the listener , i don’t know why it happened but all what i need to recreate the listener for the database , run autocfg for database and application , rebote the machine ( cause it showed me error that i can’t stop or start the listener ) and then reinstall apex . it works !

        thank you mike so much for your support .

        Note: from a remote machine and instead of localhost i put the ip adress of the remote machine .

        -Peter

  5. Hi Mike,

    I have installed Oracle 11g and APEX4.2 on personal laptop. Till yesterday it was working but now getting below error, also I couldn’t see XDB entry in Listner.

    This site can’t be reached
    localhost refused to connect.
    Search Google for localhost 8080 apex
    ERR_CONNECTION_REFUSED

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s