ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 10g 1to2 schema级流复制配置

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

原创 Linux操作系统 作者:anchen211 时间:2008-12-02 16:52:23 0 删除 编辑

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

我的复制环境:

源数据库是一个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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 流复制同步中断
请登录后发表评论 登录
全部评论

注册时间:2008-11-04

  • 博文量
    129
  • 访问量
    178648