The following errors were generated in the “datapatch” part of applying January CPU 2021 on Oracle 19c database:
Validating logfiles…done
Patch 32218454 apply: WITH ERRORS
logfile: /orcl/db22/cfgtoollogs/sqlpatch/32218454/24018797/32218454_apply_db22_2021Feb12_01_13_32.log (errors)
-> Error at line 28715: script rdbms/admin/prvtgwmpl.plb
- Warning: Package Body created with compilation errors.
-> Error at line 28722: script rdbms/admin/prvtgwmpl.plb
- 3422/8 PL/SQL: Statement ignored
-> Error at line 28723: script rdbms/admin/prvtgwmpl.plb
- 3422/50 PLS-00201: identifier 'DBMS_RANDOM' must be declared
-> Error at line 28724: script rdbms/admin/prvtgwmpl.plb
- 5631/9 PL/SQL: Statement ignored
-> Error at line 28725: script rdbms/admin/prvtgwmpl.plb
- 5631/9 PLS-00201: identifier 'DBMS_LOB' must be declared
-> Error at line 28726: script rdbms/admin/prvtgwmpl.plb
- 5688/9 PL/SQL: Statement ignored
-> Error at line 28727: script rdbms/admin/prvtgwmpl.plb
- 5688/9 PLS-00201: identifier 'DBMS_LOB' must be declared
-> Error at line 28728: script rdbms/admin/prvtgwmpl.plb
- 6477/4 PL/SQL: Statement ignored
-> Error at line 28732: script rdbms/admin/prvtgwmpl.plb
- 6477/4 PLS-00201: identifier 'DBMS_LOB' must be declared
-> Error at line 28733: script rdbms/admin/prvtgwmpl.plb
- 6534/4 PL/SQL: Statement ignored
-> Error at line 28734: script rdbms/admin/prvtgwmpl.plb
- 6534/4 PLS-00201: identifier 'DBMS_LOB' must be declared
-> Error at line 28735: script rdbms/admin/prvtgwmpl.plb
- 6625/6 PL/SQL: Statement ignored
-> Error at line 28736: script rdbms/admin/prvtgwmpl.plb
- 6625/17 PLS-00201: identifier 'DBMS_LOB' must be declared
-> Error at line 28737: script rdbms/admin/prvtgwmpl.plb
- 6895/14 PL/SQL: Statement ignored
-> Error at line 28738: script rdbms/admin/prvtgwmpl.plb
- 6895/14 PLS-00201: identifier 'DBMS_LOB' must be declared
-> Error at line 28742: script rdbms/admin/prvtgwmpl.plb
- 6930/14 PL/SQL: Statement ignored
-> Error at line 28743: script rdbms/admin/prvtgwmpl.plb
- 6930/14 PLS-00201: identifier 'DBMS_LOB' must be declared
-> Error at line 28744: script rdbms/admin/prvtgwmpl.plb
- 6932/14 PL/SQL: Statement ignored
-> Error at line 28745: script rdbms/admin/prvtgwmpl.plb
- 6932/14 PLS-00201: identifier 'DBMS_LOB' must be declared
-> Error at line 28746: script rdbms/admin/prvtgwmpl.plb
- 7048/6 PL/SQL: Statement ignored
-> Error at line 28747: script rdbms/admin/prvtgwmpl.plb
- 7048/6 PLS-00201: identifier 'DBMS_LOB' must be declared
-> Error at line 28769: script rdbms/admin/prvtgwmpl.plb
- 3422/8 PL/SQL: Statement ignored
-> Error at line 28770: script rdbms/admin/prvtgwmpl.plb
- 3422/50 PLS-00201: identifier 'DBMS_RANDOM' must be declared
-> Error at line 28771: script rdbms/admin/prvtgwmpl.plb
- 5631/9 PL/SQL: Statement ignored
-> Error at line 28772: script rdbms/admin/prvtgwmpl.plb
- 5631/9 PLS-00201: identifier 'DBMS_LOB' must be declared
-> Error at line 28773: script rdbms/admin/prvtgwmpl.plb
- 5688/9 PL/SQL: Statement ignored
-> Error at line 28774: script rdbms/admin/prvtgwmpl.plb
- 5688/9 PLS-00201: identifier 'DBMS_LOB' must be declared
-> Error at line 28775: script rdbms/admin/prvtgwmpl.plb
- 6477/4 PL/SQL: Statement ignored
-> Error at line 28779: script rdbms/admin/prvtgwmpl.plb
- 6477/4 PLS-00201: identifier 'DBMS_LOB' must be declared
-> Error at line 28780: script rdbms/admin/prvtgwmpl.plb
- 6534/4 PL/SQL: Statement ignored
-> Error at line 28781: script rdbms/admin/prvtgwmpl.plb
- 6534/4 PLS-00201: identifier 'DBMS_LOB' must be declared
-> Error at line 28782: script rdbms/admin/prvtgwmpl.plb
- 6625/6 PL/SQL: Statement ignored
-> Error at line 28783: script rdbms/admin/prvtgwmpl.plb
- 6625/17 PLS-00201: identifier 'DBMS_LOB' must be declared
-> Error at line 28784: script rdbms/admin/prvtgwmpl.plb
- 6895/14 PL/SQL: Statement ignored
-> Error at line 28785: script rdbms/admin/prvtgwmpl.plb
- 6895/14 PLS-00201: identifier 'DBMS_LOB' must be declared
-> Error at line 28789: script rdbms/admin/prvtgwmpl.plb
- 6930/14 PL/SQL: Statement ignored
-> Error at line 28790: script rdbms/admin/prvtgwmpl.plb
- 6930/14 PLS-00201: identifier 'DBMS_LOB' must be declared
-> Error at line 28791: script rdbms/admin/prvtgwmpl.plb
- 6932/14 PL/SQL: Statement ignored
-> Error at line 28792: script rdbms/admin/prvtgwmpl.plb
- 6932/14 PLS-00201: identifier 'DBMS_LOB' must be declared
-> Error at line 28793: script rdbms/admin/prvtgwmpl.plb
- 7048/6 PL/SQL: Statement ignored
-> Error at line 28794: script rdbms/admin/prvtgwmpl.plb
- 7048/6 PLS-00201: identifier 'DBMS_LOB' must be declared
Please refer to MOS Note 1609718.1 and/or the invocation log
/orcl/db22/cfgtoollogs/sqlpatch/sqlpatch_76633_2021_02_12_01_13_10/sqlpatch_invocation.log
for information on how to resolve the above errors.
To fix this problem:
the above errors generated because multiple built-in oracle pacakges are revoked from “PUBLIC” as best security practice
if you query dba_registry_sqlpatch you will find that the patch is applied with ERRORS ….to overcome this and esnure success
patches being applied…grant these packages temporarly to public:
sqlplus / as sysdba
SQL> grant execute on DBMS_RANDOM to PUBLIC;
SQL> grant execute on UTL_FILE to PUBLIC;
SQL> grant execute on UTL_HTTP to PUBLIC;
SQL> grant execute on dbms_lob to PUBLIC;
SQL> grant execute on dbms_sql to PUBLIC;
SQL> grant execute on dbms_job to PUBLIC;
SQL> exit;
then, re-run datapatch again:
cd $ORACLE_HOME/OPatch
./datapatch -verbose
after that revoke the permissions:
SQL> revoke execute on DBMS_RANDOM from PUBLIC;
SQL> revoke execute on UTL_FILE from PUBLIC;
SQL> revoke execute on UTL_HTTP from PUBLIC;
SQL> revoke execute on dbms_lob from PUBLIC;
SQL> revoke execute on dbms_sql from PUBLIC;
SQL> revoke execute on dbms_job from PUBLIC;
then perform re-compilation:
SQL> ?@/rdbms/admin/utlrp.sql
SQL> select comp_name,status from dba_registry;