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

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?

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