Heterogeneous Database Sync

Hi

As a part of ORACLE to PostgreSQL Migration, I come across to implement a trigger on Oracle, which sync it's data to PostgreSQL. I have tried with
a simple table as below, which is hopefully helpful to others.

Find this link to configure the heterogeneous dblink to postgres.

I believe, the below approach works effectively with the Primary Key tables of Oracle Database.
If we don't have primary key in a table, then the UPDATE,DELETE statements going to fire multiple times in Postgres, which leads performance issues.

ORACLE

CREATE TABLE test(t INT PRIMARY KEY);

CREATE OR REPLACE TRIGGER testref AFTER INSERT OR UPDATE OR DELETE ON test
FOR EACH ROW
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
 C number;
 N number;
BEGIN
 c:=DBMS_HS_PASSTHROUGH.OPEN_CURSOR@pglink;
 IF INSERTING THEN
  DBMS_HS_PASSTHROUGH.PARSE@pglink(c, 'INSERT INTO test VALUES('||:NEW.t||');');
  n:=DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@pglink(c);
 ELSIF DELETING THEN
  DBMS_HS_PASSTHROUGH.PARSE@pglink(c, 'DELETE FROM test WHERE t='||:OLD.t);
  n:=DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@pglink(c);
 ELSIF UPDATING THEN
  DBMS_HS_PASSTHROUGH.PARSE@pglink(c, 'UPDATE test SET t='||:NEW.T||' WHERE t='||:OLD.T);
  n:=DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@pglink(c);
 END IF;
 DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@pglink(c);
COMMIT;
END;
/
SQL> INSERT INTO test VALUES(1);
SQL> INSERT INTO test VALUES(2);
SQL> INSERT INTO test VALUES(3);


SQL> DELETE FROM test WHERE t=2;

SQL> UPDATE test SET t=10 WHERE t=1;
SQL> COMMIT;
Commit complete.

PostgreSQL

CREATE TABLE test(t INT PRIMARY KEY);
postgres=# SELECT * FROM test;
t
----
3
10
(2 rows)

NOTE
Oracle's DG4ODBC don't support the 2 phase commit with PostgreSQL. In the above case, if we issue ROLLBACK in oracle, the data in postgresql will remain, which will lead to inconsistency. To fix this, i believe we have to use Asynchronous data copy by scheduling a job in Oracle as below.

CREATE SEQUENCE test_seq ORDER;

CREATE TABLE test_backup(SEQ INT, EVT VARCHAR(2), t INT, PRIMARY KEY(SEQ, EVT, t));

-- Trigger, which records the test table's information into test_backup.

CREATE OR REPLACE TRIGGER testref AFTER INSERT OR UPDATE OR DELETE ON test
FOR EACH ROW
DECLARE
BEGIN
IF INSERTING THEN
  INSERT INTO test_backup VALUES(test_seq.nextval, 'I', :NEW.t);
ELSIF DELETING THEN
  INSERT INTO test_backup VALUES(test_seq.nextval, 'D', :OLD.t);
ELSIF UPDATING THEN
    INSERT INTO test_backup VALUES(test_seq.nextval, 'UD', :OLD.t);
  INSERT INTO test_backup VALUES(test_seq.nextval, 'UI', :NEW.t);
END IF;
END;
/

-- Procedure, which sync the test_backup table to postgres test table.

create or replace PROCEDURE SYNCORATOPG
IS
REC TEST_BACKUP%ROWTYPE;
C NUMBER;
N NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
TYPE SYNCID IS TABLE OF INTEGER;
SYNCNUM SYNCID;
CNT INT:=0;
BEGIN
  
    BEGIN
        SYNCNUM:=SYNCID();
        FOR REC IN (SELECT * FROM test_backup ORDER BY SEQ ASC)
        LOOP
        SYNCNUM.EXTEND;
        CNT:=CNT+1;
        SYNCNUM(CNT):=REC.SEQ;
        c:=DBMS_HS_PASSTHROUGH.OPEN_CURSOR@TOPG;
        IF REC.EVT = 'I' OR REC.EVT='UI' THEN
           DBMS_HS_PASSTHROUGH.PARSE@TOPG(c, 'INSERT INTO test VALUES('||REC.t||');');
        ELSIF REC.EVT = 'D' OR REC.EVT='UD' THEN
           DBMS_HS_PASSTHROUGH.PARSE@TOPG(c, 'DELETE FROM test WHERE t='||REC.T);
        END IF;
      
         n:=DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@TOPG(c);
         DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@TOPG(c);
        END LOOP;
        
        COMMIT;
    END;
    
    BEGIN
      FOR i IN 1..SYNCNUM.COUNT
      LOOP
        DELETE FROM test_backup WHERE SEQ=SYNCNUM(i);
        SYNCNUM.DELETE(i);
      END LOOP;
       COMMIT;
    END;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20000, 'Fatal error.'||SQLERRM);
END;

--Scheduling a JOB in Oracle, which call the above SYNC procedure for every 1 minute.

BEGIN
DBMS_JOB.isubmit (
job => 113,
what => 'BEGIN SYNCORATOPG; END;',
next_date => SYSDATE,
interval => 'SYSDATE + 1/(24*60) /* 1 Minute */');

COMMIT;
END;
/

-- Inserting Sample Data In Oracle

INSERT INTO test VALUES(-1);
INSERT INTO test VALUES(-2);
INSERT INTO test VALUES(-3);

UPDATE test SET t=-20 WHERE t=-2;
DELETE FROM test WHERE t=-1;
INSERT INTO test VALUES(-2);
COMMIT;

-- PostgreSQL

postgres=# SELECT * FROM test;
 t
----
  -3
 -20
  -2
(3 rows)

As usual, welcome your inputs.

 --Dinesh Kumar