Has anyone had any luck with taking a backup (approx 30 tables) from an Oracle database's live environment, then loading a test environment with this backup and doing a comparison between the test environment and the backup taken from the live environment? Preferably without using queries against the live environment.

Note that the live environment will have transactions changing its data by the time the test environment is loaded, so I can't do a comparison using the live environment's data after loading the test environment.

The idea is to pick up any missing records, columns or even tables that the backup has missed. It would also be great to know if the actual data values were the same.

I'm thinking a hash function might be the best way to go. Are there any tools available for this?

  • 2
    You don't normally need luck when you are using export or the data pump, they are very reliable. Have you had previous errors in backup? What were they?
    – kevinskio
    Sep 29, 2011 at 10:21
  • 2
    "... that the backup has missed"? Why do you think export is missing anything? Also, export<>backup. Sep 29, 2011 at 10:35
  • 1
    Please post your backup command/script Sep 29, 2011 at 12:42
  • 1
    You have DBAs? Are they responsible for the restore too? I think you need to continue this conversation with them. Eg ask them "why are my tables missing?" Sep 29, 2011 at 12:52
  • 1
    Yes we do and they are responsible for the restore as well. It was an oversight on their part. I'm just surprised no one has had experience using a hash or otherwise to confirm the backup went according to expectations.
    – toop
    Sep 29, 2011 at 12:57

2 Answers 2


It sounds to me like the used procedure is somehow suboptimal :-) We have two easy ways to create an identical test Database from a live DB: Simple Cloning and Snapshot Standby. Simple Cloning is the RMAN command duplicate target database: http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmdupdb.htm#BRADV010

Snapshot Standby is even more comfortable, because you can do it very fast & easy multiple times subsequently after your live DB did change: http://uhesse.wordpress.com/2010/09/24/snapshot-standby-database-in-action/


A comparison really requires comparing the environments in some way. I would create a database link on your test system to your live system and then do some simple data dictionary comparisons on user_tables and user_tab_columns. Any differences you find should be brought to the attention of the DBA so he can determine which of the following applies.

  1. The structure wasn't properly backed up.
  2. The structure wasn't properly restored.
  3. The structure was changed after the backup.

Having the specific differences from the queries will help him track the problem down.

Since you have concerns about missing tables and columns, I'd address that first and then you can look into comparing the content, see Compare content of databases in Oracle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.