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

My fault, I keep starting new threads (I'm in panic mode if you have not noticed). Also some of the exchanges were in email, although it appears that channel is not working very well.

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

Well, I have done this, although I did not do it early enough to get back to the point in time of the failure.

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

Unfortunately we dropped support, this year. Partly because (I think) there were issues with V7 not being supported, and partly because we could not afford to pay for the class of support that is required to get phone support these days.

So, I find myself in a position of begging for support from the Oracle community. Not a good position to be in, I admit, but times are tough in our industry, and I really Could not come up with a strong enough argument to justify the cost.

Here is the sequence as best I can recall it.

The database has all of it's .dbf files on mirrored disk. It's archivelog files are on a non mirrored disk. That disk failed, while the instance was being backed up using the orabck.sh script. I did a dbshut, shutdown the computer, changed th init files so Oracle would not come up automaticly. Then I replaced the disk, and restored all available archivelog files.

When I tried to do a dbstart I got a message about data file 1 needing media recovery. I tried going through and doing (I think) "alter databse recover datafile "xxx". This worked up to a point, at which time one of the files (7 I think) said it needed an archivelog file newer than I had.

Turned out there was a problem with my hot backup mechanism, such that none of the backed up .dbf files > 2G are useful. Before I realized this, I had deleted one of the .dbf files (which contains only indexes).

Now, thnaks to your help, I have managed to take that one offline. I think the next thing I need to try to do is the "alter database open", right. Should I use the RESETLOGS flag?

BTW, I _do_ have (I'm pretty certain) a complete dump of data from the day before.

Any help whatsoeer, that anyone can give would be _greatly appreciated_!!!

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