Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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? – kevinsky Sep 29 '11 at 10:21
"... that the backup has missed"? Why do you think export is missing anything? Also, export<>backup. – Jack Douglas Sep 29 '11 at 10:35
Please post your backup command/script – Jack Douglas Sep 29 '11 at 12:42
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?" – Jack Douglas Sep 29 '11 at 12:52
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 '11 at 12:57

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:

Snapshot Standby is even more comfortable, because you can do it very fast & easy multiple times subsequently after your live DB did change:

share|improve this answer

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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