AutoPatch error: - ORA-01502 ,ORA-06512 - adphistExecBulkActionPkg: Unexpected Database Error

AutoPatch error:

adphistExecBulkActionPkg: Unexpected Database Error

AutoPatch error:

ORA-01502: index 'APPLSYS.AD_PATCH_RUN_BUG_ACTIONS_U1' or partition of such index is in unusable state

ORA-06512: at "APPS.AD_FILE_UTIL", line 2254

ORA-06512: at line 3

Error calling adphistExecBulkActionPkg().

0 patches uploaded from the ADPSV format patch history files: Sun Mar 13 2011 12:00:12

SELECT * from USER_INDEXES WHERE STATUS = ‘INVALID’; SQL statement.

Solution to this error is simple. You can:

   1. Drop the specified index and/or recreate the index

   2. Rebuild the specified index

   3. Rebuild the unusable index partition

2nd one :

ORA-01502: index 'indexname' or partition of such index is in unusable state

The reason is that some indexes have been marked unusable by a direct load or by a DDL operation, or shrinking space etc. Here is simple solution.

   1. Drop the specified index and/or recreate the index

   2. Rebuild the specified index

   3. Rebuild the unusable index partition

Here we rebuilding UNUSABLE indexes online, quering to USER_INDEXES view.

$sqlplus /nolog

Sql> connect system/oracle

Sql> SELECT count(*),status FROM all_indexes GROUP BY status;

Sql>  select 'alter index '||owner||'.'||index_name||' rebuild online ;' from dba_indexes where status = 'UNUSABLE' ;