解决表SMON_SCN_TIME上报告的ORA-08102

王亮

ORA-08102: 未找到索引关键字, 对象号 , 文件 , 块 ()

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]

帮助解决表SMON_SCN_TIME上的ORA-8102。 Smon进程在警报日志文件中报告该错误。

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