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

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<W22y9.70169$g9.199443_at_newsfeeds.bigpond.com>...
> Hi Scott,
>
> What I would do if someone somehow managed to delete a bunch of datafiles
> would be to restore them from backup and fully recover them.

Personally, I have usually set it up so that re-creating to a baseline is easy, but don't run dev instances in archivelog mode unless there is a good reason. I like to be able to say to developers, "Hey, I've been a developer, I don't want to put any unecessary constraints on you that would prevent you from being creative. So here's how we can quickly go back to a standard point if you eff things up. I'm not giving you DBA privs, and if you were good enough to be a DBA you would know why [a bit of challenge properly presented rarely hurts! And sometimes they _are_ good enough to work with, which is no different than having multiple DBA's] and here's how we must deal with objects/privileges etc so we can eventually get your work into production. And here is how we will backup your work outside of Oracle in case you do shoot yourself in the foot. And this is how we will set up synonyms read-only to a large dataset for when you need to test scalability. And here is how you should create test data for us to use. And here is how we document all this..." And on anon.

And even with a fairly redundant backup scheme, it's still sometimes not paranoid enough.

>
> Cheers
>
> Richard
> "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.

But hey, it gives you a chance to poke good-natured fun at someone, and give them a bit of an adrenaline rush as you describe what would happen in production...

jg

--
@home is bogus.
949, 0, "incorrect datafile %s specified for table %s subpartition
%s\n"
// *Cause: The specified datafile is not in the tablespace of the
//         specified subpartition.  Due to use of FILE= keyword on
//         a subpartitioned table.  When loading all subpartitions of
//         a subpartitioned table, the subpartitions must be in the
same
//         tablespace.  When loading a single subpartition, the
tablespace
//         of the subpartition being loaded must be the same as the
tablespace
//         in which the specified datafile file resides.
/