Re: Datafiles debacle: How to remove unrecoverable datafile tablespace and partitions

From: Yann CAUCHARD <yann.cauchard_at_spcconsultants.com>
Date: Fri, 8 Feb 2002 17:41:00 +0100
Message-ID: <a40v0f$2u2q$1@news6.isdnet.net>

You have to re-create a 'flavor-like' datafile from the definition stored by Oracle in contro file, then drop it...

SVRMGR> alter tablespace tempo offline for recover;  Statement processed.

SVRMGR> alter database create datafile
     2> '/ORACLE/oradata/db80/DATAFILE/tempo_1.dbf'
     3> as '/ORACLE/oradata/db80/DATAFILE/tempo_1.dbf';

Statement processed.
SVRMGR> recover tablespace tempo;
Media recovery complete.
SVRMGR> alter database open;
Statement processed.
SVRMGR> select * from v$recover_file;
FILE# ONLINE ERROR CHANGE# TIME

---------- ------- ------------------ ---------- ---------

0 rows selected.
SVRMGR> hope it helps.

Yann

Chuck Moore a écrit dans le message
<70dead44.0202080756.33e21651_at_posting.google.com>...
>In my playing around with 8i, I accidently deleted some datafiles at
>the OS level (rm raw_20020123.dbf) before having dropped the table
>partitions in the tablespace, and thus also before dropping the
>tablespace. The database is not all that critical, so I never set up
>any extensive backup procedures nor archiving of the logs. The data
>is old and I want it gone. A backup did happen to exist, but I had
>already "alter database datafile '.../raw_20020123.dbf' offline drop"
>to be able to open the database for other work to continue. This
>marked the file for recovery. But I don't have the log files to
>effect a roll forward for the recovery. I don't care. I just want to
>remove the parition references (rb1%_20020123), tablespace
>(raw_20020123), and datafile from the database so that I can do full
>table scans again. The tablespace is defined as locally managed. How
>do I tell the data dictionary to remove the information event though
>the datafile is unavailable?
Received on Fri Feb 08 2002 - 10:41:00 CST