This discussion is archived
12 Replies Latest reply: Mar 20, 2013 5:23 AM by APC RSS

Using DBMS Flashback AS OF SCN in SQL

bobmagan Newbie
Currently Being Moderated
I would like to use the DBMS Flashback capability in some SQL to look at data from a table based on the SCN.
ie.
select ename
from emp AS OF SCN 15387125
where empno = 442

I know the length of time you can use flashback AS OF SCN to is controllable (thought the default was 900 seconds), but I want to make sure. I haven't changed any DB params on my system and the above statement works longer than the 900 seconds.

If I try to look back at something that happened a few days ago I get:
     ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9_3490907396$" too small

Can someone confirm what I need to look at/configure for the AS OF SCN time.

thanks
  • 1. Re: Using DBMS Flashback AS OF SCN in SQL
    APC Oracle ACE
    Currently Being Moderated
    The duration available to Flashback is dependent on how the parameter DB_FLASHBACK_RETENTION_TARGET. The default (in 10G) is 1440 minutes (i.e. 1 day). [url http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams050.htm#REFRN10233]Find out more.

    So unless you have an especially parsimonious DBA you should have considerably longer than quarter of an hour. But not, obviously, "a few days". Although that parameter specifies an upper bound, and the actual flashback window may be smaller if there has been a lot of activity which has eaten up all the flashback area.

    Cheers, APC
  • 2. Re: Using DBMS Flashback AS OF SCN in SQL
    bobmagan Newbie
    Currently Being Moderated
    Thanks for the info.

    Edited by: bobmagan on Mar 18, 2013 6:09 AM
  • 3. Re: Using DBMS Flashback AS OF SCN in SQL
    Hoek Guru
    Currently Being Moderated
    maybe they upped the default from 10g.
    Nope, the default is still 1440 mins:
    http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams061.htm#REFRN10233
  • 4. Re: Using DBMS Flashback AS OF SCN in SQL
    bobmagan Newbie
    Currently Being Moderated
    yep, your right. I guess someone must have changed it on my DB.

    thanks
  • 5. Re: Using DBMS Flashback AS OF SCN in SQL
    APC Oracle ACE
    Currently Being Moderated
    Hoek wrote:
    Nope, the default is still 1440 mins:
    Thanks Hoek. I'm working on 10g at the moment, so that's where all my favourites take me ;)

    Cheers, APC
  • 6. Re: Using DBMS Flashback AS OF SCN in SQL
    bobmagan Newbie
    Currently Being Moderated
    I guess I need a little more help on this. I am receiving the same error if I attempt a flashback query on a record that was changed about 18 hrs ago. This is my test DB so there isn't alot of activity. I'd like to guarantee that I could run a flashback query for a 48 hr period. I looked at some of the info from my 11g DB and did some queries:

    My DB_FLASHBACK_RETENTION_TARGET is set to 2880 (which is 2 days)
    undo_management set to AUTO
    undo_retention set to 900

    select max(maxquerylen)
    from v$undostat; 4048

    select (4048/60)/60 query,(900/60)/60 retention
    from dual;

    query
    1.124444444

    retention
    .25

    Am I reading this right that I only have 1 hr guaranteed retention time for flashback? During my tests I was definitely able to query the info back further than 1 hr. Again, looking to have 48 hr window.

    Thanks
  • 7. Re: Using DBMS Flashback AS OF SCN in SQL
    bobmagan Newbie
    Currently Being Moderated
    Need more info

    Edited by: bobmagan on Mar 19, 2013 4:42 AM
  • 8. Re: Using DBMS Flashback AS OF SCN in SQL
    padders Pro
    Currently Being Moderated
    Where did you get 1 hour from?
  • 9. Re: Using DBMS Flashback AS OF SCN in SQL
    bobmagan Newbie
    Currently Being Moderated
    Is that not the 'query' part of the select below? Maybe I'm not understanding what that number is supposed to be

    select (4048/60)/60 query,(900/60)/60 retention
    from dual;

    query
    1.124444444

    retention
    .25
  • 10. Re: Using DBMS Flashback AS OF SCN in SQL
    bobmagan Newbie
    Currently Being Moderated
    Can someone please confirm what I need to change?

    thanks
  • 11. Re: Using DBMS Flashback AS OF SCN in SQL
    padders Pro
    Currently Being Moderated
    select (4048/60)/60 query,(900/60)/60 retention
    First column 'query' is showing your maximum query length in hours.

    Second column 'retention' is showing your current undo_retention time in hours (you have 0.25 hours = 15 mins).

    General advice would be to set undo_retention sufficient to cover your maximum query length to avoid getting snapshot too old.

    As far as I understand it the 'AS OF SCN' syntax is using undo information to answer a query as of a point in time hence I expect the guaranteed availability of that undo information to be based on the undo_retention parameter. That means if you wanted to use AS OF SCN for up to 24 hours you would need to set the parameter to that value (in seconds), but be aware that this may require you to allocate significantly more undo tablespace.

    As with all read consistency you might well be able to query further back if the information is available, this is about guaranteeing the information is available.
  • 12. Re: Using DBMS Flashback AS OF SCN in SQL
    APC Oracle ACE
    Currently Being Moderated
    I'm following up Padders comment regarding the {font:courier new}undo_retention{font} parameter being a guaranteed minimum.

    I suggest you have a look at the TUNED_RETENTION statistic in V$UNDOSTAT. That shows the size in the actual flashback window. That is, it shows the age of the oldest data in the UNDO tablespace. Hence the stat shows you the maximum value you could have plugged into a flashback query at that point. This should be a lot higher than the{font:courier new}undo_retention{font} value, especially if you are oin a quiet system.

    Cheers, APC

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points