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

Changes Installing Oracle Grid Infrastructure 12.2

Starting with Oracle Grid Infrastructure 12c Release 2 (12.2), Oracle Grid Infrastructure software is available as an image file for download and installation.

You must extract the image software into the directory where you want your Grid home to be located, and then run the gridSetup.sh (no more runInstaller) script to start Oracle Grid Infrastructure installation.

Before Install  (Important):
Since it’s only a image and you just unpack it, if download is corrupted then you will get a corrupted Oracle Home.
Then I recommend you perform a check before.

Example:

grid@lpi-oracle:/u01/mount> unzip -t aixppc64_12201_grid_home.zip |grep -v OK
Archive: aixppc64_12201_grid_home.zip
No errors detected in compressed data of aixppc64_12201_grid_home.zip.

How to Install

Just unpack the zip image on new OH.

If you unpack on /tmp/grid and run gridSetup.sh the new OH will be /tmp/grid.

grid@lpi-oracle:/u01/mount> unzip aixppc64_12201_grid_home.zip -d /u01/app/grid/product/12.2.0/grid/
 creating: /u01/app/grid/product/12.2.0/grid/OPatch/
 inflating: /u01/app/grid/product/12.2.0/grid/OPatch/README.txt
 creating: /u01/app/grid/product/12.2.0/grid/OPatch/auto/
 creating: /u01/app/grid/product/12.2.0/grid/OPatch/auto/core/
 creating: /u01/app/grid/product/12.2.0/grid/OPatch/auto/core/bin/
 inflating: /u01/app/grid/product/12.2.0/grid/OPatch/auto/core/bin/opatchauto.sh
 inflating: /u01/app/grid/product/12.2.0/grid/OPatch/auto/core/bin/opatchautoCopy.sh
.
.
.
finishing deferred symbolic links:
 /u01/app/grid/product/12.2.0/grid/bin/lbuilder -> ../nls/lbuilder/lbuilder
 /u01/app/grid/product/12.2.0/grid/javavm/admin/cbp.jar -> ../../javavm/jdk/jdk8/admin/cbp.jar
 /u01/app/grid/product/12.2.0/grid/javavm/admin/classes.bin -> ../../javavm/jdk/jdk8/admin/classes.bin
 /u01/app/grid/product/12.2.0/grid/javavm/admin/lfclasses.bin -> ../../javavm/jdk/jdk8/admin/lfclasses.bin
 /u01/app/grid/product/12.2.0/grid/javavm/admin/libjtcjt.so -> ../../javavm/jdk/jdk8/admin/libjtcjt.so
 /u01/app/grid/product/12.2.0/grid/javavm/lib/jce.jar -> ../../javavm/jdk/jdk8/lib/jce.jar
 /u01/app/grid/product/12.2.0/grid/javavm/lib/security/US_export_policy.jar -> ../../../javavm/jdk/jdk8/lib/security/US_export_policy.jar
 /u01/app/grid/product/12.2.0/grid/javavm/lib/security/cacerts -> ../../../javavm/jdk/jdk8/lib/security/cacerts
 /u01/app/grid/product/12.2.0/grid/javavm/lib/security/java.security -> ../../../javavm/jdk/jdk8/lib/security/java.security
 /u01/app/grid/product/12.2.0/grid/javavm/lib/security/local_policy.jar -> ../../../javavm/jdk/jdk8/lib/security/local_policy.jar
 /u01/app/grid/product/12.2.0/grid/javavm/lib/sunjce_provider.jar -> ../../javavm/jdk/jdk8/lib/sunjce_provider.jar
 /u01/app/grid/product/12.2.0/grid/jdk/jre/lib/ppc64/classic/libjvm.a -> libjvm.so
 /u01/app/grid/product/12.2.0/grid/jdk/jre/lib/ppc64/j9vm/libjvm.a -> libjvm.so
 /u01/app/grid/product/12.2.0/grid/jdk/jre/lib/ppc64/libjsig.a -> libjsig.so
 /u01/app/grid/product/12.2.0/grid/lib/libjavavm12.a -> ../javavm/jdk/jdk8/lib/libjavavm12.a
 /u01/app/grid/product/12.2.0/grid/lib/libodm12.so -> libodmd12.so


 

You can’t change ORACLE HOME during installation, you can set ORACLE_BASE only.

Note: Download and copy the Oracle Grid Infrastructure image files to the local node only. During installation, the software is copied and installed on all other nodes in the cluster.

Grid_install

Grid_install1

 

Another new important Feature.

 

Starting with Oracle Grid Infrastructure 12c Release 2 (12.2), Oracle Grid Infrastructure installer supports the option of deploying Oracle Domain Services Clusters and Oracle Member Clusters.

Two ways to Deploying a Grid Installation for a Cluster:

Oracle Standalone Cluster – Default Installation of Oracle Clusterware

Oracle Cluster Domain –  If you have multiples Oracle Clusterware Installation in your infraestructure and want manage it as single Cluster, then you must start think deploy a Cluster Domain.
Multiple cluster configurations are grouped under an Oracle Cluster Domain for management purposes and make use of shared services available within that Oracle Cluster Domain. The cluster configurations within that Oracle Cluster Domain include Oracle Domain Services Cluster and Oracle Member Clusters.

foto_oracle

Later I will create a new post explaining the new installation feature Oracle Domain Services Clusters and Oracle Member Clusters.