Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    9

    Unanswered: how to avoid use of bad datafile?

    Hi everyone,
    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!

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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.

    See: http://download-west.oracle.com/docs...tore.htm#16817
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Oct 2004
    Posts
    9
    Thank you for the suggestions.

    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?

    Thanks!
    Last edited by bessemerboy; 03-09-05 at 14:55. Reason: typo

  4. #4
    Join Date
    Oct 2004
    Posts
    9
    Oops, here's another point i forgot to mention. This db is 8.1.7.4 on RedHat Linux. I just talked to a guy who says he's encountered it before and never found a solution other than upgrading.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •