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:
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
Hi Mike,
Is the issue related to upgrade from only 11.2.0.4 TO 12.2 and higher versions
or upgrade from any version to 12.2 and higher ?
Regards,
Leszek
Thanks – I made it more clear now 🙂
ADDED:
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.
Thanks for the hint, Leszek!
Cheers,
Mike
Mike,
Is there a way to proactively check for this issue in 11g database prior to upgrade?
Thanks
Arun
Hi Arun,
yes – if TEXT/CONTEXT is present, you will likely hit this issue.
Cheers,
Mike
You should note that the procedure must be run as CTXSYS.
Hi Chris,
not sure if you need to. It switches the user before.
Cheers,
Mike
Mike,
Thanks for updating the post. The workaround in MOS note 2475629.1 is not very clear. Let’s say I am using DBUA to to upgrade. The upgrade fails with ORA-1403. DBUA offers to flashback the database to 11g. If I do not choose this option, then DBUA leaves the database in UPGRADE mode. What steps do I need to do to apply the workaround and how exactly do I proceed to complete the upgrade? Can I open the database in NORMAL mode, apply the fix as per MOS note and re-run DBUA?
Thanks,
Arun
Arun,
I can give you exactly one recommendation:
Use AutoUpgrade.
And when the DBUA leaves you in pain and tears (sorry!), you can always step in and save your souls with “dbupgrade -R” 😉
As I tried to outline in the blog post, you can apply the manual fix after this has happened. As the database is in UPGRADE mode, you can apply the manual fix at this stage, then call “dbupgrade -R”. At least, this is my understanding. But the best would it be to check this BEFORE the upgrade.
Cheers,
Mike