12.2 upgrade driver failed | jtfiaibu.sql | DRG-10507: duplicate index name: JTF_AMV_ITEMS_URL_CTX

Error:

12.2 upgrade driver failed on jtfiaibu.sql  with DRG-10507: duplicate index name: JTF_AMV_ITEMS_URL_CTX

"sqlplus -s APPS/***** @/jaydg/oraapps/jayprod/apps/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql '/jaydg/oraapps/jayprod/apps/fs1/EBSapps/appl/jtf/12.0.0/patch/115/sql/jtfiaibu.sql &un_jtf &pw_jtf &un_apps'

Connected.

PL/SQL procedure successfully completed.

Connected.

DECLARE

*

ERROR at line 1:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

ORA-20000: Oracle Text error:

DRG-10507: duplicate index name: JTF_AMV_ITEMS_URL_CTX

ORA-06512: at ""CTXSYS.DRUE"", line 160

ORA-06512: at ""CTXSYS.TEXTINDEXMETHODS"", line 366

ORA-06512: at ""SYS.DBMS_SQL"", line 1199

ORA-06512: at line 37

Cause:

 jtfiaibu.sql will try to drop and recreate the JTF_AMV_ITEMS_URL_CTX. But Please note that dropping text index using DROP INDEX sql is not enough as it may have some traces in ctxsys.dr$% views. So we had to delete the index from ctxsys.dr% views. 

I feel this issue occurs only on 11.2.0.4 database & not on 12c+ databases.

Solution:

a. Please make sure Oracle Text component is valid in dba_registry.  If not, resolve the dba_registry issue and restart the worker.

b. If Oracle Text is valid & still you are facing the same issue, then follow the below steps.

SQL> select idx_id from ctxsys.dr$index where idx_name='JTF_AMV_ITEMS_URL_CTX';

    IDX_ID

----------

      4153 

SQL> show user;

USER is "APPS"

SQL>  delete from ctxsys.dr$index_value where IXV_IDX_ID = 4153;

86 rows deleted.

SQL> delete from ctxsys.dr$index_object where IXO_IDX_ID = 4153;

9 rows deleted.

SQL> delete from ctxsys.dr$index where idx_id = 4153;

1 row deleted.

SQL> commit;

Commit complete.

C.  Delete DR$ tables .. PLease make sure it doens't have any rows. if required, take a backup of the same.

SQL> select table_name from dba_tables where table_name like 'DR%JTF_AMV_ITEMS_URL_CTX%';

TABLE_NAME

------------------------------

DR$JTF_AMV_ITEMS_URL_CTX$I

DR$JTF_AMV_ITEMS_URL_CTX$K

DR$JTF_AMV_ITEMS_URL_CTX$N

DR$JTF_AMV_ITEMS_URL_CTX$R

Drop the above tables.

d. Restart the worker