Monday, May 18, 2009

OracleXE and Apex 3.2 on Ubuntu 9.0.4

Introduction
I'm a huge fan of Apex. It is a very quick and good way to develope data driven applications. With it you can make extreamly affordable applications. Especially if it is compined with OracleXE and a free of charge Linux like Ubuntu. This is what I'm going to do in this short how-to. I'm going to install OracleXE (10g) on Ubuntu 9.0.4. Apex is bundled with OracleXE, so no installation is needed for that. How ever I'm going to upgrade the Apex installation from 2.1 to 3.2

Requirements
First of all you need 32bit os. Install Ubuntu 9.0.4, the desktop version. I had some problems in previous version of Ubuntu with the server release in regards to OracleXE, thats why I'm using the desktop version. Just make a default installation. I did mine on VmWare, running of my Windows XP gaming pc.

Then grab Oracle Database 10g Express Edition (Universal), Download oracle-xe-universal_10.2.0.1-1.0_i386.deb from oracle.com. For the uninitiated this is Oracles free database. You can use it with 1gb memory, 1CPU and 4gb of data for (almost) any purpose free of charge. The full lisence can be found her.

Get Apex 3.2 for oracle.com

Installing OracleXE
sudo aptitude install libaio1
sudo dpkg -i oracle-xe-universal_10.2.0.1-1.0_i386.deb
When running the above the installer might abort and say that you do not have enough SWAP. If it does run the following four lines. Where 700 is changed for the amount of SWAP you are missing.
sudo dd if=/dev/zero of=/SWAP.img bs=1M count=700
sudo losetup /dev/loop7 /SWAP.img
sudo mkswap /dev/loop7
sudo swapon /dev/loop7
Note that after the first reboot this SWAP image is no longer going to be used, which is fine. We only need it during the install. However, ff you want to keep it you either have to add it in fstab or run the following after each boot.
sudo swapon /dev/loop7
When then installer finishes it tells you to run the post install, do that:
sudo /etc/init.d/oracle-xe configure
It will ask you a bunch of question, if your unsure of the answer just hit enter and you'll get the default. Well you need to enter a password though.

http://127.0.0.1:8080/apex is usually the url for your new database. From this web interface you can manage some aspects of the database, but I'm a sqlplus fan. Note the 127.0.0.1 address is only valid from your computer. If you need to reach the database from a different computer substitute 127.0.0.1 for the ip or domain of your computer where OracleXE is running.

Note that the installer created a database with SID = XE for you. This might come as a surprise to some experienced DBA's as Oracle usually does not do this. You have to do it your self after the install.

Upgrading Apex 2.1 -> 3.2
Unzip the software you downloaded from Oracle.com
unzip apex_3.2.zip
sudo mv apex /usr/lib/oracle/xe/app/oracle/product/
sudo chown oracle:dba -R /usr/lib/oracle/xe/app/oracle/product/apex
The installer for the databse created a oracle user, so lets login with it and get sqlplus working:
sudo su - oracle
Set the path and oracle environment variables:
export PATH=$PATH:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/
export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_SID=XE
It's time to upgrade apex. When you installed OracleXE it came with a apex installation, but we want to have the newest, Apex 3.2.

This will take hand of the pre-install requirement:
cd /usr/lib/oracle/xe/app/oracle/product/apex
sqlplus / as sysdba
ALTER SYSTEM SET SHARED_POOL_SIZE='100M' SCOPE=spfile;
shutdown immediate
startup
@apexins SYSAUX SYSAUX TEMP /i/
The last line starts the update scripts. It will run for a while, so be patient.

Since we are running apex on OracleXE we have to do a couple of special steps, these are the ones below. Note that password should be substituted for what ever you want the admin password to be.
@apxchpwd
@apxldimg.sql /usr/lib/oracle/xe/app/oracle/product/
@apxxepwd.sql password
If you want your applications to be available from other computers run:
exec dbms_xdb.setListenerLocalAccess(l_access => FALSE);
There are a few more settings you might want to change, they can be found her.

The administration page for your apex can be found at: http://127.0.0.1:8080/apex/apex_admin

Username is admin, and password is the one you sett when runing @apxchpwd

The application interface that user are going to use when developing applications are at:
http://127.0.0.1:8080/apex/apex

When the application is done the final applications will be available at:
http://127.0.0.1:8080/apex/f?p=#APPNUMBER#
Where #APPNUMBER# is the number of the application, for instance 100, http://127.0.0.1:8080/apex/f?p=100


More documentation and lots of good information can be found at: