ORA-1403 during TEXT / CONTEXT post upgrade

When you see an ORA-1403 during TEXT / CONTEXT post upgrade phase, regardless of using AutoUpgrade, dbupgrade, catctl or DBUA, no worries. Two people reported this to me this week. And my dear support colleague Klaus Herndl told me about the existing MOS note. But there is a bit more to explain, especially as the bug is non-public and the MOS note doesn’t tell you when this can happen.

ORA-1403 during TEXT / CONTEXT post upgrade

Photo by Moritz Schmidt on Unsplash

What happens?

This is the error pattern you may see during the post upgrade phase when Oracle Text is installed in your database:

Oracle Database Release 19 Post-Upgrade Status Tool     02-13-2020 06:59:49

Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS

Oracle Server                          UPGRADED      19.6.0.0.0  00:14:28
Oracle Text
   ORA-01403: no data found
   ORA-06512: at line 28
   ORA-06512: at line 8
   ORA-01403: no data found
   ORA-06512: at line 28
   ORA-06512: at line 8
Oracle Text                             INVALID      19.6.0.0.0  00:00:35
Oracle Real Application Clusters       UPGRADED      19.6.0.0.0  00:00:00
Oracle Workspace Manager               UPGRADED      19.6.0.0.0  00:01:02
Oracle Label Security                  UPGRADED      19.6.0.0.0  00:00:10
Oracle XML Database                    UPGRADED      19.6.0.0.0  00:03:03
Final Actions                                                    00:03:15
Post Upgrade                                                     00:01:28

Total Upgrade Time: 00:24:01

Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.

Grand Total Upgrade Time:    [0d:0h:24m:01s]

The database upgrade completed. But ORACLE TEXT (formerly known as CONTEXT option) is INVALID.

And just to be clear, this can happen with every upgrade to Oracle Database 12.2.0.1, 18c and 19c – from whatever supported source version you are upgrading from.

How to fix this?

There are two ways to fix this. You either apply patch 28640772 to your target Oracle Home and repeat the upgrade.

Or you follow the manual fix outlined in:

If you are sure that this happened only for Oracle Text – you run this procedure:

connect / as sysdba
spool ctx_postupgrade.log
set echo on
show user
-- Delete the errors from the log
DELETE FROM sys.registry$error WHERE trim(identifier) = 'CONTEXT';
-- Check
SELECT count(*) Err_count FROM sys.registry$error WHERE trim(identifier) = 'CONTEXT';
-- Run Text's postupgrade script and validate Text
ALTER SESSION SET CURRENT_SCHEMA = CTXSYS;
@?/ctx/admin/@ctxposup.sql
EXECUTE dbms_registry.loaded('CONTEXT');
EXECUTE sys.validate_context;
ALTER SESSION SET CURRENT_SCHEMA = SYS;
-- Check
select comp_id, version, status from dba_registry
where comp_id = 'CONTEXT';
spool off

When will this be fixed?

This is the actual reason why I write this blog post. At first, as a reminder to myself. Then of course to alert you in case this happens in your environment. And at third, because the MOS note does not clearly give a statement when this will be fixed but instead mentions only 18c. You can see the non-public bug 28640772 is non-public, but there is a bit more to know.

The issue will be fixed in Oracle 20c. It can happen with all upgrades to Oracle Database 12.2.0.1, 18c and 19c where COMP_ID = CONTEXT is present in CDB_REGISTRY. There are a few one-off fixes available for Oracle 12.2.0.1 and Oracle 18c. But not a single one for Oracle 19c so far. Hence, I think it is good for you to know that this issue happens when you upgrade to:

  • Oracle Database 12.2.0.1
  • Oracle Database 18c
  • Oracle Database 19c

and no fix so far is included in or scheduled for any RU or RUR.

–Mike

Share this: