The unpleasant installation process of APEX5 in 12c

Since the early days of HTML DB the installation (or updating) process stayed almost the same. You could update your APEX database environment in just a few simple steps, this worked very stable for years.  With the releases of Oracle Database 12c and Apex 5 this easy handling changed for the worse.

This blogpost is about performing a new, clean APEX5 installation in a 12c database. This apparently simple task already contains some unpleasant surprises. This blogpost is not about migrating existing apex environments into 12c, these scenarios would go beyond its scope: the database architectures (11g, CDB 12c, Non-CDB 12c, PDB 12c), the different migration paths among each other, different APEX version levels and not to mention what happens when you plug it into another cdb environment. Administrators won't get quickly bored in 12c.

Make your choice, install APEX in a CDB or a PDB?
The latest database release 12.1.0.2 is shipped with APEX version 4.2.5, installed in the root container (CDB). Accordingly, one of the first steps will be an upgrade to APEX version 5. The "Non-CDB 12c"-architecture is already deprecated again, so you have to decide, if it's better to upgrade the existing CDB installation or to perform a new installation in a PDB.

If you have plans to actively use the multitenant option you should (at least) read the whitepaper Deploying and developing Oracle APEX with Oracle DB 12c and the Installation guide to make the decision. The recommended way is, according to the current 12c database documentation, to install APEX in a CDB. This enables centralized management and ensures every PDB runs in the same version. This recommendation has changed in the APEX 5 documentation - for the majority of use cases, now APEX should be better installed in PDB. Recently the blog post Why you should remove APEX from the CDB$ROOT also strongly recommends to install APEX in PDB, mainly to achieve higher flexibilty.


Installation in a CDB
You need DB patch no. 20618595 to upgrade APEX from version 4.2 to version 5 in a CDB. I won't go in detail here, the installation procedure is described in blogpost Upgrading to APEX 5.0 in your Oracle Database Release 12.1 CDB by Jason Straub. People with no greater affinity to DBA activities, will probably have some difficulties with the implementation of the necessary installation tasks.

The required patch is only available for Linux and AIX, although in the meantime APEX5 was released over half a year ago. In consequence, if you're on Windows, Solaris or HP-UX and followed conscientiously the advices from Oracle, this means you upgraded early to db 12c (premier support for 11gR2 ended in january 2015) and migrated your apex environment to CDB, you still have no upgrade path to apex 5. In this case, the only (undocumented) option is to deinstall your (productive?) APEX environment and perform a new, fresh APEX installation.

Installation in a PDB
At first the default apex installation has to be removed from the CDB before you can start with the APEX5 installation in PDB. Browsing the Installation guide brings you quickly to the section Uninstalling Application Express from a CDB. After executing the mentioned script "apxremov.sql", everything looks fine at the first glance. Though a message is displayed to scan all the logfiles for ORA-errors, the console output shows no errors. But when you examine the logfiles, you'll see that the script tries to remove an APEX5 installation. Of course this doesn't exist yet, your installation still resides under the schema APEX_040200.

With the release of 12c the perl utility "catcon.pl" was introduced. It allows you to run SQL scripts in multitenant environments, the way the APEX removal script was also executed. Obviously the tool doesn't offer the option (or at least no script known to me used it yet) to hand over fatal errors to the console output. It's therefore all the more unfortunate that the tool creates already with one single execution not less than 34 different logfiles. It would have been desirable, Oracle had involved a better quality assurance for such an essential, new administration tool.

Back to the failed APEX deinstallation: so we need a script to remove APEX4.2 from the CDB. This can be found in the APEX distribution delivered with database installation at $ORACLE_HOME/apex. If you execute the "apxremov.sql" from this location now, your APEX_040200 schema will still stay alive. In Version 4.2 the scripting names are different. "apxremov.sql" also exists there, but it doesn't take any effect in a CDB. You'll have to execute a script called "apxremov_con.sql".

------------------------------------------------------
Update 04.12.15
There is no need to set the following undocumented parameter, just execute the correct script "$ORACLE_HOME/apex/apxremov_con.sql". Thanks to Jason for clarifying this.
------------------------------------------------------
When executing this script, you'll hit the error "ORA-28014: cannot drop administrative users". To avoid this error, you have to set the undocumented database parameter "_oracle_script". As you might know, using undocumented '_' parameters can violate your Oracle support contract. Those parameters should only be used when Oracle tells you to use them - but no words about that in the release notes, installation guide or at support.oracle.com. So the official way would be to create a service request at Oracle support. Otherwise, risking to violate your support contract and execute the following statement:
      alter session set "_oracle_script"=true;
Now your APEX4.2 installation should be removed successfully and you can start with your APEX5 installation.

Conclusion

APEX administrators are facing new challenges with the 12c database architecture. Just a simple upgrade from APEX 4.2 to 5 in 12c is anything but a quick and casual process. Probably not just a few guys might have thought "Let me try the latest releases, no big deal to install 12c and Apex 5!" - only to become quite frustrated after some time. Oracle should find a remedy, at least the documentation or release notes should point out the pitfalls. Of course the APEX development team, an absolutely passionate and outstanding team at Oracle, are rather dependent on the features (and patches) the Database development team delivers.
With the future release of DB 12cR2 the handling will probably get easier again - at least if it's shipped with installed APEX 5 in PDB$SEED then.

/* Example how to remove the default APEX 4.2.5 installation 
   from CDB and install APEX 5 in a PDB */

/* Navigate to APEX 4.2 directory (from database installation) */
oracle@srv> cd $ORACLE_HOME/apex

/* Open SQLPlus */
oracle@srv> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 27 19:05:41 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

/* Allow administration of common cdb users */
/* not necessary!
SQL> alter session set "_oracle_script"=true;

Session altered.
*/

/* Remove APEX 4.2 from CDB */
SQL> @apxremov_con.sql;

[...]

SQL> exit

/* Navigate to your APEX5 installation directory */
oracle@srv> cd /opt/oracle/install/apex5

/* Open SQLPlus */
oracle@srv> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 27 19:05:41 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

/* Switch to your pdb */
SQL> alter session set container=[pdb_name];

Session altered.

/* Install APEX5 */
SQL> @apexins.sql [apex_tablespace] [files_tablespace] [temp_tablespace] [images]

[...]