ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

Issue :

Planning ODS Load Concurrent Program fails with below error in the log file:

ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

Cause:

The issue is caused by the following setup:
The indexes with MSC might have corrupted.

Solution :

Please follow following steps to drop and recreate the indexes to fix the issue:

Note:
Please changes the APPS/APPS in step 3 with apps user and password.

1. Run the following SQL to generate all the 'drop' statements needed to drop all the indexes on the relevant MSC_ tables:

SELECT 'drop index msc.'||index_name||';'
FROM (
SELECT ods.table_name ,
  ai.index_name ,
  SUBSTR(ods.table_name,5)
  || '__'
  || TO_CHAR(mip.instance_id) partition_name
FROM msc_ods_table_v ods ,
  msc_inst_partitions mip ,
  all_indexes ai
WHERE ods.partition_type = 'R'
AND ods.table_name = ai.table_name
MINUS
SELECT ods.table_name ,
  aip.index_name ,
  aip.partition_name
FROM all_ind_partitions aip ,
  all_indexes ai ,
  msc_ods_table_v ods
WHERE ods.partition_type = 'R'
AND ods.table_name = ai.table_name
AND ai.index_name = aip.index_name
AND ai.owner = aip.index_owner);

2. Copy the resulting DROP statements to a notepad.

3. Execute the following SQL to generate all the 'adjava' commands to add the indexes back to the tables.

Note: In line 2, substitute the correct password for MSC and APPS (the second occurrence). In line 3, substitute the correct server name, port, and SID name. 

SELECT 'adjava -ms128m -mx256m -nojit oracle.apps.fnd.odf2.FndXdfCmp '
|| 'MSC MSC APPS APPS '
|| 'thin ebs.example.com:1521:EBSDB index '
|| '$MSC_TOP/patch/115/xdf/'||table_name||'.xdf '
|| '$FND_TOP/patch/115/xdf/xsl '
|| 'logfile=$HOME/adjava_XDF.log'
|| 'changedb=y"' as xdf_command, table_name
FROM (
SELECT ods.table_name ,
  ai.index_name ,
  SUBSTR(ods.table_name,5)
  || '__'
  || TO_CHAR(mip.instance_id) partition_name
FROM msc_ods_table_v ods ,
  msc_inst_partitions mip ,
  all_indexes ai
WHERE ods.partition_type = 'R'
AND ods.table_name = ai.table_name
MINUS
SELECT ods.table_name ,
  aip.index_name ,
  aip.partition_name
FROM all_ind_partitions aip ,
  all_indexes ai ,
  msc_ods_table_v ods
WHERE ods.partition_type = 'R'
AND ods.table_name = ai.table_name
AND ai.index_name = aip.index_name
AND ai.owner = aip.index_owner);

4. Copy the results to a notepad

5. Execute the DROP statements.

6. Execute the ADJAVA statements in Application Node.

7. Retest the collections.
 

Ref : 

Planning ODS Load Errors With ORA-14098: Index Mismatch For Tables In ALTER TABLE EXCHANGE PARTITION (Doc ID 2251732.1)