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; --冲突的状态
