Tag: oracle 19c md/admin/prvtimd.plb

Oracle 19c upgrade Errors ORA-04067: not executed, package body “MDSYS.MDPRVT_IDX” does not exist

arcsdegeoLeave a comment

The following errors were thrown when upgrading an Oracle database from 12cR1 to 19c release:

*** 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: /export/db2/upgrade_19c_log//catupgrd0.log AT LINE NUMBER: 991895

Identifier ORDIM 21-01-28 01:23:32
SCRIPT = [/orcl/db2/product/19.3/md/admin/prvtimd.plb]
ERROR = [ORA-04067: not executed, package body “MDSYS.MDPRVT_IDX” does not exist]
STATEMENT = [declare
already_exists exception;
pragma exception_init(already_exists, -00955);
column_exists exception;
pragma exception_init(column_exists, -01430);
begin
begin

execute immediate
‘ CREATE TABLE MDSYS.SDO_INDEX_AGGREGATES_TABLE
(SDO_INDEX_OWNER varchar2(32),
SDO_INDEX_NAME varchar2(32),
SDO_INDEX_OID varchar2(32),
SDO_NUMTILES NUMBER,
SDO_RTREE_NUM_NODES NUMBER,
SDO_NUM_ROWS NUMBER,
SDO_NUM_BLKS NUMBER,
CONSTRAINT unique_aggregate
PRIMARY KEY (SDO_INDEX_OWNER, SDO_INDEX_NAME)) ‘;
exception
when already_exists then NULL;
when others then
SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE,
‘EXCEPTION[prvtimd.sql(‘ || $$PLSQL_LINE || ‘)1]: ‘ || SQLERRM); RAISE;
end;

begin
execute immediate
‘ Alter table MDSYS.SDO_INDEX_AGGREGATES_TABLE
modify (SDO_INDEX_OWNER varchar2(130),
SDO_INDEX_NAME varchar2(130)) ‘;
exception
when others then
SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE,
‘EXCEPTION[prvtimd.sql(‘ || $$PLSQL_LINE || ‘)2]: ‘ || SQLERRM); RAISE;
end;

begin
execute immediate
‘ CREATE TABLE MDSYS.SDO_INDEX_METADATA_TABLE
(SDO_INDEX_OWNER varchar2(32),
SDO_INDEX_TYPE varchar2(32),
SDO_LEVEL NUMBER,
SDO_NUMTILES NUMBER,
SDO_MAXLEVEL NUMBER,
SDO_COMMIT_INTERVAL NUMBER,
SDO_INDEX_TABLE varchar2(32),
SDO_INDEX_NAME varchar2(32),
SDO_INDEX_PRIMARY NUMBER,
SDO_TSNAME varchar2(32),
SDO_COLUMN_NAME varchar2(2048),
SDO_RTREE_HEIGHT number,
SDO_RTREE_NUM_NODES number,
SDO_RTREE_DIMENSIONALITY number,
SDO_RTREE_FANOUT number,
SDO_RTREE_ROOT varchar2(32),
SDO_RTREE_SEQ_NAME varchar2(32),
SDO_FIXED_META RAW(255),
SDO_TABLESPACE varchar2(32),
SDO_INITIAL_EXTENT varchar2(32),
SDO_NEXT_EXTENT varchar2(32),
SDO_PCTINCREASE NUMBER,
SDO_MIN_EXTENTS NUMBER,
SDO_MAX_EXTENTS NUMBER) ‘;
exception
when already_exists then NULL;
when others then
SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE,
‘EXCEPTION[prvtimd.sql(‘ || $$PLSQL_LINE || ‘)3]: ‘ || SQLERRM); RAISE;
end;

begin
execute immediate
‘ Alter table MDSYS.SDO_INDEX_METADATA_TABLE
modify (SDO_COLUMN_NAME varchar2(2048)) ‘;
exception
when others then
SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE,
‘EXCEPTION[prvtimd.sql(‘ || $$PLSQL_LINE || ‘)4]: ‘ || SQLERRM); RAISE;
end;

begin
execute immediate
‘ Alter table MDSYS.SDO_INDEX_METADATA_TABLE add ( SDO_INDEX_DIMS number) ‘;
exception
when column_exists then NULL;
when others then
SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE,
‘EXCEPTION[prvtimd.sql(‘ || $$PLSQL_LINE || ‘)5]: ‘ || SQLERRM); RAISE;
end;

begin
execute immediate
‘ Alter table MDSYS.SDO_INDEX_METADATA_TABLE add ( SDO_LAYER_GTYPE varchar2(32)) ‘;
exception
when column_exists then NULL;
when others then
SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE,
‘EXCEPTION[prvtimd.sql(‘ || $$PLSQL_LINE || ‘)6]: ‘ || SQLERRM); RAISE;
end;

begin
execute immediate
‘ Alter table MDSYS.SDO_INDEX_METADATA_TABLE add
( SDO_RTREE_PCTFREE number) ‘;
exception
when column_exists then NULL;
when others then
SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE,
‘EXCEPTION[prvtimd.sql(‘ || $$PLSQL_LINE || ‘)7]: ‘ || SQLERRM); RAISE;
end;

begin
execute immediate
‘ Alter table MDSYS.SDO_INDEX_METADATA_TABLE add
( SDO_INDEX_PARTITION varchar2(32)) ‘;
exception
when column_exists then NULL;
when others then
SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE,
‘EXCEPTION[prvtimd.sql(‘ || $$PLSQL_LINE || ‘)8]: ‘ || SQLERRM); RAISE;
end;

Identifier ORDIM 21-01-28 01:23:32
SCRIPT = [/orcl/db2/product/19.3/md/admin/prvtimd.plb]
ERROR = [ORA-06512: at line 206]

STATEMENT = [as above]

Identifier ORDIM 21-01-28 01:23:32
SCRIPT = [/orcl/db2/product/19.3/md/admin/prvtimd.plb]
ERROR = [ORA-06508: PL/SQL: could not find program unit being called: “MDSYS.MDPRVT_IDX”]

STATEMENT = [as above]

Identifier ORDIM 21-01-28 01:23:32
SCRIPT = [/orcl/db2/product/19.3/md/admin/prvtimd.plb]
ERROR = [ORA-06512: at “MDSYS.SDO_INDEX_METADATA_UPDATE”, line 4]

STATEMENT = [as above]

Identifier ORDIM 21-01-28 01:23:32
SCRIPT = [/orcl/db2/product/19.3/md/admin/prvtimd.plb]
ERROR = [ORA-04088: error during execution of trigger ‘MDSYS.SDO_INDEX_METADATA_UPDATE’]

STATEMENT = [as above]

Identifier ORDIM 21-01-28 01:23:32
SCRIPT = [/orcl/db2/product/19.3/md/admin/prvtimd.plb]
ERROR = [ORA-06512: at line 199]
STATEMENT = [as above]

Investigation/Solution:

I found out multimedia component was invalid after the upgrade:

SQL> set pages 1000

SQL> set lines 300

SQL>

SQL>

SQL> select COMP_ID,COMP_NAME,VERSION,VERSION_FULL,STATUS from dba_registry where COMP_NAME=’Oracle Multimedia’;

COMP_ID                        COMP_NAME

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

VERSION                        VERSION_FULL                   STATUS

—————————— —————————— ——————————————–

ORDIM                          Oracle Multimedia

19.0.0.0.0                     19.3.0.0.0                     INVALID

 what I have done …. I have rolled-back the upgrade using flashback to Oracle 12cR1 , then applied the latest patches on 19c binaries with 19.10 (January 2021)…. And re-performed the upgrade again.

Observation: the upgrade will generate a file under /tmp directory called db2_catctl.dat in my case  (/tmp/$ORACLESID_catctl.dat) you better remove it before re-running the upgrade process again.

Also, ensure /etc/oratab file is pointing to Oracle 19c binaries

cd $ORACLE_HOME/rdbms/admin

sqlplus / as sysdba

SQL> startup upgrade;

SQL> exit;

nohup $ORACLE_HOME/perl/bin/perl catctl.pl -n 2 -l /orcl/tmp/upgrade_19c_log/ catupgrd.sql &

check the upgrade logs….it should be successful

then recompile the objects and check database components:

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

SQ> select * from dba_registry;