master replication冲突规则(Update,Uniqueness)
时间：2010-06-29 来源：rushmeplz
1.master replication 异步复制时同时update的冲突规则(dbms_repcat.add_update_resolution)
overwrite and discard
极少用，除了认为规定某一站点的数据绝对正确，发生冲突时overwrite或者discard其他站点的数据
Minimum and Maximum
根据parameter_column_name的值来决定应该应用哪个站点的column_group的值
Timestamp
为复制表增加一个timestamp的column，根据时间戳来解决冲突，需要站点间的时间同步
Priority Groups
为某一特定column分配一个优先级，发生冲突时根据优先级来解决冲突
Site Priority
为某一站点分配一个优先级，发生冲突时高优先级的站点数据将覆盖低优先级的站点数据
测试
Minimum and Maximum
表结构
CREATE TABLE TEST.USER_STATE
(
ID NUMBER,
NUM NUMBER,
STATE VARCHAR2(20 BYTE)
)
TABLESPACE USERS ALTER TABLE TEST.USER_STATE ADD (
CONSTRAINT USER_STATES_PK
PRIMARY KEY
(ID) 建立column_group exec dbms_repcat.make_column_group(sname =>'test',oname =>'user_state',column_group =>'cg',list_of_column_names =>'id,num,state'); 应用冲突规则 exec dbms_repcat.add_update_resolution(sname =>'test',oname =>'user_state',column_group =>'cg',sequence_no =>1,method =>'MAXIMUM',parameter_column_name =>'num'); 复制更新到其他的站点 exec dbms_repcat.generate_replication_support('test','user_state','table'); 激活复制 exec dbms_repcat.resume_master_activity('async_rg'); 注册冲突检测的表状态 exec dbms_repcat.register_statistics(sname =>'test',oname =>'user_state'); ORCL SQL> update test.user_state set num=100 where id=20; GC SQL> update test.user_state set num=80 where id=20; ORCL commit; GC commit; ORCL exec dbms_job.run(141); SQL> select * from test.user_state where id=20; ID NUM STATE
---------- ---------- --------------------
20 100 normal GC SQL> select * from test.user_state where id=20; ID NUM STATE
---------- ---------- --------------------
20 100 normal select * from dba_represolution_statistics查看冲突发生的时间和解决方式 TimeStamp 为表增加timestamp的column exec dbms_repcat.alter_master_repobject(sname =>'test',oname =>'user_state',type =>'table',ddl_text =>'ALTER TABLE TEST.USER_STATE ADD (time DATE)'); 应用复制到其他站点 exec dbms_repcat.generate_replication_support('test','user_state','table'); 调整column_group和dbms_repcat.add_update_resolution exec dbms_repcat.drop_update_resolution(sname =>'test',oname =>'user_state',column_group =>'cg',sequence_no =>1);
exec dbms_repcat.drop_column_group(sname =>'test',oname =>'user_state',column_group =>'cg'); exec dbms_repcat.make_column_group(sname =>'test',oname =>'user_state',column_group =>'cg',list_of_column_names =>'id,num,state,time');
exec dbms_repcat.add_update_resolution(sname =>'test',oname =>'user_state',column_group =>'cg',sequence_no =>1,method =>'MAXIMUM',parameter_column_name =>'time'); 应用复制到其他站点 exec dbms_repcat.generate_replication_support('test','user_state','table'); 激活复制组 exec dbms_repcat.resume_master_activity('async_rg'); ORCL SQL> update test.user_state set num=100,time=sysdate where id=20; 1 row updated. SQL> commit; Commit complete. GC SQL> update test.user_state set num=80,time=sysdate where id=20; 1 row updated. SQL> commit; Commit complete. ORCL exec dbms_job.run(141); SQL> select * from test.user_state; ID NUM STATE TIME
---------- ---------- -------------------- -------------------
20 100 normal 2010-06-29 23:10:55 GC SQL> select * from test.user_state; ID NUM STATE TIME
---------- ---------- -------------------- -------------------
20 100 normal 2010-06-29 23:10:55 select * from dba_represolution_statistics; --查看冲突状态 Uniqueness 增加uniqueness冲突规则 exec dbms_repcat.ADD_UNIQUE_RESOLUTION(sname =>'test',oname =>'user_state',constraint_name =>'user_states_pk',sequence_no =>1,method =>'discard',parameter_column_name =>'num'); 应用复制到其他站点 exec dbms_repcat.generate_replication_support('test','user_state','table'); 激活复制组 exec dbms_repcat.resume_master_activity('async_rg'); 查看现有冲突规则 select * from dba_represolution; ORCL SQL> insert into test.user_state values(100,100,'normal',sysdate); 1 row created. SQL> commit; Commit complete. GC SQL> insert into test.user_state values(100,20,'normal',sysdate); 1 row created. SQL> commit; Commit complete. ORCL exec dbms_job.run(141); SQL> select * from test.user_state where id=100; ID NUM STATE TIME
---------- ---------- -------------------- -------------------
100 100 normal 2010-06-29 23:29:44 GC SQL> select * from test.user_state where id=100; ID NUM STATE TIME
---------- ---------- -------------------- -------------------
100 20 normal 2010-06-29 23:15:05 select * from dba_represolution; --表上现有的冲突规则 select * from dba_represolution_statistics; --冲突的状态
(
ID NUMBER,
NUM NUMBER,
STATE VARCHAR2(20 BYTE)
)
TABLESPACE USERS ALTER TABLE TEST.USER_STATE ADD (
CONSTRAINT USER_STATES_PK
PRIMARY KEY
(ID) 建立column_group exec dbms_repcat.make_column_group(sname =>'test',oname =>'user_state',column_group =>'cg',list_of_column_names =>'id,num,state'); 应用冲突规则 exec dbms_repcat.add_update_resolution(sname =>'test',oname =>'user_state',column_group =>'cg',sequence_no =>1,method =>'MAXIMUM',parameter_column_name =>'num'); 复制更新到其他的站点 exec dbms_repcat.generate_replication_support('test','user_state','table'); 激活复制 exec dbms_repcat.resume_master_activity('async_rg'); 注册冲突检测的表状态 exec dbms_repcat.register_statistics(sname =>'test',oname =>'user_state'); ORCL SQL> update test.user_state set num=100 where id=20; GC SQL> update test.user_state set num=80 where id=20; ORCL commit; GC commit; ORCL exec dbms_job.run(141); SQL> select * from test.user_state where id=20; ID NUM STATE
---------- ---------- --------------------
20 100 normal GC SQL> select * from test.user_state where id=20; ID NUM STATE
---------- ---------- --------------------
20 100 normal select * from dba_represolution_statistics查看冲突发生的时间和解决方式 TimeStamp 为表增加timestamp的column exec dbms_repcat.alter_master_repobject(sname =>'test',oname =>'user_state',type =>'table',ddl_text =>'ALTER TABLE TEST.USER_STATE ADD (time DATE)'); 应用复制到其他站点 exec dbms_repcat.generate_replication_support('test','user_state','table'); 调整column_group和dbms_repcat.add_update_resolution exec dbms_repcat.drop_update_resolution(sname =>'test',oname =>'user_state',column_group =>'cg',sequence_no =>1);
exec dbms_repcat.drop_column_group(sname =>'test',oname =>'user_state',column_group =>'cg'); exec dbms_repcat.make_column_group(sname =>'test',oname =>'user_state',column_group =>'cg',list_of_column_names =>'id,num,state,time');
exec dbms_repcat.add_update_resolution(sname =>'test',oname =>'user_state',column_group =>'cg',sequence_no =>1,method =>'MAXIMUM',parameter_column_name =>'time'); 应用复制到其他站点 exec dbms_repcat.generate_replication_support('test','user_state','table'); 激活复制组 exec dbms_repcat.resume_master_activity('async_rg'); ORCL SQL> update test.user_state set num=100,time=sysdate where id=20; 1 row updated. SQL> commit; Commit complete. GC SQL> update test.user_state set num=80,time=sysdate where id=20; 1 row updated. SQL> commit; Commit complete. ORCL exec dbms_job.run(141); SQL> select * from test.user_state; ID NUM STATE TIME
---------- ---------- -------------------- -------------------
20 100 normal 2010-06-29 23:10:55 GC SQL> select * from test.user_state; ID NUM STATE TIME
---------- ---------- -------------------- -------------------
20 100 normal 2010-06-29 23:10:55 select * from dba_represolution_statistics; --查看冲突状态 Uniqueness 增加uniqueness冲突规则 exec dbms_repcat.ADD_UNIQUE_RESOLUTION(sname =>'test',oname =>'user_state',constraint_name =>'user_states_pk',sequence_no =>1,method =>'discard',parameter_column_name =>'num'); 应用复制到其他站点 exec dbms_repcat.generate_replication_support('test','user_state','table'); 激活复制组 exec dbms_repcat.resume_master_activity('async_rg'); 查看现有冲突规则 select * from dba_represolution; ORCL SQL> insert into test.user_state values(100,100,'normal',sysdate); 1 row created. SQL> commit; Commit complete. GC SQL> insert into test.user_state values(100,20,'normal',sysdate); 1 row created. SQL> commit; Commit complete. ORCL exec dbms_job.run(141); SQL> select * from test.user_state where id=100; ID NUM STATE TIME
---------- ---------- -------------------- -------------------
100 100 normal 2010-06-29 23:29:44 GC SQL> select * from test.user_state where id=100; ID NUM STATE TIME
---------- ---------- -------------------- -------------------
100 20 normal 2010-06-29 23:15:05 select * from dba_represolution; --表上现有的冲突规则 select * from dba_represolution_statistics; --冲突的状态
相关阅读 更多 +
