Re: Restore Table

Yes, I see what you are saying. I do agree.

Sorry to be a pain, but do you still need to replace the system dbf if it needs media recovery or is corrupt?

Thanks,

Paul.

In article <4Oq%7.53067$rt4.3713_at_afrodite.telenet-ops.be>, "koert54" <koert54_at_nospam.com> wrote:

> It shouldn't matter if the system is corrupt or not ... the reason he's
> getting the message 'system needs more recovery' is because he *only*
> restored the datafiles
> that contained the table and *then* tried to do a recovery until time -
> this is not possible because all datafiles are more recent (except for
> the ones he had restored)
> than the time he want's to roll forward to ... So if you actually *want*
> downtime and want to do it the easy way then he should :
> - bring the db down
> - restore ALL database files except the controlfiles - recover database
> until time 'x' ;
> - alter database open resetlogs ;
> - export the table
> - shutdown the DB
> - restore ALLLLL database files again - this time including controlfile
> ! (you can't use it anymore - incomplete recovery) - recover database
> using backup controlfile until cancel ; (now we try to recover until we
> reach the online redologs - so almost a complete recovery) - cancel
> after the last archive apply (you lost the online redologs by using
> resetlogs the first time)
> - alter database open resetlogs ; (hey - you've lost data !!!) - import
> the table
>
> Not only do you need to restore your full DB twice - you even lose
> transactions on the production system by using the resetlogs the first
> time !
> This procedure will take 10 times as much time for restoring and
> recovering , you'll lose transactions, and you have a large downtime -
> but it's easy (or by the book)
> By using a clone - you only have to recover what you need, you don't
> have downtime, it's 10 times faster and you don't loose transactions
> because you do the resetlogs
> on the clone - however, you do need to create a crippled clone and a new
> controlfile so maybe it's more difficult.... Anyhow - try it out - it
> might come in handy when losing a small table on a very large DB and the
> boys need it back - it's worth the exercise ...
>
>
> "Paul Fagan" <paulfagan_at_hotmail.com> wrote in message
> news:20020111.004433.532670688.3724_at_hotmail.com...

>> You will still need to restore all data files if your system tablespace
>> is corrupt or needs media recovery and you want to do an incomplete
>> recovery or am I wrong on this one?
>>
>> This is the case?
>>
>> Cheers,
>>
>> Paul.
>>
>> In article <Adq%7.53033$rt4.3858_at_afrodite.telenet-ops.be>, "koert54"
>> <koert54_at_nospam.com> wrote:
>>
>> > 1/2 year ago I had the same situation - unfortunately I couldn't
>> > afford downtime ... so I restored only
>> > system, temp, rollback and the datafiles I needed to **** another
>> > location**** because I didn't have a spare 100GB (!) for the full DB
>> > ... so I restored about 5GB and performed the procedure I put down
>> > previously in this thread ... this worked perfectly - heck I even
>> > created a DB link from the restored/recover/uncomplete DB to the
>> > production DB and pulled the data over that way ! At first I had a
>> > problem because when extracting the data it wanted to do an index
>> > look-up (and I didn't restore indexes) so I had to use a hint to
>> > force full
>> > table scan ...
>> > But I'm 100% sure it's do-able - without production downtime -
>> > without wasting space (I'm not going to restore/recover a 100GB DB
>> > for one lousy 100Mb
>> > table :-) ) ... maybe there was some misunderstanding before - I'm
>> > talking about *2 DB's* - and not offlining tablespaces of the
>> > production DB and restoring part of it
>> > to the original location ........
>> >
>> > To put it another way - perform the recovery on a partial clone of
>> > the production DB and extract the data from there without touching
>> > the production environment.
>> >
>> > Maybe RMAN is not able to handle it - but that doesn't mean the DB
>> > can't :-)
>> >
>> >
>> > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
>> > news:3c3e032f$0$4215$afc38c87_at_news.optusnet.com.au...
>> >> As soon as you know you are trying to do an incomplete recovery
>> >> (which is indeed what you are trying to do), you have to restore ALL
>> >> datafiles, not just the one you think is affected.  Failure to do so
>> >> notoriously produces the extremely unhelpful error message about
>> >> 'system needs more recovery to be consistent'.
>> >>
>> >> So, restore all datafiles, recover until time 'xx:xx:xx', then open
>> > database
>> >> resetlogs, then shutdown immediate, and take a new closed database
>> >> backup (because you've just rendered all previous backups and
>> >> archives useless
>> > for
>> >> future recoveries).
>> >>
>> >> Regards
>> >> HJR
>> >> --
>> >> ---------------------------------------------- Resources for Oracle:
>> >> http://www.hjrdba.com ===============================
>> >>
>> >>
>> >> "Steve James" <stevejames73_at_hotmail.com> wrote in message
>> >> news:lrj%7.9700$X87.1540288_at_news2-win.server.ntlworld.com...
>> >> > Hi,
>> >> >
>> >> > A little problem I am having on a test system.
>> >> >
>> >> > I am running the system in archive log mode.
>> >> >
>> >> > I make a hot backup of all the data files at 1:00pm. After backing
>> >> > up all the data files I issue an alter system switch
>> >> logfile.
>> >> >
>> >> > At 4pm, I drop a table which I didn't want to do, so I want to do
>> >> > a
>> >> recover
>> >> > until time to five mins before I dropped the table.
>> >> >
>> >> > Is the correct process for this as follows?:
>> >> >
>> >> > Shutdown the database
>> >> > replace the backup data file which would of contained the dropped
>> >> > table. (the backup I took at 1pm).
>> >> > startup mount
>> >> > recover database until time '2001-01-10:15:55:00'
>> >> >
>> >> > I am getting a series of error messages regarding the system
>> >> > tablespace needing more recovery but this doesn't make much sense.
>> >> > I have been over
>> >> the
>> >> > Oracle documentation , which seems to go along with the process I
>> >> > have written above.
>> >> >
>> >> > Can anyone verify whether what I am doing is correct?
>> >> >
>> >> >
>> >> > Thanks
>> >> >
>> >> > Steve
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>