Home / server / Questions / 89749
In Process
Juraj
Asked: 2009-12-02 02:33:38 +0800 CST

How to fix corrupt indexes blocks in SYSAUX tablespace

  • 772

we had power failure that left corruption in data files. It was recovered from RMAN backup including logs and all is OK, the DB is working.. except that I noticed this:

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE FILESPERSET=10;
...snip..
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    FAILED 0              24312        139056          71820306
  File Name: /u02/oradata/ORCL/datafile/o1_mf_sysaux_4kjcn4qg_.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              35222
  Index      101            39306
  Other      0              40216

Other datafiles are all OK. Now the question is, what to do with these indexes? I will try (outside working hours) to do 'alter index... rebuild' for all indexes in SYSAUX but AFAIK that won't help, dropping and recreating the indexes is necessary...but is there SQL source to recreate them? It is Oracle 11.1.0.6 on Linux.

oracle

2 Answers

  1. "is there SQL source to recreate them" You can use DBMS_METADATA.GET_DDL to retrieve/reconstruct the SQL to rebuild indexes. A query on DBA_SEGMENTS would tell you what indexes exist in SYSAUX.

    • 1

  2. There are two other corruption checks it would be prudent to run, particularly as the RMAN validate has uncovered issues:

    a) dbverify

    b) ANALYZE TABLE . VALIDATE STRUCTURE CASCADE [ONLINE]

    And yes you would need to recreate the indexes involved, a rebuild doesn't work here.

    • 1