1

I have a requirement to retrieve (extract) data from an Oracle database (either 9i or 10g) as at a given point in time.

I will need access to the data at this point of time for several days afterwards (as the database is large enough that the extract process will take several days to complete). The database is also in constant use.

For example, on January 3rd, I may want to query data as it was on January 1st and I may need access to the data as it was on January 1st for several days (maybe until about January 7th). What is the best way to achieve this?

I am not a DBA, but have looked into a number of solutions, that may form the basis of a solution, but would appreciate any advice on my best course of action.

I am aware of flashback and the ability to query a table as it was at a given point in time. I am, however aware that keeping the amount of history I'd need to (probably at least 7 days worth) is likely to make this infeasible based on the size and high usage of the database. I am also aware that when you set flashback to record a given number of days worth of data, you may end up with significantly less than this based on high usage, so this would seem like a risky approach anyway?

I am also aware of creating a Guaranteed Restore Point, but that this will only be an option if my database is 10g or above(?) If I did this, I believe I would have to turn Flashback off (?) as if I don't, flashback will have to keep logs all the way back to the time of the guaranteed restore point (and so in this example I'd end up with at least 7 days worth of history and risk the database running out of disk space). With a guaranteed restore point in place, is it possible to run a SELECT query on a table as at this restore point using something like the following:

CREATE RESTORE POINT test_restore_point GUARANTEE FLASHBACK DATABASE;

SELECT * FROM tbl1 AS OF test_restore_point;

The only examples I have seen involve actually restoring the database to the restore point (which I don't want to do) rather than querying from it. I am unable to test this for myself as I currently only have access to a 9i database.

I'd be very grateful for any advice on how I can achieve my objectives using this functionality or any other.

Thanks

Improve this question
1
  • 1
    You can use flashback queries for 9i (oracle-base.com/articles/9i/flashback-query.php) and 10g (oracle-base.com/articles/10g/articles-10g.php). Just note that without restore points you need enough undo space to hold all the back data. With restore point, your DML might run a little slow. In 10g+ you might want to look at 'Total Recall' or 'Flashback data Archive' as it is known now. This will give you more flexibility plus automatic data purge etc, but that functionality is actually much more usable from 11gr2 onwards.
    – Raj
    Commented Dec 9, 2014 at 17:04

1 Answer 1

Reset to default
1

A query like

SELECT * FROM tbl1 AS OF test_restore_point;

is not possible.

Restorepoints can be used with the command FLASHBACK DATABASE. FLASHBACK DATABASE works similar like a restore.

  • First the whole database will be brought to a state before the time of the restore point. But this is not accomplished by copying a whole backup over each data file but it is achieved by applying flashback logs. For each data block that was modified after making the restore point a backup was saved in the flashback logs.

  • Then the redo files are applied to roll forward the database to the restore point.

So when you flashback to a restore point the whole database is set to the point in time of the restore point. This is not what you want.

You can use flashback queries to queries old data but this data can only be supplied by the database if it is still found in the undo tablespace.

A reliable mechanism to hold the original data for a fixed period of time is Total recall/Flashback Data Archive. As Justin Cave pointed out this Feature is only available for Oracle 11.1 or higher versions.

The following licensing rules hold

Flashback Data Archive (formerly known as Total Recall)

For releases earlier than Oracle Database 11g Release 2 (11.2.0.4): You must license the Oracle Advanced Compression option to use Flashback Data Archive.

Beginning with Oracle Database 11g Release 2 (11.2.0.4): Flashback Data Archive—without history table optimization—is available in all editions.

A Flashback Data Archive holds the undo information of a selected table for a fixed period of time but it used a dedicated tablespace and is therefore not influenced by the cleanup meachanisms of the undo segments.

The table can be queried in the usual way with a flashback query.

You need Oracle Enterprise Edition and an additional license to use this feature.

Improve this answer
3
  • 1
    Total recall/ flashback data archive was introduced in 11g, I believe, so it wouldn't be available to the original poster that is trying to use 9i and 10g. Since you mention licensing, flashback data archive becomes part of the database license in 12.1, it's no longer an extra cost option.
    – Justin Cave
    Commented Dec 10, 2014 at 1:02
  • @Justin Cave, thank you for this comment, I added this in the text.
    – miracle173
    Commented Dec 10, 2014 at 4:22
  • Thanks for the answer and subsequent comments. From what has been said it sounds like my best (only) option using 9i and 10g is to set my undo tablespace as large as possible and use flashback in the hope that my extract will complete before the required history is lost? Is there a way to check the date/time of the latest history I have available in the undo tablespace? I have seen reference to the following: SELECT to_char(oldest_flashback_time, 'YYYY-MM-DD HH24:MI:SS') FROM v$flashback_database_log but, in 9i, this table doesn't seem to exist. Is there another way to check in 9i?
    – wwfc1887
    Commented Dec 10, 2014 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.