Error during creating Inventory Part "domain index is marked LOADING/FAILED/UNUSABLE"

Thank you Maduranga . issue has been solved.

Just Drop Index and recreated.

We are still getting this error even after deleting it (We got it actually while we are copying an inventory part) 

Has anyone found the root cause? @Maharawaththa @IFS_Shekhar 

Hi @EnzoFerrari,
Please refer KBA: 

https://community.ifs.com/technical-issues-101/error-ora-29861-domain-index-is-marked-loading-failed-unusable-3756

Best Regards,
Yasas 

@Yasas_AK, thank you sir 

Hi @EnzoFerrari,
You are most welcome !
Best Regards,
Yasas 

@Yasas Kasthuriarachchi, we scheduled our background jobs throughout the month as opposed to the month end which we had before. Due to the time taken to complete some of these background jobs, the error occurs for a significant time period sometime. For instance, we have an event setup to issue parts in IFS. This event seems to be affected by  "domain index is marked LOADING/FAILED/UNUSABLE". - not sure if it’s the cause

In your opinion, can events in IFS be affected? Any idea how to technically investigate to confirm it? Would be great if we can find a permanent solution for this annoying problem

Hi @EnzoFerrari,
The noted issue in thread and KBA addressed with regard to search domains and I haven’t seen a particular affect with events. Also you could schedule these background jobs to run during a weekend or off peak time so it will not interfere with your day to day business activities.

As per your information it seems as if the error ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE still persists due to a failed domain Index relevant to a component and you need to identify that and follow as per KBA noted above. So first you need to identify if any of your search domains has warnings or errors and mitigate them as described. Then you could isolate your issue and debug based on the point of issue occurrence (triggering point).

Best Regards,
Yasas

Maybe to identify which domain index has gone rogue, run the following SQL as appowner:

SELECT * FROM all_indexes
WHERE DOMIDX_OPSTATUS != 'VALID' OR STATUS != 'VALID' OR DOMIDX_STATUS != 'VALID'

When I checked the LCS knowledgebase and one particular case, they hinted at an ORACLE bug and this was from 1 year ago:

Q1: Is there a patch for this error? No

Q2: When does this error occur?
This error occurs when an attempt has been made to access a domain index that is being built or is marked failed or is marked unusable by a DDL operation. (DDL - Data Definition Language). Indexes can become unusable due to several reasons such as maintenance operations in the relevant table, etc.

Q3: There was information in the following communities. Is this information valid for IFS10 Update6?
Yes. But this is not a bug in IFS Application. This issue applies to Oracle version 12.1.3 and later.

If you have an oracle support account you can learn more about this error from the below link.
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=426329799051667&parent=EXTERNAL_SEARCH&sourceId=HOWTO&id=2215038.1&_afrWindowMode=0&_adf.ctrl-state=5yfrm4b6g_4

According to this documentation, this error happens from oracle  Version 12.1.3 and later. Currently there is no oracle patch for this. (this was as of last year)

Only workaround that they have provided as the solution, is to Drop and create the index.

You might also want to check if your customer is on Apps9 or Apps10. An Apps9 patch 144955 is supposed to let you know which index is unusable rather than the “domain index is marked LOADING/FAILED/UNUSABLE” which does not identify the domain index.

But at the time of writing this patch is only done for Apps9 and the Apps10 line is still openandpending since 2018. R&D will be notified/asked whether a patch for Apps10 will be forthcoming.