<< Oracle高可用>>部分书面作业 - 第十三课 Oracle高级复制

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--