BIOVIA Workbook Full Text Search error after upgrade to Oracle 19c

GS 2022-12-16

Several customers have reported issues with the Full Text search not working properly in Workbook after upgrading the Oracle Database to 19c. This is due to an Oracle bug. Depending on the version, one or more Oracle patches may need to be applied. Please see the following document on the Oracle Support Website: Oracle Text Mandatory and Recommended Patches (Doc ID 2644957.1).

If experiencing the known bug, the following error will be present in the Oracle Alert log:
ORA-00600: internal error code, arguments: [kdsgrp1-kdsgrp]
and the corresponding Oracle trace file will include the following in the problem query:
"where (contains(VaultText_obj.Text, )".

If this occurs, then after confirming you have the required patches, you will also need to drop and create the VAULTTEXT_TEXT index in the SYMYXDB schema.
Dropping and recreating the index requires downtime, and can take a long time depending on the amount of data in the system. You should stop all Vault Services before doing so.

Below are the commands to drop and recreate the index:

drop index VAULTTEXT_TEXT force;

Create index VAULTTEXT_TEXT on VAULTTEXT (TEXT) indextype is ctxsys.context parameters ('FILTER CTXSYS.AUTO_FILTER FORMAT COLUMN format CHARSET COLUMN encoding SYNC (EVERY "sysdate+(3/1440)")');

In order to speed up the creation of the index, the parallel option can be added to the end of the statement, assuming you have the Oracle license to do so.

For example:
Create index VAULTTEXT_TEXT on VAULTTEXT (TEXT) indextype is ctxsys.context parameters ('FILTER CTXSYS.AUTO_FILTER FORMAT COLUMN format CHARSET COLUMN encoding SYNC (EVERY "sysdate+(3/1440)")') parallel 12;

The number used is dependent on the number of CPUs on your Database Server, so you’ll need to discuss with your DBA what an appropriate number is. The higher you can go, the faster it will be.

If you use this option, please be sure to change it back to noparallel after it is done, using the statement below:
Alter index VAULTTEXT_TEXT noparallel;

After running these statements, the error should no longer occur.