SYSAUX corruption and restore

SYSAUX corruption and restore

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;



Leave a Reply

Your email address will not be published. Required fields are marked *