We have a 10.2.0.1 database on which the SYSAUX01 datafile became corrupt back in May2010.
The redo log file required by Oracle did not exist (a separate problem) so we added a new datafile AUXSYS02. This worked happily until yesterday when an error occurred performing a specific task -
Microsoft OLE BD provider for Oracle ORA-00604: error occurred at recursive SQL level 2.
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORACLE\SYSAUX01.DBF
ORA-06512: at line 19
For some reason it has reverted back to datafile SYSAUX01?
When I run
select * from dba_data_files where tablespace_name = 'SYSAUX';
it shows both files, 01 as status RECOVER and 02 as ONLINE;
Please could someone tell me how to remove the link to the SYSAUX01 datafile. I have tried taking it offline and offline drop but apparently you can't do that on SYSAUX datafiles.
I have also tried adding another SYSAUX03 datafile but the error persists.
Any guidance gratefully appreciated.