Upgrading to APEX 5 on Oracle XE 11g

It’s a Bank Holiday weekend here in the UK.
This is usually a time for doing odd-jobs as a distraction from watching the rain come down.
This time around, rather than subject you to another lament about the Great British Summer ( or lack thereof), I’m going to go through the steps needed to install APEX5 on Oracle 11gXE.

Now, I know that the documentation doesn’t mention Express Edition.
I also know that the instructions that Oracle do have for upgrading APEX on XE haven’t yet been updated to account for APEX5.
I know this because I’ve spent a wet Bank Holiday finding this stuff out the hard way so that (hopefully), you don’t have to.
What I’m going to cover here is :

• Pre-installation checks
• Getting APEX5
• Installation
• Configuration

I would say “let’s get cracking before the sun comes out”, but that would only give us until around the second week in July…

The environment

I’m dong this on Oracle Database Express Edition 11gR2.
As I’m doing this on a Linux machine, you may see the odd indication of this but the essential steps outlined here will apply for all Operating Systems
I’m starting with a clean installation of Oracle XE, so the current version of APEX is 4.0. However, these steps should still be valid when upgrading from any APEX4x version.

Incidentally, if you really want to hang on to the default XE Database Management Application, you’ll probably want to have a look at the steps required to back it up prior to upgrade.

Pre-Installation checks

The documentation details several checks. However, as we’re running on XE11g, we don’t have to worry too much about some of them.

If you really want to double-check….

Oracle Database Version

The minimum version required is 11.1.07. XE runs 11.2.0.2 as we can see with the following query in SQL*Plus :

select banner
from v$version
where banner like 'Oracle Database%';

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> 

Oracle XML DB

That’s there as well, by default…

select comp_name, version, status
from dba_registry
where comp_id = 'XDB';

COMP_NAME					   VERSION			  STATUS
-------------------------------------------------- ------------------------------ --------------------------------------------
Oracle XML Database				   11.2.0.2.0			  VALID

Web Listener Requirements

Oracle XE ships with the Embedded PL/SQL Gateway by default.
Provided you can hit the Database Home Page (e.g. by clicking on the Getting Started With Oracle Database 11g Express Edition desktop shortcut), you’re good to go.

There are some pre-requisites that you will need to verify.

Memory Target Setting

Once again, on a fresh XE11g install, you shouldn’t have any problems.
In memory terms, the smallest machine I’ve installed XE11g on had a total of 1GB RAM. Even in an environment as constrained as this, the MEMORY_TARGET should still meet the minimum requirement of 300MB.

To check, login to SQL*Plus and ….

SQL> show parameter memory_target

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
memory_target			     big integer 1G
SQL> 

Space Requirements

The easy bit first. If you’re planning to download the English Language Only version of APEX5, you’ll need 250MB of space on disk.
If you’re going for the full version, this goes up to 630 MB.

At this point, the installation instructions then start talking about the “Oracle Application Express tablespace”.
This is simply the default tablespace for the schema that will be the APEX owner. Once the installation happens this will be a schema called APEX_050000. It will be created with SYSAUX as it’s default tablespace. This is exactly the same as for the existing APEX04000 user that shipped with XE11g. Incidentally, we’ll also need to know the user’s temporary tablespace for the installation, so we may as well verify both of them now…

SQL> select default_tablespace, temporary_tablespace
  2  from dba_users
  3  where username = 'APEX_040000';

DEFAULT_TABLESPACE	       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYSAUX			       TEMP

SQL> 

The amount of space required in SYSAUX is 220MB plus 60MB per additional language installed.
There is also a requirement for 100MB in the SYSTEM tablespace.

Working out how much space is available isn’t entirely straightforward. However, we can get most of the way with the following query :

select tablespace_name,
  file_name,
  (maxbytes - bytes) / 1024/1024 as "Available Space MB",
  autoextensible
from dba_data_files
where tablespace_name in ('SYSAUX', 'SYSTEM')
/
TABLESPACE_NAME 	       FILE_NAME					  Available Space MB AUT
------------------------------ -------------------------------------------------- ------------------ ---
SYSAUX			       /u01/app/oracle/oradata/XE/sysaux.dbf			  31677.9844 YES
SYSTEM			       /u01/app/oracle/oradata/XE/system.dbf				 150 YES

SQL> 

If you’re query returns an Available Space MB figure less than the requirements, don’t worry too much.
Provided the tablespace is Autoextensible and there is enough space on disk, it will automatically grow as it needs more space.

Browser Version

The minimum requirements for Web browsers are :

• Firefox version 35
• Chrome version 40
• Safari version 7
• IE version 9

Getting APEX5

Right, once you’re happy with the pre-requisite steps, head over to the OTN APEX5 download page and download you’re required version.
This will be one of :

• Oracle Application Express 5.0 – All languages
• Oracle Application Express 5.0 – English language only

I’ve gone for the English language only version.

As mentioned previously, the Download Page does state that :

“Application Express 5.0 can also be used with Oracle Database 11g Express Edition (XE), but is supported only through the OTN discussion forum, and not through Oracle Support Services.”

However, the installation instructions page it links to has yet to be updated for APEX5 at the time of writing.

Anyway, I now have a file called apex_5.0_en.zip in my Downloads directory.

As I’m on Linux, I’m going to unzip and deploy this to the ORACLE_BASE directory (/u01/app/oracle).
To avoid any issues with file permissions, I’ll do this as the oracle OS user.

I should point out that it doesn’t really matter where you deploy the files to. Also, you don’t have to be oracle to do this.
I’ve just done it this way to keep things simple.

cd $HOME/Downloads
sudo su oracle
unzip -d /u01/app/oracle apex_5.0_en.zip

You’ll then see something like …

...
  inflating: /u01/app/oracle/apex/core/template.plb  
  inflating: /u01/app/oracle/apex/core/dev_grants.sql  
  inflating: /u01/app/oracle/apex/apxsqler.sql  
  inflating: /u01/app/oracle/apex/apxprereq.sql  
  inflating: /u01/app/oracle/apex/apxupgrd.sql  
  inflating: /u01/app/oracle/apex/apxconf.sql  
  inflating: /u01/app/oracle/apex/coreins5.sql  
  inflating: /u01/app/oracle/apex/apxdvins.sql  
  inflating: /u01/app/oracle/apex/apxchpwd.sql  
 extracting: /u01/app/oracle/apex/apxexit.sql  
  inflating: /u01/app/oracle/apex/catapx.sql  
  inflating: /u01/app/oracle/apex/apxe102.sql  

After that, you should have a sub-directory call apex where you’ve unzipped the file.
NOTE – you can stop being the oracle user now.

Installation

A bit of housekeeping to start with – we need to make sure that the APEX_PUBLIC_USER database account is unlocked :

select account_status
from dba_users
where username = 'APEX_PUBLIC_USER';

If the account_status is LOCKED then…

alter user apex_public_user account unlock;

User altered.

select account_status from dba_users where username = 'APEX_PUBLIC_USER';

ACCOUNT_STATUS
--------------------------------
OPEN

SQL>       

NOTE – strictly speaking, you should also perform this check for the ANONYMOUS user. However, if Oracle XE is newly installed, or if you’re running the Embedded PL/SQL Gateway, it should be unlocked.
If you want to satisfy yourself that this is, in fact, the case :

select account_status
from dba_users
where username = 'ANONYMOUS';

Loading APEX5 into the database

As of APEX5, we now have the option of installing just the APEX runtime…but where’s the fun in that ?
We want the full-blown development environment…

The script we need to run to do the installation – apexins.sql – takes three parameters :

• the default tablespace of the APEX owner schema
• the default tablespace of the FLOWS_FILES schema
• a temporary tablespace in the database
• a virtual directory for APEX images

We already know that the default tablespace for the APEX owner is SYSAUX.
We also know that the temporary tablespace is called TEMP.
As for the FLOWS_FILES schema…

SQL> select default_tablespace
  2  from dba_users
  3  where username = 'FLOWS_FILES';

DEFAULT_TABLESPACE
------------------------------
SYSAUX

SQL> 

As for the virtual directory – “/i/” always seems to work.

Now, change directory to the apex directory you’ve created as part of the unzip step, and connect to the database as sys as sysdba.

cd /u01/app/oracle/apex 

sqlplus sys as sysdba

…and run the script…

@apexins.sql SYSAUX SYSAUX TEMP /i/

After several minutes worth of messages whizzing up your screen you’ll get :

PL/SQL procedure successfully completed.

-- Now beginning upgrade. This will take several minutes.-------
-- Ensuring template names are unique -------

…finally, you’ll get…

Thank you for installing Oracle Application Express 5.0.0.00.31

Oracle Application Express is installed in the APEX_050000 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
http://host:port/apex/apex_admin     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex/apex_admin     (Oracle REST Data Services)

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex     (Oracle REST Data Services)


PL/SQL procedure successfully completed.






1 row selected.

Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
mike@mike-Aspire-E3-112 /u01/app/oracle/apex $ 

Configuration

Re-setting the ADMIN password

The next step is to set the APEX ADMIN password. Note that, even if you’ve already done this for the previously installed APEX version, you’ll need to do it again here, using the script that’s shipped with this version of APEX.
Also, despite any configuration changes you may have made to the APEX password complexity rules, the password you set will need to conform to the following :

• Password must contain at least 6 characters.
• Password must contain at least one numeric character (0123456789).
• Password must contain at least one punctuation character(!”#$%&()“*+,-/:;?_).
• Password must contain at least one upper-case alphabetic character.
• Password must not contain username.

Bearing this in mind, connect to the database again as SYS AS SYSDBA and you’ll be prompted as follows….

@apxchpwd.sql

================================================================================
This script can be used to change the password of an Application Express
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN] 
User "ADMIN" exists.
Enter ADMIN's email [ADMIN] 
Enter ADMIN's password [] 
Changed password of instance administrator ADMIN.

Load Images

One final step – we need to load the images.
As we’re running the Embedded PL/SQL Gateway, we’ll need to use the apex_epg_config.sql script.
This script takes, as a parameter, the name of the directory that you’ve extracted the apex zip into – i.e. without the /apex directory itself. As you can see from the output below, it does take a while (8 minutes in this case) :

@apex_epg_config.sql /u01/app/oracle

SQL> @apex_epg_config.sql /u01/app/oracle

. Loading images directory: /u01/app/oracle/apex/images
timing for: Load Images
Elapsed: 00:08:00.92
SQL> 

Post Installation Steps

Just before we can go and play with our shiny new APEX version, we need to do a little tidying.
First of all, confirm the port that the PL/SQL Gateway is listening on :

select dbms_xdb.gethttpport
from dual;

Then, finally, shutdown and re-start the database.

Once it comes back up, point your browser to :

http://localhost:port/apex/apex_admin

…where port is the port number returned by the query above.

Login as user ADMIN with the password that you set for admin when you ran apxchpwd.sql. If all goes well, you should now see…

It may be raining outside, but at least you now have a cosy APEX5 installation to play with.