ansaurus

Question

Unable to Recover Corrupt Oracle Datafile Block In SysAux

Answer 1

+1 A:

Your latest backup of datafile 3 is from 24-MAR-09. I don't think you have enough archive to recover the datafile to 25 SEP 09. As far as I know you can not drop and recreate the SYSAUX tablespace. It contains data related to options and features such as OLAP, Text, Ultra Search, Intermedia, Spatial, Workspace, Data Mining, EM Repository, Streams, LogMiner, Logical Standby, Statspack, Job Scheduler. If you have Oracle support I would get help from them. I would try to do an export or expdp as soon as possible of the appication schema's. To get some kind of data backup, I suspect you have no recoverable backup at the moment.

And I forgot, APC is right : don't panic.

Robert Merkwürdigeliebe 2009-09-25 11:52:45

Answer 2

+2 A:

Check the completion times. You have a file for every day from 13-JAN-09 until the 24-MAR-09. Since then, nada. So what RMAN is telling is that it cannot find any backups of datafile 3 for the last six months.

Don't panic.

How big is your database? Is it feasible to do an export? There may be a simpler solution, but if you can get your data out you have the option to recreate the database.

edit

According to the documentation it is possible to drop the SYSAUX tablespace. Details to follow.

edit 2

Dropping and re-creating SYSAUX tablespace is not for the faint-hearted. I confess that this is the first time I have attempted it. Right now I am stymied by this:

SQL> drop tablespace sysaux including contents and datafiles
  2  /
drop tablespace sysaux including contents and datafiles
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace


SQL> drop index xdb.xdb$acl_xidx force;
drop index xdb.xdb$acl_xidx force
*
ERROR at line 1:
ORA-29868: cannot issue DDL on a domain index marked as LOADING


SQL>

This is a road block. DROP INDEX FORCE is supposed to be the workaround for ORA-29868, so I'm non-plussed to see that message. Anyway, once I get beyond this hill I expect there will be others. It don't think it's a tidy process. But then, to be fair, it's not something we're meant to be doing at all.

So, it's good news that you can do an export, because I very much fear that recreating the database is your best bet. Good luck.

Oh, and once you've sorted this situation out, impress upon your bosses the urgency of hiring a DBA (or getting you properly trained, if that is what you would like).

Finally, this is a very good teaching for you. The first time I had to do a restore I discovered the previous DBA had not enabled ARCHIVELOG mode, and I hadn't checked. My punishment was to visit all the users and inform them they had lost a day's work. The moral is, a backup which has not been tested does not exist.

APC 2009-09-25 12:05:08

Thanks for the responses.I'm not panicking....yet ;o)Database is definitely small enough to export. In fact that's what I'm using at the moment so we have something in the way of a backup until I get the auto backups working again.Sounds like I'm going to have to recreate the database though - not ideal but anything is better than data loss!

javadeveloper 2009-09-25 12:41:41

Thank you for putting so much effort into your reply APC. Recreating the database sounds like the best option. Time to start making sure I've got everything in place to do it...

javadeveloper 2009-09-25 14:49:28

ansaurus

tags:

views:

answers:

Unable to Recover Corrupt Oracle Datafile Block In SysAux

related questions