Re: What do you do when someone drops a bunch of datafiles ?

Okay, I already got an answer to this from our lead DBA, and thought I would post:

What I usually do is issue the command 'alter database backup controlfile to trace;'. This creates a file in the /usr/oracle/databases/<instance name>/logfile directory that has all the commands to recreate a control file should they be deleted. I then take this file and at the bottom is a listing of all the missing datafiles with a label such as 'MISSING'. I bring this file into vi and then edit it to prepend the 'ALTER DATABASE DATAFILE ' before the datafile name and append ' OFFLINE DROP;' after the datafile name.

"Scott W" <CantGiveMyAddressBecauseIm_at_work.com> wrote in message news:C_.cnSa1.8Fwv1WgXTWQlg_at_News.GigaNews.Com...
> Besides strangle them :). Scenario: someone deletes a bunch of datafiles
and
> you can't bring up Oracle, and you end up doing an alter database datafile
> 'datafilename' offline drop, one datafile at a time until it comes up... I
> have spent the last two hours doing this to an 9.2 instance trying to
bring
> it back up. Is there any way to drop all missing datafiles at once so you
> don't have to go through this pain ? Or at least get a listing of them so
I
> can create a SQL script ?
>
> Thanks, Scott
>
> PS, this is not a prod environment, but a dev environment, thus many
people
> are in the dba group. This kind of thing is a cost of doing business, and
> thankfully it doesn't happen often.
>
>
>
Received on Tue Nov 05 2002 - 14:06:25 CST