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

Upgrading from Oracle 12cR1 to 12cR2 error ORA-40362: invalid object SYS.ODCIPARTINFOLIST

After upgrading a database from Oracle 12cR1 to 12cR2 (Non-CDB) environment, the following errors were thrown in the post-upgrade logs, and XDB component was not valid:

Oracle Database 12.2 Post-Upgrade Status Tool           02-24-2020 09:47:50

Component                               Current         Version  Elapsed Time

Name                                    Status          Number   HH:MM:SS

Oracle Server

   ORA-40362: invalid object SYS.ODCIPARTINFOLIST specified in the statement

Oracle Server                           INVALID      12.2.0.1.0  00:06:34

Oracle Text

    ORA-06550: line 4, column 22: PLS-00905: object CTXSYS.TEXTINDEXMETHODS is i

nvalid

ORA-06550: line 4, column 6:

PL/SQL: Statement ignored

PLS-00905: object CTXSYS.TEXTINDEXMETHODS is invalid ORA-06550: line 4, colu

mn 6:

PL/SQL: Statement ignored

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

    PL/SQL: Statement ignored

    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: Declar

ation ignored

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

ORA-0655

2: 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 CTXSY

S.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 ignore

d

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

ORA-06552: PL/SQL: D

eclaration ignored

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

ORA

-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_CONTAI

NS 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 i

gnored

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

ORA-06552: PL/S

QL: Declaration ignored

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

d

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: object CTXSYS.CTX_C

ONTAINS is invalid

ORA-06552: PL/SQL: Declaration ignored

ORA-06553: PLS-905: ob

ject CTXSYS.CTX_CONTAINS is invalid

Solution:

sqlplus / as sysdba

SQL> GRANT EXECUTE ON ODCIPartInfoList TO PUBLIC;

SQL> spool xdbreload.log

SQL> set echo on;

SQL> shutdown immediate;

SQL> startup upgrade;

SQL> @?/rdbms/admin/xdbrelod.sql

SQL> shutdown immediate;

SQL> startup;

SQL> @?/rdbms/admin/utlrp.sql

SQL> spool off

*** check database components after that:

SQL> select comp_name,status from dba_registry;

if still NOT Valid…..then perform a “drastic measure” by removing Oracle Text component and re-install it again !

To Remove Oracle Text:

sqlplus / as sysdba

SQL>@?/ctx/admin/catnoctx.sql
SQL> drop procedure sys.validate_context;
SQL>@?/rdbms/admin/utlrp.sql

check database components

SQL> select name,status from dba_registry;

if everything is Ok ….then we can install oracle text using DBCA (Database Configuration Assistant)

export DISPLAY=PCXXXXX:0.0

cd $ORACLE_HOME/bin

dbca