Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
I did just 'alter index... rebuild' for all the indexes in SYSAUX. It resolved only 10 of the corrupted blocks but the listener (why?!) went haywire and had to be restarted. So it got me thinking what could happen if I start actually dropping and recreating them? So I will rather get Metalink access from our customer and ask there. –  Juraj Dec 2 '09 at 9:19

2 Answers 2

"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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.