Fix index corruption found using analyze table validate structure

We have struggled with a corrupted database and have gone through too many issues and challenges to document in a blog post. But, I thought I would document the fix to some index corruption that I found during the process. This is all on Red Hat 64 bit Linux Oracle 12.1.0.2 database.

A couple of coworkers of mine built a clone of the corrupt production database and my job was to see if there was any corruption left after they applied fixes to the known issues. I decided to work through Oracle Support’s documentation about fixing corruption. I started with this high level document:

Master Note for Handling Oracle Database Corruption Issues (Doc ID 1088018.1)

This document led me to first try using RMAN to find any remaining corruption. I followed this document:

How to identify all the Corrupted Objects in the Database with RMAN (Doc ID 472231.1)

I just ran the recommended simple commands:

rman target /

backup validate check logical database;

There was no corruption.

Next I started working through this Oracle Support document:

Identify the Corruption Extension for Block Corruption, Table/Index Inconsistency, Data Dictionary and Lost Writes (Doc ID 836658.1)

I liked the idea of looking for corruption for lost writes because we saw a number of internal errors that seemed to point to lost writes. These are some of the errors that we saw in production:

ORA-00600: internal error code, arguments: [kdifind:kcbz_objdchk]
ORA-00600: internal error code, arguments: [4137], [47.32.257993]
ORA-00600: internal error code, arguments: [kdsgrp1]
ORA-00600: internal error code, arguments: [ktprPURT_badundo]
ORA-00600: internal error code, arguments: [kturbleurec1]

So, the next check I did was with the dbv command based on the Oracle support document. I wrote this query to build all the dbv commands:

select 
'dbv file='||FILE_NAME||' blocksize='||
(select value from v$parameter where name='db_block_size')
from dba_data_files
order by FILE_NAME;

This produced commands like this:

dbv file=/u01/app/oracle/oradata/orcl/example01.dbf blocksize=8192

None of the dbv commands showed any corruption. So, I was beginning to think we had a clean system but then I tried the analyze table validate structure command from the same Oracle Support document and found corruption. I ran the command against every table. I had to run utlvalid.sql out of the $ORACLE_HOME/rdbms/admin directory to create the invalid_rows table. Then I ran these queries to build all the analyze commands:

select 
'analyze table '||owner||'."'||table_name||
'" validate structure cascade;'
from dba_tables
where PARTITIONED='NO'
order by owner,table_name;


select 
'analyze table '||owner||'."'||table_name||
'" validate structure cascade into invalid_rows;'
from dba_tables
where PARTITIONED='YES'
order by owner,table_name;

I ran the script that these queries built and got these errors:

ORA-01499: table/index cross reference failure - see trace file
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

I ran the analyze commands again on the ones with resource busy and they ran without error. But I had three that consistently failed with ORA-01499. They were these three system tables:

SYS.WRH$_SEG_STAT_OBJ
SYS.WRH$_SQLTEXT
SYS.WRH$_SQLSTAT

This led me to yet another Oracle Support document to help diagnose the ORA-01499 errors:

ORA-1499. Table/Index row count mismatch (Doc ID 563070.1)

I needed to find the trace files that the analyze command created for each table. So, I ran the analyzes like this:

alter session set max_dump_file_size = unlimited;
ALTER SESSION SET tracefile_identifier = 'bobbydurrett';
analyze table SYS."WRH$_SQLSTAT" 
validate structure cascade into invalid_rows;

This put my name in the trace file name so I could find it easily. I found a line like this in the trace file for each command:

row not found in index tsn: 1 rdba: 0x00817bfa

I ran the script from the Oracle Support document like this:

SELECT owner, segment_name, segment_type, partition_name
FROM DBA_SEGMENTS
WHERE header_file = (SELECT file# 
                     FROM   v$datafile 
                     WHERE  rfile# =
 dbms_utility.data_block_address_file(
to_number('00817bfa','XXXXXXXX'))
                       AND  ts#= 1)
AND header_block = dbms_utility.data_block_address_block(
to_number('00817bfa','XXXXXXXX'));

This led me to the corrupt indexes:

SYS
WRH$_SQLSTAT_INDEX
INDEX PARTITION
WRH$_SQLSTA_2469445177_11544

SYS
WRH$_SEG_STAT_OBJ_INDEX
INDEX

SYS
WRH$_SQLTEXT_PK
INDEX

I ran these commands to fix the first two:

alter index SYS."WRH$_SQLSTAT_INDEX" 
modify partition WRH$_SQLSTA_2469445177_11544 unusable;

alter index SYS."WRH$_SQLSTAT_INDEX" 
rebuild partition WRH$_SQLSTA_2469445177_11544;

alter index SYS."WRH$_SEG_STAT_OBJ_INDEX" unusable;

alter index SYS."WRH$_SEG_STAT_OBJ_INDEX" rebuild;

But then I found that SYS.”WRH$_SEG_STAT_OBJ_PK was also corrupt but the rebuild failed:

SQL> alter index SYS."WRH$_SEG_STAT_OBJ_PK" rebuild;
alter index SYS."WRH$_SEG_STAT_OBJ_PK" rebuild
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

This led me to yet another Oracle Support document:

ORA-01452: Cannot Create Unique Index; Duplicate Keys Found (Doc ID 332494.1)

I had to use these steps on both WRH$_SEG_STAT_OBJ_PK and WRH$_SQLTEXT_PK. I’m not sure why they had duplicate rows but I assume it was due to the index corruption.

SQL> select rowid,DBID, TS#, OBJ#, DATAOBJ#, CON_DBID 
from SYS.WRH$_SEG_STAT_OBJ
where  rowid not in (select min(rowid) from SYS.WRH$_SEG_STAT_OBJ 
group by DBID, TS#, OBJ#, DATAOBJ#, CON_DBID);  2

ROWID                    DBID        TS#       OBJ#   DATAOBJ#   CON_DBID
------------------ ---------- ---------- ---------- ---------- ----------
AAACEhAACAAA5nMAAi 2469445177         13     373044     373044 2469445177

SQL> alter session set skip_unusable_indexes=true;

Session altered.

SQL> alter table SYS.WRH$_SEG_STAT_OBJ 
disable constraint WRH$_SEG_STAT_OBJ_PK;

Table altered.

SQL> delete from SYS.WRH$_SEG_STAT_OBJ 
where rowid='AAACEhAACAAA5nMAAi';

1 row deleted.

SQL> commit;

SQL> CREATE UNIQUE INDEX SYS.WRH$_SEG_STAT_OBJ_PK 
ON SYS.WRH$_SEG_STAT_OBJ
  2  (DBID, TS#, OBJ#, DATAOBJ#, CON_DBID)
  3  LOGGING
  4  TABLESPACE SYSAUX
  5  PCTFREE    10
  6  INITRANS   2
  7  MAXTRANS   255
  8  STORAGE    (
  9              INITIAL          64K
 10              NEXT             1M
 11              MINEXTENTS       1
 12              MAXEXTENTS       UNLIMITED
 13              PCTINCREASE      0
 14              BUFFER_POOL      DEFAULT
 15             );

Index created.

SQL> alter table SYS.WRH$_SEG_STAT_OBJ 
enable  constraint WRH$_SEG_STAT_OBJ_PK;

Table altered.

I didn’t need the skip_unusable_indexes alter command so I left it off for the second PK index:

alter table SYS.WRH$_SQLTEXT disable constraint WRH$_SQLTEXT_PK;

select rowid,DBID, SQL_ID, CON_DBID from SYS.WRH$_SQLTEXT
where  rowid not in (select min(rowid) from SYS.WRH$_SQLTEXT 
group by DBID, SQL_ID, CON_DBID); 

delete from SYS.WRH$_SQLTEXT where rowid='AAACBvAACAABB6UAAE';

commit;

CREATE UNIQUE INDEX SYS.WRH$_SQLTEXT_PK ON SYS.WRH$_SQLTEXT
(DBID, SQL_ID, CON_DBID)
LOGGING
TABLESPACE SYSAUX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );
           
alter table SYS.WRH$_SQLTEXT enable constraint WRH$_SQLTEXT_PK;

Sorry that this post is so long but I thought it would be fun to document my index corruption journey. It was mostly a matter of navigating Oracle Support’s web site and following their recommendations. But, I thought it might help to document this particular situation and some of my queries.

Bobby