Oracle XE on Debian

This document describes installing Oracle 10g Express Edition (formerly known as HTML DB) on a Debian based system. My original document can be found here.

About OracleXE

Oracle Database Express Edition (XE) is an entry-level, small-footprint database based on the Oracle Database 10g Release 2 code base that's free to develop, deploy, and distribute; fast to download; and simple to administer.

Basic requirements

- Debian based host (32 bit) with minimum 4GB of free space for oracle data + 500Mb for package installation. - Min. 1Gb of ram.

1) As always start up by adding additional lines to sources.list and update package list:

# echo "deb http://oss.oracle.com/debian/ unstable main non-free" >> /etc/apt/sources.list
# apt-get update

2) Installation now may begin with installing following 2 packages from freshly updated list:

# apt-get install oracle-xe-client 
# apt-get install oracle-xe

3) Installation is now completed. Please notice runtime kernel parametar changes: `cat /etc/sysctl.conf`. Proceed further with configuration - just accept defaults - and enter password for SYS and SYSTEM accounts:

# sh /etc/init.d/oracle-sh configure

That's it. Oracle listener should be up and running on socket 1521: `lsof -n -i TCP:1521`, with web configuration console attached to your loopback device `firefox http://127.0.0.1:8080/apex` - this is a default installation.

Oracle XE hints

- You can always preview/edit your current Oracle-XE settings with: `vim /etc/default/oracle-xe`.
- Since installation of previously installed packages is rather heavily space demanding, delete them from apt cache:

# apt-get clean 

Disable/enable Oracle XE Service

- Your Debian based system should meet the minimum requirements for swap space, otherwise installastion would fail. Based on the amount of physical memory available on the system, Oracle Database 10g Express Edition requires 1006 MB of swap space (YMMV). In order to overcome this issue you can allways extend your partitions with like Knoppix ot Gparted. The alterntative way without reboot can be done by issuing:

# dd if=/dev/zero of=/myswapfile bs=1M count=1000
# mkswap /myswapfile
# swapon /myswapfile

- You can always disable Oracle XE startup in boot time with (no update-rc.d method here ;-) ):

# chmod -x /etc/init.d/oracle-xe 

The alernative way to the same is to edit /etc/default/oracle-xe and change:

#ORACLE_DBENABLED=true
ORACLE_DBENABLED=false

Web console management

- If you have installed OracleXE database on remote *nix server, you can easily setup a ssh tunnel in order to gain access to a web administration console (since web console is bound to loopback device and therefore unavailable outside to the network):

$ ssh oracle-xe-server -L 8081:localhost:8080

While keeping this proces running (or keep it on background via `screen` or `nohup`), you may now login to Oracle XE web administrations console on your local computer: http://localhost:8081/apex/. Please notice that your ssh daemon on your local computer should allow TCP forwarding: AllowTcpForwarding yes in your sshd configuration file.

SQL Remote connection management

- By default OracleXe installation does not allow sql network connections to your XE database. To enable it, logon to web management console and enable "Remote connections": "Administration->enable "Available from local server and remote clients"-> press "Apply Changes". The same procedure can also be done from the commandline:

$ sqlplus -S system/password@//localhost/XE <<!
EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE); 
EXIT;
/
!

If your SQL client uses a Oracle Instant client installation, the SQL connection can be obtained by entering:

[atrakic@adm2-43 ~]$ sqlplus username/password@//oraclexe.hostname.or.ip//XE

If somewhat connection is refused (due to firewall restrictions) you can use same ssh trick as done previously:

$ ssh oracle-xe-server -L 1512:localhost:1512

where your sql connection should be like this:

[atrakic@adm2-43 ~]$ sqlplus username/password@//localhost//XE

User management

Add user

- In order to add single sql user with non-dba privilages to OracleXE execute following commands:

--
-- Contents of: this_script.sql
--
CREATE USER myuser IDENTIFIED BY the_password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;
CREATE ROLE myconnection;
GRANT CREATE session, CREATE table, CREATE view, CREATE procedure, CREATE synonym TO myconnection;
GRANT myconnection TO myuser;
/

You can run this SQL script with:

# su - oracle 
oracle@server:~$ sqlplus / as sys
SQL> @this_script.sql
SQL> exit;

Alternatively use web managment console to add new Oracle XE users accordingly.

Delete user

- For deleting a sql users from OracleXE use web management console or execute following commands from the commandline:

# su - oracle 
oracle@server:~$ sqlplus / as sys
SQL> drop user myuser cascade;
SQL>exit;

Run sample applications

Unlock sample HR user and proceed as explained in 'Getting started guide'.

Run HR schema on ruby on rails

Is explained further in this article.

Resources

- For more info about OracleXE read: Oracle Database 10g Express Edition Tutorial.