Re: 2 questions, deleting a tablespace on a damaged instance and tablespace

"Stan Brown" <stanb_at_panix.com> wrote in message news:bl9c9p$lh5$2_at_reader2.panix.com...
> In <VfWdb.129222$bo1.81848_at_news-server.bigpond.net.au> "Richard Foote"
 <richard.foote_at_bigpond.com> writes:
>
> >"Stan Brown" <stanb_at_panix.com> wrote in message
> >news:bl9a5b$ks4$1_at_reader2.panix.com...
> >> In <bl99bk$kbg$1_at_reader2.panix.com> Stan Brown <stanb_at_panix.com>
 writes:
> >>
> >> >In <duUdb.129017$bo1.58527_at_news-server.bigpond.net.au> "Richard Foote"
 <richard.foote_at_bigpond.com> writes:
> >>
> >>
> >> >>"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> >> >>news:3f774ec9$0$32058$afc38c87_at_news.optusnet.com.au...
> >> >>> Stan Brown wrote:
> >> >>>
 

> >> >OK, thnaks for the clarification. I wasn't having any luck with the
> >> >original syntax.
 

> >> >So, for the file I have already deleted physicaly (which maps one to
 one
 to
> >> >a tablespace), then I should use something like "drop tablespcae
> >> >'table_space_name'"? ? After doing a startup mount?
 

> >> >Or would it be an alter databse command?
 

> >> >The exact syntax would be _most_ helpful. The instnaces is 7.3.4.5
> >>
> >>
> >> OK, I definatley need some syntax help here. I've tried the following:
> >>
> >> SVRMGR> drop tablespace INDEX1_TS ;
> >> drop tablespace INDEX1_TS
 

> >Hi Stan,
 

> >You can't drop a tablespace unless the database is open. As Howard
 mentioned
> >previously, you need to take the stuffed datafile offline first whilst
 the
> >database is in a mounted state. However, IIRC with Oracle7, you get an
 error
> >if you include the DROP clause if the database is in archivelog mode (the
> >drop clause is simply ignored in later releases). So you need to issue:
 

> >ALTER DATABASE DATAFILE 7 OFFLINE;
 
> >You should now be able to open the database (assuming nothing else is
> >wrong).
 

> >ALTER DATABASE OPEN;
 
> >You can now drop this damned tablespace of yours.
 

> >DROP TABLESPACE index1_ts INCLUDING CONTENTS CASCADE CONTRAINTS;
>
>
> OK next:
>
> ALTER DATABASE OPEN
> *
> ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
>
> I want to use RESETLOGS, here, correct?
>

Hi Stan,

I haven't followed this whole thread through (I seem to miss posts here and there).

BUT this is not just a case of simply dropping a deleted index datafile and moving on, as I had thought. Your database is out of sync for reasons I haven't a clue about and it appears some recover is necessary.

BEFORE doing anything else, I would strongly recommend taking a full backup of your database, including all your data, control and redo log files so you can always return to this current state.

NEXT I would strongly contact Oracle Support and get them to walk you through whatever recovery process is necessary. It's probably not going to be that difficult a process but if you haven't got much experience and you value your data, it would be the prudent thing to do.

Cheers

Richard Received on Mon Sep 29 2003 - 08:43:56 CDT