Re: Compare data in tables of different structures

• From: DA Morgan <damorgan@xxxxxxxxx>
• Date: Wed, 04 Jan 2006 09:44:25 -0800

Hi, dear all,

Currently we have two Oralce 9i databases in the production
environment. We synchronize them *logically* every day. *logically*
means these 2 databases are not exactly identical, the schemas inside
are different, the table structures inside are different too, but we
synchronize the data in the 2 databases in daily basis, bi-directional.

Just a simple example: In DB 1, we have table 1, and in DB 2, we have
table 2 and 3. Table 2 and 3 together contain the same data as table 1
does. Another scenario is, two tables contain the same data, but having
different structures. I hope you have gotten the meaning. ( It is much
more complex than this simple example in our reality, and impossible to
alter the table structures too, due to the existing business rules).

The problem we are facing is, the databases don't synchronize well with
each other, some records gone missing, some columns of records become
different, etc.(Currently some other people manually examine them and
update ). We use xml files to synchronize.

We know the corresponding tables, and now we need to find and analyze
the record-level and column level differences among them. Yes, find the
differences first only, not solve them at this stage.

I have thought of 4 ways to tackle it:

1. Create identical Views out of the corresponding tables. and compare
the views first. then trace the view data to their tables. Is this a
good option in terms of viability and complexity?

2. Write PL/SQL scripts. I haven't thought of how to write them, but I
think this programming can solve it, but can be very difficult and
complex.

3. Buy 3rd party tools, such as DBDiff or dbBalance. This will incur
extra cost and at this moment, my director is not approval of this.

4. Any Oracle9i built-in tools to help?

Do you have any comments and ideas of whether these ideas are possible?
or any other better solution?  Thanks in advance.

Regards,
Warren

PS: I am not really an oracle and db expert, but assigned this task.
and we will migrate this system to a new one, but at this moment we
have to solve this for the production people.

Look at the built-in DBMS_RECTIFIER_DIFF package. There is a demo of
it at www.psoug.org. Click on Morgan's Library and scroll down.

But it seems to me that the very fact that discrepancies exist
indicates that your replication methodology is flawed.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@xxxxxxxxxxxxxxxx
(replace x with u to respond)
.

• References:
  • Compare data in tables of different structures
    • From: warren . liyongbo

• Prev by Date: Re: Certified Hardware?
• Next by Date: Re: Oracle RAM backup failure
• Previous by thread: Compare data in tables of different structures
• Next by thread: Re: Compare data in tables of different structures
• Index(es):
  • Date
  • Thread

• Security
• UNIX
• Linux
• Coding
• Usenet

• Archive
• About
• Privacy
• Search
• Imprint