Thursday, December 3, 2015

The unpleasant installation process of APEX5 in 12c

Since the early days of HTML DB the installation (or updating) process stayed almost the same. You could update your APEX database environment in just a few simple steps, this worked very stable for years.  With the releases of Oracle Database 12c and Apex 5 this easy handling changed for the worse.


This blogpost is about performing a new, clean APEX5 installation in a 12c database. This apparently simple task already contains some unpleasant surprises. This blogpost is not about migrating existing apex environments into 12c, these scenarios would go beyond its scope: the database architectures (11g, CDB 12c, Non-CDB 12c, PDB 12c), the different migration paths among each other, different APEX version levels and not to mention what happens when you plug it into another cdb environment. Administrators won't get quickly bored in 12c.

Make your choice, install APEX in a CDB or a PDB?
The latest database release 12.1.0.2 is shipped with APEX version 4.2.5, installed in the root container (CDB). Accordingly, one of the first steps will be an upgrade to APEX version 5. The "Non-CDB 12c"-architecture is already deprecated again, so you have to decide, if it's better to upgrade the existing CDB installation or to perform a new installation in a PDB.

If you have plans to actively use the multitenant option you should (at least) read the whitepaper Deploying and developing Oracle APEX with Oracle DB 12c and the Installation guide to make the decision. The recommended way is, according to the current 12c database documentation, to install APEX in a CDB. This enables centralized management and ensures every PDB runs in the same version. This recommendation has changed in the APEX 5 documentation - for the majority of use cases, now APEX should be better installed in PDB. Recently the blog post Why you should remove APEX from the CDB$ROOT also strongly recommends to install APEX in PDB, mainly to achieve higher flexibilty.


Installation in a CDB
You need DB patch no. 20618595 to upgrade APEX from version 4.2 to version 5 in a CDB. I won't go in detail here, the installation procedure is described in blogpost Upgrading to APEX 5.0 in your Oracle Database Release 12.1 CDB by Jason Straub. People with no greater affinity to DBA activities, will probably have some difficulties with the implementation of the necessary installation tasks.

The required patch is only available for Linux and AIX, although in the meantime APEX5 was released over half a year ago. In consequence, if you're on Windows, Solaris or HP-UX and followed conscientiously the advices from Oracle, this means you upgraded early to db 12c (premier support for 11gR2 ended in january 2015) and migrated your apex environment to CDB, you still have no upgrade path to apex 5. In this case, the only (undocumented) option is to deinstall your (productive?) APEX environment and perform a new, fresh APEX installation.


Installation in a PDB
At first the default apex installation has to be removed from the CDB before you can start with the APEX5 installation in PDB. Browsing the Installation guide brings you quickly to the section Uninstalling Application Express from a CDB. After executing the mentioned script "apxremov.sql", everything looks fine at the first glance. Though a message is displayed to scan all the logfiles for ORA-errors, the console output shows no errors. But when you examine the logfiles, you'll see that the script tries to remove an APEX5 installation. Of course this doesn't exist yet, your installation still resides under the schema APEX_040200.

With the release of 12c the perl utility "catcon.pl" was introduced. It allows you to run SQL scripts in multitenant environments, the way the APEX removal script was also executed. Obviously the tool doesn't offer the option (or at least no script known to me used it yet) to hand over fatal errors to the console output. It's therefore all the more unfortunate that the tool creates already with one single execution not less than 34 different logfiles. It would have been desirable, Oracle had involved a better quality assurance for such an essential, new administration tool.

Back to the failed APEX deinstallation: so we need a script to remove APEX4.2 from the CDB. This can be found in the APEX distribution delivered with database installation at $ORACLE_HOME/apex. If you execute the "apxremov.sql" from this location now, your APEX_040200 schema will still stay alive. In Version 4.2 the scripting names are different. "apxremov.sql" also exists there, but it doesn't take any effect in a CDB. You'll have to execute a script called "apxremov_con.sql".

------------------------------------------------------
Update 04.12.15
There is no need to set the following undocumented parameter, just execute the correct script "$ORACLE_HOME/apex/apxremov_con.sql". Thanks to Jason for clarifying this.
------------------------------------------------------
When executing this script, you'll hit the error "ORA-28014: cannot drop administrative users". To avoid this error, you have to set the undocumented database parameter "_oracle_script". As you might know, using undocumented '_' parameters can violate your Oracle support contract. Those parameters should only be used when Oracle tells you to use them - but no words about that in the release notes, installation guide or at support.oracle.com. So the official way would be to create a service request at Oracle support. Otherwise, risking to violate your support contract and execute the following statement:
      alter session set "_oracle_script"=true;
Now your APEX4.2 installation should be removed successfully and you can start with your APEX5 installation.

Conclusion
APEX administrators are facing new challenges with the 12c database architecture. Just a simple upgrade from APEX 4.2 to 5 in 12c is anything but a quick and casual process. Probably not just a few guys might have thought "Let me try the latest releases, no big deal to install 12c and Apex 5!" - only to become quite frustrated after some time. Oracle should find a remedy, at least the documentation or release notes should point out the pitfalls. Of course the APEX development team, an absolutely passionate and outstanding team at Oracle, are rather dependent on the features (and patches) the Database development team delivers.
With the future release of DB 12cR2 the handling will probably get easier again - at least if it's shipped with installed APEX 5 in PDB$SEED then.


/* Example how to remove the default APEX 4.2.5 installation 
   from CDB and install APEX 5 in a PDB */

/* Navigate to APEX 4.2 directory (from database installation) */
oracle@srv> cd $ORACLE_HOME/apex

/* Open SQLPlus */
oracle@srv> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 27 19:05:41 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

/* Allow administration of common cdb users */
/* not necessary!
SQL> alter session set "_oracle_script"=true;

Session altered.
*/

/* Remove APEX 4.2 from CDB */
SQL> @apxremov_con.sql;

[...]

SQL> exit

/* Navigate to your APEX5 installation directory */
oracle@srv> cd /opt/oracle/install/apex5

/* Open SQLPlus */
oracle@srv> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 27 19:05:41 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

/* Switch to your pdb */
SQL> alter session set container=[pdb_name];

Session altered.

/* Install APEX5 */
SQL> @apexins.sql [apex_tablespace] [files_tablespace] [temp_tablespace] [images]

[...]

10 comments:

  1. Hi Robert,

    Thank you for composing this very detailed post. It has been challenging to support different installation options for the two different architectures for the Oracle database. As you pointed out, we now recommend removing APEX from CDB$ROOT and installing locally in PDBs. We came to this recommendation after almost two years of experience running internal deployments of APEX in CDB$ROOT, where not all PDBs were using APEX.

    One correction that I would like to make is that it is not necessary to use the parameter "_oracle_script" to remove APEX from CDB$ROOT and all PDBs. You only need to run $ORACLE_HOME/apex/apxremov_con.sql while connected to CDB$ROOT. I have removed APEX from CDB$ROOT in countless 12.1.0.2.0 databases running only $ORACLE_HOME/apex/apxremov_con.sql and have never seen the error you reported in the logs. If you have the time, can you drop me a note with steps where I can reproduce that error? If we missed some scenario where this error is thrown, we will work to get it fixed.

    Finally, I agree that the installation documentation could be more clear. We should have had a note about running apxremov.sql from the 5.0 source, stating that this should only be run against 5.0 installations, and not the 4.2.5 installation that is installed by default in 12.1.0.2.0.

    Regards,

    Jason Straub

    ReplyDelete
  2. Hi Jason,

    thanks for your detailed feedback.

    I created a new CDB for testing the "_oracle_script"-parameter issue again. After running "$ORACLE_HOME/apex/apxremov_con.sql" without setting the parameter, the APEX4.2 installation was removed successfully, so you're absolutely right. Probably i mixed this error in combination with the wrong script "apxremov.sql". Sorry about that, i will update the blog.

    Best regards
    Robert

    ReplyDelete
  3. I would like to Upgrade APEX5 on my cdb is that fine or need to use my PDB for this upgrade?

    ReplyDelete
    Replies
    1. Hi Raghu,

      it depends.. if you didn't used your apex installation in cdb yet, i would recommend to deinstall this installation and install the apex environment in a pdb (see script in blogpost).
      If you want to upgrade the cdb installation and you're on Linux or AIX, then you need patch no. 20618595 and you need to follow the instructions from the mentioned blogposting by Jason Straub.
      If you want to upgrade the cdb installation and you're NOT on Linux or AIX, then you have to export your APEX applications and workspaces and (after testing your backups..) remove the apex 4 environment completely. Now you can fresh install APEX 5 and import your stuff again.
      Regards Robert

      Delete
  4. Installed Apex5 on CDB.But am getting thsi error if after i ran
    @reset_image_prefix.sql

    There is a problem with your environment because the Application Express files have not been loaded. Please verify that you have copied the images directory to your application server as instructed in the Installation Guide. In addition, please verify that your image prefix path is correct. Your current path is /i/ (it should contain both starting and ending forward slashes, such as the default /i/). Use the SQL script reset_image_prefix.sql if you need to change it.
    SQL> @reset_image_prefix.sql

    ReplyDelete
  5. Raghu/Robert,

    I am seeing the same issue irrespective of where I install the APEX 5 and it is very confusing. Did you find the fix for the error?

    Thanks
    Kris

    ReplyDelete
  6. Yeah,I fixed it.

    First we have to drop APEX from all PDB's.Then only we can Install APEX on CDB with out any issue.

    Thanks,
    Raghu

    ReplyDelete
  7. would you be able to share your steps? I am still receiving the same error message. I am guessing it is a very simple one that stops my instance to show up.






    Thanks
    Kris

    ReplyDelete
  8. Hi Kris,

    I am not sure what si the exact error are you getting.

    as I understand are you try to upgrade APEX 4 to 5 on CDB.



    Run the "Remove APEX from the PDB script:
    SQL> @apxremov_con.sql

    Check afterwards if APEX has been removed - also check for invalid objects. If necessary recompile.

    Once APEX has been removedfrom all PDBS,now you can start the isntall or upgrade of APEX.

    Please email me raghudiddi@gmail.com for more details.I will help you in detail.

    ReplyDelete
  9. Raghu,

    That helped to fix my error much appreciated your time.

    Thanks
    Kris

    ReplyDelete