CODEWITHSUNDEEP
oracle-databasesnapshot
Bhasker Pandya
Bhasker Pandya

Reputation: 193

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?

Upvotes: 7

Views: 26128

Answers (6)

Ilia Maskov
Ilia Maskov

Reputation: 1898

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

Upvotes: 0

rogerdpack
rogerdpack

Reputation: 66711

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

Upvotes: 0

elwood
elwood

Reputation: 29

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

Upvotes: 0

Gaius
Gaius

Reputation: 2585

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.

Upvotes: 4

Leigh Riffel
Leigh Riffel

Reputation: 6641

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.

Upvotes: 1

dpbradley
dpbradley

Reputation: 11915

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

Upvotes: 7

Related Questions