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.

Apply Patch 31088341 before Upgrade to prevent ORA-1403

Photo by Grant Durr on Unsplash

What is the issue?

This applies to all 19c upgrades at least until 19.9.0. 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. Hopefully in a future RU, this fix will be included. I will update the blog post then.

More Information and Links

–Mike

 

Share this: