诗檀软件专业数据库修复团队
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
适用于：
Oracle Database - Enterprise Edition – 版本9.2.0.1 到11.2.0.1 [Release 9.2 to 11.2]
本文信息适用于任何平台。
*** 22-Feb-2012检查相关性***
目标
本文的目标是帮助解决表SMON_SCN_TIME上的ORA-8102。 Smon进程在警报日志文件中报告该错误。
在文件/opt/bdump/emrep_smon_1241286.trc中的错误：
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj#
Ora-8102 indicates a corruption in the Index involved.
分析表SMON_SCN_TIME validate structure ；没问题
联机分析表SMON_SCN_TIME validate structure cascade；失败并生成Ora-1499
解决方案
首先确认表没有任何损坏，通过运行：
SQL> analyze table SMON_SCN_TIME validate structure;
表被分析了。
它应该干净地显示出被分析表的信息。
找出表中涉及的索引
SQL> Select index_name,index_type,owner from dba_indexes where table_name='SMON_SCN_TIME' ;
INDEX_NAME INDEX_TYPE OWNER
------------------------------ --------------------------- ------------------------------
SMON_SCN_TIME_TIM_IDX NORMAL SYS
SMON_SCN_TIME_SCN_IDX NORMAL SYS
获得DDL来重建这两个索引
SQL> Set long 10000000
SQL> Select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX','SYS') FROM DUAL ;
DBMS_METADATA.GET_DDL('INDEX','SMON_SCN_TIME_TIM_IDX','SYS')
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_TIM_IDX" ON "SYS"."SMON_SCN_TIME" ("T
IME_MP")
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)
TABLESPACE "SYSTEM"
SQL> Select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_SCN_IDX','SYS') FROM DUAL ;
DBMS_METADATA.GET_DDL('INDEX','SMON_SCN_TIME_SCN_IDX','SYS')
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_SCN_IDX" ON "SYS"."SMON_SCN_TIME" ("S
CN")
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)
TABLESPACE "SYSTEM"
一旦你有了索引所需的DDL 。Drop索引
Drop index sys.SMON_SCN_TIME_TIM_IDX ;
Drop index sys.SMON_SCN_TIME_SCN_IDX ;
重新运行
SQL>Analyze table SMON_SCN_TIME validate structure cascade online ;
如果干净显示If it comes out clean
使用以上步骤中dbms_metadata.get_ddl生成的脚本重建两个被drop的索引
重新运行
SQL>Analyze table SMON_SCN_TIME validate structure cascade online ;
这应该会干净显示
参考
NOTE:1088018.1 - Master Note for Handling Oracle Database Corruption Issues
NOTE:563070.1 - ORA-1499. Table/Index row count mismatch