Can someone explain ORA-29861 error in plain english and its possible cause?

Question

I have an application implemented in Grails framework using underlying Hibernate. After it runs for a while, I got an Oracle DB error and resolved it by rebuilding the offending index. I wonder if anyone can propose the possible cause(s) and ways to prevent it from happening.

Caused by: org.springframework.jdbc.UncategorizedSQLException:

Hibernate operation: Could not execute JDBC batch update; uncategorized SQLException for SQL [update RSS_ITEM set guid=?, pubdate=?, link=?, rss_source_id=?, title=?, description=?, rating_raw=?, rating_tuned=?, date_created=?, date_locked=? where RSS_ITEM_ID=?]; SQL state [99999]; error code [29861]; ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

; nested exception is java.sql.BatchUpdateException: ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

Answer 1

http://ora-29861.ora-code.com/

Cause: An attempt has been made to access a domain index that is being built or is marked failed by an unsuccessful DDL or is marked unusable by a DDL operation.

Action: Wait if the specified index is marked LOADING Drop the specified index if it is marked FAILED Drop or rebuild the specified index if it is marked UNUSABLE.

That should hopefully be enough context. Can you figure out the problem from that?

Answer 2

Domain indexes are a special type of index. It is possible to build our own using OCI but the chances are you're using one of the index types offered by Oracle Text. I say this as your table seems to include free text columns.

The most commonly used Text index is the CTXSYS.CONTEXT index type. The point about this index type is that it is not maintained transactionally, so as to minimize the effort involved in indexing large documents. What happens instead is that a background process, such as a database job, kicks off the index synchronization on a regular basis. The index is unusable while it is being synchronized. If the resync fails for any reason then you will need to drop and recreate the index.

Is this a regular occurrence? If so you may need to re-appraise your application. Perhaps a different sort of index (such as CTXSYS.CTXCAT) might be more appropriate. One thing which strikes me about your error message is that your UPDATE statement touches a lot of columns, including what looks like the primary key. This makes me think you have a single generic update statement which sets every column regardless of whether it has actually changed. This is bad practice with normal indexes; it will kill your application if you are using text indexes.