Master Replication (二)

master replication双向复制表数据不同步的解决

 

masterdef(GC)

 

SQL> select count(*) from test.am_cntgds;

  COUNT(*)
----------
         3

 

master(ORCL)

 

SQL> select count(*) from test.am_cntgds;

  COUNT(*)
----------
         0

 

DBMS_RECTIFIER_DIFF

 

The DBMS_RECTIFIER_DIFF package contains APIs used to detect and resolve data inconsistencies between two replicated sites.

 

masterdef(GC)

 

建立存放差异数据的missing_rows表

 

SQL> create table test.missing_rows as select * from test.am_cntgds where 1=0;

 

建立存放差异rowid的missing_location表

 

SQL> create table test.missing_location(present varchar(128),absent varchar(128),r_id rowid);

 

比较产生差异数据

 

SQL> exec dbms_rectifier_diff.differences(sname1 =>'test',oname1 =>'am_cntgds',reference_site =>'gc.lab.com',sname2 =>'test',oname2 =>'am_cntgds',comparison_site =>'orcl.lab.com',missing_rows_sname =>'test',missing_rows_oname1 =>'missing_rows',missing_rows_oname2 =>'missing_location',missing_rows_site =>'gc.lab.com',max_missing =>500,commit_rows =>100);

 

SQL> select count(*) from test.missing_rows;

  COUNT(*)
----------
         3

 

使用dbms_rectifier_diff.rectify进行数据整合

 

第一个表存在的数据，在第二个表不存在的，将会被插入到第二个表

 

第二个表存在的数据，在第一个表不存在的，将会在第二个表删除

 

SQL> exec dbms_rectifier_diff.rectify(sname1 =>'test',oname1 =>'am_cntgds',reference_site =>'gc.lab.com',sname2 =>'test',oname2 =>'am_cntgds',comparison_site =>'orcl.lab.com',missing_rows_sname =>'test',missing_rows_oname1 =>'missing_rows',missing_rows_oname2 =>'missing_location',missing_rows_site =>'gc.lab.com',commit_rows =>100);

 

SQL> select count(*) from test.missing_rows;

  COUNT(*)
----------
         0