Re: need to startup database without a datafile

Well, offlining doesn't mean it's gone forever. It just lets you get what's left of the database open around the problem. It's the drop tablespace command that means 'this thing's stuffed and never coming back'.

So if the file suddenly re-materialised, and you hadn't already dropped the tablespace, there should be no harm in trying to bring the tablespace online, which will prompt for media recovery -at which point the thing can be completely recovered.

But if you've done a 'drop tablespace', then you've a problem -because that will have changed the control file, too. Which means that if any recovery is possible, it's going to be of the 'with backup controlfile' flavour -which means resetlogs and everything else associated with them. And, of course, the entire database goes back to the time just before the 'drop tablespace' command. Expensive.

What you're after is a TSPITR, a tablespace point in time recovery, which is bloody hard to do properly (the course documents say "call support"!!). So it might be theoretically possible to do, but it wouldn't be pretty.

The other alternative that presents itself is equally messy: take a full database export. Perform an incomplete recovery with backup controlfile to the point just prior to the drop tablespace command. Open the database resetlogs. Perform a full import with ignore=Y. Results are a bit unpredictable, but in theory it should just about do the deed -I'd expect to see a flurry of primary key constraint violations, followed by the addition of all rows that were inserted after the tablespace was dropped.

I'll let you try that one, Jason!

Regards
HJR

--

Oracle Resources : http://www.geocities.com/howardjr2000
========================================


"Jason" <foucault4_at_home.com> wrote in message
news:H1mD7.165448$5A3.59865899_at_news1.rdc2.pa.home.com...


> I have a follow up question along this line...

> Howard's procedure for dropping the tablespace has been posted several

times


> (very kind of you to post it again, by the way).  But my question is let's

> say that the datafile isn't really corrupt but simply temporarily

> unavailable.  Is there any way to add the tablespace back in later on?

> Would it work to restore to a point in time before the drop and apply the

> redo logs to bring the other tablespaces back up to speed?

> If that works, is there any way to do this if you're not archiving the

redo


> logs?

>

> "Howard J. Rogers" <howardjr_at_www.com> wrote in message

> news:3bdde29a$0$21197$afc38c87_at_news.optusnet.com.au...

> > From the mount state, alter database datafile X offline;

> > alter database open;

> > drop tablespace X;

> >

> > Should do it.

> >

> > Regards

> > HJR

> > --

> >

> > Oracle Resources : http://www.geocities.com/howardjr2000

> > ========================================

> >

> >

> > "Yannis Markakis" <yannis_removethis_markakis_at_yahoo.com> wrote in

message


> > news:9rklr4$psv$1_at_usenet.otenet.gr...

> > > Hello all,

> > > I got this problem,

> > > a server on Windows 2000 with service pack is running an 8.1.7 oracle

> > > database. While trying to create a large materialized view the

database


> > > crashed and wouldn't startup again. I found that the datafile which

> > consists

> > > a tablespace in which I was creating the materialized view is

corrupted


> > and

> > > by now I can mount the database but not open it.

> > >

> > > I would rather drop that tablespace and the datafile which appears to

be


> > > corrupted that go back to the whole database restore procedure. Is

there


> a

> > > way that I can start the database without this one tablespace or

> datafile?

> > >

> > > Excuse me if that has been posted again. Any help would be very

> > appreciated.

> > >

> > > Yannis

> > >

> > >

> >

> >

>

>