Upgrading to Application Express 5.0 in Your Oracle Database Release 12.1 Multitenant Container Database (CDB)

Woo hoo! Oracle Application Express 5.0 has been released. Read all about it here.

The purpose of this blog post is to provide information on how to upgrade your 12.1 Multitenant Container Databases (CDBs) to APEX 5.0, and specifically ones where you have Oracle Application Express installed in CDB$ROOT (the root), and you are making use of Oracle Application Express 4.2.x in the PDBs in this CDB. There has been confusion on this topic, even from some of my colleagues at Oracle.

You can ignore this blog post if any of the following conditions are true in your situation:

1. You are on a release lower than 12.1
2. You are running 12.1, but it is a non container database (non-CDB)
3. You are running a 12.1 CDB, but you have de-installed Oracle Application Express from the CDB, and you are running Oracle Application Express locally in all or some of your PDBs

Since you are still reading this post, this means you have a 12.1 CDB, where Oracle Application Express is installed in the root, and you are actively using APEX in your PDBs, and you wish to upgrade the root and all of your PDBs to APEX 5.0.

We have tried to make it painfully clear in multiple places in the 5.0 documentation set, and on our known issues page, that if you want to upgrade your 12.1 CDB to APEX 5.0, you must first download and install the database patch number 20618595 prior to upgrading to APEX 5.0.

Below is a step by step guide based on my experience in upgrading one of our 12.1.0.2.0 development environments to Oracle Application Express 5.0.

First, install the latest OPatch into your $ORACLE_HOME, by doing the following:

1. Login to support.oracle.com
2. Click Patches & Updates tab
3. Simple search on patch number 6880880 and Platform Linux x86-64
4. Download the patched titled "Patch patch of version 12.1.0.1.7 for Oracle software releases 12.1.0.x (APR 2015)"
5. Execute the following OS commands

cd $ORACLE_HOME
mv OPatch OPatch_save
unzip /p6880880_121010_Linux-x86-64.zip
$ORACLE_HOME/OPatch/opatch version

OPatch Version: 12.1.0.1.7

OPatch succeeded.

Next, you need to download and apply database patch 20618595.

1. Login to support.oracle.com
2. Click Patches & Updates tab
3. Simple search on patch number 20618595
4. Download either the 12.1.0.1.0 or 12.1.0.2.0 patch. I was on 12.1.0.2.0 so I downloaded that version.
5. Execute the following OS commands

unzip /p20618595_121020_Linux-x86-64.zip
cd 20618595
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Ensure you see "Prereq "checkConflictAgainstOHWithDetail" passed." in the output. If so, proceed with shutting down all services for $ORACLE_HOME.

lsnrctl stop
sqlplus / as sysdba
shutdown immediate
exit

$ORACLE_HOME/OPatch/opatch apply
$ORACLE_HOME/OPatch/opatch lsinventory

You should now see something in your terminal similar to:

Patch  20618595     : applied on Thu Apr 16 13:17:43 PDT 2015
Unique Patch ID:  18723077
   Created on 9 Apr 2015, 15:28:51 hrs PST8PDT
   Bugs fixed:
     20618595

If you do not, do not proceed. Get help from a DBA. Do not attempt to to upgrade to APEX 5.0. Do not pass Go! Do not collect $200.

Now, if you did see output in your terminal similar to the above, do the following.

lsnrctl start
sqlplus / as sysdba
startup
alter pluggable database all open;
exit

cd $ORACLE_HOME/OPatch
./datapatch

You should see output similar to:

    The following patches will be applied:
      20618595 ()

catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_26531.lst
catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any
Installing patches...
Patch installation complete.  Total patches installed: 6

Validating logfiles...done
SQL Patching tool complete on Thu Apr 16 13:49:29 2015

Again, if you do not, and you see any type of error, get help from a DBA and do not proceed until this patch is successfully applied to the database. Once the patch is successfully applied, simply connect to CDB$ROOT as SYSDBA, and run either apexins.sql or apxrtins.sql depending on your installation type. That's it. At the end of the installation your CDB and all PDBs will be successfully upgraded to APEX 5.0. I guarantee it!