Skip to content
  • About
Search
Close

Geodata Master

Oracle Database Technical Articles & GeoSpatial Data Technology

Tag: MDSYS.MDPRVT_IDX

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

February 3, 2021 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;

Blog Stats

  • 642,586 hits

Recent Posts

  • Oracle Database Immutable Tables
  • Oracle datapump error ORA-39077: unable to subscribe agent KUPC$A_1_083325450472000
  • Oracle 19c Upgrade Error ORA-00119: invalid specification for system parameter DISPATCHERS
  • Oracle 21c New Feature in SQL*Plus – set jsonprint
  • Patching Oracle 21c database – October 2021 CPU [21.4.0.0.211019]
Follow Geodata Master on WordPress.com

Top Posts & Pages

  • Oracle PDB and when is DAPTATCH required to be executed
  • Oracle Database Product Life Cycle Support 19c,18c, 12cR2 Long Term & Innovation Releases !
  • RMAN List Backup Summary command
  • revoking a tablespace quota from user
  • Performing Import Data Pump with EXCLUDE=STATISTICS
  • location of Oracle SQL Developer Connections File
  • ora-28031 maximum of 148 enabled roles exceeded
  • ORA-31633: unable to create master table while performing expdp operation
  • sqlplus error for Oracle 12c in IBM AIX : rtld: 0712-001 Symbol CreateIoCompletionPort was referenced
  • Oracle 19c (19.12) has introduced New Security Feature “Gradual Database Password Rollover”

Archives

  • May 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • August 2021
  • July 2021
  • June 2021
  • May 2021
  • April 2021
  • March 2021
  • February 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • April 2020
  • March 2020
  • January 2020
  • December 2019
  • November 2019
  • October 2019
  • September 2019
  • August 2019
  • July 2019
  • June 2019
  • May 2019
  • April 2019
  • March 2019
  • February 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018
  • July 2018
  • May 2018
  • April 2018
  • March 2018
  • February 2018
  • January 2018
  • December 2017
  • November 2017
  • October 2017
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • May 2017
  • April 2017
  • March 2017
  • February 2017
  • January 2017
  • December 2016
  • November 2016
  • October 2016
  • September 2016
  • August 2016
  • July 2016
  • June 2016
  • April 2016
  • February 2016
  • January 2016
  • December 2015
  • November 2015
  • October 2015
  • September 2015
  • August 2015
  • July 2015
  • June 2015
  • May 2015
  • April 2015
  • March 2015
  • February 2015
  • January 2015
  • December 2014
  • November 2014
  • October 2014
  • September 2014
  • August 2014
  • July 2014
  • June 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • January 2014
  • December 2013
  • November 2013
  • October 2013
  • September 2013

Recent Comments

Oracle Database Immu… on Oracle Database 19c Blockchain…
Rajesh on Oracle Database java component…
Oracle database Unif… on Exploring Oracle 12c Unified…
Bud on Oracle 21c New Feature in SQL*…
ChenAIQ on datapatch errors while Patchin…

Search in my blog

Follow Geodata Master on WordPress.com

Top Posts & Pages

  • Oracle PDB and when is DAPTATCH required to be executed
  • Oracle Database Product Life Cycle Support 19c,18c, 12cR2 Long Term & Innovation Releases !
  • RMAN List Backup Summary command
  • revoking a tablespace quota from user
  • Performing Import Data Pump with EXCLUDE=STATISTICS
  • location of Oracle SQL Developer Connections File
  • ora-28031 maximum of 148 enabled roles exceeded
  • ORA-31633: unable to create master table while performing expdp operation
  • sqlplus error for Oracle 12c in IBM AIX : rtld: 0712-001 Symbol CreateIoCompletionPort was referenced
  • Oracle 19c (19.12) has introduced New Security Feature “Gradual Database Password Rollover”

Blog Stats

  • 642,586 hits

Archives

  • May 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • August 2021
  • July 2021
  • June 2021
  • May 2021
  • April 2021
  • March 2021
  • February 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • April 2020
  • March 2020
  • January 2020
  • December 2019
  • November 2019
  • October 2019
  • September 2019
  • August 2019
  • July 2019
  • June 2019
  • May 2019
  • April 2019
  • March 2019
  • February 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018
  • July 2018
  • May 2018
  • April 2018
  • March 2018
  • February 2018
  • January 2018
  • December 2017
  • November 2017
  • October 2017
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • May 2017
  • April 2017
  • March 2017
  • February 2017
  • January 2017
  • December 2016
  • November 2016
  • October 2016
  • September 2016
  • August 2016
  • July 2016
  • June 2016
  • April 2016
  • February 2016
  • January 2016
  • December 2015
  • November 2015
  • October 2015
  • September 2015
  • August 2015
  • July 2015
  • June 2015
  • May 2015
  • April 2015
  • March 2015
  • February 2015
  • January 2015
  • December 2014
  • November 2014
  • October 2014
  • September 2014
  • August 2014
  • July 2014
  • June 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • January 2014
  • December 2013
  • November 2013
  • October 2013
  • September 2013
Blog at WordPress.com.
Back to top
  • Follow Following
    • Geodata Master
    • Join 27 other followers
    • Already have a WordPress.com account? Log in now.
    • Geodata Master
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar