Securing Oracle distributed transactions against network failures

Question

I am synchronizing a table in a local database with data from a table on a database on the opposite side of the earth using distributed transactions. The networks are connected through vpn over the internet. Most of the time it works fine, but when the connection is disrupted during an active transaction, a lock is preventing the job from running again. I cannot kill the locking session. Trying to do so just returns "ORA-00031: Session marked for kill" and it is not actually killed before i cycle the local database.

The sync job is basically

CURSOR TRANS_CURSOR IS    
SELECT COL_A, COL_B, COL_C    
 FROM REMOTE_MASTERTABLE@MY_LINK    
 WHERE UPDATED IS NULL;    

BEGIN    
  FOR TRANS IN TRANS_CURSOR LOOP    

    INSERT INTO LOCAL_MASTERTABLE    
      (COL_A, COL_B, COL_C)    
    VALUES    
      (TRANS.COL_A, TRANS.COL_B, TRANS.COL_C);    

    INSERT INTO LOCAL_DETAILSTABLE (COL_A, COL_D, COL_E)
      SELECT COL_A, COL_D, COL_E
      FROM REMOTE_DETAILSTABLE@MY_LINK
      WHERE COL_A = TRANS.COL_A;

    UPDATE REMOTE_MASTERTABLE@MY_LINK SET UPDATED = 1 WHERE COL_A = TRANS.COL_A;    
  END LOOP;    
END;

Any ideas to make this sync operation more tolerant to network dropouts would be greatly appreciated. I use Oracle Standard Edition One, so no Enterprise features are available.

TIA Søren

Answer 1

First off, do you really need to roll your own replication solution? Oracle provides technologies like Streams that are designed to allow you to replicate data changes from one system to another reliably without depending on the database link being always available. That also minimizes the amount of code you have to write and the amount of maintenance you have to perform.

Assuming that your application does need to be configured this way, Oracle will have to use the two-phase commit protocol to ensure that the distributed transaction happens atomically. It sounds like transactions are being left in an in-doubt state. You should be able to see information about in-doubt transactions in the DBA_2PC_PENDING view. You should then be able to manually handle that in-doubt transaction.

Answer 2

You may want to use bulk processing instead of looping. Bulk DML can often give huge performance gains, and if there is a large amount of network lag then the difference may be dramatic if Oracle is retrieving one row at a time. Decreasing the run time won't fix the error, but it should help avoid it. (Although Oracle may already be doing this optimization behind the scenes.)

EDIT

Bulk processing might help, but the best solution would probably be to use only SQL statements. I did some testing and the below version ran about 20 times faster than the original. (Although it's difficult to know how closely my sample data and self-referencing database link model your real data.)

BEGIN
    INSERT INTO LOCAL_MASTERTABLE    
      (COL_A, COL_B, COL_C)    
    SELECT COL_A, COL_B, COL_C    
     FROM REMOTE_MASTERTABLE@MY_LINK
     WHERE UPDATED IS NULL;    

    INSERT INTO LOCAL_DETAILSTABLE (COL_A, COL_D, COL_E)
      SELECT REMOTE_DETAILSTABLE.COL_A, REMOTE_DETAILSTABLE.COL_D, REMOTE_DETAILSTABLE.COL_E
      FROM REMOTE_DETAILSTABLE@MY_LINK
        INNER JOIN (SELECT COL_A FROM REMOTE_MASTERTABLE@MY_LINK WHERE UPDATED IS NULL) TRANS
        ON REMOTE_DETAILSTABLE.COL_A = TRANS.COL_A;

    UPDATE REMOTE_MASTERTABLE@MY_LINK SET UPDATED = 1 WHERE UPDATED IS NULL;
END;
/