Re: Help getting past missing archivelog file

In <3f743a3f$0$32058$afc38c87_at_news.optusnet.com.au> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> writes:

>Alistair Thomson wrote:
 

>> Hi Stan
>>
>> You can restore until cancel or until a point in time, check out the
>> syntax below:
>>
>> SVRMGRL> connect internal
>>
>> SVRMGRL> startup mount db_name;
>>
>> SVRMGRL> recover database until time '2003-09-23:14:40:00';
>>
>> While still in svrmgrl, execute the normal command to open the database,
>> using the RESETLOGS option. This forces the database to reset the redo log
>> sequence number information in the control files and the online redo log
>> files This in turn makes sure that any redo log entry data that followed
>> the "RECOVER DATABASE UNTIL" time will not be applied to the database:
>>
>> SVRMGRL> alter database open resetlogs;
>>
>> Now backup your database.
>>
>>
>>
>> Hope it helps
>>
>>
>>
>> Alistair

>I have to say that's a dreadful piece of advice! Sorry, but it is so. The
>guy already has stated that the datafile in question (which is all that is
>being recovered here) only contains indexes, and that those indexes can be
>re-created. Your suggestion to do a database-wide incomplete recovery means
>that you will be causing data loss on the entire database, totally
>unnecessarily.
 

>The correct response to this sort of situation is to alter database datafile
>X offline drop. That will permit him to open the rest of his database, drop
>the tablespace, re-create it, and then re-create all the indexes. And no
>data will have been lost in the meantime.
 

>The only slight bummer to this approach is that it is easier said than done
>to get rid of the index tablespace, because some of those indexes might be
>being used to enforce constraints... and all those constraints will
>therefore have to be disabled before the tablespace drop can be
>accomplished... and then re-enabled at the end of it. But that's merely
>just a lot of hard manual work... it's still better than losing data he
>didn't need to lose in the first place!

Actually (speaking as the OP) In my case the incomplete recovery would probably be better. Here is is the rest of the story.

I've moved on further to determine that there _are_ additional datafile that want non existent archivelogs. These datafiles, unfortunately contain tables.

Now I need to explain the nature of the database. It's archival data from a process control system. There are virtually no dependencies between various datasets, thus almost no constraints. At this point in time I'm losing more (real time) data because the database is still down, that there is any likleyhood of losing from a 24 (or even 48) hour old recovery.

The best thing for me, is to get it back up as soon as possible.

Given all the above, I've been unable to get a partial recovery to work. The latest ever was a cancel based recovery that would up, after I did alter database open resetlogs, with an error message about file +3 being to new!

I guess I'm going to restore all the (necessary ?) files from the previous days tape backup. Given the size of the database, this will take _a long_ time!

Does anyone have a better suggestion?

>>
>>
>> "Stan Brown" <stanb_at_panix.com> wrote in message
>> news:bkvmst$o8u$1_at_reader2.panix.com...
>>> I've got a V& Oracle instance that suffered a disk failure on the
>>> disk containing the archivelog files last night.
>>>
>>> I'm trying to get it back up.
>>>
>>> When I tried to start it up it started complaining about various
 tablespace
>>> files (starting with file #1) needing "media recovery".
>>>
>>> I have restored all the archivelog files that I have, and proceeded as
>>> follows:
>>>
>>> svrmgrl
>>> startup
>>> alter database recover datafile 'xxx' ;
>>> shutdown
>>>
>>> repeating the last 3 steps to determine the next datafile that needs
>>> recovery.
>>>
>>> Unfortunately, I have now reached a point at which I don't know how to
>>> proceed. Here is what I am seeing:
>>>
>>> Database mounted.
>>> ORA-01113: file 7 needs media recovery
>>> ORA-01110: data file 7: '/db_local1/oradata/pwhse/indexes/index1.dbf'
>>>
>>> alter database recover datafile
>>> '/db_local1/oradata/pwhse/indexes/index1.dbf'
>>> *
>>> ORA-00279: Change 108474696 generated at 09/24/03 23:15:23 needed for
>>> thread 1
>>> ORA-00289: Suggestion : /dumpdisk/archivelogs/pwhse/arch/1_2164278.dbf
>>> ORA-00280: Change 108474696 for thread 1 is in sequence #2164278
>>>
>>> Unfortunately I don;t _have_ the archivelog file that is being requested.
>>>
>>> The good news is this the tablespace in question contains _just_ indexes,
>>> and can easily be recreated if necessary by re running my initial db
>>> creation scripts.
>>>
>>> What are my options here. Can I force the database to only recover to the
>>> last available archivelog file? If not, what other options do I have?
>>>
>>>
>>> --
>>> "They that would give up essential liberty for temporary safety deserve
>>> neither liberty nor safety."
>>> -- Benjamin Franklin
 

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