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: Quick flashback question

Re: Quick flashback question

From: Anurag Varma <avoracle_at_gmail.com>
Date: 8 Mar 2007 10:04:01 -0800
Message-ID: <1173377041.212152.191550@s48g2000cws.googlegroups.com>


On Mar 8, 12:49 pm, GS <g..._at_canada.com> wrote:
> Anurag Varma wrote:
> > On Mar 8, 12:11 pm, GS <g..._at_canada.com> wrote:
>
> >>I've used this feature only once before, so I am a bit rusty here..
>
> >>A user just came and said they might have trashed some data in a table
> >>or tables, I am just waiting to hear back more details. In any case,
> >>they dont want to do a PIT recovery of the whole database, so I said I'd
> >>look into how far back I could query the tables when I find out which
> >>ones they need. (there is no export current enough to help here)
>
> >>I checked the DB and the undo retention was set for 3 hours, so I
> >>figured they were out of luck, but just out of curiosity I queried a
> >>table from the schema using the "as of" syntax for a point in time
> >>yesterday and it returned data. I was expecting it to return an error.
>
> >>The undo tablespace is 500MB of which only 23MB are currently being
> >>used. My understanding of flashback query was that you could only query
> >>back as far as undo retention is set. When I query the v$undostat view
> >>it is goes back just over 2 days.
>
> >>If the database has very little activity can you query beyond the undo
> >>retention parameter? Been searching tahiti and from what I see there I
> >>should be limited to what the retention param is set to.
>
> >>thanks in advance!
>
> > Yes, if the database has very little activity, then you can in lot of
> > cases
> > query beyond the undo retention parameter if your expired undo has not
> > been overwritten yet.
>
> > In fact in 10gr2, oracle "autotunes" your undo_retention parameter
> > and tries filling upto 85% of the undo tablespace in some cases.
> > It also tunes the undo_retention paramter based on the undo generated
> > (see tuned_undoretention column in v$undostat)
>
> > Anurag
>
> thanks
>
> The table I test queried is pretty static, so when I find out the actual
> tables that may have lost or trashed data I'll find out more. The db is 91R2

You might want to find out quickly ... murphy's law might apply otherwise :)
And just because you can go back prior to undo_retention period on one table .. does not mean it will work for others. It all depends on whether the undo has been overwritten.

Anurag Received on Thu Mar 08 2007 - 12:04:01 CST

Original text of this message

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