Oracle高级复制Step by Step.doc

Oracle高级复制Step by Step实施起因某项目中遇到内外网数据库同步的问题，假设电信机房和某机房网络物理隔绝，需要借助中间服务器进行数据库同步，这个中间服务器既可以是数据库服务器也可以是某种中间件或者某个程序。因为两头都是Oracle数据库，所以暂时先考虑使用数据库同步的机制来进行预研，当然仅仅是预研而已，距离实施还有一定距离，比如暂时只考虑数据库的单向同步，少量数据的双向同步采用其他方法进行。另外一个目的是为了简化预研的难度，比如省略了发布模板(deployment template)和冲突解决方案(Conflict Resolution)。Master site（主站点） Oracle实例为sourceMaterialized view site（中间站点）Oracle实例为midMaterialized view site Level2（目标站点）Oracle实例为dest数据流方向为sourcemid, middest 网络连通为sourcemid,middest;sourcedest高级复制中的几个概念1) replication object：复制对象，指需要作复制的对象（object），包括表，索引，存储过程等等。复制对象的更新遵循事务一致性规则（transactionally consistentmanner）。2) replication groups：复制组，是复制对象（replication object）的集合称为group，oracle 以replication group 的形式来管理复制。一个组可以包含多个模式的object，一个模式也可以有多个组中的object，但是每个replication object 都只能属于一个replication group。3) replication sites：复制站点，包含两种类型，主体站点（master sites）和实体化视图站点（materialized view sites）。一个site 可以担任一个replicationgroup 中的mater site 同时又担任另外一个replication group 中的materializedview site，注意必须是另外一个组，而不能是同一个replication group。4) scheduled links：一个数据库链接（database link），包含一个由用户定义的计划，来将需要更新的事务推到其它的master sites，当创建scheduled link 的时候，oracle将在本地任务队列中创建一个任务。5) master definition site：主体定义站点，大部分的高级复制配置都需要在一个站点上作，这个站点就是maserdef siteAsynchronous Data Replication Mechanisms示意图创建replication站点设置master主站点SQL connect system/inxitesource已连接。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 -设置清除延迟序列的jobSQL 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/inxitesource已连接。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 midSQL set echo onSQL conn system/inxitemid已连接。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/inxitemid已连接。SQL create public database link source using source;数据库链接已创建。SQL conn mviewadmin/mviewadminmid已连接。SQL create database link source connect to proxy_mviewadmin identified by proxy_mviewadmin;数据库链接已创建。SQL select * from tabsource;未选定行SQL -create the propagator/receiver database link;SQL connect propagator/propagatormid;已连接。SQL create database link source connect to repadmin identified by repadmin;数据库链接已创建。SQL -Schedule purge at the mid materialized view siteSQL conn mviewadmin/mviewadminmid已连接。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 siteSQL conn mviewadmin/mviewadminmid已连接。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 siteSQL -create proxy materialized view administratorSQL connect system/inxitemid已连接。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 refresherSQL 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设置物化视图站点2SQL -connect as system at level 2 materialized view site destSQL connect system/inxitedest已连接。SQL -create level2 materialized view site users at destSQL 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 propagatorSQL 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 refresherSQL 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 siteSQL -create a public database linkSQL connect system/inxitedest已连接。SQL create public database link mid using mid;数据库链接已创建。SQL connect mviewadmin/mviewadmindest已连接。SQL create database link mid connect to proxy_mviewadmin identified by proxy_mviewadmin;数据库链接已创建。SQL select * from tabmid;未选定行SQL -create a propagator/receiver database linkSQL connect propagator/propagatordest已连接。SQL create database link mid connect to mviewadmin identified by mviewadmin;数据库链接已创建。SQL select * from tabmid;未选定行SQL -Schedule purge at level 2 materialized view site at dest siteSQL conn mviewadmin/mviewadmindest已连接。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 siteSQL conn mviewadmin/mviewadmindest;已连接。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/repadminsource;已连接。SQL -create the test schema at source master siteSQL -.SQL -create the master groupSQL begin 2 dbms_repcat.create_master_repgroup( 3 gname=test_repg); 4 end; 5 /PL/SQL 过程已成功完成。SQL -add object to master groupSQL SQL BEGIN 2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT ( 3 gname = test_repg, 4 type = TABLE, 5 oname = 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 oname = 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 oname = 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 oname = dept, 5 type = TABLE, 6 min_communication = TRUE); 7 END; 8 /PL/SQL 过程已成功完成。SQL -start replicationSQL begin 2 dbms_repcat.resume_master_activity( 3 gname=test_repg); 4 end; 5 /PL/SQL 过程已成功完成。SQL spool off创建物化视图组SQL conn test/testsource;已连接。SQL create materialized view log on test.test;实体化视图日志已创建。SQL create materialized view log on test.dept;实体化视图日志已创建。SQL CONN system/inxitemid;已连接。SQL CREATE SMALLFILE TABLESPACE test 2 DATAFILE E:OracleDBDBFILE01.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 conn system/inxitedest已连接。SQL CREATE SMALLFILE TABLESPACE test 2 DATAFILE E:OracleDBDBFILE01.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 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/testmid;已连接。SQL create database link source connect to proxy_refresher identified by proxy_refresher;数据库链接已创建。SQL select * from tabsource;未选定行SQL -create the materialized view groupSQL connect mviewadmin/mviewadminmid;已连接。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 groupSQL 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 groupSQL create materialized view test.test_mv1 2 refresh fast with primary key for update 3 as select * from test.testsource;实体化视图已创建。SQL create materialized view test.dept_mv1 2 refresh fast with primary key for update 3 as select * from test.deptsource;实体化视图已创建。SQL select * from test.dept_mv1;ID Name-1 market2 finacialSQL begin 2 dbms_repcat.create_mview_repobject( 3 gname=test_repg, 4 sname=test, 5 oname=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 oname=dept_mv1, 6 type=SNAPSHOT, 7 min_communication=true); 8 end; 9 /PL/SQL 过程已成功完成。SQL -add objects to the refresh groupSQL 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 siteSQL connect test/testmid;已连接。SQL create materialized view log on test.test_mv1;实体化视图日志已创建。SQL create materialized view log on test.dept_mv1;实体化视图日志已创建。SQL connect test/testdest;已连接。SQL create database link mid connect to proxy_refresher identified by proxy_refresher;数据库链接已创建。SQL select * from tabmid;未选定行SQL -create the materialized view groupSQL connect mviewadmin/mviewadmindest;