记录一次处理ORA-08102问题的案例

问题描述

在生产环境发生数据异常，客户对异常记录进行修改的时候，发生如下ORA-08102错误，无论通过什么方式都无法修改对应异常记录

问题分析

什么是ORA-08102错误

ORA-08102错误的完整描述是“ORA-08102: index key not found, obj# string, file string, block string”。简单来说，这个错误表示Oracle数据库在尝试访问某个索引时，无法找到预期的索引键。这通常意味着索引已经损坏或不一致，导致数据库无法正确使用该索引。

如何解决ORA-08102错误

通常解决方案是重建索引。

问题解决

由于是生产环境且数据库版本比较老也没有打补丁，重建索引看似非常简单，实则处理起来有点麻烦，下面就是整个问题处理过程

首先找到报错对象号对应的索引名

[oracle@tianjin-db-1 ~]$ sqlplus cmstj/sph

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 13 21:41:56 2025

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select owner,object_name from dba_objects where object_id=26365;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
CMSTJ
IDX_SCM_SALBILL_RELEASEDATE


SQL>

重建索引，更新数据发现继续报ORA-08102错

SQL> drop index IDX_SCM_SALBILL_RELEASEDATE;

Index dropped.

SQL> create index IDX_SCM_SALBILL_RELEASEDATE on SCM_SALBILL_HDR (RELEASEDATE)
  2    tablespace USERS
  3    pctfree 10
  4    initrans 2
  5    maxtrans 255
  6    storage
  7    (
  8      initial 18M
  9      next 1M
 10      minextents 1
 11      maxextents unlimited
 12    );

Index created.

SQL> select billno,releasedate,canceldate from scm_salbill_hdr where id=30732429;

BILLNO                                             RELEASEDATE
-------------------------------------------------- -------------------
CANCELDATE
-------------------
19891500403                                        2828-00-00 01:01:01
2700-00-00 01:01:01


SQL> update scm_salbill_hdr set releasedate=null,canceldate=null where id=30732429;
update scm_salbill_hdr set releasedate=null,canceldate=null where id=30732429
       *
ERROR at line 1:
ORA-08102: index key not found, obj# 154214, file 96, block 443739 (2)

SQL> 

继续查找报错对象号对应的索引名

--查找上面报错的154214对象
SQL> select owner,object_name from dba_objects where object_id=154214;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
CMSTJ
IDX_SALBILL_CMR


SQL>

继续重建索引

SQL> drop index IDX_SALBILL_CMR;

Index dropped.

SQL> create index IDX_SALBILL_CMR on SCM_SALBILL_HDR (CREATEDATE, MODIFYDATE, RELEASEDATE, INVOICEDATE)
  2    tablespace USERS
  3    pctfree 10
  4    initrans 2
  5    maxtrans 255
  6    storage
  7    (
  8      initial 64K
  9      next 1M
 10      minextents 1
 11      maxextents unlimited
 12    );
create index IDX_SALBILL_CMR on SCM_SALBILL_HDR (CREATEDATE, MODIFYDATE, RELEASEDATE, INVOICEDATE)
                                *
ERROR at line 1:
ORA-00600: internal error code, arguments: [smboPut:fixedlen2], [1], [7], [2], [8181043],
[], [], [], [], [], [], []


SQL>

此时创建索引失败报错ORA-00600，不要慌想办法解决，因为日期字段排序引起的bug处理如下

SQL> alter session set "_newsort_enabled"=false;

Session altered.

SQL> alter system flush shared_pool;

System altered.

SQL> create index IDX_SALBILL_CMR on SCM_SALBILL_HDR (CREATEDATE, MODIFYDATE, RELEASEDATE, INVOICEDATE)
  2    tablespace USERS
  3    pctfree 10
  4    initrans 2
  5    maxtrans 255
  6    storage
  7    (
  8      initial 18M
  9      next 1M
 10      minextents 1
 11      maxextents unlimited
 12    );

Index created.

SQL> 

再尝试更新，发现ORA-08102报错依旧。我们同上方式查找报错对象号对应的索引名称，重建索引

SQL> update scm_salbill_hdr set releasedate=null,canceldate=null where id=30732429;
update scm_salbill_hdr set releasedate=null,canceldate=null where id=30732429
       *
ERROR at line 1:
ORA-08102: index key not found, obj# 598765, file 188, block 652998 (2)

--查找上面报错的598765对象
SQL> select owner,object_name from dba_objects where object_id=598765;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
CMSTJ
IDX_OPD_SCM_SALBILL_HDR


SQL> drop index IDX_OPD_SCM_SALBILL_HDR;

Index dropped.

SQL> create index IDX_OPD_SCM_SALBILL_HDR on SCM_SALBILL_HDR (SYSOPTDATE)
  2    tablespace USERS
  3    pctfree 10
  4    initrans 2
  5    maxtrans 255
  6    storage
  7    (
  8      initial 64K
  9      next 1M
 10      minextents 1
 11      maxextents unlimited
 12    );

Index created.

SQL> 

最后一次更新，发现之前重建的索引又报ORA-08102了。我们再次重建，同时验证索引的完整性

SQL> update scm_salbill_hdr set releasedate=null,canceldate=null where id=30732429;
update scm_salbill_hdr set releasedate=null,canceldate=null where id=30732429
       *
ERROR at line 1:
ORA-08102: index key not found, obj# 686625, file 189, block 1651159 (2)

--查找上面报错的686625对象
SQL> select owner,object_name from dba_objects where object_id=686625;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
CMSTJ
IDX_SCM_SALBILL_RELEASEDATE


SQL> drop index IDX_SCM_SALBILL_RELEASEDATE;

Index dropped.

SQL> create index IDX_SCM_SALBILL_RELEASEDATE on SCM_SALBILL_HDR (RELEASEDATE)
  2    tablespace USERS
  3    pctfree 10
  4    initrans 2
  5    maxtrans 255
  6    storage
  7    (
  8      initial 27M
  9      next 1M
 10      minextents 1
 11      maxextents unlimited
 12    );

Index created.
--验证索引的完整性
SQL> ANALYZE INDEX IDX_SCM_SALBILL_RELEASEDATE VALIDATE STRUCTURE;

Index analyzed.

SQL> update scm_salbill_hdr set releasedate=null,canceldate=null where id=30732429;

1 row updated.

SQL> commit;

Commit complete.

SQL> select billno,releasedate,canceldate from scm_salbill_hdr where id=30732429;

BILLNO                                             RELEASEDATE         CANCELDATE
-------------------------------------------------- ------------------- -------------------
19891500403

SQL> 

至此我们已经完成异常数据的更新，解决问题。

总结

本文记录一次处理ORA-08102错误的全过程，其中包含查找报错对应的索引对象和重建索引，同时解决创建索引*ORA-00600: internal error code, arguments: [smboPut:fixedlen2]*的错误。以上如有错误也请各位留言指正，希望本文能给各位小伙伴一些帮助😃