Geek Answers Handbook

What is the best way to get a “snapshot” of an Oracle database that is constantly updated?

I want to take a sequential snapshot of an Oracle database that is constantly being updated using the TIBCO DB adapter.

Typically, TIBCO updates several tables at once, and then COMMIT. If I go through all the tables, taking a snapshot once a day, I could capture the data from table A before committing and from table B after committing, but if A and B have relationships, then they will no longer match properly.

Is "SET TRANSACTION READ ONLY" a way?
eg

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 may be wrong, unimportant!)

Or is there something better I can do?

+6
oracle snapshot
source share
6 answers

This is very easy to do using an Oracle function called Flashback . As long as you know when the previous version was (time or scn) and it was in the flashback window, you can simply request it.

+4
source share

If “snapshot” means a full copy of the database in a consistent mode, I would restore the database from a backup and restore it to the right point in time. Oracle recovery processes will take care of consistency (tracked by System Change Number or SCN).

If you use RMAN for backup and recovery, there is a “DUPLICATE DATABASE” command with a time suggestion that will make it relatively painless.

On the other hand, if you just want to extract multiple tables in a consistent manner, I can present two options:

  • Export a group of tables using the matched = y option (older) exp utility
  • Use the new expdp utility with the flashback_time option
+7
source share

In addition to dpbradley's suggestions, if these are just a few tables that are not too large and you have a flashback request available, you can create a copy of the tables using the flashback query from the same timestamp.

+1
source share

Perhaps it helps export in a consistent manner. Take a look at http://www.dba-oracle.com/tips_oracle_export_utility.htm

0
source share

You can also use snapshots based on storage level, although the oracle seems to think using RMAN is the best way: http://www.oracle.com/technetwork/database/features/availability/rman-fra-snapshot-322251. html

0
source share

First of all, as other guys say, Oracle has special tools for snapshots, and it’s better to use it for this task for me. But if we look at the problem, in particular, we will see that it does not repeat and phantom reads the problem [1], so everything about transaction isolation levels . We have a SERIALISABLE level in Oracle to avoid (but this does not mean that it is good for your task in the general case) these problems [2], therefore, if you do not want to receive any surprises and want to get the databases in a consistent state at a specific point in time (transaction start time), you must do the following:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

References:

0
source share

More articles:


All Articles