Upgrade to Oracle APEX 20.1

Upgrade from APEX 4.2 to APEX 20.1 on Oracle 12.1

 

Just a summary of thoughts of a small issue I encountered while helping out on APEX upgrade. 

 

Situation was on premise Oracle 12.1 running with pre-installed APEX 4.2 version and ask was to upgrade to latest 20.1.0.00.13 release. 

 

You may say this is a big leap forward but in theory it should be working fine. Normally APEX is very very good with upgrades to newer versions so this might catch you off guard.

 

What you would do is download latest APEX version, unzip it and start a famous line

@apexins.sql APEX APEX TEMP /i/

Then so called phase 1 and 2 would finish fine but at the end of phase 3 you would get strange errors like:

 

 not ok 8 - Creating FLOWS_FILES grants and synonyms |   0.00
# Message: ORA-38824: A CREATE OR REPLACE command may not change the EDITIONABLE property of an existing object.
# Statement: create or replace synonym wwv_flow_id sharing=NONE for "APEX_200100".wwv_flow_id
# Message: ORA-38824: A CREATE OR REPLACE command may not change the EDITIONABLE property of an existing object.
# Statement: create or replace synonym wwv_flow_file_api sharing=NONE for "APEX_200100".wwv_flow_file_api
# Message: ORA-38824: A CREATE OR REPLACE command may not change the EDITIONABLE property of an existing object.
# Statement: create or replace synonym wwv_flow sharing=NONE for "APEX_200100".wwv_flow

# Message: ORA-02298: cannot validate (FLOWS_FILES.WWV_FLOW_FILE_OBJ_FK) - parent keys not found

# Statement: alter table wwv_flow_file_objects$ add constraint wwv_flow_file_obj_fk foreign key (security_group_id) references "APEX_200100".wwv_flow_companies(provisioning_company_id) on delete cascade

ok 9 - Creating Jobs |   0.00
ok 10 - Creating Dev Jobs |   0.00
not ok 11 - Installing FLOWS_FILES Objects |   0.00
# Message: ORA-38824: A CREATE OR REPLACE command may not change the EDITIONABLE property

 

I tried checking my support links, blog posts etc... but there was nothing similar or that obvious. Closest to what I found has this description: 

 

/****** ON MY SUPPORT *************/

Cause
   Corrupted APEX 5.X Source Instance  ( The constraints and/or triggers were disabled causing discrepancies in the data)
    
    There are some application that violated some constraints on the source and when the upgrade is done those corrupted application cause a failure
    The Complete Solution would be:

          1) Backup your APEX work with APEX Exports from the source

         Read and follow: How to Migrate ALL APEX Applications & Workspaces from One Instance to Another Using the APEXExport Utility (Doc ID 1995509.1)

          2) Clean up current APEX installation

                 Connecting in sql*plus as sys and do
                     drop user APEX_200100 cascade;
                     @apxremov.sql                             
                     -- This script must be from the apex 5.X directory (or source version)

          3) Install Again the latest version of APEX ( i.e 20.1.0.00.13)

       4) Import into APEX your work, Workspaces first then the applications and its shared components              

We were not upgrading to APEX 5.x version nor was it installed. But perhaps it indicates expect future trouble in any newer version of APEX. 

 

Not sure, there were quite a few people who had a look at this and no one made sense to why it was happening ;)

 

Once you accept that fact - the only way to fix this seems to be dropping all your APEX schemas and reinstalling it from scratch:

1. cd $ORACLE_HOME/apex
   SQL> @apxremov.sql
   SQL> exit
   
2. SQL> drop user APEX_030200 cascade;
3. SQL> drop user APEX_040200 cascade; 
   
4. cd /tmp/apex20.1/
   SQL> @apxremov.sql
   SQL> exit
   
5. SQL> drop user APEX_200100 cascade;   -- not needed but just in case
   
6. SQL> drop user APEX_INSTANCE_ADMIN_USER cascade

Only after these steps did my @apexins.sql run. 

 

I am still not sure what it was but here it is for the reference. Sometimes it is good to know it does happen else it could be many many hours later you trying to figure out what happened. :D

 

It is more than obvious that you should backup your database and all of your apps/workspaces/static files and similar before running this fix. But you should do this before any major upgrade anyway.

 

Huge inconvenience is when you have to do this in production but hey once you get it going and receive an immediate feedback that APEX runs way faster now all your troubles have payed off. ;)

 

Happy APEXing,
Lino