【问题记录】oer 8102.2 表与索引记录不一致 写trace文件

这两天告警日志一直在报以下警告：

Mon Dec30 19:54:39 2013

Errors infile /u01/oracle/admin/eptdb/udump/eptdb2_ora_2093088.trc:

Mon Dec30 19:54:43 2013

Errors infile /u01/oracle/admin/eptdb/udump/eptdb2_ora_2093088.trc:

 

trace文件内容如下：

/u01/oracle/admin/eptdb/udump/eptdb2_ora_2093088.trc

OracleDatabase 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With thePartitioning, Real Application Clusters, Data Mining and Real ApplicationTesting options

ORACLE_HOME= /u01/oracle/product/10.2/db

Systemname:    AIX

Nodename:      querydb2

Release:        3

Version:        5

Machine:        00C752254C00

Instancename: eptdb2

Redothread mounted by this instance: 2

Oracleprocess number: 168

Unixprocess pid: 2093088, image: oracle@querydb2

 

***2013-12-30 19:54:29.190

***ACTION NAME:() 2013-12-30 19:54:29.182

***MODULE NAME:(r4edi@xib01 (TNS V1-V3)) 2013-12-30 19:54:29.182

***SERVICE NAME:(eptsvc) 2013-12-30 19:54:29.182

***SESSION ID:(993.44029) 2013-12-30 19:54:29.182

oer 8102.2 - obj# 58779, rdba: 0x15c4a1c8(afn 87, blk#303560)

kdk key8102.2:

  ncol: 3, len: 23

  key: (23):

 04 53 45 4e 44 0a 31 33 32 32 30 35 35 36 2d39 06 0c c6 a2 0b 00 02

  mask: (4096):

 21 00 81 d6 00 00 00 00 00 00 00 00 00 00 0000 00 00 00 00 0a 00 00 00 00

oer8102.<code> - obj# <object id>, rdba: <rdba value>(afn<file#>, blk# <block#>)

kdk key8102.2:

ncol:<number of columns in the key including the rowid>, len: <keylength>

key:(<length>):<hexadecimal value>

 

obj#:   object_id for the affected index indba_objects.

rdba:   relative data block address where the key issupposed to be stored in the index.

afn:     absolute file number where the affectedindex block is stored.

(file_id indba_data_files, file# in v$datafile).

blk#:   Index block number where the key is supposedto be stored.

出现oer 8102.2的错误，有两种可能:1.坏块；2.表和索引数据不一致

trace文件太难读懂，一开始没有发现oer8102.2 - obj# 58779, rdba: 0x15c4a1c8(afn 87, blk# 303560)这一行，而往下看到：

***2013-12-30 19:54:29.194

ksedmp:internal or fatal error

CurrentSQL statement for this session:

UPDATE EDI_EXPMSGLOG SET MsgStatus=:1,MsgSentTime=:2 WHEREMsgLogId = :3

….

SO:700000343479b40, type: 4, owner: 70000034331e628, flag: INIT/-/-/0x00

(session) sid: 993 trans: 700000328a19730, creator:70000034331e628, flag: (100041) USR/- BSY/-/-/-/-/-

DID: 0002-00A8-00018D82, short-term DID: 0002-00A8-00018D81

txn branch: 0

oct: 6, prv: 0, sql: 7000002b3539be0, psql: 7000003022f6f20, user:74/XIB

service name: eptsvc

O/S info:user: xib, term: , ospid: 344532, machine: xib01

program: r4edi@xib01 (TNS V1-V3)

applicationname: r4edi@xib01 (TNS V1-V3), hash value=1842786423

last waitfor 'db file sequential read' blocking sess=0x0 seq=13115 wait_time=162 secondssince wait started=1

file#=33, block#=6a20b, blocks=1

看到这些还以为是应用的问题，就让应用去看下有没有报错，后来反馈说有报8102错误，于是再去trace文件中查找是否有改错，搜索一下果然有该错。

oer 8102.2 - obj# 58779, rdba: 0x15c4a1c8(afn 87, blk#303560)

通过object_number确定出问题的对象：

select * fromdba_objects where object_id=58779;

OWNER OBJECT_NAME   OBJECT_TYPE

XIB          IDX_EXPMSGLOG_MSGSTATUS      INDEX

 

select OWNER,TABLE_NAMEfrom dba_indexes

whereindex_name='IDX_EXPMSGLOG_MSGSTATUS';

OWNER TABLE_NAME

XIB          EDI_EXPMSGLOG

 

比较索引与表中的数据：

select count(*) from

(SELECT /*+ FULL(t1)*/ count(msglogid)

    FROM xib.EDI_EXPMSGLOG t1

    MINUS

    SELECT /*+ index(t IDX_EXPMSGLOG_MSGSTATUS)*/ count(msglogid)

FROMxib.EDI_EXPMSGLOG t where msglogid is not null);

 

COUNT(*)

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

150

 

表比index数据多，因此将索引重建下就可以解决oer 8102.2错误

alter index xib. IDX_EXPMSGLOG_MSGSTATUS rebuildonline;

 

查看结果：

select count(*) from

(SELECT /*+ FULL(t1)*/ count(msglogid)

    FROM xib.EDI_EXPMSGLOG t1

    MINUS

    SELECT /*+ index(t IDX_EXPMSGLOG_MSGSTATUS)*/ count(msglogid)

FROMxib.EDI_EXPMSGLOG t where msglogid is not null);

 

COUNT(*)

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

0

 

 

 

参考：惜分飞博客http://www.xifenfei.com/2264.html