Recover from dropped SYSAUX datafile
727229Oct 12 2009 — edited Oct 13 2009Trying 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.