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

score 3 · Answer 1 · answered Feb 23 '11 at 14:54

3

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.

answered Feb 23 '11 at 14:54

Justin Cave

227,342
24
367
384

Thank you for pointing me towards the DBA_2PC_PENDING view, I will investigate on next lockup. Oracle Streams are not for me, I'm running Standard Edition (One). Isn't Streams superceeded by GoldenGate technologies btw? – TheRoadrunner Feb 23 '11 at 19:56
@TheRoadrunner - Even in Standard Edition One, 11g supports both Streams (with a few limitations) and materialized view replication (though not multi-master materialized view replication). – Justin Cave Feb 24 '11 at 06:03
I'll have to consider streams again, then. In the mean time, the line broke again, and I have the locked situation - nothing in DBA_2PC_PENDING though? – TheRoadrunner Feb 24 '11 at 14:10

Jon Heller · Answer 2 · 2011-02-24T06:54:30.953

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;
/

Thanks for your advice. I am using bulk inserts. This was just an overly simple sample. I only use one row at a time for the master table, seven or so details tables are bulk inserted resulting in hundreds if not thousands of records per master record. — TheRoadrunner, Feb 23 '11 at 20:02
@TheRoadRunner am I missing something ? Your sample still shows loop..insert. — Sathyajith Bhat, Feb 24 '11 at 06:25
@Sathya: As I wrote, still looping on the master table, but doing bulk insert on details table(s). — TheRoadrunner, Feb 24 '11 at 07:27

Securing Oracle distributed transactions against network failures

2 Answers2