Backup & Recovery

Logical corruption (indexes) table/index cross reference failure

User Rating:  / 3
PoorBest 
Parent Category: Articles
Created on Wednesday, 22 January 2014 16:42
Last Updated on Wednesday, 22 January 2014 21:17
Published on Wednesday, 22 January 2014 16:42
Written by Guy Lambregts
Hits: 10408

Logical corruption (indexes) table/index cross reference failure ( ORA-01499 )

 

Huge trace files are found

 

I found many huge weird trace files in the ADR repository, they all started with

oer 8102.2 - obj# 72649, rdba: 0x04c0c129(afn 19, blk# 49449) kdk key 8102.2:


What is the object referenced by obj# 72649 ?


16:07:11 SQL> select object_name from dba_objects where data_object_id = 72649;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
IDX_COCONUT


In which tablespace this oBject is located ?


16:08:10 SQL> select tablespace_name from dba_indexes where index_name='IDX_COCONUT';

TABLESPACE_NAME
------------------------------
TS_INDEX_05

 

Is there logical corruption in the tablespaces detected by RMAN ?

 

RMAN> validate check logical tablespace TS_INDEX_05;

Starting validate at 20-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=303 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00019 name=/u01/oradata/PLATINUM/TS_INDEX_05.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07

List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
19 OK 0 1012 94029 8896983242984

File Name: /u01/oradata/PLATINUM/TS_INDEX_05.dbf

Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 9899
Other 0 82785

Finished validate at 20-JAN-14

 

On which table is the index built ?


16:09:34 SQL> select table_name from dba_indexes where index_name='IDX_COCONUT';

TABLE_NAME
------------------------------
T_COCONUT

 

16:10:00 SQL> select tablespace_name from dba_tables where table_name='T_COCONUT';

TABLESPACE_NAME

------------------------------
TS_DATA_05


There is indeed a table row index key mismatch


16:16:30 SQL> analyze table DB_OWNER.T_COCONUT validate structure cascade;
analyze table DB_OWNER.T_COCONUT validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file


What is the trace file showing ?


row not found in index tsn: 18 rdba: 0x04c02262
env [0x7fff7a66c300]: (scn: 0x0817.7d87057a xid: 0x0093.014.00018015 uba: 0x00000000.0000.00 statement num=0 parent xid: 0x0000.000.00000000 st-scn: 0x0000.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0817.7d86de54 flg: 0x00000060)

 

Let us drop an recreate the index


16:25:52 SQL> drop index "DB_OWNER"."IDX_COCONUT";

Index dropped.

16:26:16 SQL> CREATE INDEX "DB_OWNER"."IDX_COCONUT" ON "DB_OWNER"."T_COCONUT" ("LAST_UPDATE_TIME") TABLESPACE "TS_INDEX_05";

Index created.


The index corruption has gone


16:26:41 SQL> analyze table DB_OWNER.T_COCONUT validate structure cascade;

Table analyzed.

 

See also VALIDATE ONLINE PHYSICAL AND LOGICAL CONSISTENCY OF THE ORACLE DATABASE

See also LOGICAL CORRUPTION (INDEXES)