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
Pingback: Log Buffer #519: A Carnival of the Vanities for DBAs – Cloud Data Architect