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.
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 18.104.22.168.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 22.214.171.124.0 00:00:35 Oracle Real Application Clusters UPGRADED 126.96.36.199.0 00:00:00 Oracle Workspace Manager UPGRADED 188.8.131.52.0 00:01:02 Oracle Label Security UPGRADED 184.108.40.206.0 00:00:10 Oracle XML Database UPGRADED 220.127.116.11.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
And just to be clear, this can happen with every upgrade to Oracle Database 18.104.22.168, 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:
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; @?/email@example.com 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
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 22.214.171.124, 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 126.96.36.199 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 188.8.131.52
- Oracle Database 18c
- Oracle Database 19c
and no fix so far is included in or scheduled for any RU or RUR.