Friday, April 17, 2015

Upgrading to Application Express 5.0 in Your Oracle Database Release 12.1 Multitenant Container Database (CDB)

Woo hoo! Oracle Application Express 5.0 has been released. Read all about it here.

The purpose of this blog post is to provide information on how to upgrade your 12.1 Multitenant Container Databases (CDBs) to APEX 5.0, and specifically ones where you have Oracle Application Express installed in CDB$ROOT (the root), and you are making use of Oracle Application Express 4.2.x in the PDBs in this CDB. There has been confusion on this topic, even from some of my colleagues at Oracle.

You can ignore this blog post if any of the following conditions are true in your situation:
  1. You are on a release lower than 12.1
  2. You are running 12.1, but it is a non container database (non-CDB)
  3. You are running a 12.1 CDB, but you have de-installed Oracle Application Express from the CDB, and you are running Oracle Application Express locally in all or some of your PDBs
Since you are still reading this post, this means you have a 12.1 CDB, where Oracle Application Express is installed in the root, and you are actively using APEX in your PDBs, and you wish to upgrade the root and all of your PDBs to APEX 5.0.

We have tried to make it painfully clear in multiple places in the 5.0 documentation set, and on our known issues page, that if you want to upgrade your 12.1 CDB to APEX 5.0, you must first download and install the database patch number 20618595 prior to upgrading to APEX 5.0.

Below is a step by step guide based on my experience in upgrading one of our 12.1.0.2.0 development environments to Oracle Application Express 5.0.

First, install the latest OPatch into your $ORACLE_HOME, by doing the following:
  1. Login to support.oracle.com
  2. Click Patches & Updates tab
  3. Simple search on patch number 6880880 and Platform Linux x86-64
  4. Download the patched titled "Patch patch of version 12.1.0.1.7 for Oracle software releases 12.1.0.x (APR 2015)"
  5. Execute the following OS commands
cd $ORACLE_HOME
mv OPatch OPatch_save
unzip /p6880880_121010_Linux-x86-64.zip
$ORACLE_HOME/OPatch/opatch version

OPatch Version: 12.1.0.1.7

OPatch succeeded.


Next, you need to download and apply database patch 20618595.
  1. Login to support.oracle.com
  2. Click Patches & Updates tab
  3. Simple search on patch number 20618595
  4. Download either the 12.1.0.1.0 or 12.1.0.2.0 patch. I was on 12.1.0.2.0 so I downloaded that version.
  5. Execute the following OS commands
unzip /p20618595_121020_Linux-x86-64.zip
cd 20618595
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Ensure you see "Prereq "checkConflictAgainstOHWithDetail" passed." in the output. If so, proceed with shutting down all services for $ORACLE_HOME.

lsnrctl stop
sqlplus / as sysdba
shutdown immediate
exit

$ORACLE_HOME/OPatch/opatch apply
$ORACLE_HOME/OPatch/opatch lsinventory

You should now see something in your terminal similar to:

Patch  20618595     : applied on Thu Apr 16 13:17:43 PDT 2015
Unique Patch ID:  18723077
   Created on 9 Apr 2015, 15:28:51 hrs PST8PDT
   Bugs fixed:
     20618595


If you do not, do not proceed. Get help from a DBA. Do not attempt to to upgrade to APEX 5.0. Do not pass Go! Do not collect $200.

Now, if you did see output in your terminal similar to the above, do the following.

lsnrctl start
sqlplus / as sysdba
startup
alter pluggable database all open;
exit

cd $ORACLE_HOME/OPatch
./datapatch

You should see output similar to:

    The following patches will be applied:
      20618595 ()

catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_26531.lst
catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any
Installing patches...
Patch installation complete.  Total patches installed: 6

Validating logfiles...done
SQL Patching tool complete on Thu Apr 16 13:49:29 2015


Again, if you do not, and you see any type of error, get help from a DBA and do not proceed until this patch is successfully applied to the database. Once the patch is successfully applied, simply connect to CDB$ROOT as SYSDBA, and run either apexins.sql or apxrtins.sql depending on your installation type. That's it. At the end of the installation your CDB and all PDBs will be successfully upgraded to APEX 5.0. I guarantee it!



12 comments:

Connor McDonald said...

Nice post. It would perhaps also be useful for the apex team to post a blog entry for those people who do make the error and forget to install the patch.

I assume the Apex upgrade goes wrong ? What's the rededial action to take once that occurs ? Or are people "dead in the water" ?

Cheers,
Connor

Jason Straub said...

Connor,

Good point. You will first need to revert your CDB to the 4.2.x release, using the following CDB instructions:

https://docs.oracle.com/cd/E59726_01/install.50/e39144/trouble.htm#BABHJECG

Next you will want to drop the APEX_050000 schema from the failed release, using the following CDB instructions:

https://docs.oracle.com/cd/E59726_01/install.50/e39144/trouble.htm#HTMIG270

Once you have completed both steps above, you can apply the database patch, then attempt the upgrade to APEX 5.0 again.

Regards,

Jason

Robert Schäfer said...

Hello Jason,

thanks for your interesting post. I'm wondering, patch number 20618595 is only available for Linux-x86-64 (yet). When running a 12.1 CDB on any other platform, i can't use APEX5 at the moment?

Best regards
Robert Schaefer

Jason Straub said...

Hi Robert,

Yes, if you need to upgrade APEX 4.2.x that you are currently using in a CDB, you will need the database patch 20618595 for your platform. If you are not currently using APEX in your CDB (meaning none of the PDBs in your CDB is using APEX), you can remove APEX 4.2.x using apxremov_con.sql (from any 4.2.5 or greater source, like a 12.1.0.2. $ORACLE_HOME/apex/apxremov_con.sql), and then you can install APEX 5.0 into your CDB without applying the patch. To expedite the patch for your platform (if you need to do the upgrade, you should file an SR with Oracle Support.

Regards,

Jason

Unknown said...

Jason -

Do you recommend Apex in CDB$ROOT or PDB. The documentation is conflicting.

From the 12c documentation: https://docs.oracle.com/database/121/HTMIG/db_pluggable.htm#HTMIG29171
"As a best practice, Oracle recommends having a common Oracle Application Express installation in the root container, which ensures every PDB is running the same version of Oracle Application Express. This offers ease of administration by enabling centralized management of Oracle Application Express infrastructure tasks, such as upgrading and patching. The other alternative is to uninstall Application Express from CDB$ROOT, PDB$SEED, and all other PDBs, and then install Application Express locally into each PDB."

From the APEX 5.0 documentation that you reference: https://docs.oracle.com/cd/E59726_01/install.50/e39144/db_pluggable.htm#HTMIG29435
"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.""

Thanks.

Jason Straub said...

Hi Pete,

The APEX 5.0 documentation reflects a change in our recommendation, based on many internal deployments at Oracle and experience we gained in the two years between Oracle Database 12c and APEX 5.0.

Regards,

Jason

Unknown said...

Hi,

While installing ORDS 3.0 in Oracle 12c, is it mandatory to have APEX installed? can you please clarify?

Thanks,
Srini

Jason Straub said...

Hi Srini,

With ORDS 3.0, you do not need to have Oracle Application Express installed and configured.

Regards

Jason

Unknown said...

Hi Jason -
The apxremov_con.sql script was repeatedly failing with the error

"catconInit: database is not open on the default instance".

I spent many unhappy hours trawling the net before I managed to run it successfully! Here's what I ended up having to do:

1) cd $ORACLE_HOME/apex
2) Log into the root container as sysdba
3) shutdown immediate
4) startup upgrade
5) alter pluggable database pdb1 open upgrade;
6) alter session set "_oracle_script"=true;
7) @apxremov_con
8) shutdown immediate
9) startup
10) alter pluggable database pdb1 open read write;

I had installed Oracle 12.1.0.2.0 on ubuntu linux 16.04 and was trying to deinstall APEX 4.2.5.00.08

Anonymous said...

Hi Jason,

i have installed oracle enterprise linux 6U7 x86_64 with rdbms 12.1.0.2.0
i tried to run you install plan, but.

OPatch is 12.2.0.1.5 now.
OPatch 20618595 went without a problem.

i keep getting the same errors...

==
[oracle@produktie OPatch]$ ./datapatch
SQL Patching tool version 12.2.0.0.0 on Tue Jul 26 19:16:30 2016
Copyright (c) 2014, Oracle. All rights reserved.

Connecting to database...OK
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Determining current state...done
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT EVEONLINE PDB$SEED
Nothing to roll back
The following patches will be applied:
20618595 ()

catcon: ALL catcon-related output will be written to /tmp/sqlpatch_catcon__catcon_26468.lst
catcon: See /tmp/sqlpatch_catcon_*.log files for output generated by scripts
catcon: See /tmp/sqlpatch_catcon__*.lst files for spool files, if any
catconInit: database is not open on the default instance
Installing patches...
catconExec: catconInit has not been run
catconWrapUp: catconInit has not been runPatch installation complete. Total patches installed: 3

Validating logfiles...done
SQL Patching tool complete on Tue Jul 26 19:16:43 2016
===

catconInit : Database is not open on the default instance.
but the only container not open is PDB$SEED the rest is open en read/write

please point me in the right direction as i like to solve this but i can't find a solution

Cheers Tonny.
ps upgrade to apex 5.0 gave that problem also before the patch.


Jason Straub said...

Hi Tonny,

It appears that catcon.pl is not executing correctly in your environment. I would open an SR.

Regards,

Jason

Anonymous said...

thanks...
i always forget the biggest knowledgebank a dba has.... support.oracle.com :)
the problem was with glogin.sql (or SQLPATH) unset SQLPATH and voila... works.

i feel dumb...