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.