APEX is invalid after upgrading to 12.2 – ORA-20001

We are running the AutoUpgrade-for-RAC beta test since last week. And the first issue one customer encountered … is neither an AutoUpgrade nor a RAC issue. It has to do with APEX and SPATIAL, and missing grants. APEX is invalid after upgrading to 12.2 – ORA-20001

Photo by Aditya Vyas on Unsplash

What is happening?

You are doing an upgrade to Oracle Database 12.2.0.1. And you receive a number of errors during the component validation phase.

19:04:14 SQL> EXECUTE dbms_registry_sys.validate_components;
...(19:04:15) Starting validate_apex for APEX_180100
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_DIM_ARRAY" to APEX_180100
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_DIM_ELEMENT" to APEX_180100
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_ELEM_INFO_ARRAY" to APEX_180100
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_GEOMETRY" to APEX_180100
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_ORDINATE_ARRAY" to APEX_180100
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_POINT_TYPE" to APEX_180100
...(19:04:21) Checking missing sys privileges
...(19:04:21) Re-generating APEX_180100.wwv_flow_db_version
... wwv_flow_db_version is up to date
...(19:04:21) Key object existence check
...(19:04:23) Setting DBMS registry for APEX to INVALID
...(19:04:23) Exiting validate_apex

This is taken from the autoupgrade logs.

And this is what you’ll see in the upgrade worker’s log:

2020-02-08 19:04:25.645 ERROR 
DATABASE NAME: siguirt_1
         CAUSE: ERROR at Line 412 in [/tmp/upg_logs/SID_1/101/dbupgrade/sid_autocompile20200208182040sid0.log]
        REASON: ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_DIM_ARRAY" to
        ACTION: [MANUAL]
        DETAILS:  - CheckForErrors.checkForErrors 
2020-02-08 19:04:25.714 ERROR 
DATABASE NAME: siguirt_1
         CAUSE: ERROR at Line 414 in [/tmp/upg_logs/SID_1/101/dbupgrade/sid_autocompile20200208182040sid0.log]
        REASON: ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_DIM_ELEMENT" to
        ACTION: [MANUAL]
        DETAILS:  - CheckForErrors.checkForErrors 
2020-02-08 19:04:25.783 ERROR 
DATABASE NAME: siguirt_1
         CAUSE: ERROR at Line 416 in [/tmp/upg_logs/SID_1/101/dbupgrade/sid_autocompile20200208182040sid0.log]
        REASON: ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_ELEM_INFO_ARRAY" to
        ACTION: [MANUAL]
        DETAILS:  - CheckForErrors.checkForErrors 
2020-02-08 19:04:25.854 ERROR 
DATABASE NAME: siguirt_1
         CAUSE: ERROR at Line 418 in [/tmp/upg_logs/SID_1/101/dbupgrade/sid_autocompile20200208182040sid0.log]
        REASON: ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_GEOMETRY" to APEX_180100
        ACTION: [MANUAL]
        DETAILS:  - CheckForErrors.checkForErrors 
2020-02-08 19:04:25.922 ERROR 
DATABASE NAME: siguirt_1
         CAUSE: ERROR at Line 419 in [/tmp/upg_logs/SID_1/101/dbupgrade/sid_autocompile20200208182040sid0.log]
        REASON: ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_ORDINATE_ARRAY" to
        ACTION: [MANUAL]
        DETAILS:  - CheckForErrors.checkForErrors

Why is this happening?

As Bug 25293022 is non-public, you can’t read it. The bug’s subjects says:

• Bug 25293022 – REVOKE EXECUTE GRANT ON SPATIAL TABLE CAN INVALIDATE APPLICATION TABLES

From what I know there was an issue with grants on SPATIAL types. As a fix, these had to be revoked and regranted. But this can lead to application tables being invalidated. This issue happens as part of the upgrade. As a result, the component validation fails, and APEX has invalid objects.

How to fix this?

Very simple at the moment – upgrade to Oracle 19c (or 18c if you need to) instead. There the fix for Bug 25293022 has been included. But our suspicion is that we may see such errors more often with PDBs and different APEX versions in each PDB. Just a suspicion …

Support released:

• MOS Note: 2274616.1 – APEX is Invalid After Upgrading to 12.2 – ORA-20001: MISSING GRANT

but the workaround mentioned is phrased a bit static. It recommends to GRANT the missing EXECUTE privileges to APEX_050100. But in our above case this won’t lead to success. The missing GRANTs need to be granted to APEX_180100 here:

grant execute on "MDSYS"."SDO_DIM_ARRAY" to APEX_180100;
grant execute on "MDSYS"."SDO_DIM_ELEMENT" to APEX_180100;
grant execute on "MDSYS"."SDO_ELEM_INFO_ARRAY" to APEX_180100;
grant execute on "MDSYS"."SDO_GEOMETRY" to APEX_180100;
grant execute on "MDSYS"."SDO_ORDINATE_ARRAY" to APEX_180100;
grant execute on "MDSYS"."SDO_POINT_TYPE" to APEX_180100;

Then validate the component again:

set serveroutput on
execute SYS.VALIDATE_APEX;

I’m sometimes a bit scared how incomplete some MOS notes are. This one misses the semicolons, the execute for the validation – and assumes that there could be only one APEX version … 🙁

Further Links

• MOS Note: 2274616.1 – APEX is Invalid After Upgrading to 12.2 – ORA-20001: MISSING GRANT
• Save Upgrade Downtime – Upgrade APEX upfront

–Mike