Why you should remove APEX from the CDB$ROOT

Upgrade Blog posts about Oracle Application Express:


Oracle APEX (Application Express) is great piece of software. But it gets installed by default into the container database’s CDB$ROOT unless you’d customized your CDB creation via scripts in Oracle 12.1.0.2. See:

Create Container Databases (CDB) with less options – it’s now supported in Oracle 12.1.0.2

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.

Oracle APEX Multitenant

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.

Different APEX versions in Multitenant

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).

Remove APEX from CDB$ROOT will remove it from all PDBs as well

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:

And Finally …

I’d recommend to NEVER have APEX in any CDB$ROOT. I see the advantage of having it as a new PDB will always have APEX provisioned with it. That is convenient, no doubt. But honestly if you really need APEX in most or all of your PDBs then create your own PDB$MASTER and provision from this one instead of using the PDB$SEED. In all other cases it’s by far easier to install APEX inside the PDB when you really need it. Even with the penalty that it requires some extra 10 or more minutes until it is ready to go.

Given also the fact that APEX upgrade may take a while as well (see this blog post: https://blogs.oracle.com/UPGRADE/entry/save_upgrade_downtime_upgrade_apex  ) there’s another advantage which personally I consider way more important:

Having APEX locally in the PDB only offers you way more freedom and flexibility. All the complicated scenarios come up only when either the source has no APEX in the CDB$ROOT (APEX locally in the PDB) but the target has any version (most likely a different one) or the target has no APEX in the CDB$ROOT but the source had it. And even more fun, both CDBs have APEX in the CDB$ROOT but at different versions.

This can be totally avoided by NEVER having APEX installed in your CDB$ROOT.

Which means also for the Oracle Cloud DBaaS databases:
Remove APEX from the CDB$ROOT and install it into your PDB before really starting up doing APEX stuff inside. APEX is such cool piece of software – and it’s free – and powerful – and easy!!!

And (thanks to Jason again for this hint) if you look into the APEX 5.0 Installation Guide you’ll find this tip:

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.”

–Mike

10 thoughts on “Why you should remove APEX from the CDB$ROOT

  1. Hello Mike,

    Nice blog post, you have a small typo. You mentioned @apexremov_con.sql. But the script is: SQL> @apxremov_con.sql

    Greetings,
    Bernhard

  2. Bernhard,

    honestly I don’t see a big benefit in using DB Express 12c. It has not much functionality, especially if one doesn’t use Diag/Tuning. I’d favor SQL Developer instead with some useful plugins.

    And to be honest, I wasn’t aware that DB Express is APEX based. But now it makes sense to me – so a comment will be added that this will trash DB Express as well 😉

    Ouch … you are making my life more complicated 😉

    Thanks again for this great input!!!
    Mike

  3. Hi,
    in DBaaS Oracle Cloud and APEX 5.0.0.00.31 the script changed again. It is simply apxremov.sql . It will automatically look for CDB/PDB,nonCDB removal.
    Rgds
    Sebastian

  4. Pingback: Upgrade downtime credited to APEX | Upgrade your Database - NOW!

  5. Pingback: Save Upgrade downtime: Upgrade APEX upfront | Upgrade your Database - NOW!

  6. Pingback: Installing Oracle Application Express 5.1 – Azure Output

Leave a Reply

Your email address will not be published. Required fields are marked *