Oracle高级复制Step by Step

Oracle 高级复制Step by Step

实施起因

某项目中遇到内外网数据库 同步的问题，假设电信机房和某机房网络物理隔绝，需要借助中间服务器进行数据库同步，这个中间服务器既可以是数据库服务器也可以是某种中间件或者某个程序。

因为两头都是Oracle数据库，所以暂时先考虑使用数据库同步的机制来进行预研，当然仅仅是预研而已，距离实施还有一定距离，比如暂时只考虑数据库的单向同步，少量数据的双向同步采用其他方法进行。另外一个目的是为了简化预研的难度，比如省略了发布模板(deployment template)和冲突解决方案(Conflict Resolution)。

    Master site（主站点）           Oracle实例为source

    Materialized view site（中间站点）Oracle实例为mid

Materialized view site Level2（目标站点）Oracle实例为dest

数据流方向为source->mid, mid->dest

    网络连通为source?mid,mid?dest;source<>dest

高级复制中的几个概念

1) replication object：复制对象，指需要作复制的对象（object），包括表，索引，

存储过程等等。复制对象的更新遵循事务一致性规则（transactionally consistent

manner）。

2) replication groups：复制组，是复制对象（replication object）的集合称为group，

oracle 以replication group 的形式来管理 复制。一个组可以包含多个模式的object，

一个模式也可以有多个组中的object，但是每个replication object 都只能属于一个

replication group。

3) replication sites：复制站点，包含两种类型，主体站点（master sites）和实

体化视图站点（materialized view sites）。一个site 可以担任一个replication

group 中的mater site 同时又担任另外一个replication group 中的materialized

view site，注意必须是另外一个组，而不能是同一个replication group。

4) scheduled links：一个数据库链接（database link），包含一个由用户定义的计划，

来将需要更新的事务推到其它的master sites，当创建scheduled link 的时候，oracle

将在本地任务队列中创建一个任务。

5) master definition site：主体定义站点，大部分的高级复制配置都需要在一个站

点上作，这个站点就是maserdef site

创建replication站点

设置master主站点

已连接。

SQL> --在主站点创建复制管理员帐号

SQL> create user repadmin identified by repadmin;

用户已创建。

SQL> --在主站点给该管理员赋予管理员权限

SQL> begin

  2    dbms_repcat_admin.grant_admin_any_schema(

  3      username=>'repadmin');

  4  end;

  5  /

PL/SQL 过程已成功完成。

SQL> --授予相应的权限

SQL> grant comment any table to repadmin;

授权成功。

SQL> grant lock any table to repadmin;

SQL> grant select any dictionary to repadmin;

SQL> --在主站点注册传播用户

  2    dbms_defer_sys.register_propagator(

SQL> --在主站点注册接受用户

  2    dbms_repcat_admin.register_user_repgroup(

  3      username=>'repadmin',

  4      privilege_type='receiver',

  5      list_of_gnames=>NULL);

  6  end;

  7  .

SQL> --设置清除延迟序列的job

  2    dbms_defer_sys.schedule_purge(

  3      next_date=>sysdate,

  4      interval=>'sysdate+1/24',

  5      delay_seconds=>0);

  7  /

SQL> --在主站点创建代理物化视图管理员用户

SQL> create user proxy_mviewadmin identified by proxy_mviewadmin;

  3      username=>'proxy_mviewadmin',

  4      privilege_type=>'proxy_snapadmin',

SQL> grant select_catalog_role to proxy_mviewadmin;

SQL>--创建代理刷新用户

SQL> create user proxy_refresher identified by proxy_refresher;

SQL> grant create session to proxy_refresher;

SQL> grant select any table to proxy_refresher;

SQL> spool off

设置物化视图站点

SQL> --Connect as system at materialize view site at mid

SQL> set echo on

SQL> --在物化视图站点（中间站点）创建物化视图用户

SQL> --创建物化视图管理员

SQL> create user mviewadmin identified by mviewadmin;

  3      username=>'mviewadmin');

SQL> grant comment any table to mviewadmin;

SQL> grant lock any table to mviewadmin;

SQL> grant select any dictionary to mviewadmin;

SQL> --创建传播者帐号

SQL> create user propagator identified by propagator;

  3      username=>'propagator');

SQL> --创建刷新帐号

SQL> create user refresher identified by refresher;

SQL> grant create session to refresher;

SQL> grant alter any materialized view to refresher;

SQL> --把物化视图管理员帐号注册为传播者

  3      username=>'mviewadmin',

  4      privilege_type=>'receiver',

SQL>--创建公共数据库链接到master站点(source站点)

SQL> create public database link source using 'source';

数据库链接已创建。

SQL> create database link source connect to proxy_mviewadmin identified by proxy_mviewadmin;

未选定行

SQL> --create the propagator/receiver database link;

SQL> create database link source connect to repadmin identified by repadmin;

SQL> --Schedule purge at the mid materialized view site

  5      delay_seconds=>0,

  6      rollback_segment=>'');

  7  end;

  8  /

SQL> --Schedule push at the mid materialized view site

  2    dbms_defer_sys.schedule_push(

  3      destination=>'source',

  5      next_date=>sysdate,

  6      stop_on_error=>false,

  7      parallelism=>0);

  8  end;

  9  /

SQL> --create proxy users at the mid materialized view site

SQL> --create proxy materialized view administrator

SQL> --Create the proxy refresher

设置物化视图站点2

SQL> --connect as system at level 2 materialized view site dest

SQL> --create level2 materialized view site users at dest

SQL> --create the propagator

SQL> --create the refresher

SQL> --create database links to master materialized view site

SQL> --create a public database link

SQL> create public database link mid using 'mid';

SQL> create database link mid connect to proxy_mviewadmin identified by proxy_mviewadmin;

SQL> --create a propagator/receiver database link

SQL> create database link mid connect to mviewadmin identified by mviewadmin;

SQL> --Schedule purge at level 2 materialized view site at dest site

SQL> --Schedule push at the dest materialized view site

  3      destination=>'mid',

  7      delay_seconds=>0,

  8      parallelism=>0);

  9  end;

 10  /

创建主站点组

SQL> --create the test schema at source master site

SQL> --...

SQL> --create the master group

  2    dbms_repcat.create_master_repgroup(

  3      gname=>'test_repg');

SQL> --add object to master group

SQL>

SQL> BEGIN

  2  DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

  3  gname => 'test_repg',

  4  type => 'TABLE',

  5  name => 'test',

  6  sname => 'test',

  7  use_existing_object => TRUE,

  8  copy_rows => FALSE);

  9  END;

  5  name => 'dept',

  2  DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (

  3  sname => 'test',

  4  name => 'test',

  5  type => 'TABLE',

  6  min_communication => TRUE);

  7  END;

  4  name => 'dept',

SQL> --start replication

  2    dbms_repcat.resume_master_activity(

创建物化视图组

SQL> create materialized view log on test.test;

实体化视图日志已创建。

SQL> create materialized view log on test.dept;

SQL> CREATE SMALLFILE TABLESPACE "test"

  2  DATAFILE 'E:\OracleDB\DBFILE01.DB' SIZE 200M AUTOEXTEND ON NEXT 200M MAXSIZE 8000M

  3  LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

表空间已创建。

SQL> CREATE USER "TEST" PROFILE "DEFAULT" IDENTIFIED BY "test" DEFAULT TABLESPACE "test" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;

SQL> GRANT "CONNECT" TO "TEST";

SQL> GRANT "DBA" TO "TEST";

SQL> GRANT "DELETE_CATALOG_ROLE" TO "TEST";

SQL> GRANT "EXECUTE_CATALOG_ROLE" TO "TEST";

SQL> GRANT "EXP_FULL_DATABASE" TO "TEST";

SQL> GRANT "GATHER_SYSTEM_STATISTICS" TO "TEST";

SQL> GRANT "IMP_FULL_DATABASE" TO "TEST";

SQL> GRANT "RESOURCE" TO "TEST";

SQL> GRANT "SCHEDULER_ADMIN" TO "TEST";

SQL> GRANT "SELECT_CATALOG_ROLE" TO "TEST";

SQL> GRANT CREATE PROCEDURE TO TEST;

SQL> GRANT CREATE SEQUENCE TO TEST;

SQL> GRANT CREATE VIEW TO TEST;

SQL> GRANT CREATE SYNONYM TO TEST;

SQL> GRANT ALTER SESSION TO TEST;

SQL> GRANT CREATE MATERIALIZED VIEW TO TEST;

SQL> GRANT ALTER ANY MATERIALIZED VIEW TO TEST;

SQL> GRANT CREATE DATABASE LINK TO TEST;

SQL> grant create session to test;

SQL> grant create table to test;

SQL> GRANT CREATE SESSION TO TEST;

SQL> GRANT CREATE TABLE TO TEST;

SQL> create database link source connect to proxy_refresher identified by proxy_refresher;

SQL> --create the materialized view group

  2    dbms_repcat.create_mview_repgroup(

  3      gname=>'test_repg',

  4      master=>'source',

  5      propagation_mode=>'ASYNCHRONOUS');

SQL> --create the refresh group

  2    dbms_refresh.make(

  3      name=>'mviewadmin.test_repg',

  4      list=>'',

  6      interval=>'sysdate+1/24',

  7      implicit_destroy=>false,

  8      rollback_seg=>'',

  9      push_deferred_rpc=>true,

 10      refresh_after_errors=>false);

 11  end;

 12  /

SQL> --add object to the materialized view group

SQL> create materialized view test.test_mv1

  2  refresh fast with primary key for update

实体化视图已创建。

SQL> create materialized view test.dept_mv1

SQL> select * from test.dept_mv1;

ID         Name

---------------------------

1         market

2          finacial

  2    dbms_repcat.create_mview_repobject(

  4      sname=>'test',

  5      name=>'test_mv1',

  6      type=>'SNAPSHOT',

  7      min_communication=>true

  8  );

  5      name=>'dept_mv1',

  7      min_communication=>true);

SQL> --add objects to the refresh group

  2  DBMS_REFRESH.MAKE (

  3  name => 'mviewadmin.test_refg',

  4  list => '',

  5  next_date => SYSDATE,

  6  interval => 'SYSDATE + 1/24',

  7  implicit_destroy => FALSE,

  8  rollback_seg => '',

  9  push_deferred_rpc => TRUE,

 10  refresh_after_errors => FALSE);

 11  END;

  2  DBMS_REFRESH.ADD (

  4  list => 'test.test_mv1',

  5  lax => TRUE);

  6  END;

  4  list => 'test.dept_mv1',

SQL> --create materilized view logs at the master materialized view site

SQL> create materialized view log on test.test_mv1;

SQL> create materialized view log on test.dept_mv1;

SQL> create database link mid connect to proxy_refresher identified by proxy_refresher;

  4      master=>'mid',

SQL> --add objects to the materialized view group

SQL> create materialized view test.test_mv2

SQL> create materialized view test.dept_mv2

SQL> --add the materialized view to the materialized view group

  5      name=>'test_mv2',

  5      name=>'dept_mv2',

  2    dbms_refresh.add(

  3      name=>'mviewadmin.test_refg',

  4      list=>'test.test_mv2',

  5      lax=>true);

  4      list=>'test.dept_mv2',

参考文档：

Oracle?? Database Advanced Replication 10g Release 2 (10.2)

Oracle?? Database Advanced Replication Management API Reference 10g Release 2 (10.2)

关于Advanced Replication 的初步研究

本文转自baoqiangwang51CTO博客，原文链接：http://blog.51cto.com/baoqiangwang/312737，如需转载请自行联系原作者