CODEWITHSUNDEEP
oracle-databasesynchronizationoracle-sqldeveloperbidirectional
Arulselvam
Arulselvam

Reputation: 1

How to achieve bi-directional data synchronization in Oracle db 19c?

I have two databases. One is hosted locally and named 'local db' and other is hosted remotely, and is named 'remote'.

I have the same data on both databases. If one table in local db undergoes any changes like a DDL and DML operation, it should affect the other, remote database, and vice versa.

I have achieved uni-directional data synchronization by using triggers, which means I am able to make changes in one table in the local database, and it will be reflected in the remote database.

However, I need bi-directional changes.

CREATE OR REPLACE TRIGGER trg_test_order_local
AFTER INSERT OR UPDATE OR DELETE ON test_order_local
FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN
  -- Insert, update, or delete the corresponding row in the remote database
  IF INSERTING THEN
    INSERT INTO test_order_remote@test_remote1 (id, desc1, desc2, desc23)
    VALUES (:NEW.id, :NEW.desc1, :NEW.desc2, :NEW.desc23);
  ELSIF UPDATING THEN
    UPDATE test_order_remote@test_remote1
    SET id = :NEW.id, desc1 = :NEW.desc1, desc2 = :NEW.desc2, desc23 = :NEW.desc23
    WHERE id = :OLD.id AND desc1 = :OLD.desc1 AND desc2 = :OLD.desc2 and desc23 = :OLD.desc23; -- Use the unique columns for the WHERE clause
  ELSIF DELETING THEN
    DELETE FROM test_order_remote@test_remote1
    WHERE id = :OLD.id AND desc1 = :OLD.desc1 AND desc2 = :OLD.desc2 and desc23 = :OLD.desc23; -- Use the unique columns for the WHERE clause
  END IF;
  commit;
END;
/

This is my trigger code. If I run in the local database, uni-directional data sync is achieved successfully. But the same code with a db name modification done on the remote database is not working... it's throwing the following error (or sometimes the script runs for a very long time):

error - ORA-12518, TNS:listener could not hand off client connection

I verified as my listener file and TNS are configured same. The Processes and network connection are also good.

How can I resolve this error to achieve bi-direction syncing?

Upvotes: 0

Views: 226

Answers (0)

Related Questions