Oracle高级复制Step by Step

梦竹2938

　　原创于2008年12月26日，2009年10月19日迁移至此。

　　

　　Oracle高级复制Step by Step
实施起因
某项目中遇到内外网数据库同步的问题，假设电信机房和某机房网络物理隔绝，需要借助中间服务器进行数据库同步，这个中间服务器既可以是数据库服务器也可以是某种中间件或者某个程序。
因为两头都是Oracle数据库，所以暂时先考虑使用数据库同步的机制来进行预研，当然仅仅是预研而已，距离实施还有一定距离，比如暂时只考虑数据库的单向同步，少量数据的双向同步采用其他方法进行。另外一个目的是为了简化预研的难度，比如省略了发布模板(deploymenttemplate)和冲突解决方案(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> connect system/inxite@source
已连接。
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> --在主站点注册传播用户
SQL> begin
2 dbms_defer_sys.register_propagator(
3 username=>'repadmin');
4 end;
5 /
PL/SQL 过程已成功完成。
SQL> --在主站点注册接受用户
SQL> begin
2 dbms_repcat_admin.register_user_repgroup(
3 username=>'repadmin',
4 privilege_type='receiver',
5 list_of_gnames=>NULL);
6 end;
7 .
PL/SQL 过程已成功完成。
SQL> --设置清除延迟序列的job
SQL> begin
2 dbms_defer_sys.schedule_purge(
3 next_date=>sysdate,
4 interval=>'sysdate+1/24',
5 delay_seconds=>0);
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> --在主站点创建代理物化视图管理员用户
SQL> connect system/inxite@source
已连接。
SQL> create user proxy_mviewadmin identified by proxy_mviewadmin;
用户已创建。
SQL> begin
2 dbms_repcat_admin.register_user_repgroup(
3 username=>'proxy_mviewadmin',
4 privilege_type=>'proxy_snapadmin',
5 list_of_gnames=>NULL);
6 end;
7 /
PL/SQL 过程已成功完成。
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> conn system/inxite@mid
已连接。
SQL> --在物化视图站点（中间站点）创建物化视图用户
SQL> --创建物化视图管理员
SQL> create user mviewadmin identified by mviewadmin;
用户已创建。
SQL> begin
2 dbms_repcat_admin.grant_admin_any_schema(
3 username=>'mviewadmin');
4 end;
5 /
PL/SQL 过程已成功完成。
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;
用户已创建。
SQL> begin
2 dbms_defer_sys.register_propagator(
3 username=>'propagator');
4 end;
5 /
PL/SQL 过程已成功完成。
SQL> --创建刷新帐号
SQL> create user refresher identified by refresher;
用户已创建。
SQL> grant create session to refresher;
授权成功。
SQL> grant alter any materialized view to refresher;
授权成功。
SQL> --把物化视图管理员帐号注册为传播者
SQL> begin
2 dbms_repcat_admin.register_user_repgroup(
3 username=>'mviewadmin',
4 privilege_type=>'receiver',
5 list_of_gnames=>NULL);
6 end;
7 /
PL/SQL 过程已成功完成。
SQL>--创建公共数据库链接到master站点(source站点)
SQL> connect system/inxite@mid
已连接。
SQL> create public database link source using 'source';
数据库链接已创建。
SQL> conn mviewadmin/mviewadmin@mid
已连接。
SQL> create database link source connect to proxy_mviewadmin identified by proxy_mviewadmin;
数据库链接已创建。
SQL> select * from tab@source;
未选定行
SQL> --create the propagator/receiver database link;
SQL> connect propagator/propagator@mid;
已连接。
SQL> create database link source connect to repadmin identified by repadmin;
数据库链接已创建。
SQL> --Schedule purge at the mid materialized view site
SQL> conn mviewadmin/mviewadmin@mid
已连接。
SQL> begin
2 dbms_defer_sys.schedule_purge(
3 next_date=>sysdate,
4 interval=>'sysdate+1/24',
5 delay_seconds=>0,
6 rollback_segment=>'');
7 end;
8 /
PL/SQL 过程已成功完成。
SQL> --Schedule push at the mid materialized view site
SQL> conn mviewadmin/mviewadmin@mid
已连接。
SQL> begin
2 dbms_defer_sys.schedule_push(
3 destination=>'source',
4 interval=>'sysdate+1/24',
5 next_date=>sysdate,
6 stop_on_error=>false,
7 parallelism=>0);
8 end;
9 /
PL/SQL 过程已成功完成。
SQL> --create proxy users at the mid materialized view site
SQL> --create proxy materialized view administrator
SQL> connect system/inxite@mid
已连接。
SQL> create user proxy_mviewadmin identified by proxy_mviewadmin;
用户已创建。
SQL> begin
2 dbms_repcat_admin.register_user_repgroup(
3 username=>'proxy_mviewadmin',
4 privilege_type=>'proxy_snapadmin',
5 list_of_gnames=>NULL);
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> grant select_catalog_role to proxy_mviewadmin;
授权成功。
SQL> --Create the proxy refresher
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

设置物化视图站点2
SQL> --connect as system at level 2 materialized view site dest
SQL> connect system/inxite@dest
已连接。
SQL> --create level2 materialized view site users at dest
SQL> create user mviewadmin identified by mviewadmin;
用户已创建。

SQL> begin
2 dbms_repcat_admin.grant_admin_any_schema(
3 username=>'mviewadmin');
4 end;
5 /
PL/SQL 过程已成功完成。
SQL> grant select any dictionary to mviewadmin;
授权成功。
SQL> --create the propagator
SQL> create user propagator identified by propagator;
用户已创建。
SQL> begin
2 dbms_defer_sys.register_propagator(
3 username=>'propagator');
4 end;
5 /
PL/SQL 过程已成功完成。
SQL> --create the refresher
SQL> create user refresher identified by refresher;
用户已创建。
SQL> grant create session to refresher;
授权成功。
SQL> grant alter any materialized view to refresher;
授权成功。
SQL> --create database links to master materialized view site
SQL> --create a public database link
SQL> connect system/inxite@dest
已连接。
SQL> create public database link mid using 'mid';
数据库链接已创建。
SQL> connect mviewadmin/mviewadmin@dest
已连接。
SQL> create database link mid connect to proxy_mviewadmin identified by proxy_mviewadmin;
数据库链接已创建。
SQL> select * from tab@mid;
未选定行
SQL> --create a propagator/receiver database link
SQL> connect propagator/propagator@dest
已连接。
SQL> create database link mid connect to mviewadmin identified by mviewadmin;
数据库链接已创建。
SQL> select * from tab@mid;
未选定行
SQL> --Schedule purge at level 2 materialized view site at dest site
SQL> conn mviewadmin/mviewadmin@dest
已连接。
SQL> begin
2 dbms_defer_sys.schedule_purge(
3 next_date=>sysdate,
4 interval=>'sysdate+1/24',
5 delay_seconds=>0,
6 rollback_segment=>'');
7 end;
8 /
PL/SQL 过程已成功完成。
SQL> --Schedule push at the dest materialized view site
SQL> conn mviewadmin/mviewadmin@dest;
已连接。
SQL> begin
2 dbms_defer_sys.schedule_push(
3 destination=>'mid',
4 interval=>'sysdate+1/24',
5 next_date=>sysdate,
6 stop_on_error=>false,
7 delay_seconds=>0,
8 parallelism=>0);
9 end;
10 /
PL/SQL 过程已成功完成。
SQL> spool off


创建主站点组
SQL> connect repadmin/repadmin@source;
已连接。
SQL> --create the test schema at source master site
SQL> --...
SQL> --create the master group
SQL> begin
2 dbms_repcat.create_master_repgroup(
3 gname=>'test_repg');
4 end;
5 /
PL/SQL 过程已成功完成。
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;
10 /
PL/SQL 过程已成功完成。
SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'test_repg',
4 type => 'TABLE',
5 name => 'dept',
6 sname => 'test',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /
PL/SQL 过程已成功完成。
SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'test',
4 name => 'test',
5 type => 'TABLE',
6 min_communication => TRUE);
7 END;
8 /
PL/SQL 过程已成功完成。
SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'test',
4 name => 'dept',
5 type => 'TABLE',
6 min_communication => TRUE);
7 END;
8 /
PL/SQL 过程已成功完成。
SQL> --start replication
SQL> begin
2 dbms_repcat.resume_master_activity(
3 gname=>'test_repg');
4 end;
5 /
PL/SQL 过程已成功完成。
SQL> spool off

创建物化视图组

SQL> conn test/test@source;
已连接。
SQL> create materialized view log on test.test;
实体化视图日志已创建。
SQL> create materialized view log on test.dept;
实体化视图日志已创建。
SQL> CONN system/inxite@mid;
已连接。
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" DEFAULTTABLESPACE "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> conn system/inxite@dest
已连接。
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" DEFAULTTABLESPACE "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 SESSION TO TEST;
授权成功。
SQL> GRANT CREATE TABLE 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> connect test/test@mid;
已连接。
SQL> create database link source connect to proxy_refresher identified by proxy_refresher;
数据库链接已创建。
SQL> select * from tab@source;
未选定行
SQL> --create the materialized view group
SQL> connect mviewadmin/mviewadmin@mid;
已连接。
SQL> begin
2 dbms_repcat.create_mview_repgroup(
3 gname=>'test_repg',
4 master=>'source',
5 propagation_mode=>'ASYNCHRONOUS');
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> --create the refresh group
SQL> begin
2 dbms_refresh.make(
3 name=>'mviewadmin.test_repg',
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;
12 /

PL/SQL 过程已成功完成。

SQL> --add object to the materialized view group
SQL> create materialized view test.test_mv1
2 refresh fast with primary key for update
3 as select * from test.test@source;
实体化视图已创建。
SQL> create materialized view test.dept_mv1
2 refresh fast with primary key for update
3 as select * from test.dept@source;
实体化视图已创建。
SQL> select * from test.dept_mv1;
ID Name
---------------------------
1 market
2  finacial
SQL> begin
2 dbms_repcat.create_mview_repobject(
3 gname=>'test_repg',
4 sname=>'test',
5 name=>'test_mv1',
6 type=>'SNAPSHOT',
7 min_communication=>true
8 );
9 end;
10 /
PL/SQL 过程已成功完成。
SQL> begin
2 dbms_repcat.create_mview_repobject(
3 gname=>'test_repg',
4 sname=>'test',
5 name=>'dept_mv1',
6 type=>'SNAPSHOT',
7 min_communication=>true);
8 end;
9 /
PL/SQL 过程已成功完成。
SQL> --add objects to the refresh group
SQL> BEGIN
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;
12 /
PL/SQL 过程已成功完成。
SQL> BEGIN
2 DBMS_REFRESH.ADD (
3 name => 'mviewadmin.test_refg',
4 list => 'test.test_mv1',
5 lax => TRUE);
6 END;
7 /
PL/SQL 过程已成功完成。
SQL> BEGIN
2 DBMS_REFRESH.ADD (
3 name => 'mviewadmin.test_refg',
4 list => 'test.dept_mv1',
5 lax => TRUE);
6 END;
7 /
PL/SQL 过程已成功完成。
SQL> --create materilized view logs at the master materialized view site
SQL> connect test/test@mid;
已连接。
SQL> create materialized view log on test.test_mv1;
实体化视图日志已创建。
SQL> create materialized view log on test.dept_mv1;
实体化视图日志已创建。
SQL> connect test/test@dest;
已连接。
SQL> create database link mid connect to proxy_refresher identified by proxy_refresher;
数据库链接已创建。
SQL> select * from tab@mid;
未选定行
SQL> --create the materialized view group
SQL> connect mviewadmin/mviewadmin@dest;
已连接。
SQL> begin
2 dbms_repcat.create_mview_repgroup(
3 gname=>'test_repg',
4 master=>'mid',
5 propagation_mode=>'ASYNCHRONOUS');
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> --create the refresh group
SQL> BEGIN
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;
12 /
PL/SQL 过程已成功完成。
SQL> --add objects to the materialized view group
SQL> create materialized view test.test_mv2
2 refresh fast with primary key for update
3 as select * from test.test_mv1@mid;
实体化视图已创建。
SQL> create materialized view test.dept_mv2
2 refresh fast with primary key for update
3 as select * from test.dept_mv1@mid;
实体化视图已创建。
SQL> --add the materialized view to the materialized view group
SQL> begin
2 dbms_repcat.create_mview_repobject(
3 gname=>'test_repg',
4 sname=>'test',
5 name=>'test_mv2',
6 type=>'SNAPSHOT',
7 min_communication=>true);
8 end;
9 /
PL/SQL 过程已成功完成。
SQL>
SQL> begin
2 dbms_repcat.create_mview_repobject(
3 gname=>'test_repg',
4 sname=>'test',
5 name=>'dept_mv2',
6 type=>'SNAPSHOT',
7 min_communication=>true);
8 end;
9 /
PL/SQL 过程已成功完成。
SQL> --add objects to the refresh group
SQL> begin
2 dbms_refresh.add(
3 name=>'mviewadmin.test_refg',
4 list=>'test.test_mv2',
5 lax=>true);
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> begin
2 dbms_refresh.add(
3 name=>'mviewadmin.test_refg',
4 list=>'test.dept_mv2',
5 lax=>true);
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> spool off


参考文档：
Oracle?? Database Advanced Replication 10g Release 2 (10.2)
Oracle?? Database Advanced Replication Management API Reference 10g Release 2 (10.2)
关于Advanced Replication 的初步研究ITPUB个人空间6}AR`?0|.ne4qXNrPA6517ITPUB个人空间(H'@(xkz�{X{_ITPUB个人空间0~w:D4[`;D:s7m_ITPUB个人空间']2]"mTteITPUB个人空间X-o],?w2F