ORA-08102解决办法

一、日志报错

Mon Apr 11 08:55:44 2011

Errors in file /oraclelog/ngcrm/udump/ngcrm_ora_22244.trc:

二、查看TRACE文件

Dump file /oraclelog/ngcrm/udump/ngcrm_ora_22244.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /oracle/app/oracle/product/10.2.0

System name:    HP-UX

Node name:      crmdb1

Release:        B.11.31

Version:        U

Machine:        ia64

Instance name: ngcrm

Redo thread mounted by this instance: 1

Oracle process number: 400

Unix process pid: 22244, image: oracle@crmdb1

 

*** 2011-04-10 10:59:46.602

*** ACTION NAME:() 2011-04-10 10:59:46.566

*** MODULE NAME:(serv_sgip@ngcrmift (TNS V1-V3)) 2011-04-10 10:59:46.566

*** SERVICE NAME:(ngcrm) 2011-04-10 10:59:46.566

*** SESSION ID:(625.846) 2011-04-10 10:59:46.566

oer 8102.2 - obj# 218325, rdba: 0x11c2ed13(afn 71, blk# 191763)

kdk key 8102.2:

  ncol: 2, len: 19

  key: (19):  0b 31 33 30 37 39 35 30 37 35 34 39 06 11 0f be 8c 00 0c

  mask: (4096):

*** 2011-04-10 10:59:46.606

ksedmp: internal or fatal error

Current SQL statement for this session:

delete from TL_B_SMS where DEAL_STATE='1' and DEAL_TIME

DELETE语句执行时报错，错误号为8102.2

三、查找错误原因

$ oerr ora 8102

08102, 00000, "index key not found, obj# %s, file %s, block %s (%s)"

// *Cause:  Internal error: possible inconsistency in index

// *Action:  Send trace file to your customer support representative, along

//           with information on reproducing the error

四、告诉开发人员停止应用进程号22244

五、登录主机手工执行报错的SQL，验证错误的存在

SQL> delete from UCR_CRM1.TL_B_SMS where DEAL_STATE='1' and DEAL_TIME

delete from UCR_CRM1.TL_B_SMS where DEAL_STATE='1' and DEAL_TIME

                     *

ERROR at line 1:

ORA-08102: index key not found, obj# 218325, file 71, block 191763 (2)

六、根据提示信息，怀疑是索引与表中数据不同步造成的。重新建立索引

SQL> select OBJECT_NAME,SUBOBJECT_NAME  from DBA_OBJECTS where object_id=218325;

 

OBJECT_NAME

--------------------------------------------------------------------------------

SUBOBJECT_NAME

------------------------------

INDEX_TL_B_SMS_1

P_I_TL_B_SMS_2_4

--出问题的索引为P_I_TL_B_SMS_2_4,为了保险起见，重建所有索引，防止因其它索引导致问题再次出现。

 

SQL> select index_name,partition_name,status from dba_ind_partitions where index_name in ('PK_TL_B_SMS');

 

INDEX_NAME                     PARTITION_NAME                 STATUS

------------------------------ ------------------------------ --------

PK_TL_B_SMS                    P_I_TL_B_SMS_2_1               USABLE

PK_TL_B_SMS                    P_I_TL_B_SMS_2_2               USABLE

PK_TL_B_SMS                    P_I_TL_B_SMS_2_3               USABLE

PK_TL_B_SMS                    P_I_TL_B_SMS_2_4               USABLE

PK_TL_B_SMS                    P_I_TL_B_SMS_2_5               USABLE

PK_TL_B_SMS                    P_I_TL_B_SMS_2_6               USABLE

PK_TL_B_SMS                    P_I_TL_B_SMS_2_7               USABLE

PK_TL_B_SMS                    P_I_TL_B_SMS_2_8               USABLE

PK_TL_B_SMS                    P_I_TL_B_SMS_2_9               USABLE

PK_TL_B_SMS                    P_I_TL_B_SMS_2_10              USABLE

PK_TL_B_SMS                    P_I_TL_B_SMS_2_11              USABLE

 

INDEX_NAME                     PARTITION_NAME                 STATUS

------------------------------ ------------------------------ --------

PK_TL_B_SMS                    P_I_TL_B_SMS_2_12              USABLE

 

12 rows selected.

--共12个分区，需全部重建索引

 

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_2 REBUILD PARTITION  P_I_TL_B_SMS_2_1 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_2 REBUILD PARTITION  P_I_TL_B_SMS_2_2 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_2 REBUILD PARTITION  P_I_TL_B_SMS_2_3 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_2 REBUILD PARTITION  P_I_TL_B_SMS_2_4 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_2 REBUILD PARTITION  P_I_TL_B_SMS_2_5 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_2 REBUILD PARTITION  P_I_TL_B_SMS_2_6 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_2 REBUILD PARTITION  P_I_TL_B_SMS_2_7 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_2 REBUILD PARTITION  P_I_TL_B_SMS_2_8 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_2 REBUILD PARTITION  P_I_TL_B_SMS_2_9 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_2 REBUILD PARTITION  P_I_TL_B_SMS_2_10 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_2 REBUILD PARTITION  P_I_TL_B_SMS_2_11 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_2 REBUILD PARTITION  P_I_TL_B_SMS_2_12 online;

 

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_3 REBUILD PARTITION  P_I_TL_B_SMS_2_1 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_3 REBUILD PARTITION  P_I_TL_B_SMS_2_2 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_3 REBUILD PARTITION  P_I_TL_B_SMS_2_3 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_3 REBUILD PARTITION  P_I_TL_B_SMS_2_4 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_3 REBUILD PARTITION  P_I_TL_B_SMS_2_5 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_3 REBUILD PARTITION  P_I_TL_B_SMS_2_6 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_3 REBUILD PARTITION  P_I_TL_B_SMS_2_7 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_3 REBUILD PARTITION  P_I_TL_B_SMS_2_8 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_3 REBUILD PARTITION  P_I_TL_B_SMS_2_9 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_3 REBUILD PARTITION  P_I_TL_B_SMS_2_10 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_3 REBUILD PARTITION  P_I_TL_B_SMS_2_11 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_3 REBUILD PARTITION  P_I_TL_B_SMS_2_12 online;

 

ALTER INDEX  UCR_CRM1.PK_TL_B_SMS REBUILD PARTITION  P_I_TL_B_SMS_2_1 online;

ALTER INDEX  UCR_CRM1.PK_TL_B_SMS REBUILD PARTITION  P_I_TL_B_SMS_2_2 online;

ALTER INDEX  UCR_CRM1.PK_TL_B_SMS REBUILD PARTITION  P_I_TL_B_SMS_2_3 online;

ALTER INDEX  UCR_CRM1.PK_TL_B_SMS REBUILD PARTITION  P_I_TL_B_SMS_2_4 online;

ALTER INDEX  UCR_CRM1.PK_TL_B_SMS REBUILD PARTITION  P_I_TL_B_SMS_2_5 online;

ALTER INDEX  UCR_CRM1.PK_TL_B_SMS REBUILD PARTITION  P_I_TL_B_SMS_2_6 online;

ALTER INDEX  UCR_CRM1.PK_TL_B_SMS REBUILD PARTITION  P_I_TL_B_SMS_2_7 online;

ALTER INDEX  UCR_CRM1.PK_TL_B_SMS REBUILD PARTITION  P_I_TL_B_SMS_2_8 online;

ALTER INDEX  UCR_CRM1.PK_TL_B_SMS REBUILD PARTITION  P_I_TL_B_SMS_2_9 online;

ALTER INDEX  UCR_CRM1.PK_TL_B_SMS REBUILD PARTITION  P_I_TL_B_SMS_2_10 online;

ALTER INDEX  UCR_CRM1.PK_TL_B_SMS REBUILD PARTITION  P_I_TL_B_SMS_2_11 online;

ALTER INDEX  UCR_CRM1.PK_TL_B_SMS REBUILD PARTITION  P_I_TL_B_SMS_2_12 online;

 

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_1 REBUILD PARTITION  P_I_TL_B_SMS_2_1 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_1 REBUILD PARTITION  P_I_TL_B_SMS_2_2 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_1 REBUILD PARTITION  P_I_TL_B_SMS_2_3 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_1 REBUILD PARTITION  P_I_TL_B_SMS_2_4 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_1 REBUILD PARTITION  P_I_TL_B_SMS_2_5 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_1 REBUILD PARTITION  P_I_TL_B_SMS_2_6 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_1 REBUILD PARTITION  P_I_TL_B_SMS_2_7 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_1 REBUILD PARTITION  P_I_TL_B_SMS_2_8 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_1 REBUILD PARTITION  P_I_TL_B_SMS_2_9 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_1 REBUILD PARTITION  P_I_TL_B_SMS_2_10 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_1 REBUILD PARTITION  P_I_TL_B_SMS_2_11 online;

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_1 REBUILD PARTITION  P_I_TL_B_SMS_2_12 online;

 

ALTER INDEX  UCR_CRM1.INDEX_TL_B_SMS_4 REBUILD  online;

 

 

七、再次执行SQL，成功

SQL> delete from UCR_CRM1.TL_B_SMS where DEAL_STATE='1' and DEAL_TIME

 

55 rows deleted.

 

SQL> commit;

 

Commit complete.

八、启应用

开发人员重新开启应用程序，问题不再出现

九、问题总结：

经和应用人员核实，此表UCR_CRM1.TL_B_SMS更新较频繁，数据库自动收集统计信息为周一至周五晚间。问题发生在周日上午，最后收集的统计信息是上周五晚上，导致表中记录与索引信息不一致，产生此错误，建议每周末定期对此表收集统计信息。