ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 附2:利用EM设置流生成的脚本源文件

附2:利用EM设置流生成的脚本源文件

原创 Linux操作系统 作者:tcczxhj 时间:2009-03-06 16:54:56 0 删除 编辑

2:利用EM设置流生成的脚本源文件

OS:windows2003 sp2   (简体中文企业版)       Oracle 10.2.0.3
source:tczf        192.168.0.10                 destnation:tccz    192.168.0.12    

同步的三个用户分别为:cf123,fs3i,gzhb.

准备工作:初始化参数修改,归档模式修改,创建流管理员,设置数据库链接。在目的库上建好用户。从脚本中可以判断,利用EM设置流应该源数据库所在的服务器上完成,否则显示完成但应用不成功。

-----script1------setup.sql----------------------------------------------------------------------
set echo on;
 ACCEPT strm_pwd_src PROMPT 'Enter Password of Streams Admin "strmadmin" at Source : ' HIDE  
 ACCEPT strm_pwd_dest PROMPT 'Enter Password of Streams Admin "strmadmin" at Destination : ' HIDE  
connect   "STRMADMIN"/&strm_pwd_src;
BEGIN 
  DBMS_STREAMS_ADM.SET_UP_QUEUE( 
    queue_table => '"STREAMS_CAPTURE_QT"', 
    queue_name  => '"STREAMS_CAPTURE_Q"', 
    queue_user  => '"STRMADMIN"'); 
END; 
/
BEGIN 
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( 
    schema_name        => '"CF123"', 
    streams_type       => 'capture', 
    streams_name       => '"STREAMS_CAPTURE"', 
    queue_name         => '"STRMADMIN"."STREAMS_CAPTURE_Q"', 
    include_dml        => true, 
    include_ddl        => true, 
    include_tagged_lcr => false, 
    inclusion_rule     => true); 
END; 
/
BEGIN 
  DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( 
   schema_name            => '"CF123"', 
   streams_name           => '"STREAMS_PROPAGATION"', 
    source_queue_name      => '"STRMADMIN"."STREAMS_CAPTURE_Q"', 
    destination_queue_name => '"STRMADMIN"."STREAMS_APPLY_Q"@TCCZ.REGRESS.RDBMS.DEV.US.ORACLE.COM', 
    include_dml            => true, 
    include_ddl            => true, 
    source_database        => 'TCZF.REGRESS.RDBMS.DEV.US.ORACLE.COM', 
    inclusion_rule         => true );
END; 
/
BEGIN 
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( 
    schema_name        => '"FS3I"', 
    streams_type       => 'capture', 
    streams_name       => '"STREAMS_CAPTURE"', 
    queue_name         => '"STRMADMIN"."STREAMS_CAPTURE_Q"', 
    include_dml        => true, 
    include_ddl        => true, 
    include_tagged_lcr => false, 
    inclusion_rule     => true); 
END; 
/
BEGIN 
  DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( 
   schema_name            => '"FS3I"', 
   streams_name           => '"STREAMS_PROPAGATION"', 
    source_queue_name      => '"STRMADMIN"."STREAMS_CAPTURE_Q"', 
    destination_queue_name => '"STRMADMIN"."STREAMS_APPLY_Q"@TCCZ.REGRESS.RDBMS.DEV.US.ORACLE.COM', 
    include_dml            => true, 
    include_ddl            => true, 
    source_database        => 'TCZF.REGRESS.RDBMS.DEV.US.ORACLE.COM', 
    inclusion_rule         => true );
END; 
/
BEGIN 
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( 
    schema_name        => '"GZHB"', 
    streams_type       => 'capture', 
    streams_name       => '"STREAMS_CAPTURE"', 
    queue_name         => '"STRMADMIN"."STREAMS_CAPTURE_Q"', 
    include_dml        => true, 
    include_ddl        => true, 
    include_tagged_lcr => false, 
    inclusion_rule     => true); 
END; 
/
BEGIN 
  DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( 
   schema_name            => '"GZHB"', 
   streams_name           => '"STREAMS_PROPAGATION"', 
    source_queue_name      => '"STRMADMIN"."STREAMS_CAPTURE_Q"', 
    destination_queue_name => '"STRMADMIN"."STREAMS_APPLY_Q"@TCCZ.REGRESS.RDBMS.DEV.US.ORACLE.COM', 
    include_dml            => true, 
    include_ddl            => true, 
    source_database        => 'TCZF.REGRESS.RDBMS.DEV.US.ORACLE.COM', 
    inclusion_rule         => true );
END; 
/
COMMIT;
-----------------------------------------------------------------------------------------------------
connect "STRMADMIN"/&strm_pwd_dest@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.12)(PORT=1521)))(CONNECT_DATA=(SID=TCCZ)(SERVER=DEDICATED)))";
BEGIN 
  DBMS_STREAMS_ADM.SET_UP_QUEUE( 
    queue_table => '"STREAMS_APPLY_QT"', 
    queue_name  => '"STREAMS_APPLY_Q"', 
    queue_user  => '"STRMADMIN"'); 
END; 
/
BEGIN 
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( 
    schema_name        => '"CF123"', 
    streams_type       => 'apply', 
    streams_name       => '"STREAMS_APPLY"', 
    queue_name         => '"STRMADMIN"."STREAMS_APPLY_Q"', 
    include_dml        => true, 
    include_ddl        => true, 
    include_tagged_lcr => false, 
    inclusion_rule     => true); 
END; 
/
BEGIN 
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( 
    schema_name        => '"FS3I"', 
    streams_type       => 'apply', 
    streams_name       => '"STREAMS_APPLY"', 
    queue_name         => '"STRMADMIN"."STREAMS_APPLY_Q"', 
    include_dml        => true, 
    include_ddl        => true, 
    include_tagged_lcr => false, 
    inclusion_rule     => true); 
END; 
/
BEGIN 
  DBMS_STREAMS_ADM.ADD_SCHEMA_RULES( 
    schema_name        => '"GZHB"', 
    streams_type       => 'apply', 
    streams_name       => '"STREAMS_APPLY"', 
    queue_name         => '"STRMADMIN"."STREAMS_APPLY_Q"', 
    include_dml        => true, 
    include_ddl        => true, 
    include_tagged_lcr => false, 
    inclusion_rule     => true); 
END; 
/

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

请登录后发表评论 登录
全部评论

注册时间:2009-03-06

  • 博文量
    47
  • 访问量
    100553