The following errors were thrown when upgrading an Oracle database from 12cR1 to 19c release:
*** WARNING: ERRORS FOUND DURING UPGRADE ***
- Evaluate the errors found in the upgrade logs
and determine the proper action. - 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;
Geodata Master 