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;

Advertisement

Oracle 19c upgrade error ORA-40362: invalid object SYS.ODCIPARTINFOLIST specified in the statement

As its known by now Oracle 19c database release is the long-term release for Oracle database system and by now your Infrastructure should be based on it.

One of the database systems while upgrading it from Oracle 12cR1 to 19c, the below errors generated (this was the first upgrade failure I have ever faced since my journey with 19c upgrade for the last 1 year).

Serial   Phase #:105  [orcl] Files:1    Time: 3s

Serial   Phase #:106  [orcl] Files:1    Time: 0s

Serial   Phase #:107  [orcl] Files:1     Time: 34s

——————————————————

Phases [0-107]         End Time:[2021_01_22 11:17:33]

——————————————————

Grand Total Time: 1359s

*** WARNING: ERRORS FOUND DURING UPGRADE ***

1. Evaluate the errors found in the upgrade logs

    and determine the proper action.

2. Rerun the upgrade when the problem is resolved

REASON:

      ERRORS FOUND: During Upgrade

         FILENAME: /orcl/export/upgrade_19c_log//catupgrd0.log AT LINE NUMBER: 802204

——————————————————

Identifier CATPROC 21-01-22 10:59:28

SCRIPT    = [/orcl/ora/product/19.3/rdbms/admin/catodci.sql]

ERROR    = [ORA-40362: invalid object SYS.ODCIPARTINFOLIST specified in the statement]

STATEMENT = [GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC]

——————————————————

——————————————————

Identifier CONTEXT 21-01-22 11:08:54

SCRIPT    = [/orcl/ora/product/19.3/ctx/admin/t1202000.sql]

ERROR    = [ORA-06550: line 4, column 22: PLS-00905: object CTXSYS.TEXTINDEXMETHODS is invalid

ORA-06550: line 4, column 6:

PL/SQL: Statement ignored

]

STATEMENT = [alter indextype context using TextIndexMethods]

——————————————————

——————————————————

Identifier CONTEXT 21-01-22 11:08:54

SCRIPT    = [/orcl/ora/product/19.3/ctx/admin/t1202000.sql]

ERROR    = [PLS-00905: object CTXSYS.TEXTINDEXMETHODS is invalid ORA-06550: line 4, column 6:

PL/SQL: Statement ignored

]

STATEMENT = [as above]

——————————————————

——————————————————

Identifier CONTEXT 21-01-22 11:08:54

SCRIPT    = [/orcl/ora/product/19.3/ctx/admin/t1202000.sql]

ERROR    = [ORA-06550: line 4, column 6: PL/SQL: Statement ignored

]

STATEMENT = [as above]

——————————————————

——————————————————

Identifier CONTEXT 21-01-22 11:08:54

SCRIPT    = [/orcl/ora/product/19.3/ctx/admin/t1202000.sql]

ERROR    = [PL/SQL: Statement ignored]

STATEMENT = [as above]

——————————————————

——————————————————

Identifier CONTEXT 21-01-22 11:08:54

SCRIPT    = [/orcl/ora/product/19.3/ctx/admin/dr0itype.sql]

ERROR    = [ORA-06552: PL/SQL: Declaration ignored ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAINS is invalid

]

STATEMENT = [create or replace operator contains binding

  (varchar2, varchar2) return number

     with index context, scan context TextIndexMethods

     compute ancillary data without column data using ctx_contains.Textcontains

The solution of this error is the following:

modify/edit the following file:

vi $ORACLE_HOME/rdbms/admin/catodci.sql

replace this statement: GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC

with

begin

  execute immediate ‘alter type ODCIPartInfo compile’;

  execute immediate ‘alter type ODCIPartInfoList compile’;

  begin

  execute immediate ‘GRANT EXECUTE ON ODCIPartInfo TO PUBLIC’;

  exception

  when others then

  null;

  end;

  begin

  execute immediate ‘GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC’;

  exception

  when others then

  null;

  end;

  begin

  execute immediate ‘GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC’;

  exception

  when others then

  null;

  end;

  begin

  execute immediate ‘GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC’;

  exception

  when others then

  null;

  end;

exception

  when others then

  null;

end;

/

After that, re-run the upgrade command