I'm trying to do something which seems like it should be simple, but i can't get it to work. I have an index tablespace with a bad datafile (block corruption). The datafile is offline. There was one index (partitioned) which used that datafile; i want to drop and recreate the index, hopefully avoiding the bad datafile. I grabbed the DDL for the index, dropped it and tried to recreate it. Since the bad datafile is offline i assumed Oracle would just recreate the index using other datafiles -- but i keep getting an ORA-00372 (file xxx cannot be modified at this time). How can i make sure Oracle avoids that bad datafile?
Any help anyone can offer would be very much appreciated. Apologies if this is an incredibly obvious question. Thanks!
A tablespace is made up off ALL the datafiles that comprise it. If one datafile is bad, then the tablespace is bad. If you have a backup of the datafile, and the archive logs since the backup, you can restore the datafile and then recover it.
Ahhh, yes, backups -- that would be nice. Unfortunately the only backup we have contains this same corruption in the offline datafile (it's a beta db). Not an ideal situation to be sure, but that's what's there.
So, given that, regardless of whether or not it's the proper thing to do -- recreating the index with the datafile offline should work, shouldn't it?
One thing i forgot to mention: the status of the datafile in v$datafile is "recover", not "offline". I did an extra "alter database datafile 'xxx' offline" just to make sure it shouldn't be accessed. But v$datafile doesn't show it as "offline". Could the "recover" status be related, i wonder?
Last edited by bessemerboy; 03-09-05 at 14:55.