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.

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.

When does this happen?

Thanks to my colleagues from the TEXT team, especially to Aleksandra, I can share a bit more details. And you can check upfront whether your database may be affected or not. We will also discuss to include an upgrade check.

You can run the following procedure:

declare
  l_owner#  number;
  l_count   number;

begin
  select user# into l_owner# from sys."_BASE_USER" where name='CTXSYS';
  select idx_id into l_count from dr$index where
    idx_name = 'ENT_EXT_DICT_OBJ' and
    idx_owner# = l_owner#;

end;

/

Check this before upgrade when you have ORACLE TEXT installed. If it errors out with ORA-1403, you will hit the issue. If it completes successfully, you won’t see the error – and you can stop reading now.

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:

• MOS Note: 2475629.1 – ORA-01403 From Oracle Text’s Postupgrade ctxposup.sql Script

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

Another workaround?

There is another workaround possible. You would need to create a default policy for CTXSYS, the owner of TEXT.

sqlplus / as sysdba

alter session set current_schema=ctxsys;

exec CTX_DDL.create_policy('CTXSYS.ENT_EXT_DICT_OBJ',
      filter        => 'CTXSYS.NULL_FILTER',
      section_group => 'CTXSYS.NULL_SECTION_GROUP',
      lexer         => 'CTXSYS.BASIC_LEXER',
      stoplist      => 'CTXSYS.EMPTY_STOPLIST',
      wordlist      => 'CTXSYS.BASIC_WORDLIST'
  );

But it’s generally not advised to twiddle with the index in this way.

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. It is not very likely to happen, though. 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. But backports for 19c should be available soon as well. 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