Index partition corruption detected via RMAN validate (11.1) and resolution

SELF-MEMO: How to fix corruption on indexed partitions (i was bringing my Extended RAC 11.1.0.6 configuration back to life):

RMAN> validate database check logical;

Starting validate at 07-JAN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=120 instance=erac2 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=+DATA1/erac/datafile/system.259.654366597
input datafile file number=00004 name=+DATA1/erac/datafile/soeindex.dbf
input datafile file number=00003 name=+DATA1/erac/datafile/soedata.dbf
input datafile file number=00008 name=+DATA1/erac/datafile/undotbs5.270.655999987
input datafile file number=00002 name=+DATA1/erac/datafile/sysaux.260.654366615
input datafile file number=00007 name=+DATA1/erac/datafile/undotbs4.269.655999975
input datafile file number=00005 name=+DATA1/erac/datafile/users.264.654366663
channel ORA_DISK_1: validation complete, elapsed time: 00:05:46
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              20084        459520          13210477
  File Name: +DATA1/erac/datafile/system.259.654366597
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              431075
  Index      0              6321
  Other      0              2040

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    FAILED 0              16058        45584           13210492
  File Name: +DATA1/erac/datafile/sysaux.260.654366615
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              10669
  Index      2              7436
  Other      0              11421

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              39255        126976          12914293
  File Name: +DATA1/erac/datafile/soedata.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              85332
  Index      0              12
  Other      0              2377

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              13378        276649          12928336
  File Name: +DATA1/erac/datafile/soeindex.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              258967
  Other      0              4304

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              44           640             13209647
  File Name: +DATA1/erac/datafile/users.264.654366663
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              532
  Index      0              1
  Other      0              63

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              10238        25600           13210492
  File Name: +DATA1/erac/datafile/undotbs4.269.655999975
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              15362

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     0              120          89600           13210482
  File Name: +DATA1/erac/datafile/undotbs5.270.655999987
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              89480

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/erac/erac2/trace/erac2_ora_12052.trc for details
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2
Control File OK     0              1146
Finished validate at 07-JAN-11

RMAN>

Querying for exact info about affected segments:

SQL> SELECT * FROM v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         2       7260          1           13210364 CORRUPT
         2       7308          1           13210260 CORRUPT

SQL>

worth_blogging_corruption_rman_erac2

Fixing in this case is simple, just executed below SQL DML statements to rebuild those partitions:

alter index WRH$_SERVICE_STAT_PK rebuild partition WRH$_SERVIC_427808814_0;
alter index WRH$_SERVICE_STAT_PK rebuild partition WRH$_SERVICE_STAT_MXDB_MXSN;
alter index WRH$_PARAMETER_PK rebuild partition WRH$_PARAME_427808814_0;
alter index WRH$_PARAMETER_PK rebuild partition WRH$_PARAMETER_MXDB_MXSN;

Again I’ve executed RMAN validation to ensure everything is ok:

(..)
channel ORA_DISK_1: validation complete, elapsed time: 00:05:36
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              20084        459520          13258305
  File Name: +DATA1/erac/datafile/system.259.654366597
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              431075
  Index      0              6321
  Other      0              2040

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              15982        45584           13258305
  File Name: +DATA1/erac/datafile/sysaux.260.654366615
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              10669
  Index      0              7505
  Other      0              11428

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              39255        126976          12914293
  File Name: +DATA1/erac/datafile/soedata.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              85332
  Index      0              12
  Other      0              2377

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              13378        276649          12928336
  File Name: +DATA1/erac/datafile/soeindex.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              258967
  Other      0              4304

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              44           640             13209647
  File Name: +DATA1/erac/datafile/users.264.654366663
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              532
  Index      0              1
  Other      0              63

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              10238        25600           13258305
  File Name: +DATA1/erac/datafile/undotbs4.269.655999975
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              15362

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     0              120          89600           13258101
  File Name: +DATA1/erac/datafile/undotbs5.270.655999987
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              0
  Other      0              89480

channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:00
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2
Control File OK     0              1146
Finished validate at 07-JAN-11

RMAN>

Comments are closed.