Upgrade Blog posts about Oracle Application Express:
- Why you should remove APEX from the CDB$ROOT (Nov 19, 2015)
- Upgrade Time Credited to APEX (Sept 1, 2015)
- Save Upgrade Downtime – Upgrade APEX upfront (Jun 6, 2014)
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:
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:
- Remove APEX from the CDB$ROOT before plug in your PDB with a different APEX version
- 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.
- 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 - Connect to your CDB$ROOT:
sqlplus / as sysdba - Run the “Remove APEX from the CDB$ROOT” script:
SQL> @apxremov_con.sql
- 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
- 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.
- 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< - 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 …
- 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
- 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/
- Unplug a PDB from CDB1 and want to plug it into CDB2 but you’ll get an error in PDB_PLUG_IN_VIOLATIONS:
- 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.
- PDB has APEX 4.2
- Unplug a PDB from CDB1 (having APEX locally in the PDB only) and want to plug it into CDB2 – works if …
- 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
- That is actually the best case as you don’t have to care.
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
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
Hi Mike,
But what about Database Express which is also using APEX? Please can you also comment on that?
Thanks
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
Thanks Bernhard – good catch!!! I corrected it 🙂
Cheers
Mike
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
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
Thanks Sebastian!
Cheers
Mike
Please see this link: http://swiftsight.tech/installing-oracle-application-express-apex-5-x-in-a-pluggable-database-12c/
Thanks 😉
Awesome Mike . . Very helpful thanks a lot.