Apply Patch 31088341 before Upgrade to prevent ORA-1403

As I learned from a customer this week, this patch is a must have when you use Partitioning and you attempt to upgrade to Oracle 19.9.0 or earlier. So please apply patch 31088341 before Upgrade to prevent ORA-1403 happening.

Photo by Grant Durr on Unsplash

What is the issue?

This applies to all 19c upgrades at least until 19.9.0.

The fix is included from 19.10.0 Release Update onward. So if you are upgrading to 19.10.0 or higher, you can stop reading here (thanks Pablo for the hint!).

You may see this error pattern in catupgrd0.log:

==Error from catupgrd0.log===

=================================================================
20:34:11 SQL> Rem BEGIN bug 28757685: repair tabpart$
20:34:11 SQL> Rem
=========================================================================
20:34:11 SQL> /* bug 28757685: tabpart$.hiboundval and tabpart$.hiboundlen
are incorrect
20:34:11 SQL> * when tables are autolist partitioned and partitioning
columns are CHAR.
20:34:11 SQL> */
20:34:11 SQL> DECLARE
20:34:11 2 bhiboundval RAW(32767);
20:34:11 3 newhiboundval VARCHAR2(32767);
20:34:11 4 pos BINARY_INTEGER;
20:34:11 5 len BINARY_INTEGER;
20:34:11 6 multicol BOOLEAN;
20:34:11 7 numvals BINARY_INTEGER;
20:34:11 8 posnum BINARY_INTEGER;
20:34:11 9 typenum BINARY_INTEGER;
20:34:11 10 charsetform BINARY_INTEGER;
20:34:11 11 BEGIN
20:34:11 12 FOR alp IN (SELECT tp.obj#, tp.bo#,
..
..
20:34:11 129 WHEN OTHERS THEN
20:34:11 130 DBMS_SYSTEM.ksdwrt(DBMS_SYSTEM.trace_file,
20:34:11 131 'error when repairing tabpart$');
20:34:11 132 RAISE;
20:34:11 133 END;
20:34:11 134 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 132
ORA-06512: at line 75
ORA-06512: at line 75

=============

At first, your database is supposed to work fine. Hence, no panic. But you may get issues when you try to export a multi-value list partitioned table.

• ORA 1403 during upgrade script for multi column list partitioned tables with pkey of either char/binary data types.

This fix will exchange the a18.sql in ?/rdbms/admin. It is tagged as Bug 31088341 – A18.SQL NEEDS CHANGES IN THE REPAIR TABPART$ PORTION ADDED AS PART OF FIX#28757685.

The issue is a follow-up to Bug 30084975 – 19C UPGRADE FAILS WITH ERROR “ORA-01403: NO DATA FOUND” which is a follow-up to an incorrect fixup script delivered for Bug 28757685 – PDML:ORA-14308 WHEN AUTO LIST PARTITIONED TABLE DBMS_METADATA.GET_DDL GENERATED DDL.

How to prevent it?

At least until Oracle Database RU 19.9.0 you need to apply this patch for Bug 31088341 which exchanges the a18.sql file in ?/rdbms/admin. As mentioned above, the fix is included from 19.10.0 onward.

More Information and Links

• Bug 31088341 – A18.SQL NEEDS CHANGES IN THE REPAIR TABPART$ PORTION ADDED AS PART OF FIX#28757685
• Bug 30084975 – 19C UPGRADE FAILS WITH ERROR “ORA-01403: NO DATA FOUND”
• Bug 28757685 – PDML:ORA-14308 WHEN AUTO LIST PARTITIONED TABLE DBMS_METADATA.GET_DDL GENERATED DDL
• MOS Note: 2579106.1 – DATABASE UPGRADE TO 19C REPORTS ERROR “ORA-01403: NOT DATA FOUND”

–Mike