Unable to recover SYSAUX01 datafile

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.

Col_G
Col_GAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

clint93Commented:
Hi,
Did you try to execute the "offline drop" command to ask the controlfile to continue without that datafile:

alter database datafile '/E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORACLE\SYSAUX01.DBF' offline drop;
0
Col_GAuthor Commented:
Hi Clint93,

Thanks for you promt response.
Yes we did, it made no difference to the status of the datafile. it still shows as RECOVER.

Col
0
clint93Commented:
Is the database in archivelog mode? Do you have any backup of the db?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Col_GAuthor Commented:
Yes the database is in archivelog mode but back in 2010 when the original issue occured on datafile SYSAUX01 Oracle could not find the required redo log file to recover it. Back-ups were also taken but the physical file was never actually created by Oracle.
This is why we added another datafile to SYSAUX which seemed to work beautifully util yesterday.
0
clint93Commented:
Ok, I understand better what happened. It is very strange that it seemed to work correctly until yesterday. Did you recreate your controlfile?
0
Col_GAuthor Commented:
Not that I am aware of. Is this something we need to do now?
0
gvsbnarayanaCommented:
Hi,
    This sort of issues can come up as long as you do not drop the datafile. If the problem persists, contact Oracle Support and work with them on dropping the SYSAUX tablespace and create a new one.
HTH
Regards,
Badri.
0
Col_GAuthor Commented:
Thanks Bardi, I'll log a call with Oracle.

Regards,
Col
0
clint93Commented:
A solution would consist in exporting all the schemas you need (except of course the schemas using the SYSAUX tablespace) and reimport them in a new database.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrjoltcolaCommented:
You can't simply remove a datafile, because it houses objects.

You can recreate the SYSMAN schema (SYSAUX tablespace) while the DB is up, and you can also recover SYSAUX tablespace using RMAN blockrecover or DBMS_REPAIR, but the SYSAUX tablespace needs to be offline while doing so. Recreating it will lose your ADDM reports, etc. but won't affect your core database.

Also see metalink article 278100.1 for the official way.
0
Col_GAuthor Commented:
Thank you all for you comments. We are thinking of an export and import into new database but might try your solution first mrjoltcola.

Will keep you posted.

Col
0
Col_GAuthor Commented:
Hi mrjoltcola:

For your info, this is Oracle's response to your suggestion: -
~~~~~~~~~~~~~~~~~~~~~~~
I am afraid Note 278100.1 only discusses the SYSMAN schema that is stored in the SYSAUX tablespace.
The Sysman schema is one of the many schema's that are stored in the SYSAUX tablespace.
It does not explain how to drop the SYSAUX tablespace and recreate it as this is not supported.

Rman blockrecovery cannot be used in this case because it is a complete datafile that is offline, not a single block.

The same applies to DBMS_REPAIR which does not work an a datafile level but rather block level.
~~~~~~~~~~~~~~~~~~~~~~~~

Oracle have also suggested we transport the tablespaces to a new database but the corrupt SYSAUX01 file will not allow this.
We have tried to export just the OWNERS associated with our application but again corrupt SYSAUX01 file will not allow this.
So we're back onto Oracle.

If it's ok with you I  will leave this open for while longer until we have a solution to post.
0
mrjoltcolaCommented:
Did Oracle mention a datafile recovery with RMAN?
0
Col_GAuthor Commented:
Hi all,

Once again that you for your help.

The only way we were able to get around this issue was by installing another instance of Oracle on the server, creating a public database link between the old and the new instance: -
CREATE PUBLIC DATABASE LINK dblink CONNECT TO system IDENTIFIED BY password USING 'Oracle';
and then creating the required tables via a script similar to this: -
Create table table1 as select * from table1@dblink;
One all tables where in place we recreated the Indexes, sequences, procedures, triggers etc from our own application scripts.

All up and running again - phewww!
0
Col_GAuthor Commented:
Given that even Oracle Support were stumpted, we did pretty well.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.