Keeping tables synchronized in Oracle

Question

We're about to run side-by-side testing to compare a legacy system with a new shiny version. We have an Oracle database table, A, that stores data for the legacy system, and an equivalent table, B, that stores data for the new system, so for the duration of the test, the database is denormalized. (Also, the legacy system and table A are fixed - no changes allowed)

What I want to do is to allow the infrequent DML operations on A to propagate to B, and vice-versa. I started with a pair of triggers to do this, but hit the obvious problem that when the triggers run, the tables are mutating, and an exception is thrown.

Is there a standard way of handling this issue? I've read differing reports on whether or not using dbms_scheduler is the way to go...

Thanks,

Andy

Update: I've ended up chickening out of the whole issue and ensured that all stored procedures that update A, also update B, and vice versa.

I've marked Quassnoi's answer as accepted, because I'd follow his suggestions if faced with the same issue in the future.

I've marked up JosephStyon's answer, because I briefly got things working by adding two insert/update statement level triggers on tables A and B, then doing his merge procedure using A or B as the master table, depending on which trigger ran (although first I checked that the target table would be changed by the merge, earlying out if not).

Answer 1

Put the below three statements in a stored procedure, then run it as a scheduled job as often as you like:

--Assume that "A" is a master, and "B" needs to be synched

--If no match in "A", delete from "B"
DELETE FROM B
WHERE NOT EXISTS(
                SELECT *
                FROM A
                WHERE A.PRIMARY_KEY = B.PRIMARY_KEY
                );

--If there is a match, but they are different, then update "B"
update 
  (
  select
    a.field1 as new_value1
   ,b.field1 as old_value1
   ,a.field2 as new_value2
   ,b.field2 as old_value2
   ,....
   ,a.fieldN as new_valueN
   ,b.fieldN as old_valueN
  from
    a
   ,b
 where a.primary_key = b.primary_key
 )
set
  old_value1 = new_value1
 ,old_value2 = new_value2
 ,....
 ,old_valueN = new_valueN;


--if the record is new to "A", then insert it into "B"
INSERT INTO B
SELECT *
FROM A 
WHERE NOT EXISTS(
                SELECT *
                FROM B 
                WHERE B.PRIMARY_KEY = A.PRIMARY_KEY
                );

Answer 2

Do you really mean DDL, not DML?

With DML you can have a look at Oracles Multi Master Replication to keep the tables in synch or you could also have a look at the tool SymmetricDS for this purpose.

With DDL the only solution I'm aware of is again the Oracle advanced replication.

Answer 3

I'd create A and B as views over a single normalized (or denormalized) table, and created an INSTEAD OF trigger over these views to handle DML operations.

If the query plans matter, it's better to keep two copies of tables: A_underlying and B_underlying and create the views just like this:

CREATE VIEW A
AS
SELECT  *
FROM    A_underlying

CREATE VIEW B
AS
SELECT  *
FROM    B_underlying

The predicates will be pushed into the views, and the query plans for actual tables and views will be the same.

In INSTEAD OF triggers over both view, you should put the data into both underlying tables.

Answer 4

Oracle 10g and above have implemented Change Notification as an asynchronous process. It's automatic and the package is included with the server install of Oracle 10g and above.

You can see here for some info.