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

 


Upgrading Oracle 9i Database to Oracle 10g Database with Near-Zero Downtime

An Oracle database upgrade is a process of transforming an existing Oracle database into a later or the current release of the Oracle Database.

This upgrade process normally requires a significant amount of database down time.

This task of upgrade becomes more difficult for businesses which need to be 24*7 operational and anyoutage negatively affects business operations and customer service. In 24*7 organizations like Banks the database availability is so crucial that the downtime is bargained in minutes. Minimizing downtime is a challenge every organization faces.

Oracle Streams replication enables us to perform an upgrade with near-zero downtime.

This is accomplished by configuring Oracle Streams on the existing Oracle 9i database which captures all the changes on the source database and replicates the changes to thetarget database (Oracle 10g).

The rest of the document presents a step-by-step approach of setting the Oracle Streams replication and performing an upgrade to Oracle 10g database.

See link below

Upgrading Oracle 9i database to Oracle 10g with near zero downtime

Enjoy


Upgrading from Oracle 9i to Oracle Database 11g: A Real World Customer Experience

This technical Oracle White Paper will cover a whole Proof-of-Concept (PoC) done with one of the largest retail companies of the world.

The goals of this“Move on to Oracle Database 11g” PoC were to test the easiest way to upgrade400 databases directly from Oracle 9.2.0.8 to Oracle Database 11.1.0.6, and to evaluate Oracle Database 11g’s performance features such as Oracle Tuning Pack, SQL Plan Management, Real Application Testing with Database Replay, SQLPerformance Analyzer, and PL/SQL Native Compilation. Thereby it should beassured that nightly OLTP batch runs complete in the same amount of time, orfaster, than in the current production environment.

Upgrading directly from Oracle 9i to Oracle Database 11g will not only guaranteePremium Support until August 2012 – it will offer you many useful features tomake this upgrade the most predictable and risk free database upgrade ever,ensuring excellent performance in the new Oracle Database 11g environment.

See link below

9i_to_11g_real_world_customer_experience

Enjoy


Best Practices for Upgrading to Oracle Database 11g Release 2

All you need to know during the upgrade of the database.

See link below

best-practices-for-upgrading-11gr2

Enjoy