Clean up APEX – Journey to the Cloud IV

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;