What's the best way to take a "snapshot" of an Oracle database that is constantly being updated?
I want to take a consistent snapshot of an Oracle database that is constantly being updated by a TIBCO DB Adaptor.
Typcially TIBCO updates a bunch of tables at once, and then COMMITs. If I traverse through all the tables, taking a snapshot once a day, then I could grab data from table A before the commit, and from table B after the commit - but if A and B have a relationship, then they will no longer match properly.
Is "SET TRANSACTION READ ONLY" the way to go?
e.g.
COMMIT
SET TRANSACTION READ ONLY
SELECT * FROM A WHERE A.ADB_UPDATEDDATE > TODAY()-1
SELECT * FROM B WHERE B.ADB_UPDATEDDATE > TODAY()-1
etc.
COMMIT
(TODAY syntax might not be correct, not important!)
Or is there something better that I can do?
Answer
If by "snapshot" you mean a full copy of the database in a consistent mode, then I would restore the database from a backup and recover it until the desired point in time. The Oracle recovery processes will take care of of the consistency (tracked by System Change Number or SCN).
If you are using RMAN for backups and recovery, there is a "DUPLICATE DATABASE" command with a time clause that will make this relatively painless.
On the other hand, if you're just looking to extract a few tables in a consistent mode I can think of two options:
- Export the group of tables with the consistent=y option of the (older) exp utility
- Use the newer expdp utility with the flashback_time option