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

In <bl9h2m$n17$1_at_reader2.panix.com> Stan Brown <stanb_at_panix.com> writes:

>In <wGWdb.129243$bo1.103576_at_news-server.bigpond.net.au> "Richard Foote" <richard.foote_at_bigpond.com> writes:
 

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

>OK, I decided to forge ahead on this (probably a stupid decision, but I'm
>under a lot of pressure to get _something_ working here). I reasoned that I
>probably di want to use RESETLOGS, but when I do this I get:
 

>ALTER DATABASE OPEN RESETLOGS
>*
>ORA-01194: file 1 needs more recovery to be consistent
>ORA-01110: data file 1: '/oracle_home/oradata/pwhse/system01.dbf'
 

>Now, here is the history of this particular data file. I saved a copy of it
>as found when the system came back up from the disk replacement. Then I
>recovered it. Somewhere along the way, someone told me that I should not
>have done media recovery, so I was headed down the road of replacing all the
>.dbf files with the ones from the hot backup (which as you will recall, I
>was unable to do). However, since I had saved a copy of this one, I _did_
>restore it to the "as found" state'
 

>Given that most of the other datafiles have been "media recovered", should I
>do that to this one, again?

Here is my plan, I would like some kind soul to comment on it.

starup recover
take offline all datafiles that can't be recovered. alter database open resetlogs
drop the datafiles I've taken offline
use my tablespace creation scripts to recreate these datafiles use my scripts to recrate tables/indexes that live in them restore the table data from my dump.

Does this sound like it will work?

Is thee a better plan?

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
						-- Benjamin Franklin