oracle索引逻辑损坏,【案例】Oracle报错oer 8102.2 数据库坏块或index索引不一致导致跟踪文件报错...

obj#: object_id for the affected index in dba_objects.

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

afn: absolute file number where the affected index block is stored.

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

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

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

3.找出oer 8102.2相关对象SQL> col object_name for a30

SQL> col owner for a10

SQL> select object_name,owner,object_type

2 from dba_objects where object_id=4152;

OBJECT_NAME OWNER OBJECT_TYPE

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

WRI$_SEGADV_OBJLIST_IDX_TS SYS INDEX

SQL> select OWNER,TABLE_NAME from dba_indexes

2 where index_name='WRI$_SEGADV_OBJLIST_IDX_TS';

OWNER TABLE_NAME

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

SYS WRI$_SEGADV_OBJLIST

SQL> ANALYZE TABLE sys.WRI$_SEGADV_OBJLIST VALIDATE STRUCTURE CASCADE;

ANALYZE TABLE sys.WRI$_SEGADV_OBJLIST VALIDATE STRUCTURE CASCADE

*

ERROR at line 1:

ORA-01499: table/index cross reference failure - see trace file

4.分析坏块(逻辑/物理)

SQL> ANALYZE INDEX WRI$_SEGADV_OBJLIST_IDX_TS VALIDATE STRUCTURE;

Index analyzed.

SQL> ANALYZE TABLE WRI$_SEGADV_OBJLIST VALIDATE STRUCTURE;

Table analyzed.

[oracle@bas bdump]$ dbv file=/opt/app/oracle/oradata/BAS/system01.dbf

DBVERIFY: Release 10.2.0.1.0 - Production on Sat Dec 24 21:14:38 2011

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

DBVERIFY - Verification starting : FILE = /opt/app/oracle/oradata/BAS/system01.dbf

DBVERIFY - Verification complete

Total Pages Examined : 552960

Total Pages Processed (Data) : 360156

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 167596

Total Pages Failing (Index): 0

Total Pages Processed (Other): 1961

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 23247

Total Pages Marked Corrupt : 0

Total Pages Influx : 0

Highest block SCN : 2890198330 (2750.2890198330)

检测证明，对象以及对象所属的数据文件，无坏块现象

5.分析表和index不一致

--找出index对应列

SQL> SELECT table_name , column_name from dba_ind_columns

2 WHERE index_name='WRI$_SEGADV_OBJLIST_IDX_TS' order by table_name;

TABLE_NAME COLUMN_NAME

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

WRI$_SEGADV_OBJLIST TS_ID

--确定对应列是否允许为null

SQL> desc WRI$_SEGADV_OBJLIST

Name Null? Type

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

AUTO_TASKID NUMBER

TS_ID NUMBER

OBJN NUMBER

OBJD NUMBER

STATUS VARCHAR2(40)

TASK_ID NUMBER

REASON VARCHAR2(40)

REASON_VALUE NUMBER

CREATION_TIME TIMESTAMP(6)

PROC_TASKID NUMBER

END_TIME TIMESTAMP(6)

SEGMENT_OWNER VARCHAR2(30)

SEGMENT_NAME VARCHAR2(81)

PARTITION_NAME VARCHAR2(30)

SEGMENT_TYPE VARCHAR2(18)

TABLESPACE_NAME VARCHAR2(30)

--确认在表中对应列是否有空值

SQL> SELECT /*+ FULL(t1) */ count(TS_ID)

2 FROM WRI$_SEGADV_OBJLIST t1

3 WHERE t1.TS_ID IS NULL;

COUNT(TS_ID)

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

0

--表比index多数据

SQL> SELECT /*+ FULL(t1) */ TS_ID

2 FROM WRI$_SEGADV_OBJLIST t1

3 MINUS

4 SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ TS_ID

5 FROM WRI$_SEGADV_OBJLIST t where ts_id is not null;

no rows selected

--index中数据条数

SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ count(TS_ID)

2 FROM WRI$_SEGADV_OBJLIST t

3 where ts_id is not null;

COUNT(TS_ID)

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

901

--表中数据条数

SQL> SELECT /*+ FULL(t1) */ count(TS_ID)

2 FROM WRI$_SEGADV_OBJLIST t1 ;

COUNT(TS_ID)

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

937

--index中不同值数量

SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */

2 COUNT(DISTINCT TS_ID)

3 FROM WRI$_SEGADV_OBJLIST t WHERE TS_ID IS NOT NULL;

COUNT(DISTINCTTS_ID)

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

5

--表中不同值数量

SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ TS_ID

2 FROM WRI$_SEGADV_OBJLIST t WHERE ts_id IS NOT NULL

3 MINUS

4 SELECT /*+ FULL(t1) */ TS_ID

5 FROM WRI$_SEGADV_OBJLIST t1 ;

TS_ID

----------

4

--对比可以知道index中的唯一值比表中，这个也就解释了，为什么表中总条数多，

--但是他们两做减法的时候，记录为空

--索引表比表多数据

SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ TS_ID

2 FROM WRI$_SEGADV_OBJLIST t WHERE ts_id IS NOT NULL

3 MINUS

4 SELECT /*+ FULL(t1) */ TS_ID

5 FROM WRI$_SEGADV_OBJLIST t1 ;

TS_ID

----------

4

上面的检测证明:1.表中有索引中无的数据,2.索引中有表中不存在数据

6.oer 8102.2解决问题

SQL> alter index WRI$_SEGADV_OBJLIST_IDX_TS rebuild online;

Index altered.

--测试index中总条数

SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ count(TS_ID)

2 FROM WRI$_SEGADV_OBJLIST t

3 where ts_id is not null;

COUNT(TS_ID)

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

937

--无多余index项(以前唯一值为4的记录已经不存在)

SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ TS_ID

2 FROM WRI$_SEGADV_OBJLIST t WHERE ts_id IS NOT NULL

3 MINUS

4 SELECT /*+ FULL(t1) */ TS_ID

5 FROM WRI$_SEGADV_OBJLIST t1 ;

no rows selected

--通过上述测试，证明表和index不一致问题解决