Backup & Recovery
Logical corruption (indexes) table/index cross reference failure
User Rating: / 3
- Details
-
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: 10409
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)