Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Recover from dropped SYSAUX datafile

727229Oct 12 2009 — edited Oct 13 2009
Trying to recover a DEV database without rebuilding it, but not hopeful at this point. Basically, someone dropped the SYSAUX (and a few other) datafiles, so now they are showing up in the database as MISSING0003 instead of the actual filename. There are no backups, but I want to drop the tablespaces (not SYSAUX) and in trying to do so, I'm getting an error about the SYSAUX tablespace for some reason. How can I rebuild or recover from the SYSAUX issue, since I cannot drop the SYSAUX tablespace, as it seems to be preventing me from dropping any of the other tablespaces that have missing datafiles as well. I really just need to recreate the tablespaces, but get the below error when I try to drop the schemas (with cascade) or the tablespaces (including contents).

SQL> drop tablespace DATA001 including contents;
drop tablespace DATA001 including contents
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01111: name for data file 3 is unknown - rename to correct file
ORA-01110: data file 3: 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00003'
ORA-06512: at line 19

Any help to get past the first issue of dropping the non-SYSTEM tablespaces would be great, and any follow-up on how to rebuild/recreate/recover from the dropped SYSAUX datafiles would be appreciated as well.

DB is 10gR2 on Windows.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 10 2009
Added on Oct 12 2009
4 comments
1,010 views