Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186

    Unanswered: Select from .. as of - using archived redo logs - 10g

    Hi,

    I was under the impression I could issue a "Select from .. as of" statement back in time if I have the archived redo logs.

    I've been searching for a while and cant find an answer.

    My undo_management=AUTO, database is 10.2.0.1 standard edition, the retention is the default of 900 seconds as I've never changed it.

    I want to query a table as of 24 hours ago so I have all the archived redo logs from the last 48 hours in the correct directory

    When is issue the following query

    select * from supplier_codes AS OF TIMESTAMP
    TO_TIMESTAMP('2009-08-11 10:01:00', 'YYYY-MM-DD HH24:MIS')

    I get a snapshot to old ORA-01555 error. I guess that is because my retention is only 900 seconds but I thought the database should query the archive redo logs or have I got that totally wrong?!

    My undo tablespace is set to AUTOEXTEND ON and MAXSIZE UNLIMITED so there should be no space issues

    Any help would be greatly appreciated!

    Thanks
    Robert
    Last edited by robert xr4x4; 08-12-09 at 07:55.
    There are 10 types of people in the world, those that know Binary and those that don't.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool No flachback

    In order to do that, you need to enable flashback recovery.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    Quote Originally Posted by LKBrwn_DBA
    In order to do that, you need to enable flashback recovery.
    So do I need to have an enterprise edition of the database to be able to enable flashback recovery?

    So the only way to do a query "as of" would be to either have enterprise edition oracle or resore from a backup and apply the logs to the point I want to query?
    There are 10 types of people in the world, those that know Binary and those that don't.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Quote Originally Posted by robert xr4x4
    So do I need to have an enterprise edition of the database to be able to enable flashback recovery?

    So the only way to do a query "as of" would be to either have enterprise edition oracle or resore from a backup and apply the logs to the point I want to query?
    Or...install logminer -- a bit more cumbersome but works.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    (I don't know if flashback query is an Enterprise feature or not.)

    To use FLASHBACK DATABASE you need to enable flashback. To run a flashback query you do not. However, for the latter, you are not querying redo logs, or archived redo logs. You are querying the UNDO tablespace.

    You can ask that a specific amount of UNDO be retained for these types of queries by specifying a value (in minutes) for the init parm UNDO_RETENTION, which basically specifies how far back in time you want to be able to query. The larger this number, the larger your UNDO tablespace needs to be:

    Code:
       UNDO_RETENTION specifies (in seconds) the low threshold value of 
       undo retention. The system retains undo for at least the time specified 
       in this parameter and automatically tunes the undo retention period to 
       satisfy the undo requirements of the queries.
    
       The UNDO_RETENTION parameter can only be honored if the current undo 
       tablespace has enough space. If an active transaction requires undo space 
       and the undo tablespace does not have available space, then the system 
       starts reusing unexpired undo space. This action can potentially cause some 
       queries to fail with a "snapshot too old" message.
    Also, you can guarantee the number of minutes specified above is actually reserved by setting UNDO RETENTION GUARANTEE to yes, which I believe is done at the tablespace level, just for the UNDO tablespace. That could be lead to the UNDO tablespace growing sporadically large for a system that incurs inconsistent data volumes.

    --=Chuck

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by chuck_forbes
    (I don't know if flashback query is an Enterprise feature or not.)
    Depends, flashback Query is available with the free express edition already.
    The other things like flashback table, flashback database or flashback session (the thing I find really cool), are only available in the enterprise edition.

    http://www.oracle.com/database/product_editions.html

    You are querying the UNDO tablespace
    Except for the new Flashback Archive in 11g. There you can go back to any point in time even years if you have enough disk space.

    Using flashback archives if a lot faster than the UNDO based flashback (because the archives are "just" tables), so for any "data versioning" where I need to keep historic data, I'd go with a flashback archive if I had 11g Enterprise

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    So there IS the ability to use the archived redo logs ... but in 11g. I'm defnitely getting a little too 10g-centric in my answers

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by chuck_forbes
    So there IS the ability to use the archived redo logs ... but in 11g. I'm defnitely getting a little too 10g-centric in my answers
    No, the flashback archive is something completely different than an archived redo log.

    A flashback archive is essentially a table that holds all historical records of the original table. Only that Oracle manages that completely automatically. You could simulate the same thing with a trigger that copies each row to another table before updating or deleting it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •