How to compare the database of a backup from another environment

Posted on

Question :

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?

Answer :

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:

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.

Leave a Reply

Your email address will not be published. Required fields are marked *