1.说明高级复制和流复制的在机制和应用场景上的异同点。
不同点: 高级复制基于内部触发器, 流复制基于日志挖掘
相同点: 都只能用于Oracle对Oracle的复制
2.演示针对某个表使用高级复制进行数据同步。
复制方向:strm1 -> strm2
两端准备环境
conn / as sysdba
alter system set global_names=true;
alter database rename global_name to strm1.com;
alter system set global_names=true;
alter database rename global_name to strm2.com;
创建复制用户 (BOTH)
conn / as sysdba;
create user repadmin identified by repadmin;
alter user repadmin default tablespace users;
alter user repadmin temporary tablespace temp;
grant connect ,resource to repadmin;
execute dbms_repcat_admin.grant_admin_any_schema('REPADMIN');
grant comment any table to repadmin;
execute dbms_defer_sys.register_propagator('REPADMIN');
grant execute any procedure to REPADMIN;
create user gp identified by gp;
grant dba to gp;
conn gp/gp@strm1
create table t(x int primary key);
conn gp/gp@strm2
create table t(x int primary key);
2:连接复制用户
创建到目标端的dblink
conn repadmin/repadmin@strm1;
create database link strm2.com connect to repadmin identified by repadmin using 'strm2';
select name from v$database@strm2.com;
conn repadmin/repadmin@strm2;
create database link strm1.com connect to repadmin identified by repadmin using 'strm1';
select name from v$database@strm1.com;
3:创建复制组
conn repadmin/repadmin@strm1;
execute dbms_repcat.create_master_repgroup('rep');
select gname ,master ,status from dba_repgroup where gname = 'REP';
execute dbms_repcat.drop_master_repgroup('rep');
conn repadmin/repadmin@strm2;
--execute dbms_repcat.create_master_repgroup('rep');
select gname ,master ,status from dba_repgroup where gname = 'REP';
4:加入复制对象
conn repadmin/repadmin@strm1;
execute dbms_repcat.create_master_repobject(sname=>'gp' ,oname=>'t' ,type=>'table' ,use_existing_object=>true ,gname=>'rep' ,copy_rows=>false);
select sname,oname,status,gname from DBA_REPOBJECT;
conn repadmin/repadmin@strm2;
--execute dbms_repcat.create_master_repobject(sname=>'gp' ,oname=>'t' ,type=>'table' ,use_existing_object=>true ,gname=>'rep' ,copy_rows=>false);
5:对复制对象启动复制支持
conn repadmin/repadmin@strm1;
execute dbms_repcat.generate_replication_support('gp' ,'t' ,'table');
select sname,status,gname from dba_repgroup where gname = 'REP';
conn repadmin/repadmin@strm2;
--execute dbms_repcat.generate_replication_support('gp' ,'t' ,'table');
select sname,status,gname from dba_repgroup where gname = 'REP';
6:添加复制节点
conn repadmin/repadmin@strm1;
execute dbms_repcat.add_master_database(gname=>'rep' ,master=>'strm2.com' ,use_existing_objects=>true ,copy_rows=>false ,propagation_mode=>'synchronous');
select gname,dblink,masterdef,master from dba_repsites where gname= 'REP';
在两端查询
column masterdef format a10
column master format a10
column dblink format a25
column gname format a12
select gname ,dblink ,masterdef ,master from dba_repsites where gname='REP';
7:启动复制
conn repadmin/repadmin@strm1;
execute dbms_repcat.resume_master_activity('rep' ,true);
execute dbms_repcat.suspend_master_activity('rep');
8.验证
conn gp/gp@strm1;
insert into t values(1);
commit;
conn gp/gp@strm2;
select * from t;
参考资料:
http://www.eygle.com/archives/2005/06/oraclessoeaeaeo.html
--EOF--
3.演示使用物化视图的方式进行数据同步。
刷新方向:strm1 -> strm2
建立源表:
conn repadmin/repadmin@strm1@strm1
CREATE TABLE repadmin.t(id int primary key);
建日志:
conn repadmin/repadmin@strm1
CREATE MATERIALIZED VIEW LOG ON repadmin.T WITH PRIMARY KEY;
建物化视图:
conn repadmin/repadmin@strm2
CREATE MATERIALIZED VIEW repadmin.mw_t as select * from repadmin.t@strm1;
建立物化视图组,因为刷新只能刷新组:
conn repadmin/repadmin@strm2
BEGIN
DBMS_REFRESH.MAKE (
name => 'repadmin.mv_grp',
list => '',
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
implicit_destroy => FALSE,
rollback_seg => '',
push_deferred_rpc => TRUE,
refresh_after_errors => FALSE);
END;
把物化视图加入组:
conn repadmin/repadmin@strm2
BEGIN
DBMS_REFRESH.ADD (
name => 'repadmin.mv_grp',
list => 'repadmin.mw_t',
lax => TRUE);
END;
测试:
conn repadmin/repadmin@strm1
insert into t values(1);
commit;
conn repadmin/repadmin@strm2
SELECT * FROM repadmin.mw_t;
execute dbms_refresh.refresh('repadmin.mv_grp');
SELECT * FROM repadmin.mw_t;
参考资料:
http://docs.oracle.com/cd/B19306_01/server.102/b14227/rarmviewgroup.htm#CHDCICJC
--EOF--