Install Oracle 10 Express Database on Debian Testing

Author

Willem Moors

Date

1 Februari 2006

This document describes how to install the Oracle 10 Express Database (the beta version) on Debian linux, in a custom directory (ie. not in /usr/lib).

There's a lot of manual work involved in the following, if that scares you, then leave the premises now.

When the production Oracle 10 Express database is released, then I might pour all the following into a few handy scripts.

The following installation is based on the information in script $ORACLE_HOME/config/scripts/oracle-xe. When in doubt, consult that script.

Any comments about this document? Email them to me at :

 $ echo "moc TOD liamg TA sroom TOD melliw" | rev

1. System requirements

You need a functioning Debian Testing system. The hardware used in this article is a humble Pentium III laptop with a meagre 196 Megs of RAM.

The following software may need to be installed:

Utilities : rpm2cpio cpio

Library : libaio

2. Read the Installation Notes.

Point your browser at : http://www.oracle.com/technology/software/products/database/xe/files/install.102/b25144/toc.htm and read it.

Check the kernel parameters, you may need to change them manually.

3. Download the Oracle10 express rpm

Download the RPM containing the database from : http://www.oracle.com/technology/software/products/database/xe/index.html

The rpm this article deals with is : oracle-xe-10.2.0.1-0.1.i386.rpm.

4. Choosen directory locations and hostname, listener port, http port.

The automated installation puts the files under /usr/lib/oracle, but I prefer to keep the Oracle files separate from my linux system-files, and therefore put them under /opt.

The product files will be installed in

   /opt/db/oraclexe/app

The data files go in

   /opt/db/oraclexe/oradata

For hostname I've chosen the explicit IP address of my laptop, 172.16.1.8, (yes I know the disadvantages), for listener port I've taken the standard 1521, and for http port I selected 8080.

You probably do not need to change the ports, but the 172.16.1.8 will very very likely need to be changed in the scripts that follow (or you can change the IP address of your laptop to 172.16.1.8 of course...).

5. Setup the target directory and create the oraclexe user

Become superuser (root) on your system and make a user oraclexe, member of the dba group, and set the password:

 $ su - 
 # mkdir /opt/db/oraclexe
 # groupadd dba
 # useradd -d /opt/db/oraclexe -c 'Oracle Express' -g dba -s /bin/bash oraclexe
 # passwd oraclexe

6. Unpack the rpm

Unpack the rpm into a staging directory

 # mkdir /tmp/oracle_stage
 # cd /tmp/oracle_stage
 # rpm2cpio /path_to_your_downloaded_file/oracle-xe-10.2.0.1-0.1.i386.rpm | cpio -i --make-directories

Now move the oracle product and application files to the /opt/db/oraclexe directory:

 # mv usr/lib/oracle/xe/app /opt/db/oraclexe
 # mv usr/share/doc /opt/db/oraclexe/doc

Chown the files & change the directory access:

 # chown -R oraclexe:dba /opt/db/oraclexe
 # chmod -R 755 /opt/db/oraclexe

The files that now remain in /tmp/oracle_stage/usr/share are ignored in my setup, I don't use gnome.

7. Setup the linux environment for oraclexe

Log on as oraclexe

 # su - oraclexe

Put the following in the ~/.bashrc of user oraclexe :

 export ORACLE_HOME=/opt/db/oraclexe/app/oracle/product/10.2.0/server
 export ORACLE_OWNER=oraclexe
 export ORACLE_SID=XE
 export LSNR=$ORACLE_HOME/bin/lsnrctl
 export SQLPLUS=$ORACLE_HOME/bin/sqlplus
 export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
 export PATH=$ORACLE_HOME/bin:$PATH

Copy the .bash_profile from the skeleton

 $ cp /etc/skel/.bash_profile .

Log out and back in again, to make the above settings effective.

8. Create directories to contain the data-files

(logged on as user oraclexe)

 $ mkdir -p /opt/db/oraclexe/oradata/XE
 $ chmod 740 /opt/db/oraclexe/oradata/XE

9. Patch a whole bunch of files

Use following script to patch a whole bunch of oracle files among which the listener.ora, tnsnames.ora, init.ora files, and a slew of sql scripts.
Choices made (as mentioned above):

hostname: 172.16.1.8

port: 1521

http port: 8080

sga_target: 146800640

pga_aggregate_target: 16777216

I found the values for the last two parameters in the file 'xeseed.dbc'.

The first part of the following script backs-up the files, the second part does the changing.

Either copy/paste following text, or download the script from here: patch_script.sh.

Edit the script to change 172.16.1.8 into your favourite IP-address or host-name. Then execute this "patch_script.sh" (as user oraclexe).

 cp $ORACLE_HOME/network/admin/listener.ora $ORACLE_HOME/network/admin/listener.ora.backup
 cp $ORACLE_HOME/network/admin/tnsnames.ora $ORACLE_HOME/network/admin/tnsnames.ora.backup
 cp $ORACLE_HOME/config/scripts/postDBCreation.sql $ORACLE_HOME/config/scripts/postDBCreation.sql.backup
 cp $ORACLE_HOME/config/scripts/init.ora $ORACLE_HOME/config/scripts/init.ora.backup
 cp $ORACLE_HOME/config/scripts/XE.sh $ORACLE_HOME/config/scripts/XE.sh.backup
 cp $ORACLE_HOME/config/scripts/XE.sql $ORACLE_HOME/config/scripts/XE.sql.backup
 cp $ORACLE_HOME/config/scripts/CloneRmanRestore.sql $ORACLE_HOME/config/scripts/CloneRmanRestore.sql.backup
 cp $ORACLE_HOME/config/scripts/cloneDBCreation.sql $ORACLE_HOME/config/scripts/cloneDBCreation.sql.backup
 cp $ORACLE_HOME/config/scripts/postScripts.sql $ORACLE_HOME/config/scripts/postScripts.sql.backup
 cp $ORACLE_HOME/config/scripts/rmanRestoreDatafiles.sql $ORACLE_HOME/config/scripts/rmanRestoreDatafiles.sql.backup
 cp $ORACLE_HOME/config/scripts/shutdb.sh $ORACLE_HOME/config/scripts/shutdb.sh.backup
 cp $ORACLE_HOME/config/scripts/sqlplus.sh $ORACLE_HOME/config/scripts/sqlplus.sh.backup
 cp $ORACLE_HOME/config/scripts/startdb.sh $ORACLE_HOME/config/scripts/startdb.sh.backup
 cp $ORACLE_HOME/config/scripts/stopdb.sh $ORACLE_HOME/config/scripts/stopdb.sh.backup
 cp $ORACLE_HOME/config/scripts/initXETemp.ora $ORACLE_HOME/config/scripts/initXETemp.ora.backup
 cp $ORACLE_HOME/bin/oracle_env.csh $ORACLE_HOME/bin/oracle_env.csh.backup
 cp $ORACLE_HOME/bin/oracle_env.sh $ORACLE_HOME/bin/oracle_env.sh.backup
 cp $ORACLE_HOME/rdbms/admin/dbmssml.sql $ORACLE_HOME/rdbms/admin/dbmssml.sql.backup
 # ----------------------------------------------------------------------
 sed -i "s//172.16.1.8/g" $ORACLE_HOME/network/admin/listener.ora
 sed -i "s//172.16.1.8/g" $ORACLE_HOME/network/admin/tnsnames.ora
 sed -i "s//1521/g" $ORACLE_HOME/network/admin/listener.ora
 sed -i "s//1521/g" $ORACLE_HOME/network/admin/tnsnames.ora
 sed -i "s:(ORACLE_HOME.*):(ORACLE_HOME =/opt/db/oraclexe/app/oracle/product/10.2.0/server ):"  $ORACLE_HOME/network/admin/listener.ora
 sed -i "s//8080/g" $ORACLE_HOME/config/scripts/postDBCreation.sql
 sed -i "s:/usr/lib/oracle/xe/app/oracle:/opt/db/oraclexe/app/oracle:g" $ORACLE_HOME/config/scripts/postDBCreation.sql
 sed -i "s:%FRA_DIR%:/opt/db/oraclexe/app/oracle/product/flash_recovery_area:g" $ORACLE_HOME/config/scripts/postDBCreation.sql
 sed -i "s/%hostname%/172.16.1.8/g" $ORACLE_HOME/config/scripts/init.ora
 sed -i "s/%port%/1521/g" $ORACLE_HOME/config/scripts/init.ora
 sed -i "s:/usr/lib/oracle/xe/app/oracle:/opt/db/oraclexe/app/oracle:g" $ORACLE_HOME/config/scripts/init.ora
 sed -i "s:/usr/lib/oracle/xe/oradata:/opt/db/oraclexe/oradata:g" $ORACLE_HOME/config/scripts/init.ora
 sed -i "s/%sga_target%/146800640/" $ORACLE_HOME/config/scripts/init.ora
 sed -i "s/%pga_aggregate_target%/16777216/" $ORACLE_HOME/config/scripts/init.ora
 sed -i "s:/usr/lib/oracle/xe/app/oracle:/opt/db/oraclexe/app/oracle:g" $ORACLE_HOME/config/scripts/XE.sh
 sed -i "s:/usr/lib/oracle/xe/oradata:/opt/db/oraclexe/oradata:g" $ORACLE_HOME/config/scripts/XE.sh
 sed -i "s:/usr/lib/oracle/xe/app/oracle:/opt/db/oraclexe/app/oracle:g" $ORACLE_HOME/config/scripts/XE.sql
 sed -i "s:/usr/lib/oracle/xe/app/oracle:/opt/db/oraclexe/app/oracle:g" $ORACLE_HOME/config/scripts/CloneRmanRestore.sql
 sed -i "s:/usr/lib/oracle/xe/app/oracle:/opt/db/oraclexe/app/oracle:g" $ORACLE_HOME/config/scripts/cloneDBCreation.sql
 sed -i "s:/usr/lib/oracle/xe/oradata:/opt/db/oraclexe/oradata:g" $ORACLE_HOME/config/scripts/cloneDBCreation.sql
 sed -i "s:/usr/lib/oracle/xe/app/oracle:/opt/db/oraclexe/app/oracle:g" $ORACLE_HOME/config/scripts/postScripts.sql
 sed -i "s:/usr/lib/oracle/xe/app/oracle:/opt/db/oraclexe/app/oracle:g" $ORACLE_HOME/config/scripts/rmanRestoreDatafiles.sql
 sed -i "s:/usr/lib/oracle/xe/oradata:/opt/db/oraclexe/oradata:g" $ORACLE_HOME/config/scripts/rmanRestoreDatafiles.sql
 sed -i "s:/usr/lib/oracle/xe/app/oracle:/opt/db/oraclexe/app/oracle:g" $ORACLE_HOME/config/scripts/shutdb.sh
 sed -i "s:/usr/lib/oracle/xe/app/oracle:/opt/db/oraclexe/app/oracle:g" $ORACLE_HOME/config/scripts/sqlplus.sh
 sed -i "s:/usr/lib/oracle/xe/app/oracle:/opt/db/oraclexe/app/oracle:g" $ORACLE_HOME/config/scripts/startdb.sh
 sed -i "s:/usr/lib/oracle/xe/app/oracle:/opt/db/oraclexe/app/oracle:g" $ORACLE_HOME/config/scripts/stopdb.sh
 sed -i "s:/usr/lib/oracle/xe/app/oracle:/opt/db/oraclexe/app/oracle:g" $ORACLE_HOME/config/scripts/initXETemp.ora
 sed -i "s:/usr/lib/oracle/xe/oradata:/opt/db/oraclexe/oradata:g" $ORACLE_HOME/config/scripts/initXETemp.ora
 sed -i "s/%sga_target%/146800640/" $ORACLE_HOME/config/scripts/initXETemp.ora
 sed -i "s/%pga_aggregate_target%/16777216/" $ORACLE_HOME/config/scripts/initXETemp.ora
 sed -i "s:/usr/lib/oracle/xe/app/oracle:/opt/db/oraclexe/app/oracle:g" $ORACLE_HOME/bin/oracle_env.csh
 sed -i "s:/usr/lib/oracle/xe/app/oracle:/opt/db/oraclexe/app/oracle:g" $ORACLE_HOME/bin/oracle_env.sh
 sed -i "s:/usr/lib/oracle/xe/app/oracle:/opt/db/oraclexe/app/oracle:g" $ORACLE_HOME/rdbms/admin/dbmssml.sql

10. Run the installation script

After the above patching has been done, then take a deep breath, cross your fingers and run the XE.sh script

 $ $ORACLE_HOME/config/scripts/XE.sh

If you get the error for "loading shared libraries: libaio.so.1" then you haven't installed libaio. My system has : libaio_0.3.104-1_i386.deb

The execution of the XE.sh script will take a while, and no error messages (except one ORA-01109: database not open) should show on your screen.

11. Set the passwords

Choose an oracle password

 export ORACLE_PASSWORD="dont_leave_me_this_way"

and execute these commands (as user oraclexe) :

 $ echo "alter user flows_020100 identified by $ORACLE_PASSWORD;" | sqlplus -s / as sysdba > /dev/null 2>&1
 $ echo "alter user sys identified by $ORACLE_PASSWORD;" | sqlplus -s / as sysdba > /dev/null 2>&1
 $ echo "alter user system identified by $ORACLE_PASSWORD;" | sqlplus -s / as sysdba > /dev/null 2>&1
 $ echo "alter user flows_files identified by $ORACLE_PASSWORD;" | sqlplus -s / as sysdba > /dev/null 2>&1
 $ echo "alter user anonymous identified by $ORACLE_PASSWORD;" | sqlplus -s / as sysdba > /dev/null 2>&1

12. Some more things to to by root:

Become superuser again and do the following :

 # chmod -R 640 /opt/db/oraclexe/oradata/XE
 # chmod 750 /opt/db/oraclexe/oradata/XE
 # chown -R oraclexe:dba  /opt/db/oraclexe
 # rm -fr /opt/db/oraclexe/app/oracle/product/10.2.0/server/config/seeddb

Add the following line to the file /etc/oratab

 XE:/opt/db/oraclexe/app/oracle/product/10.2.0/server:N

if /etc/oratab didn't exist yet, then also do

 # chown oraclexe:dba /etc/oratab
 # chmod 644 /etc/oratab

13. And now the start/stop script

This is my start/stop script that you put under /etc/init.d. You can get it from here: oraclexe.

It looks like this:

 #!/bin/bash
 # Script name : /etc/init.d/oraclexe
 #
 # Start & stop Oracle 10g Express Edition Database and Listener
 RETVAL=0
 export ORACLE_HOME=/opt/db/oraclexe/app/oracle/product/10.2.0/server
 export ORA_HOME=$ORACLE_HOME
 export ORACLE_OWNER=oraclexe
 export ORA_OWNR=oraclexe
 export ORACLE_SID=XE
 export PATH=$ORACLE_HOME/bin:$PATH
 export LC_ALL=C
 export SQLPLUS=$ORACLE_HOME/bin/sqlplus
 export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
 
 
 start() {
   status=`ps -ef | grep tns | grep oraclexe`
   if [ "$status" == "" ]
   then
     if [ -f $ORACLE_HOME/bin/tnslsnr ]
     then
       echo "Starting Oracle Net Listener."
       su - oraclexe -c "$ORACLE_HOME/bin/lsnrctl start" #> /dev/null 2>&1
     fi
   fi
   echo "Starting Oracle Database 10g Express Edition Instance."
   su oraclexe -c "$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/startdb.sql" #> /dev/null 2>&1
   RETVAL=$?
   if [ $RETVAL -eq 0 ]
   then
     echo
   else
     echo Failed to start Oracle Net Listener using $ORACLE_HOME/bin/tnslsnr
     echo and Oracle Express Database using $ORACLE_HOME/bin/sqlplus.
     RETVAL=1
   fi
   touch /var/lock/oraclexe
   return $RETVAL
 }
 
 
 stop() {
   echo "Shutting down Oracle Database 10g Express Edition Instance."
   su - oraclexe -c "$SQLPLUS -s /nolog @$ORACLE_HOME/config/scripts/shutdb.sql" #> /dev/null 2>&1
   echo Stopping Oracle Net Listener.
   su - oraclexe -c "$ORACLE_HOME/bin/lsnrctl stop" > /dev/null 2>&1
   RETVAL=$?
   echo
   if [ $RETVAL -eq 0 ] && rm -f /var/lock/oraclexe
   then
     return $RETVAL
   fi
 }
 
 
 # See how we were called
 case "$1" in
   start)
     start
     ;;
   stop)
     stop
     ;;
   restart|reload)
     stop
     start
     RETVAL=$?
     ;;
   *)
     echo $"Usage: $0 {start|stop|restart}"
     exit 1
 esac

14. Startup at system bootup

If you want the Oracle express database to start/stop at system boot/shutdown, then add the appropriate links to this /etc/init.d/oraclexe script (see update-rc.d)

15. Go ahead and connect

You can use sqlplus to connect to your database now (and change the passwords, unlock accounts, etc).
(as linux user 'oraclexe')

 $ sqlplus  / as sysdba
 
 SQL*Plus: Release 10.2.0.1.0 - Beta on Wed Feb 1 17:11:46 2006
 Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 Connected to:
 Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta
 
 SQL> select * from all_users;
 
 USERNAME                          USER_ID CREATED
 ------------------------------ ---------- ---------
 FLOWS_020100                           34 10-OCT-05
 FLOWS_FILES                            33 10-OCT-05
 HR                                     32 10-OCT-05
 ANONYMOUS                              28 10-OCT-05
 XDB                                    27 10-OCT-05
 CTXSYS                                 25 10-OCT-05
 DBSNMP                                 23 10-OCT-05
 TSMSYS                                 20 10-OCT-05
 DIP                                    18 10-OCT-05
 OUTLN                                  11 10-OCT-05
 SYSTEM                                  5 10-OCT-05
 SYS                                     0 10-OCT-05
 
 12 rows selected.

16. For your information

My tnsnames.ora file looks like this:

 XE =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.8)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = XE)
     )
   )
 
 EXTPROC_CONNECTION_DATA =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
     )
     (CONNECT_DATA =
       (SID = PLSExtProc)
       (PRESENTATION = RO)
     )
   )

And my listener.ora file looks like this:

 SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (SID_NAME = PLSExtProc)
       (ORACLE_HOME =/opt/db/oraclexe/app/oracle/product/10.2.0/server )
       (PROGRAM = extproc)
     )
   )
 
 LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.8)(PORT = 1521))
     )
   )
 
 DEFAULT_SERVICE_LISTENER = (XE)