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
