Oracle datapatch error PLS-00201: identifier ‘DBMS_LOB’

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;