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 220.127.116.11. See:
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 18.104.22.168 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 22.214.171.124 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.
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).
Basically I see two potential solutions:
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.
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 ...
&OH_HOMErepresents the full path to the Oracle home.
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.
You can find more information via these links:
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."