Re: recovering data

"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 - 16:22:28 CST