通过select * from 来测试
4.双向复制环境建立(同步)
masterdef节点(ORCL)建立复制组
SQL> exec dbms_repcat.create_master_repgroup('sync_rg');
建立复制组内的复制对象
SQL> exec dbms_repcat.create_master_repobject(sname =>'test',oname =>'am_cnt',type =>'table',use_existing_object =>true,gname =>'sync_rg',copy_rows =>true);
为对象建立内置package和trigger
SQL> exec dbms_repcat.generate_replication_support('test','am_cnt','table');
添加master对象(GC)
SQL> exec dbms_repcat.add_master_database(gname =>'sync_rg',master =>'gc.lab.com',use_existing_objects =>true,copy_rows =>true,propagation_mode =>'synchronous');
use_existing_objects:Indicate true if you want to reuse any objects of the same type and shape that already exist in the schema at the new master site.
copy_rows:Indicate true if you want the initial contents of a table at the new master site to match the contents of the table at the master definition site.
这样就可以通过复制把源上的表和数据同步过去
激活repgroup
SQL> exec dbms_repcat.resume_master_activity('sync_rg',true);
查看复制组状态
SQL> select sname,status from dba_repgroup;
SNAME STATUS
------------------------------ ---------
SYNC_RG NORMAL
SQL> select sname,status from dba_repgroup;
SNAME STATUS
------------------------------ ---------
SYNC_RG NORMAL
查看管理日志
select * from dba_repcatlog;
状态为ready,exec dbms_repcat.do_deferred_repcat_admin('"SYNC_RG"', FALSE);来执行请求
状态为await_callback,等待其他master执行并返回结果
状态为error,请求执行错误
状态为do_callback,出现在master上，表示要通知masterdef执行结果
test.am_cnt表结构
SQL> desc test.am_cnt;
Name Null? Type
----------------------------------------- -------- ----------------------------
DJLX NOT NULL VARCHAR2(4)
XDJLX NOT NULL VARCHAR2(4)
QSRQ VARCHAR2(8)
JZRQ VARCHAR2(8)
OPRID VARCHAR2(8)
ADTID VARCHAR2(8)
RQ VARCHAR2(8)
HTH NOT NULL VARCHAR2(6)
SUPID VARCHAR2(4)
CORPID VARCHAR2(2)
YRSTRID VARCHAR2(10)
CHARACTER VARCHAR2(1)
KIND VARCHAR2(1)
LEGALMAN VARCHAR2(8)
JYFS VARCHAR2(1)
GLFS VARCHAR2(1)
DELIVERYTYPE VARCHAR2(1)
DELIVERYADDRESS VARCHAR2(1)
REACHDAYS NUMBER(5)
ORDERTIMES NUMBER(5)
PAYTYPE VARCHAR2(1)
PAYDAYS NUMBER(5)
BILLTYPE VARCHAR2(1)
TAX_I NUMBER(5,4)
TAX_S NUMBER(5,4)
SIGNDATE VARCHAR2(8)
STARTDATE VARCHAR2(8)
ENDDATE VARCHAR2(8)
AREA FLOAT(126)
MYSIGNATORY VARCHAR2(8)
BM VARCHAR2(10)
SIGNATORY VARCHAR2(8)
LINKMAN VARCHAR2(8)
TEL VARCHAR2(36)
BP VARCHAR2(36)
MOBILETEL VARCHAR2(36)
FAX VARCHAR2(36)
ADDR VARCHAR2(60)
ZIPCODE VARCHAR2(12)
TAXNO VARCHAR2(36)
ACCOUNT VARCHAR2(36)
BANK VARCHAR2(36)
WSXKZH VARCHAR2(36)
SHSXF FLOAT(126)
YYZZH VARCHAR2(36)
KHF FLOAT(126)
ZXSPTH VARCHAR2(1)
BZHSPTH VARCHAR2(1)
JJXSP VARCHAR2(1)
SXQ NUMBER(5)
XYKJS VARCHAR2(1)
YJKCFF VARCHAR2(1)
JS VARCHAR2(1)
JSRQ1 NUMBER(5)
JSRQ2 NUMBER(5)
JSRQ3 NUMBER(5)
JSRQSET VARCHAR2(1)
ZCKL FLOAT(126)
CEKL FLOAT(126)
CEKL2 FLOAT(126)
BDXSJE FLOAT(126)
JHXSJE FLOAT(126)
VIPBL FLOAT(126)
JLBL FLOAT(126)
ZRJBL FLOAT(126)
FLG_ORD VARCHAR2(1)
FLG_JS VARCHAR2(1)
FLG_OUT VARCHAR2(1)
AREA_HT VARCHAR2(4)
LSLX VARCHAR2(1)
SPLY VARCHAR2(1)
CGY VARCHAR2(8)
NUM1 FLOAT(126)
NUM2 FLOAT(126)
STR1 VARCHAR2(100)
STR2 VARCHAR2(100)
DHMEMO VARCHAR2(200)
NOTES VARCHAR2(100)
REFUNDTYPE VARCHAR2(1)
REFUNDRATIO FLOAT(126)
PREPAYMENTRATIO FLOAT(126)
ARRIVALWAY VARCHAR2(1)
测试
SQL> delete from test.am_cnt where yrstrid='asdf';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from test.am_cnt;
COUNT(*)
----------
20
SQL> select count(*) from ;
COUNT(*)
----------
20
5.添加与减少复制对象(masterdef)
停止repobject
SQL> exec dbms_repcat.suspend_master_activity(gname =>'sync_rg');
添加复制对象
SQL> exec dbms_repcat.create_master_repobject(sname =>'test',oname =>'am_cntgds',type =>'table',use_existing_object =>true,gname =>'sync_rg',copy_rows =>true);
增加复制支持trigger和package
SQL> exec dbms_repcat.generate_replication_support('test','am_cntgds','table');
激活repgroup
SQL> exec dbms_repcat.resume_master_activity('sync_rg',true);
删除复制对象
dbms_repcat.drop_master_repobject和dbms_repcat.drop_repgroup
6.双向同步复制(异步)
在masterdef节点添加master时指定propagation_mode='asynchronous';
SQL> exec dbms_repcat.add_master_database(gname =>'sync_rg',master =>'gc.lab.com',use_existing_objects =>true,copy_rows =>true,propagation_mode =>'asynchronous');
异步复制需要job支持使得定期同步复制(所有节点)
ORCL
begin
dbms_defer_sys.schedule_push (destination => 'gc.lab.com',
interval => 'sysdate + 10/1440',
next_date => sysdate);
end;
/ --10分钟1次push数据到gc.lab.com
begin
dbms_defer_sys.schedule_purge (next_date => sysdate,
interval => 'sysdate + 10/1440',
delay_seconds => 0,
);
end;
/ --10分钟1次清除延迟队列的job
GC
begin
dbms_defer_sys.schedule_push (destination => 'orcl.lab.com',
interval => 'sysdate + 10/1440',
next_date => sysdate);
end;
/ --10分钟1次push数据到orcl.lab.com
begin
dbms_defer_sys.schedule_purge (next_date => sysdate,
interval => 'sysdate + 10/1440',
delay_seconds => 0,
);
end;
/ --10分钟1次清除延迟队列的job
7.维护
masterdef节点因故障切换
GC
masterdef节点交换
SQL> exec dbms_repcat.relocate_masterdef(gname =>'sync_rg',old_masterdef =>'orcl.lab.com',new_masterdef =>'gc.lab.com',notify_masters =>true,include_old_masterdef =>false);
SQL> exec dbms_repcat.suspend_master_activity('sync_rg');
删除对端master节点
SQL> exec dbms_repcat.remove_master_databases(gname =>'sync_rg',master_list =>'orcl.lab.com');
当ORCL恢复后
SQL> exec dbms_repcat.drop_master_repgroup('sync_rg');
GC
exec dbms_repcat.add_master_database(gname =>'sync_rg',master =>'orcl.lab.com',use_existing_objects =>true,copy_rows =>true,propagation_mode =>'synchronous');
8.关于procedure和view的复制
procedure和view只是简单同步，而不需要生成双向复制，如
SQL> exec dbms_repcat.create_master_repobject(sname =>'test',oname =>'am_cntgds_vw',type =>'view',use_existing_object =>true,gname =>'sync_rg',copy_rows =>true);
SQL> exec dbms_repcat.create_master_repobject(sname =>'test',oname =>'proced',type =>'procedure',use_existing_object =>true,gname =>'sync_rg',copy_rows =>true);
不需要dbms_repcat.generate_replication_support
