ORA-08102

ORA-08102: index key not found, obj# 290, file 1, block 4353


Cause: Internal error: possible inconsistency in index.

Action: Send trace file to your customer support representative, along with information on reproducing the error


Well I do not have access to oracle support (if you need it, you need to buy that service). So first of all, I tried to identify which index cause that problem.


SQL>select OWNER,OBJECT_NAME,OBJECT_ID, OBJECT_TYPE from dba_objects x where x.object_id=290;                                                                                                         

OWNER       OBJECT_NAME      OBJECT_ID   OBJECT_TYPE
-------- ------------------- ---------- ------------

SYS         I_JOB_NEXT            290    INDEX

Guess what ? it's an index own by 'SYS' user. So I issued index rebuild command and it didn't work. As the index lies in system tablespace, I didn't want to move it to other tablespace. Thus I droped and created the index again. So first gather the index DDL from database

SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL('INDEX','I_JOB_NEXT') from DUAL;
output:
CREATE INDEX "SYS"."I_JOB_NEXT" ON "SYS"."JOB$" ("NEXT_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"

SQL> DROP INDEX SYS.I_JOB_NEXT;
SQL> CREATE INDEX "SYS"."I_JOB_NEXT" ON "SYS"."JOB$" ("NEXT_DATE") TABLESPACE "SYSTEM";

It works. Previously, I thought that 'REBUILD' command internaly drop an object and recreate it. But, now I need to explore 'How rebuild command works ?'.

ORA-01591: lock held by in-doubt distributed transaction string

Cause: Trying to access resource that is locked by a dead two-phase commit transaction that is in prepared state.

Action: DBA should query the DBA_PENDING_TRANSACTIONS and DBA_2PC_PENDING, and attempt to repair network connection(s) to coordinator and commit point. If timely repair is not possible, DBA should contact end user for rollback/commit the transection.

sqlplus / as sysdba;

SQL> SELECT * FROM DBA_2PC_PENDING;

SQL> ROLLBACK FORCE LOCAL_TRAN_ID;

SQL> commit;

SQL> alter system enable distributed recovery;

SQL> execute dbms_transaction.purge_lost_db_entry('LOCAL_TRAN_ID');

SQL > commit;

For more details plese go through this document