Extract from Oracle at given time 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
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.