What's the best way to take a "snapshot" of an Oracle database that is constantly being updated?

Question

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?

I'm an idiot when it comes to databases so even obvious advice would be appreciated...

-B

EDIT: Thanks all for the suggestions, I'll let you know what my team decide to go for.

Answer 1

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

Answer 2

In addition to dpbradley's suggestions, if it is only a few not too big tables and you have flashback query available you could create a copy of the tables using a flashback query as of the same timestamp.

Answer 3

This is very easy to do using an Oracle feature called Flashback. As long as you know when the previous version was (time or scn) and it's within the flashback window, you can simply query for it.