Resolving DRG-10502 and ORA-06512

Hi,
When I checked my Datapump export backup(logical backup) log file,I saw the below error message,In the below post I'm explaining the cause and the posible solution to get rid of this error in our next Datapump export backup.Remember,we need
to check our log files regularly for resolving such errors and to make sure our backup is complete error free as this is very much vital for DBA to avoid any future issues.

Error message:

ORA-39127: unexpected error from call to local_str := SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('WWV_FLOW_OH_IDX','APEX_040000','TEXTINDEXMETHODS','CTXSYS',11.02.00.00.00,newblock,0)
ORA-20000: Oracle Text error:
DRG-10502: index APEX_040000"."WWV_FLOW_OH_IDX does not exist
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 270
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 6300


Cause:

We got this error because,In my 11g rel2 database Because of the below reason:

The errors are thrown because the domain index has status FAILED, visible in USER_/DBA_INDEXES view, and no entry exists in Text data dictionary, ctxsys schema.Only metadata of valid domain indexes are exported.
I tried to rebuild the index but still the same error thrown.So finally decided to
drop it.

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select index_name, status, domidx_status, domidx_opstatus
from dba_indexes
where index_type = 'DOMAIN'
and domidx_opstatus = 'FAILED'; 2 3 4

INDEX_NAME STATUS DOMIDX_STATU DOMIDX
------------------------------ -------- ------------ ------
WWV_FLOW_OH_IDX VALID VALID FAILED

SQL> ALTER INDEX APEX_040000.WWV_FLOW_OH_IDX REBUILD ONLINE;
ALTER INDEX APEX_040000.WWV_FLOW_OH_IDX REBUILD ONLINE
*
ERROR at line 1:
ORA-29858: error occurred in the execution of ODCIINDEXALTER routine
ORA-20000: Oracle Text error:
DRG-10561: index WWV_FLOW_OH_IDX is not valid for requested operation
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 614

Solution:
To implement the solution, please execute the following steps:

* if the indexes are required in your application drop and re-create them
* if the indexes are not used by your application then drop them

Resolution:
I have drop the index as I know it is not used by our application and one valid reason i.e ctxsys:The owner of Oracle text (formerly: interMedia text) and not used by our application.

Note: Before dropping WWV_FLOW_OH_IDX,please make sure it is not used by your application or else drop and recreate.

SQL> DROP INDEX APEX_040000.WWV_FLOW_OH_IDX;

Index dropped.

SQL> select index_name, status, domidx_status, domidx_opstatus
from dba_indexes
where index_type = 'DOMAIN'
and domidx_opstatus = 'FAILED'; 2 3 4

no rows selected

References:MY ORACLE SUPPORT,ORACLE FORUM

Hope it helps...


Best regards,

Rafi.