java.sql.SQLException: ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

Christy H.

Hello,

java.sql.SQLException: ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE


at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3657)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)
at UserDAO.insertInToDetailResume(UserDAO.java:228)
at UserManager.doPost(UserManager.java:344)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.ajp.AjpAprProcessor.process(AjpAprProcessor.java:197)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2430)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2419)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:744)

SQL> select owner,index_name from all_indexes where domidx_status != 'VALID' or domidx_opstatus !='VALID';


OWNER                          INDEX_NAME
------------------------------ ------------------------------
OWNERNAME           IDX_DETAILRESUME

Please advise

Best regards

Unknown

rebuild the index

Christy H.

Thanks for your prompt reply

How can I rebuild the index? I mean the previous DBA left and not provided any docs or code as to what he did?

Best regards

Unknown

Christy H. wrote:

Thanks for your prompt reply

How can I rebuild the index? I mean the previous DBA left and not provided any docs or code as to what he did?

Best regards

in some, many, most Professional IT group application source code is stored in code repository, like subversion.

extract DDL to CREATE INDEX & run it.

Christy H.

SQL> CREATE INDEX idx_detailResume ON detailResume(detailResume)
     INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS
     ('FILTER CTXSYS.NULL_FILTER SECTION GROUP CTXSYS.HTML_SECTION_GROUP');
  2    3  CREATE INDEX idx_detailResume ON detailResume(detailResume)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object




SQL> CREATE INDEX idx_detailResume ON detailResume(detailResume)
INDEXTYPE IS CTXSYS.CONTEXT;  2  
CREATE INDEX idx_detailResume ON detailResume(detailResume)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object




SQL> 

Unknown

either

DROP INDEX

before issuing CREATE or

ALTER INDEX REBUILD instead of CREATE

Christy H.

May I use this command:

ALTER INDEX idx_detailResume REBUILD ONLINE;

Best regards

Unknown

Christy H. wrote:

May I use this command:

ALTER INDEX idx_detailResume REBUILD ONLINE;

Best regards

YES

Christy H.

SQL> ALTER INDEX idx_detailResume REBUILD ONLINE;
ALTER INDEX idx_detailResume REBUILD ONLINE
*
ERROR at line 1:
ORA-00439: feature not enabled: Online Index Build

Unknown

ALTER INDEX idx_detailResume REBUILD;

Christy H.

SQL> ALTER INDEX idx_detailResume REBUILD;
ALTER INDEX idx_detailResume REBUILD
*
ERROR at line 1:
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-10595: ALTER INDEX IDX_DETAILRESUME failed
DRG-50857: oracle error in drvxtab.create_index_indexes
ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM