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 comm开发者_如何转开发it - 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?

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

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.

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.

Maybe doing an export in consistent mode helps. Have a look at http://www.dba-oracle.com/tips_oracle_export_utility.htm

You could use "storage level" based snapshots as well, though oracle seems to think that using RMAN is a better way to go: http://www.oracle.com/technetwork/database/features/availability/rman-fra-snapshot-322251.html

First of all as others guys have said there are special tools for "snapshotting" in Oracle and it's better to use it for the task for me. But if we look on the problem in particular we will see that it's a non-repeatable reads & phantom reads problem [1], so it's all about transaction isolation levels. We have the SERIALISABLE level in Oracle to avoid (but it doesn't mean that it's good for your task in general case) these problems [2], so if you don't want to get some surprises and want to get your database in consistent state on certain moment of time (your transaction start point time) you should do this:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Links:

1. https://en.wikipedia.org/wiki/Isolation_(database_systems)
2. http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10005.htm#SQLRF01705