On one of my databases, the SYSAUX tablespace has been corrupted.
SYSAUX was introduced in 10g to store all auxiliary database metadata related to Oracle options and features. This is a mandatory tablespace and cannot be dropped. Therefore it is important to have all objects in this tablespace accessible at all times.
Here is the procedure to restore it :
1- RMAN Block Recovery
RMAN block recovery is only possible if you have a backup and all archivelogs from prior to the time of the corruption. Further, block recovery is only available for Oracle Enterprise Edition.
a) Check for corruption
RMAN> backup validate check logical tablespace SYSAUX;
RMAN> backup validate check logical datafile 3;
b) Once the above RMAN validate is completed, all corruptions found will be written to this view:
SQL> select * from v$database_block_corruption;
c) If V$DATABASE_BLOCK_CORRUPTION returns more than one corrupted block, recover them
RMAN> blockrecover corruption list;
2 – Restore and Recover the Corrupted Datafile(s)
If you have a backup of the corrupted file(s), then restore and recover the datafile(s) from backup.
Confirm the backuppieces required:
RMAN> restore datafile 3 preview;
If you have all the available backups and archivelogs, then proceed with the restore and recovery:
RMAN> alter database datafile 3 offline; RMAN> restore datafile 3; RMAN> recover datafile 3; SQL> alter database datafile 3 online;