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.
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.
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.
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] [...]
