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
