Oracle XE on Debian

Posted by trakic on Wed 16 Aug 2006 at 11:45

Tags: ,

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.

 

 


Posted by trakic (87.72.xx.xx) on Wed 16 Aug 2006 at 20:49
[ View Weblogs ]
Yet another hint to add:

If Oracle XE service is installed on a host behind the firewall, mod_proxy would do the magic (192.168.1.2 is behind the firewall):

<ifmodule mod_proxy.c>

# Reverse Proxy:
ProxyRequests Off

ProxyPass /apex http://192.168.1.2:8080/apex
ProxyPassReverse /apex http://192.168.1.2:8080/apex

ProxyPass /i http://192.168.1.2:8080/i
ProxyPassReverse /i http://192.168.1.2:8080/i

</ifmodule>

/trakic

[ Parent | Reply to this comment ]

Posted by pyuen22 (61.244.xx.xx) on Sun 1 Oct 2006 at 03:47
After installed the package, i found everything is installed at /usr partition.

i.e. a debian policy bug.

Since i put /usr partition as a ro partition. Anyone have idea how to separate those back to fulfill a debian policy; so that i can still keep my security setting as it is.

~Pat.

[ Parent | Reply to this comment ]

Posted by Anonymous (130.226.xx.xx) on Mon 2 Oct 2006 at 08:18
Take a good look at `man apt.conf` and use Pre-Invoke Pre-Install-Pkgs vars.
/Admir

[ Parent | Reply to this comment ]

Posted by OraUser (82.227.xx.xx) on Fri 11 May 2007 at 09:39
Hi

I'm stuck in Oracle9i installation on Debian Etch.

http://forums.oracle.com/forums/thread.jspa?threadID=505764&t start=0

Some help?

[ Parent | Reply to this comment ]

Posted by trakic (87.72.xx.xx) on Fri 11 May 2007 at 18:44
[ View Weblogs ]
Hi,

Is your CPU 64bit? If so please consider using a response file instead (...you'll find one at *.zip file)...

BR.Admir Trakic,Copenhgen, Denmark - OracleXE,Oracle10g on GNU/debian etch.

[ Parent | Reply to this comment ]

Posted by OraUser (82.227.xx.xx) on Mon 14 May 2007 at 01:15
Thank you Trakic,

I'm on 32bit CPU.
Finaly i achieved the installation, the day of my last post.

[ Parent | Reply to this comment ]

Posted by Anonymous (80.109.xx.xx) on Sat 8 Mar 2008 at 02:26
There's a bug in the debian Package. The postinst script makes use of the gnu bc tool, but this is packages is not listed as a dependency. So if for some reason, your package won't install correctly (no error messages ...), perhaps "apt-get install bc" and reinstalling helps ...

[ Parent | Reply to this comment ]

Posted by linulin (78.37.xx.xx) on Sat 8 Mar 2008 at 12:18

It was fixed a long time ago.

$ aptitude show oracle-xe |grep 'Version\|Depends'
Version: 10.2.0.1-1.1
Depends: libc6 (>= 2.3.2), libaio (>= 0.3.96) | libaio1 (>= 0.3.96), bc

--
...Bye..Dmitry.

[ Parent | Reply to this comment ]

Posted by Anonymous (80.109.xx.xx) on Sat 8 Mar 2008 at 14:21
Okay ...
I downloaded the deb-package manually from their website
http://www.oracle.com/technology/software/products/database/xe/ht docs/102xelinsoft.html,

and there, they are hosting still the old version ...

apt-cache show oracle-xe|grep 'Version\|Depends'
Version: 10.2.0.1-1.0
Depends: libc6 (>= 2.3.2), libaio (>= 0.3.96) | libaio1 (>= 0.3.96)


[ Parent | Reply to this comment ]

Posted by Anonymous (62.111.xx.xx) on Thu 23 Jul 2009 at 14:04

[ Parent | Reply to this comment ]

Posted by masokis (120.140.xx.xx) on Wed 22 Sep 2010 at 11:30
dear writer.. i try to do as you teach me here.. but i found a problem when i apt-get update... here my log at error part after that command:

root@OshirixNET:/home/masokis# apt-get install oracle-xe-client
Reading package lists... Done
Building dependency tree
Reading state information... Done
oracle-xe-client is already the newest version.
The following packages were automatically installed and are no longer required:
python-pyinotify libjaxp1.3-java-gcj
Use 'apt-get autoremove' to remove them.
0 upgraded, 0 newly installed, 0 to remove and 358 not upgraded.
1 not fully installed or removed.
After this operation, 0B of additional disk space will be used.
warning, in file '/var/lib/dpkg/status' near line 5356 package 'igelle-connector':
missing maintainer
Setting up mpd (0.15.12-1) ...
Starting Music Player Daemon: mpd/usr/bin/mpd: error while loading shared libraries: libva-0.31.0.6.so.1: cannot open shared object file: No such file or directory
failed!
invoke-rc.d: initscript mpd, action "start" failed.
dpkg: error processing mpd (--configure):
subprocess installed post-installation script returned error exit status 127
Errors were encountered while processing:
mpd
localepurge: Disk space freed in /usr/share/locale: 0 KiB
localepurge: Disk space freed in /usr/share/man: 0 KiB
localepurge: Disk space freed in /usr/share/gnome/help: 0 KiB
localepurge: Disk space freed in /usr/share/omf: 0 KiB
localepurge: Disk space freed in /usr/share/doc/kde/HTML: 0 KiB

Total disk space freed by localepurge: 0 KiB

E: Sub-process /usr/bin/dpkg returned an error code (1)

what i must do now ?? thanks for help.. :)

=================================
Visit me : http://www.masokis.com

[ Parent | Reply to this comment ]

Posted by Anonymous (194.182.xx.xx) on Wed 22 Sep 2010 at 11:59
If you read output more carefully this error has nothing to do with oracle xe, and you need to have dpkg in clear state, meaning purge troublesome package mpd:

`dpkg --purge mpd`.

BTW: If you only want XE server then you only need to `apt-get install oracle-xe`.

Br.Admir Trakic

[ Parent | Reply to this comment ]

Posted by masokis (120.140.xx.xx) on Fri 24 Sep 2010 at 17:05
i already finish the installation of XE. now i can access it by web panel.
but how about sqlplus ? i type it in terminal.. and it could not be found.

here..my log:
root@OshirixNET:/home/masokis# sqlplus
bash: sqlplus: command not found

i also try to search in apt.. but i found something that different
root@OshirixNET:/home/masokis# apt-cache search sqlplus
sqlline - JDBC command-line utility for issuing SQL
root@OshirixNET:/home/masokis#

hemm.. where is my mistake ?

if you want to see, how i install.. i aalready put on my weblog as my reference. but it writing in malay. use google translation..etc to read.
but the command is usual...

could it because my wrong installation ?
thanks you..for read. and i appreciate if we can help each-others :)
=================================
Visit me : http://www.masokis.com

[ Parent | Reply to this comment ]

Posted by dsmith004 (71.227.xx.xx) on Sun 16 Dec 2012 at 03:23
I have tried a number of installation methods from apt-get to downloading and installing the packages, which worked best but failed with this message: oracle-xe-client : Depends: libaio (>= 0.3.96) but it is not installable or libaio1 (>= 0.3.96) but it is not going to be installed oracle-xe-universal : Depends: libaio (>= 0.3.96) but it is not installable or libaio1 (>= 0.3.96) but it is not going to be installed I did a few web searched on the dependancies but got stuck. Tips?

[ Parent | Reply to this comment ]

Sign In

Username:

Password:

[Register|Advanced]

 

Flattr

 

Current Poll

What do you use for configuration management?








( 157 votes ~ 0 comments )