Upgrade Oracle Database from 12.1 to 12.2 “ORA-20001: MISSING GRANT”

During upgrade of Database from 12.1 to 12.2 I received a error during upgrade, I will share the solution since I didn’t not find nothing about it on MOS and Internet (googling).

I don’t know if this is a relevant info, but when I installed the Oracle 12.1 I performed a uninstall of APEX from the CBD and it was installed on PDB only.
Using procedure from below link.
https://oracle-base.com/articles/12c/multitenant-uninstall-apex-from-the-cdb-12cr1

 

SYMPTOMS

Severe errors encountered during exection of “PDBS Recompile Invalid Objects”

ERROR_upgrading_12.1_to_12.2

The errors was found on log file “/u01/app/oracle/cfgtoollogs/dbua/upgrade2017-04-17_07-39-02-PM/prdcdb/PDBSUtlprp2R0.log”.

SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> SET serveroutput on
SQL> EXECUTE dbms_registry_sys.validate_components;
...(22:53:11) Starting validate_apex for APEX_050100
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_DIM_ARRAY" to APEX_050100
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_DIM_ELEMENT" to APEX_050100
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_ELEM_INFO_ARRAY" to APEX_050100
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_GEOMETRY" to APEX_050100 
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_ORDINATE_ARRAY" to APEX_050100
ORA-20001: MISSING GRANT: grant execute on "MDSYS"."SDO_POINT_TYPE" to APEX_050100
...(22:53:11) Checking missing sys privileges
...(22:53:11) Recompiling
...(22:53:11) Checking for objects that are still invalid
...(22:53:12) Key object existence check
...(22:53:12) Setting DBMS registry for APEX to INVALID
...(22:53:12) Exiting validate_apex

PL/SQL procedure successfully completed.

How do I  Fix it:

$ sqlplus / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session set container=COMMON_APPS;

Session altered.

SQL> grant execute on "MDSYS"."SDO_GEOMETRY" to APEX_050100;
Grant succeeded.

SQL> grant execute on "MDSYS"."SDO_DIM_ARRAY" to APEX_050100;
Grant succeeded.

SQL> grant execute on "MDSYS"."SDO_DIM_ELEMENT" to APEX_050100;
Grant succeeded.

SQL> grant execute on "MDSYS"."SDO_ELEM_INFO_ARRAY" to APEX_050100;
Grant succeeded.

SQL> grant execute on "MDSYS"."SDO_GEOMETRY" to APEX_050100; 
Grant succeeded.

SQL> grant execute on "MDSYS"."SDO_ORDINATE_ARRAY" to APEX_050100;
Grant succeeded.

SQL> grant execute on "MDSYS"."SDO_POINT_TYPE" to APEX_050100;
Grant succeeded.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Click and Retry and errors is gone.

SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> SET serveroutput on
SQL> EXECUTE dbms_registry_sys.validate_components;
...(22:59:11) Starting validate_apex for APEX_050100
...(22:59:11) Checking missing sys privileges
...(22:59:11) Recompiling
...(22:59:12) Checking for objects that are still invalid
...(22:59:12) Key object existence check
...(22:59:13) Setting DBMS Registry for APEX to valid
...(22:59:13) Exiting validate_apex

PL/SQL procedure successfully completed.

The upgrade finished successful.

OK_upgrading_12.1_to_12.2

 

Advertisements

One Comment on “Upgrade Oracle Database from 12.1 to 12.2 “ORA-20001: MISSING GRANT””

  1. […] Upgrade Oracle Database from 12.1 to 12.2 “ORA-20001: MISSING GRANT” […]

    Like


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s