How to rebuild SYSAUX corrupted tablespace

corruptionoracleoracle-10gtablespaces

I have database 10g 32-bit windows without backup. I want to migrate full database to 11g, but the SYSAUX tablespace is corrupted.

Can I rebuild the SYSAUX tablespace?

Best Answer

Corruption of the SYSAUX tablespaces is apparently a normal thing. It happens every now and then and can be fixed following these steps:

Find the corrupt block (V$DATABASE_BLOCK_CORRUPTION)

Run the following script to retrieve the blocks that are corrupt: 

select * from v$database_block_corruption;

This will output a list of corrupt blocks. In most cases rebuilding the index where the corrupt block is located will fix the corruption.

Rebuild all the indexes (Script)

Run the following script to rebuild all the indexes for the SYSAUX tablespace: 

    set lines 90;
    set heading off;
    set feedback off;

    spool runme.sql

    select
    'alter index '||owner||'.'||index_name||' rebuild online tablespace sysaux;'
    from
    dba_indexes
    where tablespace_name = 'SYSAUX';

    spool off

    spool rebuild.lst
    @runme.sql
    spool off

Run DBMS_REPAIR()

Have a look at the official documentation for more details (see references)

Open up a Service Request

If all else fails, you might want to consider opening up a Service Request with Oracle.

Reference:
- sysaux corruption tips (Burleson Consulting)
- Using DBMS_REPAIR to Repair Data Block Corruption (Oracle)
- V$DATABASE_BLOCK_CORRUPTION (Oracle)

Disclaimer
In no way affiliated with any of the companies.

Related Question