Tags: oracle | ubuntu

This is Part 2 of my guide to install Oracle on Ubuntu Karmic, 32-bit and 64-bit. Part 1 can be found here.

Automating Startup & Shutdown

Oracle themselves supply two scripts called dbstart and dbshut, both found in the ORACLE_HOME/bin directory. Those scripts read another file called /etc/oratab to determine what databases actually need to automatically startedup and shutdown. All you have to do is write a script of your own that calls dbstart or dbshut, and then integrate this new script into Ubuntu's standard runlevel/service control mechanism.

Editing Oratab

You will need to edit the contents of the /etc/oratab :

sudo gedit /etc/oratab

You'll find at the moment that the file contains this one line (apart from all the commented-out ones, that is):

orcl10:/oracle/10g:N

This entry tells the system all the details of the Oracle database on your machine. Its called orcl10, it's ORACLE_HOME is /oracle/10g, and it's Not to be automatically started or stopped. So... To make sure its started automatically switch that N to a Y. NB: Even if you don't want the database to be started and stopped you will need to do this. If you don't the next step won't do anything useful

orcl10:/oracle/10g:Y

Save the file.

Editing dbstart

There's not much to change here but its one of the more important and non-obvious changes.

su - oracle
vi $ORACLE_HOME/bin/dbstart

Towards the beginning of that file, you'll find these lines:

# Set this to bring up Oracle Net Listener

ORACLE_HOME_LISTNER=/ade/vikrkuma_new/oracle

if [ ! $ORACLE_HOME_LISTNER ] ; then
echo "ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener"
else
LOG=$ORACLE_HOME_LISTNER/listener.log

Ignore Oracle's intentional spelling mistake and change "/ade/vikrkuma_new/oracle" to your ORACLE_HOME. Eg, for me this is:

ORACLE_HOME_LISTNER=/oracle/10g

The subtle point going on here is that you might have a server running many different versions of Oracle, but using only one Listener. A 10g Listener can listen for various different databases. So the place where the Listener's own executables should be found may be different from where the database's own executables are found -and that's why the two things are separately configurable. In most development cases they will be the same.

Dbshut

The second script, ORACLE_HOME/bin/dbshut might also need editing for a production environment, but you probably won't need to change for a Development machine. Dbshut will do an immediate shutdown of the database and rudely kick everyone off. This is fine for Testing but real users don't like this...!

Seeing if it Works

Try starting oracle

dbstart

Should get nothing much for a response, but look in the log.

- Production
With the Partitioning, OLAP and Data Mining options

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 08 12:22:10 2010

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

SQL> Connected to an idle instance.
SQL> ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  1220432 bytes
Variable Size             155189424 bytes
Database Buffers          377487360 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
 - Production
With the Partitioning, OLAP and Data Mining options

/oracle/10g/bin/dbstart: Database instance "orcl10" warm started.

Adding An AutoStart Script

NB : Can skip this section if you just want to have it running on command.

You now need to write a script which itself calls the dbshut and dbstart scripts. This script is usually stored in the /etc/init.d directory (and thus will require root privileges to create) and gets called dbora (though it can actually be called anything you like. The script should look like this:

#!/bin/bash
#
# /etc/init.d/dbora
#
# Startup script for Oracle databases

export ORACLE_HOME=/oracle/10g
export ORACLE_SID=orcl10
export PATH=$PATH:$ORACLE_HOME/bin

case "$1" in
start)
echo -n "Starting Oracle: "
su oracle -c $ORACLE_HOME/bin/dbstart
touch /var/lock/oracle

su oracle -c "$ORACLE_HOME/bin/emctl start dbconsole"
echo "OK"
;;
stop)
echo -n "Shutdown Oracle: "
su oracle -c $ORACLE_HOME/bin/dbshut
rm -f /var/lock/oracle
echo "OK"
;;
*)
echo "Usage: 'basename $0' start|stop"
exit 1
esac
exit 0

Make the file executable:

sudo chmod 775 /etc/init.d/dbora

You then need to link the new calling script into the runlevel scripts that Ubuntu uses to control general service startup and shutdown. That can be done by issuing the one command:

sudo update-rc.d dbora defaults 99

If you now reboot your server, you should find that immediately it comes back up, you can become the oracle user and connect to the database via SQL*Plus without any hassles

Giving it a go

me@erewhon:~$ su - oracle
Password:
oracle@erewhon:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 08 12:32:34 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> exit

Had the thing not been started correctly, however, I would have seen something more like this:

oracle@erewhon:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 08 12:32:34 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

So, provided you don't get the 'idle instance' message after a server reboot everything is working fine...! Well done...!

Running Enterprise Manager

There's also a handy Management Interface to Oracle called "Enterprise Manager". Start this with:

me@erewhon:~$ su - oracle

Password:
oracle@erewhon:~$ emctl start dbconsole
TZ set to Australia/Sydney
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:1158/em/co ... pplication
Starting Oracle Enterprise Manager 10g Database Control ................ started.
------------------------------------------------------------------
Logs are generated in directory /oracle/10g/localhost.localdomain_orcl10/sysman/log

It will take a while to get going so hang in there...!

To stop it use:

emctl stop dbconsole

Then browse to the management url http://erewhon:1158/em and then login with SYS and the password you entered earlier ("oraclepass"). Most actual routine administration tasks can be found by clicking the Administration link towards the top of the page; backup and recovery options can be found under the Maintenance link; and performance tuning diagnostics can be found under (surprise, surprise!) the Performance link.

Setup Environment

Add the following to /~/.bashrc

# NB : these should not have trailing spaces
export LD_LIBRARY_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/client/lib/:/oracle/10g/lib
export ORACLE_HOME=/oracle/10g

Double check these are correct by calling sqlplus...

If we get :

Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

Might need to add main user to the Oracle groups (eg "dba" and "oinstall"). Use the Ubuntu Users/Groups client for this. Might need to set permissions as well.

chmod -R 755 *

If we get:

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

It means there are trailing spaces on the paths.

Also try giving 755 permissions to tnslsnr in the bin directory.

If we get,

me@erewhon:~$ sqlldr
Message 2100 not found; No message file for product=RDBMS, facility=ULMessage 2100 not found; No message file for product=RDBMS, facility=ULjosh@barker:~$

then do:

sudo chmod -R o+rx /oracle/10g/

Well Done (Part 2)

You now have a functioning Oracle install on your machine. Its now time to add tablespaces, users and even some test data...! Have fun...! (And let me know any feedback on this guide / run-through)

Alternative Guides

If this didn't get Oracle working for you, can also try this guide.