Data Synchronization steps for remote Oracle databases

The main purpose of Data Synchronization in A remote Oracle database is to change the TBL_TB data in table A of the Oracle database of server, the table TBL_TB in the Oracle database B of server B also changes accordingly. (assume that the two tables have the same structure and contain only the ID and NAME columns ).

Procedure:

1) create a connection configuration: Add a connection to server B in network/admin/tnsnames. ora under the Oracle database installation directory of server:

 
 

  
  
MASTER2 =  
  
  
(DESCRIPTION =  
  
  
(ADDRESS_LIST =  
  
  
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))  
  
  
)  
  
  
(CONNECT_DATA =  
  
  
(SERVICE_NAME = master2)  
  
  
)  
  
  
)  
 
 

Assume that the IP address of a is 192.168.0.1, the IP address of B is 192.168.0.2, And the Oracle Database Service name of B is master2, and the port number of B is 1521.

2) create a DBLINK:

 
 

  
  
create public database link LINKMASTER2.Oracle.COM 
 
 

Connect to user name -- the user name used to connect to Oracle Database in B. We should fill in B this time.

Identified by "password" -- password (do not remove double quotation marks)

 
 

  
  
using 'MASTER2'; 
 
 

3) create a synonym: Create a synonym for convenience.

 
 

  
  
create or replace synonym TESTLINK  
  
  
for B.TBL_TB@LINKMASTER2.Oracle.COM;  
 
 

4) create a trigger:

Create A trigger under TBL_TB under database space A in server A, which is responsible for insertion and synchronization. The trigger's main part is as follows:

 
 

  
  
begin  
  
  
insert into TESTLINK (id,name) values (:new.id,:new.name);  
  
  
end;  
 
 

Create A trigger under TBL_TB under Oracle database space A in server A to modify and synchronize the trigger:

 
 

  
  
begin  
  
  
update TESTLINK set id=:new.id,name=:new.name where id= :old.id;  
  
  
end;  
 
 

Assume that the ID column is unique.

Action summary report:

2nd) and 3) Steps should be SQL statements executed in the environment of Data User A of server.

After creating a synonym, you can directly query the table content of a remote database: select * from TESTLINK;

Note that the firewall of server B must be disabled. Otherwise, a timeout error will be reported during connection establishment.