Re: recovering data

Good info! I can't wait to go to 9iR2.

Tanks!

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:WikW9.27506$jM5.70614_at_newsfeeds.bigpond.com...
>
> "JustAnotherDBA" <jadba_at_bellsouth.net> wrote in message
> news:3g7W9.7161$F_3.6241_at_news.bellsouth.net...
> > I thought that 1 of the big benefits of 9i was the ability to access
table
> > table as it was previously.
> >
> > In R1 you had to use a DBMS (?) package and in R2 it is simple SQL
syntax
> > statements ?
> >
> > Can anyone comment on this?
>
> It's called flashback. And yes, in 9iR1 you had to execute
> dbms_flashback.enable_at_time(sometime) or exec
> dbms_flashback.enable_at_scn(some SCN). Then all your selects were
performed
> as of that time or SCN, provided the rollback was available to construct
the
> required state of the data.
>
> And yes, in 9iR2, it's got much simpler because you can now additionally
do
> a 'select * from emp as of timestamp <sometime>' or 'select * from dept as
> of SCN <some SCN>.
>
> And you are quite right, too, that one of the touted benefits of flashback
> is that *instead* of having to do messy and incomplete recoveries after an
> incorrect mass delete of data, you could simply (in 9iR2) do a 'create
table
> blah as select * from important_table as of <5 minutes ago>' -and thus
> populate a new table with the deleted rows. Then you could simply do an
> 'insert into important_table select * from blah', and you've got your
> deleted data back. (in release 1, you had to enable flashback, open a
cursor
> to store the data, disable flashback, and then insert back into the table
> from the cursor).
>
> Trouble is, it's (probably) all a bit late for our original poster.
> Flashback only works when the required rollback (undo, if you prefer) is
> available, and you set the init.ora parameter UNDO_RETENTION to try and
make
> sure that is the case. But UNDO_RETENTION is based on clock time, so by
the
> time our poster realised his error, performed the failed incomplete
> recovery, posted a message here, and got a reply, the chances of his
> required undo still being available are slim. The undo will still be
there,
> however old it is, provided few other transactions have occurred elsewhere
> in the database, causing his original undo to be over-written (ie,
> undo_retention=60 doesn't mean the undo is wiped when 60 minutes is up,
> merely that it *can* be over-written after the 60th minute, if some other
> transaction needs the space).
>
> And whilst you cannot flashback past DDL on a table, or do flashback as
SYS,
> it is definitely possible to flashback past an incomplete recovery. So it
is
> possible in theory, clock time aside, that the required undo to get our
> original poster's records back is still there (assuming that his records
are
> still currently missing because his incomplete recovery was to a point in
> time after the deletion of them... the recovery would therefore have
> re-performed the deletion, and so re-generated the necessary undo). So,
yes,
> it might well be worth a go for him to try a flashback query, provided his
> database hasn't been too busy in the meantime.
>
> Good call. Sometimes we forget the existence of these new features, and
> still do things the tried-and-trusted-but-old-fashioned way!
>
> Regards
> HJR
>
> >
> >
> > "Antonio Da Silva" <news_at_nioto.com.NoSpamMerci> wrote in message
> > news:b07be0$b8s$1_at_news-reader10.wanadoo.fr...
> > > Hi,
> > >
> > > I just remove all content from a table in an Oracle 9i server,
> > >
> > > after many tries to restore it with the redologs, I can
> > > get it back,
> > >
> > > I use :
> > > --
> > > sql> connect internal
> > > sql> shutdown
> > > sql> startup mount
> > > sql> recover database until ='16-JAN-2003:00:00:00'
> > > sql> alter database resetlogs
> > > ---
> > > No error are displayed, and
> > > oracle says that recovery is done, but my table is always empty,
> > > did I miss something ?
> > >
> > > Thanks
> > >
> > >
> > > Tonio
> > >
> >
> >
> >
>
>
Received on Sat Jan 18 2003 - 18:43:38 CST