Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Restore Table

Re: Restore Table

From: koert54 <koert54_at_nospam.com>
Date: Fri, 11 Jan 2002 00:59:44 GMT
Message-ID: <4Oq%7.53067$rt4.3713@afrodite.telenet-ops.be>


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

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
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
Received on Thu Jan 10 2002 - 18:59:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US