Safely Upgrading to Oracle APEX 18.1

Oracle Application Express (APEX) 18.1 has been out now for a couple of days.

I typically don't wait long before doing the upgrade, as with every new release you get many new features I want to use. Also if you want to stay on top of the game, you just want to move as fast as you can. I typically start testing the Early Adopter releases and then when apex.oracle.com gets updated, I do more testing, but having it on your own system with applications that are used day-in-day-out is a different level.

So I thought to share how we update our environment in a safe way.

The first thing we do is put our maintenance pages on. We use an Apache Reverse Proxy in front of Apache Tomcat with ORDS which is connected to the Database. By specifying some ErrorDocuments the maintenance pages are being used the moment there's an error.

For example, you can add this to your httpd.conf:

ErrorDocument 404 https://s3.amazonaws.com/apexRnD/website/maintenance.html
ErrorDocument 500 https://s3.amazonaws.com/apexRnD/website/maintenance.html
ErrorDocument 503 https://s3.amazonaws.com/apexRnD/website/maintenance.html

When you update APEX you don't want any incoming connections, so we stop Apache Tomcat with ORDS. At that moment the Reverse Proxy gets an error and the ErrorDocument kicks in and serves the Maintenance page. This way if people want to use the system, they know we are working on it.

We use Oracle Database 12c container database and pluggable databases. We want to run different versions of APEX next to each other because we have to test APEX Office Print against all APEX releases. Our customers use different releases of Oracle APEX too, so when we do custom development we have to stick to their version, so we really need all supported APEX versions somewhere.

Our setup was like this before the APEX 18.1 upgrade:
- CDB: cdb
- PDB with APEX 4.2: apex42_pdb
- PDB with APEX 5.0: apex50_pdb
- PDB with APEX 5.1 (main - our most used one): apex_pdb

With every new major release of APEX we clone our main PDB and give it the name of the APEX release, so we keep the APEX release we are on.

The steps to clone a pluggable database in Oracle DB 12.1 (SQL*Plus or SQLcl):

alter pluggable database apex_pdb close immediate; 
alter pluggable database apex_pdb open read only; 
create pluggable database APEX51_PDB from APEX_PDB file_name_convert=('/u01/app/oracle/oradata/cdb/APEX_PDB/','/u01/app/oracle/oradata/cdb/APEX51_PDB/') PATH_PREFIX='/u01/app/oracle/oradata/cdb/APEX51_PDB'; 
alter pluggable database apex51_pdb open; 
alter pluggable database apex_pdb close immediate; 
alter pluggable database apex_pdb open;



After the above we have a situation like this:

- CDB: cdb
- PDB with APEX 4.2: apex42_pdb
- PDB with APEX 5.0: apex50_pdb
- PDB with APEX 5.1: apex51_pdb

- PDB with APEX 5.1: apex_pdb  - will be upgraded to APEX 18.1 (main - our most used one)

Note: if you use Transparent Data Encryption (TDE) you have to perform some additional steps.

The installation of APEX 18.1 on the database side are basically 5 steps:

1) download the software from OTN

2) unzip in /tmp folder and cd into the /tmp/apex directory

3) run SQLcl or SQLPlus as sys as sysdba and connect to the apex_pdb container

alter session set container=APEX_PDB;

4) run the apexins command

@apexins SYSAUX SYSAUX TEMP /i/

In my environment the script took about 23 minutes to complete:

Note: the APEX 18.1 scripts are in 3 phases and the wizard shows information and timings for all phases and at the end also a global timing for the whole. If you want to have less downtime you can run the phases separately - see the doc Maximizing Uptime During an Application Express Upgrade. 

5) run the apex_rest_config command

@apex_rest_config.sql

The pluggable database is ready now and contains APEX 18.1.

During the APEX upgrade and as we already have downtime, we typically make use of that time to upgrade the other components in a typical Oracle APEX stack, namely the web server (e.g. Apache Tomcat) and ORDS (Oracle REST Data Services). Another advantage of going with a new version of your middleware is that you have your working Apache Tomcat and ORDS untouched, so in case you have to rollback there's nothing to do. Note that you can prepare most of the following commands beforehand.

Upgrading the Application (web) Server:

Download Apache Tomcat.

Unzip in your folder of choice.

That is basically all you have to do (on Linux) :)

Download Oracle REST Data Services (ORDS).

Unzip in your folder of choice and cd into it.

Run: java -jar ords.war install advanced

and follow the wizard to install ORDS in APEX_PDB
* make sure you use different config dirs for ORDS in order to run multiple versions of ORDS and APEX

Once done, copy the ords.war into /apache-tomcat-version/webapps

Next copy the images folder of the apex directory to /apache-tomcat-version/webapps:

cp -R /tmp/apex/images /apache-tomcat-version/webapps/i

Start Apache Tomcat:
cd bin 
./startup.sh

Restart your Apache Reverse Proxy (and optionally take out the ErrorDocuments)

/sbin/service httpd graceful

It sometimes happens to me that APEX isn't working the first time when I run it.

Then I debug the connection and check the logs of the web server.

Another thing that often helps, is running ORDS in standalone mode as it will give me clear messages. e.g.

WARNING: *** jdbc.MaxLimit in configuration |apex|| is using a value of 10, this setting may not be sized adequately for a production environment ***
WARNING: *** jdbc.InitialLimit in configuration |apex|| is using a value of 3, this setting may not be sized adequately for a production environment ***
WARNING: The pool named: |apex|al| is invalid and will be ignored: The username or password for the connection pool named apex_al, are invalid, expired, or the account is locked
WARNING: The pool named: |apex|rt| is invalid and will be ignored: The username or password for the connection pool named apex_rt, are invalid, expired, or the account is locked


The above warning remembers me to change some parameters of ORDS. Or I could look-up my previous configuration and copy those parameters. The above warning also indicates our APEX_LISTENER user can't connect (apex_al), so we need to fix that by specifying the correct password. For example, for apex_rt I forgot which user it was, but it's easy to find by navigating to the ords config folder and view the apex_rt file. It will tell the user in the file.

Now we should have APEX 18.1 up-and-running :)

We also want to access the previous versions of APEX. So I copy the older ordsxx.war files to the new web server, but I name those ords51.war, ords50.war, so the URL I access to the different APEX versions becomes https://www.apexrnd.be/ords50/ or https://www.apexrnd.be/ords51/
https://www.apexrnd.be/ords/ is always the latest version of APEX. 

The images folder of the older APEX version (5.1) we map to /i51/ (instead of /i/ as that is of APEX 18.1 now). In order to have a different image folder you need to run in apex51_pdb following sql:
SQL> @\utilities\reset_image_prefix.sql


We upgraded our systems this weekend, the second day after 18.1 was released. We followed more or less the above procedure and things went fine. Make sure to test your own apps first before doing the upgrade. Most of our apps were running just fine, but for some, we had to replace some older plugins with new versions or remove the plugins and replace by built-in functionality.

Note: there are many different ways of updating your system. It comes down to see what works for you. What I share works for us, but for example, if you can't afford downtime you probably want to work with standby databases and load balancers. Or if you work with virtual machines or Docker, it might be useful to clone the machine and test things on the entire machine first.