oracle 10g 1to2 schema级流复制配置

最新推荐文章于 2024-09-30 17:19:22 发布

clx00001

最新推荐文章于 2024-09-30 17:19:22 发布

阅读量179

点赞数

文章标签：数据库网络

这是本人以前的一篇文档,今日温故一遍.想想我当初配置的时候还是遇到不少问题的.故贴了上来,希望会对朋友们有所帮助.

我的复制环境:

源数据库是一个RAC环境(10.2.0.4).目的数据库有两个,一个是RAC(10.2.0.4),一个是单实例((10.2.0.41).在RAC TO RAC时请注意DBLINK的写法,这里有ORACLE的一个BUG.当时解决这个问题颇费周折.

1 所有库创建用户并授权
connect / as sysdba

CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE users QUOTA UNLIMITED ON tbs_repadmin;
GRANT DBA, CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin;
GRANT EXECUTE ON DBMS_AQADM TO strmadmin;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;
GRANT EXECUTE ON DBMS_FLASHBACK TO strmadmin;

BEGIN
    DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
        grantee => 'strmadmin',
        grant_option => FALSE);
END;
/

BEGIN
    DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
        privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
        grantee => 'strmadmin',
        grant_option => FALSE);
END;
/

execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');

源库：
创建DBLINK和队列：
connect strmadmin/strmadmin
create database link VIPBILL.REGRESS.RDBMS.DEV.US.ORACLE.COM
connect to strmadmin
identified by strmadmin
using '(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = ***.***.***.***)(PORT = 1521))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = billbj.chinacache)(INSTANCE_NAME = billbj1)))';

BEGIN
     DBMS_STREAMS_ADM.SET_UP_QUEUE(
      queue_table => 'STREAMS_QUEUE_TABLE',
      queue_name => 'STREAMS_QUEUE',
      queue_user => 'STRMADMIN');
END;
/

目标库
--创建DBLINK和队列：
connect strmadmin/strmadmin
create database link BILLBJ.CHINACACHE
connect to strmadmin
identified by strmadmin
using '(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = ***.***.***.***)(PORT = 1521))(CONNECT_DATA=(SID=BILLBJ1)(SERVER = DEDICATED)))';

BEGIN
     DBMS_STREAMS_ADM.SET_UP_QUEUE(
      queue_table => 'STREAMS_QUEUE_TABLE',
      queue_name => 'STREAMS_QUEUE',
      queue_user => 'STRMADMIN');
END;
/

--创建SCHEMA规划并配置APPLY用户启动APPLY进程
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
   schema_name => 'strmadmin',
   streams_type => 'APPLY',
   streams_name => 'STRMADMIN_APPLY',
   queue_name => 'STRMADMIN.STREAMS_QUEUE',
   include_dml => true,
   include_ddl => true,
   source_database => 'BILLBJ.CHINACACHE');
END;
/

BEGIN
     DBMS_APPLY_ADM.ALTER_APPLY(
      apply_name => 'STRMADMIN_APPLY',
      apply_user => 'strmadmin');
END;
/

DECLARE
   v_started number;
BEGIN
   SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
   FROM DBA_APPLY WHERE APPLY_NAME = 'STRMADMIN_APPLY';
   if (v_started = 0) then
     DBMS_APPLY_ADM.START_APPLY(apply_name => 'STRMADMIN_APPLY');
   end if;
END;
/

源库：
--设置LogMiner表空间
connect / as sysdba

CREATE TABLESPACE LOGMNRTS DATAFILE 'logmnrts.dbf' SIZE 25M AUTOEXTEND ON
MAXSIZE UNLIMITED;

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNRTS');
END;
/

--添加传播规则
connect strmadmin/strmadmin
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
   schema_name => 'strmadmin',
   streams_type => 'CAPTURE',
   streams_name => 'STREAM_CAPTURE',
   queue_name => 'STRMADMIN.STREAMS_QUEUE',
   include_dml => true,
   include_ddl => true,
   source_database => 'BILLBJ.CHINACACHE');
END;
/

BEGIN
    DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
      schema_name => 'strmadmin',
      streams_name => 'STREAM_PROPAGATE',
      source_queue_name => 'STRMADMIN.STREAMS_QUEUE',
      destination_queue_name => 'STRMADMIN.STREAMS_QUEUE@VIPBILL.REGRESS.RDBMS.DEV.US.ORACLE.COM',
      include_dml => true,
      include_ddl => true,
      source_database => 'BILLBJ.CHINACACHE');
END;
/

--同步数据
exp USERID=SYSTEM/BjSysDb001 WNER=strmadmin FILE=strmadmin.dmp OBJECT_CONSISTENT=Y STATISTICS = NONE

传送到目标库
scp strmadmin.dmp 61.135.207.25:/home/oracle

目标库：
imp USERID=SYSTEM/vipbill FULL=Y CONSTRAINTS=Y FILE=strmadmin.dmp buffer=102400000 IGNORE=Y COMMIT=Y STREAMS_INSTANTIATION=Y

源库：
--启动CAPTURE进程
connect strmadmin/strmadmin
begin
      DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'STREAM_CAPTURE');
end;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9375/viewspace-504453/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9375/viewspace-504453/