Upgrading to APEX 4.1 on XE 11g

It’s that time of year. Slay bells ringing, children singing…and the UKOUG Conference.
This year, I was lucky to get along to attend the last day in the company of my good friend Alan.

I love going to the Conference. You get the chance to see lots of great presentations about all sorts of things in the Oracle world.
Takeaways from this year? Well, apart from the stress-ball and the cuddly Rhino ( yes, we did have a wander through the exhibition hall as well), I learned quite a bit about Application Express.

Just in case they’re struggling for an angle for APEX in the Oracle marketing department, how about :
“Application Express – Forms 3.0 for the Internet Age”

I suppose I’d better do some explaining fairly quickly before I am taken to task by any APEX aficionados who happen to be reading.

Back in the good old days, when I still had hair, Forms 3 was the character based interface for the Oracle database. A major advance on Forms 2.3, you were able to code actual PL/SQL right into the triggers. Of course, everything ran on the server back then. Forms, the database ( we don’t talk about SQL*Reportwriter…ever !)

APEX has certain similarities to it’s ancestor. The code is stored in the database itself and you can write PL/SQL in it. Of course, it is also “web-aware”. It could easily be thought of as a UI for SQL and PL/SQL…without all that mucking about with Java.

Enough of this Oracle Tech naval gazing. The point of this post is that, if you’ve downloaded Oracle 11g XE, you will have APEX4.0 included. Due to the tiresome reluctance of software vendors to use major release numbers, you may have been under the misapprehension that APEX 4.1 was just a minor tweak. The truth is a rather different.

APEX is maturing rapidly. So, if you’re running XE 11g on a Debian OS ( or even 10g XE), you may very well be interested in getting the latest version of APEX to have a play with…

NOTE – I ran this installation on 11g XE running on Mint.
I’ve tried to highlight any differences you may get when installing on 10gXE, but I haven’t actually done the installation on this database version.

Pre-Installation Checks

There are a few bits and pieces to check here :

• shared pool size
• Oracle XDB version
• PL/SQL Web Toolkit version

Shared Pool Size

The shared pool size needs to be at least 100 MB. Shouln’t be a problem. In SQL*Plus you can check this by :

show parameter shared_pool_size

Alternatively, you can just run this query :

SELECT value
FROM v$parameter
WHERE name='shared_pool_size';

VALUE

--------------------------------------------------------------------------------

0

Hmmm, not quite so straight forward then.

When this parameter is set to 0 then Oracle automatically determines the size of the shared pool.
If you’re running on a 64-bit OS then this should ( according to the documentation) equate to 128 MB. If you’re on a 32-bit OS however, this will default to 64MB.

If you need to change this parameter, then you can simply :

ALTER SYSTEM set shared_pool_size=100M;

If you do need to make this change then it’s probably best to stop and re-start the database at this point to make sure that the additional memory is utilized.

Oracle XDB

The Oracle XDB should be there by default on XE.
Just to check :

SELECT comp_name, version, status
FROM dba_registry
WHERE comp_id = 'XDB';

Provided you get a row back from this query where the status is VALID, you’re good to go.

PL/SQL Web Toolkit

You need to have version 10.1.2.0.6 or later. Once again, this should already be there on XE. If you want to check then :

SELECT owa_util.get_version
FROM dual;

On 11gXE you should get version 10.1.2.0.8.
On 10g XE you’ll probably be on version 10.1.2.0.4.

The APEX 4.1 download does come with files to install version 10.1.2.0.6 and you should be able to run this upgrade by means of the owainst.sql script which will be in the apex/owa directory after you’ve unzipped the download.
If you are going to do this, then I’d suggest you do a full backup of the database beforehand because (a) it’s just good practice and (b) I’ve no idea if this will do what it claims without error on 10gXE.

In fact, even if everything is fine at this point, it’s still a good idea to backup your database before you run the installation… unless you don’t mind losing what you have on there already.

Download Apex 4.1

Next step is to download the Apex4.1 zip file from OTN.

NOTE – if I’m starting to bore you at this point and you’re tempted to follow the “official” installation instructions, the ones for XE are a lot more concise and less confusing than the full-blown APEX installation guide ( which is here).

Once you’ve downloaded the file – apex_4.1_en.zip to give it it’s full name ( I’ve just downloaded the English Language version), you need to copy it somewhere sensible.

In my case, I did the following :

cp apex_4.1_en.zip /u01/app/oracle

Wherever you choose to drop the file, you need to make sure that there are no spaces in the path. Otherwise, the script to install the icons isn’t very happy and you end up with a blank screen…er..so I’ve heard. I mean, I’ve got this friend who tried it. No obviously, I wasn’t dumb enough to do that myself. Ahem.

Anyway, next step is to unzip the file. To save lots of messing about with file permissions and such like, I just switched to the oracle user :

cd /u01/app/oracle
sudo su oracle
sudo unzip apex_4.1_en.zip
cd apex

That last cd was to the newly created apex directory. From this point on, this is where we’ll be running the apex upgrade scripts themselves.
There is one more bit of admin before we get to that, but as the rest of this stuff happens on the database, we can connect now.

At last – the installation

You need to connect to Oracle as SYS as SYSDBA. If you’re the oracle user on the OS then – after making sure that your $ORACLE_SID is set to XE, you should simply be able to do :

sqlplus / as sysdba

If you want to be a bit more boring and conventional ( and haven’t su’d to oracle) then :

sqlplus sys as sysdba@xe

Now, whenever I’m in a multi-database environment, I do like to make sure that I’ve connected to the database I think I have. This makes it so much easier to avoid those hilarious situations where you mistakenly drop a production database or similar.
Anyway, if you’re feeling equally paranoid then :

SELECT name FROM v$database;

This will either set your mind at ease ( in my case by returning ‘XE’) or cause you to quit the SQL session with unseemly haste.

Once we’re on the correct database as SYS then we first need to unlock the APEX_PUBLIC_USER:

ALTER USER apex_public_user ACCOUNT UNLOCK;

Next, we need to find the default and temporary tablespaces for the APEX user in the database ( it should be SYSAUX and TEMP respectively) :

SELECT default_tablespace, temporary_tablespace
FROM dba_users
WHERE username = 'APEX_040000';

NOTE – if you’re running this on 10g XE with APEX 2 installed ( the version that 10gXE shipped with) then the username will be FLOWS_020100. There will also be a FLOWS_FILES user and you will need to make a note of the default tablespace for this user.

Now we can get on with running the scripts.
First up is apexins.sql, which takes three arguments :

• default tablespace of the APEX user (usually SYSAUX)
• the same as the first argument, unless you have the FLOWS_FILES user, in which case it’s the default tablespace of the FLOWS_FILES user( this is also SYSAUX by default)
• the APEX user’s temporary tablespace (usually TEMP)
• a virtual directory for the APEX images ( I used “/i/” like it says in the installation notes)

So, assuming your setup is like the one I’m using :

@apexins.sql SYSAUX SYSAUX TEMP /i/

You’ll be pleased to know that this script creates a logfile in the apex directory with a name in the format installYYYY-MM-DD HH24:MI:SS.log.
You’ll be slightly less pleased to know that it’s a bit big (over 700K). Yes, it doesn’t sound that huge…until you have to wade through it.
Anyway, this script takes a little while to run, especially on my asthmatic, ageing laptop with a whopping 1GB RAM and 2GHz Celeron Processor.

Eventually, I got the cheery message :
“Now beginning upgrade. This will take several minutes”

This did cause me to wonder vaguely what on earth the script had been doing for the previous 25 minutes.
Finally, the script completed and logged me off.

Now to load the images – by means of apxldimg.sql.
This script accepts one parameter – the directory you unzipped apex into ( in my case, /u01/app/oracle).

Reconnect to the database as SYS as SYSDBA and run the script

@apxldimg.sql /u01/app/oracle

The final step is to set the password for the APEX ADMIN user :

@apxchpwd.sql

Enter the new password when prompted (it’s hidden so you won’t see anything when you type it in).
You’ll be asked to reset the password as soon as you use it for the first time so it doesn’t need to conform to the databases password complexity rules at this stage.

Post Installation steps

If you now click on the link on your desktop to the database start page, you may get a bit of a shock.
Instead of the start page, you’ll get the rather unfriendly “Error – ERR-1014 Application not found”.

Don’t worry, you haven’t missed a step ( necessarily). We’ll fix this in a minute.
For now, point your browser at http://localhost:8080/apex/apex_admin.

You will now be presented with a login screen.
Enter admin as the username and the password you’ve just set for the admin user.

At this point, you’ll be asked to set a new password, which will have to conform to the password complexity rules ( unless you’ve disabled them) – i.e. at least one uppercase, and one punctuation character. The password will also need to be a minimum of 8 characters.

Once the password change has been processed, you’ll be asked to connect again using the new password.

You should now see :

Looks like Christmas has come early

Fixing the desktop link

When you install 11gXE, a link to the APEX home page is automatically created on your desktop.
The link calls a script called gettingstarted.sh in $ORACLE_HOME/config/scripts.

In order to fix the link, you need to edit this file.

The line :

/usr/bin/$ihttp://localhost:8080/apex/f?p=4950

needs to change to :

/usr/bin/$ihttp://localhost:8080/apex/

Deb is starting to fret that we haven’t quite got enough alcohol in the house to see us over the festive period, so it looks like I’m going to have to venture out into the cold winter evening and brave the crush at the all-night supermarket. Christmas – it’s for the kids really.