Clean up APEX – Journey to the Cloud IV

What happened so far on my Journey to the Cloud?

DBaaS Oracle Cloud

Today’s journey: Cleanup APEX removal leftovers 

When you read my “Patch” blog post from Dec 22, 2015 you’ll see that I was left with an incomplete Oracle Application Express (APEX) removal situation. Something looked really strange and didn’t work as expected and documented.

But thanks to Jason Straub and Joel Kallman, my APEX colleagues, The solution below got provided by Jason. And let me say upfront that it’s not APEX’s fault 😉 Somebody has mixed up stuff in the current DBaaS deployment and thus the correct scripts to remove things in the right order are simply missing.

How to remove Oracle APEX from the DBaaS Cloud’s CDB$ROOT

  1. Download APEX 4.2.0.6 and PDBSS 2.0 (Multitenant Self Service Provisioning Application):
    http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-archive-42-1885734.html
    http://www.oracle.com/technetwork/database/multitenant/downloads/multitenant-pdbss-2016324.htmlActually if you have removed PDBSS already as I did in my “Patch” blog post from Dec 22, 2015 you don’t have to download, unzip and execute the PDBSS removal script again.
  2. Copy the zip files to the Cloud environment
    scp -i ./yourCloudKey /media/sf_CTEMP/Download/apex_4.2.6_en.zip oracle@:/home/oracle
    
    Enter passphrase for key './yourCloudKey':
    
    apex_4.2.6_en.zip              100%   82MB 116.9KB/s   11:58

    Repeat the same with PDBSS in case you didn’t remove it already.

  3. Connect to your Cloud environment and unzip both files.
    ssh -i ./yourCloudKey oracle@
    
    cd unzip apex_4.2.6_en.zip

    Repeat the same with PDBSS in case you didn’t remove it already.

  4. Remove PDBSS by using your unzipped archive

    cd /home/oracle/pdbss
    sqlplus / as sysdba
    SQL> @pdbss_remove.sql
    
  5. Remove APEX 5 by using the existing default installation
    (this is the part I did already in my previous blog post from Dec 22)

    cd $ORACLE_HOME/apex
    sqlplus / as sysdba
    SQL> @apxremov.sql 
    
  6. Remove APEX 4.2 parts by using your unzipped archive
    (this is the part I didn’t do yet)

    cd /home/oracle/apex
    sqlplus / as sysdba
    SQL> @apxremov_con.sql
  7. Drop the DBaaS Monitor common usersAs the DBaaS Monitor is based on APEX as well removing APEX will “kill” the DBaaS Monitor as well. So in oder to have a proper working environment you must drop the common users for DBaaS Monitor as well. Otherwise you’ll receive ORA-959 synch errors in PDB_PLUG_IN_VIOLATIONS whenever you deploy a new PDB and – even worse – the PDB won’t open unrestricted.
    SQL> drop user C##DBAAS_BACKUP cascade;
    SQL> drop user C##DBAAS_MONITOR cascade;
    
  8. Finally recompilation and check for invalid objects
    sqlplus / as sysdba
    SQL> @?/rdbms/admin/utlrp.sql
    
    SQL> select object_name, owner from dba_objects where status='INVALID';
    no rows selected
  9. Now you are free to install APEX 5.0 in the PDBs of your choice.As theres’s a PDB1 already I will create APEX inside this PDB first.
    cd $ORACLE_HOME/apex
    sqlplus / as sysdba
    SQL> alter session set container=pdb1;
    SQL> create tablespace APEX5 datafile '/u02/app/oracle/oradata/CDB1/PDB1/apex5_01.dbf' size 100M autoextend ON next 1M;

    Be aware: PDB_FILE_NAME_CONVERT is not set. And your database is not in ASM. Therefore avoiding the data file path and name will let you end up with a file named after OMF standards in:

    /u02/app/oracle/oradata/CDB1/26A65D56D16F21A1E05336036A0A1AD8/datafile/o1_mf_apex5_c9wg9kly_.dbf

    Create APEX 5.0 in PDB1, change the admin password, create the APEX listener and REST service and unlock the public user:

    SQL> @apexins.sql APEX5 APEX5 TEMP /i/
    SQL> @apxchpwd.sql

    Be aware about the password requirements:

    --------------------------------------------------------------------------------
    Password does not conform to this site's password complexity rules.
    * Password must contain at least 6 characters.
    * Password must contain at least one numeric character (0123456789).
    * Password must contain at least one punctuation character
    (!"#$%&()``*+,-/:;?_).
    * Password must contain at least one upper-case alphabetic character.
    * Password must not contain username.
    --------------------------------------------------------------------------------
    
    SQL> @apex_rest_config.sql
    SQL> alter user APEX_PUBLIC_USER identified by SecretPWD account unlock;

Let me add that it is possible to have different APEX versions in different PDBs. The only thing which you’ll need to really really take care on is the images directory and the different apex homes.

That’s it … and in a future cloud deployment the extra step to remove APEX 4.2 shouldn’t be necessary anymore.

–Mike

Share this: