0

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?

  • It will be very unlikely that you'll be able to recover the database. Does it start up? Can you at least mount it? – Andrew Brennan Aug 30 '17 at 9:30
  • 1
    Yes my database in open state and also give error about Hard Drive I/O device error – Ramiz Tariq Aug 30 '17 at 10:03
  • OK that's good, see hot2use's answer – Andrew Brennan Aug 30 '17 at 14:51
1

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.

  • I wouldn't assume the corrupt block is in an index... – Andrew Brennan Aug 30 '17 at 14:51
  • True It doesn't have to be, but reading the article seems to imply that it can be common for the indexes to have a corruption in SYSAUX. – hot2use Aug 30 '17 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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