Why you should remove APEX from the CDB$ROOT

on how to customize a Single/Multitenant Database with less options.

But that is not the topic I would like to write about. I came across an pitfall in the past days twice.

Think of having a local APEX (Application Express) application in a standalone database or already inside a PDB. You’d like to plug it into a CDB, regardless if this CDB is local or in the Oracle DBaaS Cloud. But your current APEX is on a lower version than the APEX inside the target CDB.

Let’s assume you’d like to migrate this standalone database or PDB over into the cloud. But as soon as you plug it in – even though you’ll have Oracle Database 12.1.0.2 in both sites locally and in the cloud, you can’t open your PDB in read/write mode. It will open RESTRICTED only.

Then you’ll check PDB_PLUG_IN_VIOLATIONS and get a plug in error:

“APEX mismatch: PDB installed version 4.2.5.00.08 CDB installed version 5.0.0.00.31” 

Reason is simply that an Oracle Database 12.1.0.2 standard deployment will contain APEX 4.2.0 whereas the Oracle Cloud deployment in this scenario uses the more recent APEX version 5.0 already.

Having APEX installed into the CDB$ROOT was not a great idea. Actually it will cause you a lot of trouble and does not make much sense in the idea of Multitenant concept.

I see why it has been done this way as not having APEX in the CDB$ROOT would have meant not having it in the PDB$SEED either – and all further provisioned PDBs would not have had APEX deployed by default. In this case every PDB would have required a separate APEX installation – but then you’d get the freedom of different APEX versions within the same Multitenant environment. And way more freedom during unplug/plug. But I’ll elaborate this further down below.

First of all check your current APEX version with this query:

SQL> select COMP_ID, VERSION, STATUS from CDB_REGISTRY where COMP_ID='APEX' order by CON_ID;

COMP_ID  VERSION      STATUS
-------- ------------ -------
APEX     4.2.5.00.08  VALID

Find out in which container APEX is currently installed into:

SQL> select r.COMP_NAME, r.VERSION, c.NAME, c.CON_ID from CDB_REGISTRY r, V$CONTAINERS c where r.CON_ID=c.CON_ID and r.COMP_ID='APEX' order by CON_ID;

COMP_NAME                   VERSION   NAME      CON_ID
--------------------------- --------- --------- ------
Oracle Application Express  4.2.5.00  CDB$ROOT       1

Then choose the best solution for your environment to avoid trouble with APEX in a Multitenant environment.

Scenario

Migrate a PDB with containing an APEX 4.2 deployment into another CDB with APEX 5.0 already installed in the CDB$ROOT container (the standard deployment in the Oracle DBaaS Cloud).

Two Solutions

Basically I see two potential solutions:

1. Remove APEX from the CDB$ROOT before plug in your PDB with a different APEX version
2. Upgrade your APEX installation in the PDB after plugging it into the CDB to match the CDB’s APEX version

Solution 1 – Remove APEX from the CDB$ROOT 

I’d prefer Solution 1 as this will give you way more freedom. It will even allow you to have different APEX versions in the same Multitenant environment in different PDBs. But you’ll have to do this steps right after creation of the container database – or before you worked with APEX in any of your PDBs as the following steps will remove APEX from all your PDBs!!!!If you already have APEX applications inside one of your PDBs you must export them before the APEX removal. Keep in mind that the “application export” facility in APEX 4 does not carry your image files automatically (APEX 5 is smarter).

The Oracle Documentation describes the path pretty straight forward.

1. Make sure to change into the local $ORACLE_HOME/apex directory first before starting SQL*Plus as otherwise the removal process won’t work and error out – I’d assume that path variables are not carried on correctly.
   cd $ORACLE_HOME/apex
2. Connect to your CDB$ROOT:
   sqlplus / as sysdba
3. Run the “Remove APEX from the CDB$ROOT” script:
   SQL> @apxremov_con.sql
   
4. Check afterwards if APEX has been removed – also check for invalid objects. If necessary recompile.
   In my tests two objects were INVALID after the removal but could be easily fixed
   (this part is not mentioned in the documentation):

   SQL> select COMP_ID, STATUS from DBA_REGISTRY where COMP_ID='APEX';
   no rows selectedSQL> select object_name, status from dba_objects where status='INVALID';OBJECT_NAME                      STATUS
   -------------------------------- -------------
   APEX_PKG_APP_INSTALL_LOG         INVALID
   APEX_SPATIAL                     INVALID
   
   SQL> @?/rdbms/admin/utlrp.sql
   
   SQL> select object_name, status from dba_objects where status='INVALID';
   no rows selected

5. Once you have done this your PDB containing the APEX application should not give you APEX errors anymore upon plugin as there’s no mismatch anymore between CDB$ROOT’s and the PDB’s APEX.
6. Just in the case – independently of my example – you’d like to install APEX now locally into a PDB then you’ll have to use the apexins.sql script:

   cd $ORACLE_HOME/apex
   sqlplus / as sysdba
   SQL> alter session set container=PDB1;
   SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/

   Be aware – this step may run a long time (in my VBox image it took over 22 minutes to complete).timing for: Complete Installation
   Elapsed: 00:22:16.29<

7. Check the existence of the component finally:

   host &OH_HOME/perl/bin/perl -I &OH_HOME/rdbms/admin &OH_HOME/rdbms/admin/catcon.pl -b apexins -c '' apexins_nocdb.sql --pSYSAUX --pSYSAUX --pTEMP --p/i/
   

Solution 2 – Upgrade APEX in the PDB after plugin

Even though I’d prefer solution 1 there may be some cases where you’ll have to deal with Solution 2 and upgrade APEX after plugin. One simple reason could be that somebody uses APEX already in another PDB and you can’t export and preserve it for later import – then you better don’t follow Solution 1 then as it will force you to test your backup/restore strategy concept soon after …

1. Run apexins.sql (from the APEX 5.0 installation – $ORACLE_HOME/apex) only in this particular PDB after plugin. You’ll have to use catcon.pl
2. The documentation https://docs.oracle.com/cd/E59726_01/install.50/e39144/db_pluggable.htm#HTMIG29441
   may not be correct mentioning all parameters without the –p option. Jason Straub corrected it:

   host &OH_HOME/perl/bin/perl -I &OH_HOME/rdbms/admin &OH_HOME/rdbms/admin/catcon.pl -b apexins -c '' apexins_nocdb.sql --pSYSAUX --pSYSAUX --pTEMP --p/i/
   

   whereas &OH_HOME represents the full path to the Oracle home.

More Scenarios 

If you look at some of the potential scenarios you may see when trying to move PDBs around including APEX applications it is pretty obvious why having APEX only locally inside each PDB is a wise move. It will give you way more flexibility, less trouble, actually less upgrades (and APEX upgrades can take a while) – and simply less headache.

Assume that my CDB1 and CDB2 below are at the same database version – having different database versions will require upgrade/downgrade tasks in addition.

APEX installed commonly within CDB$ROOT

• CDB1 has APEX 4.2 in CDB$ROOT
  CDB2 has APEX 5.0 in CDB$ROOT
  • Unplug a PDB from CDB1 and want to plug it into CDB2 but you’ll get an error in PDB_PLUG_IN_VIOLATIONS:
    “APEX mismatch: PDB installed version 4.2.5.00.08 CDB installed version 5.0.0.00.31”
  • That is actually the simple case as you’d only have to upgrade APEX inside the PDB:
    host &OH_HOME/perl/bin/perl -I &OH_HOME/rdbms/admin &OH_HOME/rdbms/admin/catcon.pl -b apexins -c ‘<PDB_name>‘ apexins_nocdb.sql –pSYSAUX –pSYSAUX –pTEMP –p/i/
• CDB1 has APEX 5.0 in CDB$ROOT
  CDB2 has APEX 4.2 in CDB$ROOT
  • You unplug a PDB from CDB1 and want to plug it into CDB2 – but you can’t actually plug in.
  • As APEX can’t be downgraded to 4.2 in your PDB you’ll have to upgrade APEX in the CDB2 to 5.0 first.
  • Then you can plugin your PDB into CDB2

• CDB1 has APEX in CDB$ROOT
  CDB2 has NO APEX in CDB$ROOT
  • Unplug a PDB with an APEX application from CDB1 and want to plug it into CDB2- fails
  • You will need to install APEX into CDB2’s CDB$ROOT before being able to plug in the PDB
    • And it has to be the same version of APEX as used in CDB1

APEX installed locally within the PDB only

• CDB1 has NO APEX in CDB$ROOT
  CDB2 has APEX  in CDB$ROOT
  • Unplug a PDB from CDB1 (having APEX locally in the PDB only) and want to plug it into CDB2 – works if …
    • the PDBs local APEX version match the CDB2’s common APEX version
    • If they don’t match …
      • PDB has APEX 4.2
        CDB2 has APEX 5.0
        Works, but you will need to upgrade APEX in the PDB after plugin
      • PDB has APEX 5.0
        CDB2 has APEX 4.2
        Fails, and you’ll have to upgrade CDB2s common APEX 4.2 to 5.0 first before being able to plugin.
• CDB1 has NO APEX in CDB$ROOT
  CDB2 has NO APEX  in CDB$ROOT
  • That is actually the best case as you don’t have to care.
    • The only thing you will need to be aware of is that you’ll need to patch your ?/apex home in case that PDB has a higher APEX version already than the one we ship by default

More Information

You can find more information via these links:

• Upgrade APEX to APEX 5.0:
  http://jastraub.blogspot.de/2015/04/upgrading-to-application-express-50-in.html 
• Plugging in a PDB When Application Express is Installed in the Root Container
• Plugging in a PDB When Application Express Is Not in the Root Container of the Target CDB

Oracle recommends removing Oracle Application Express from the root container database for the majority of use cases, except for hosting companies or installations where all pluggable databases (PDBs) utilize Oracle Application Express and they all need to run the exact same release and patch set of Oracle Application Express. To learn more, see “Installing Application Express into Different PDBs.”