Fix ORA-29861 after restoring a Maximo Oracle database

After restoring a Maximo Oracle database, a query, upgrade or UpdateDB operation can fail with:

ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

Oracle raises this error when an operation tries to use a domain index that is still being built, was left failed by an unsuccessful DDL operation, or was marked unusable. Maximo uses Oracle domain indexes for text search, so an incomplete restore or index build can expose the problem.

Do not rebuild every domain index automatically. LOADING, FAILED and UNUSABLE describe different conditions. Work with the Oracle DBA, take a current backup, preserve each index definition, check the restore and database logs, and perform repairs in a maintenance window.

Find the affected indexes

Connect as the Maximo schema owner and query its domain indexes:

SELECT
  INDEX_NAME,
  TABLE_NAME,
  INDEX_TYPE,
  ITYP_OWNER,
  ITYP_NAME,
  STATUS,
  DOMIDX_STATUS,
  DOMIDX_OPSTATUS
FROM
  USER_INDEXES
WHERE
  INDEX_TYPE = 'DOMAIN'
  AND (
    NVL(DOMIDX_STATUS, 'UNKNOWN') <> 'VALID'
    OR NVL(DOMIDX_OPSTATUS, 'UNKNOWN') <> 'VALID'
  )
ORDER BY
  INDEX_NAME;

USER_INDEXES only reports indexes owned by the connected schema. If a DBA uses ALL_INDEXES or DBA_INDEXES, include the owner in both the query and every repair statement so an identically named index in another schema cannot be changed.

Before repairing a result, record:

the owner, index name and base table;
DOMIDX_STATUS and DOMIDX_OPSTATUS;
the index type and its parameters;
whether an index build, import or other DDL operation is still running; and
the errors that originally caused the operation to fail.

Choose the action from the index state

LOADING

LOADING can mean that an index build is still in progress. Check active database work and the import or restore job before intervening. Oracle's guidance is to wait for an active build to finish.

If no build is running and the state is stale, do not assume a normal rebuild is safe. Have the DBA investigate the failed operation and use the recovery procedure appropriate to that Oracle Text index. A forced drop and recreation can be necessary, but only after its complete definition and Maximo configuration have been captured.

FAILED or UNUSABLE

For a FAILED or UNUSABLE index, Oracle permits a rebuild or recreation. Try a rebuild only after resolving the underlying cause and confirming there is enough tablespace and temporary space:

ALTER INDEX <schema>.<index_name> REBUILD;

For example, if the affected Maximo long-description index is LDTEXT_IDX and it belongs to the MAXIMO schema:

ALTER INDEX MAXIMO.LDTEXT_IDX REBUILD;

Replace both identifiers with values verified from the data dictionary. The account needs authority to alter the index and the privileges required by its domain index type. An Oracle Text rebuild uses the index's existing preference settings when no replacement parameters are supplied.

Rebuilding is DDL and can lock resources or compete with application activity. Do not add ONLINE simply because the database edition accepts it; validate support and behavior for the exact Oracle Text index and release first.

If the rebuild fails, stop and diagnose the new Oracle or DRG- error. Capture a recreation script and the Oracle Text preferences before considering a drop. Dropping and recreating an index without those details can silently change tokenization, stoplists, storage or synchronization behavior.

Validate the repair

Rerun the diagnostic query. The repaired index should no longer appear, and both domain status columns should report VALID.

Then validate the operation that originally failed:

Run a representative Maximo text search that uses the repaired index.
Review the Oracle alert log, Oracle Text logs and Maximo logs for fresh errors.
If the error interrupted UpdateDB or an upgrade, follow IBM's documented restart or recovery point rather than assuming it can resume from any step.
Run the Maximo Integrity Checker and review its report.
Confirm that scheduled Oracle Text synchronization or optimization jobs still run.

Applying ALTER INDEX ... REBUILD to every invalid domain index is too broad. That remains the useful recovery for many FAILED or UNUSABLE indexes, but it is not the correct first response to a genuinely active LOADING state.

Settings

Keyboard shortcuts