Recover Oracle 9i Database to multiple points in time
Posted on 2006-05-04
We have a database that we need to get point-in-time snapshots from on a daily basis, from about a year ago until today. We do not have flashback queries turned on, but we do have all the redo logs archived. What I'd like to do is something like this:
1) Create a standby database whose highest SCN is just prior to the first day we need access to.
2) Use RECOVER DATABASE UNTIL '2005-06-01 17:00:00 -0500'
3) Open the database read-only and do a SELECT on the tables, dump the data as necessary (into text files, or using exp, it doesn't really matter)
4) Shutdown the database, remount it and go back to step (2) for the next day.
Is this possible in Oracle? Or do I have to completely rebuild the database for each recovery? Basically, once the database is opened (read-only, so I assume it would be NORESETLOGS), can I close it and continue recovery?