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.
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”
This does not apply to Windows platform?
The bug is generic – but unfortunately, on windows there are no one-offs as far as I’m aware. You may need to check with Oracle Support please.
Until 19.9 – please forgive my ignorance but does this mean it is fixed in 19.9?
Sorry for my phrasing – “until” means “including”. I can’t confirm inclusion into a future RU but it looks good for 19.10.
Thx a ton for your post.
188.8.131.52.210119 RU (Patch 32218454) includes patch 31088341.