ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一步一步学Streams(22) 创建单源多向复制环境(3)

一步一步学Streams(22) 创建单源多向复制环境(3)

原创 Linux操作系统 作者:junsansi 时间:2009-04-02 14:43:05 0 删除 编辑

五、 全手动方式配置复制环境 

  如何清除复制环境,想必你已经熟的不能再熟,这里就不演示了。注意前面自动配置中还创建了一些其它schema下的对象,不要忘记清除。

  全手动方式不再通过DBMS_STREAMS_ADM包来配置,而是通过DBMS_CAPTURE_ADM、DBMS_PROPAGATION_ADM、DBMS_APPLY_ADM、DBMS_RULES_ADM分别配置捕获、传播、应用进程和规则集。

  这种方式的配置流程与DBMS_STREAMS_ADM几乎完全相同,只是执行的过程不同,而且需要你手动指定的参数非常多(每个对象都需要单独指定),演示这种方式的创建,是为了让大家更好的理解streams对共享对象的处理过程。

1、 Jssweb 端配置propagation进程

  包括几个步骤,分别是创建规则集,设置具体的DDL/DML规则,添加至规则集,然后创建传播进程到jssstr端。

    JSSWEB> BEGIN

    2 DBMS_RULE_ADM.CREATE_RULE_SET(

    3 rule_set_name => ¨strmadmin.propagation_rules¨,

    4 evaluation_context => ¨SYS.STREAMS$_EVALUATION_CONTEXT¨);

    5 DBMS_RULE_ADM.CREATE_RULE(

    6 rule_name => ¨strmadmin.member_pro_dml¨,

    7 condition => ¨ :dml.get_object_owner() = ¨¨MEMBER¨¨ AND ¨ ||

    8 ¨ :dml.is_null_tag() = ¨¨Y¨¨ AND ¨ ||

    9 ¨ :dml.get_source_database_name() = ¨¨JSSWEB.JSS.CN¨¨ ¨);

    10 DBMS_RULE_ADM.CREATE_RULE(

    11 rule_name => ¨strmadmin.member_pro_ddl¨,

    12 condition => ¨ (:ddl.get_object_owner() = ¨¨MEMBER¨¨ OR ¨ ||

    13 ¨ :ddl.get_base_table_owner() = ¨¨MEMBER¨¨) AND ¨ ||

    14 ¨ :ddl.is_null_tag() = ¨¨Y¨¨ AND ¨ ||

    15 ¨ :ddl.get_source_database_name() = ¨¨JSSWEB.JSS.CN¨¨ ¨);

    16 DBMS_RULE_ADM.ADD_RULE(

    17 rule_name => ¨strmadmin.member_pro_dml¨,

    18 rule_set_name => ¨strmadmin.propagation_rules¨);

    19 DBMS_RULE_ADM.ADD_RULE(

    20 rule_name => ¨strmadmin.member_pro_ddl¨,

    21 rule_set_name => ¨strmadmin.propagation_rules¨);

    22 DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(

    23 propagation_name => ¨web_to_str¨,

    24 source_queue => ¨strmadmin.streams_queue¨,

    25 destination_queue => ¨strmadmin.streams_queue¨,

    26 destination_dblink => ¨jssstr.jss.cn¨,

    27 rule_set_name => ¨strmadmin.propagation_rules¨);

    28 END;

    29 /

    PL/SQL procedure successfully completed.
2、 Jssweb 端配置capture进程

  包括几个步骤,分别是创建规则集,设置具体的DDL/DML规则,添加至规则集,然后创建捕获进程。

    JSSWEB> BEGIN

    2 DBMS_RULE_ADM.CREATE_RULE_SET(

    3 rule_set_name => ¨strmadmin.capture_rules¨,

    4 evaluation_context => ¨SYS.STREAMS$_EVALUATION_CONTEXT¨);

    5 DBMS_RULE_ADM.CREATE_RULE(

    6 rule_name => ¨strmadmin.member_cap_dml¨,

    7 condition => ¨ :dml.get_object_owner() = ¨¨MEMBER¨¨ AND ¨ ||

    8 ¨ :dml.is_null_tag() = ¨¨Y¨¨ AND ¨ ||

    9 ¨ :dml.get_source_database_name() = ¨¨JSSWEB.JSS.CN¨¨ ¨);

    10 DBMS_RULE_ADM.CREATE_RULE(

    11 rule_name => ¨strmadmin.member_cap_ddl¨,

    12 condition => ¨ (:ddl.get_object_owner() = ¨¨MEMBER¨¨ OR ¨ ||

    13 ¨ :ddl.get_base_table_owner() = ¨¨MEMBER¨¨) AND ¨ ||

    14 ¨ :ddl.is_null_tag() = ¨¨Y¨¨ AND ¨ ||

    15 ¨ :ddl.get_source_database_name() = ¨¨JSSWEB.JSS.CN¨¨ ¨);

    16 DBMS_RULE_ADM.ADD_RULE(

    17 rule_name => ¨strmadmin.member_cap_dml¨,

    18 rule_set_name => ¨strmadmin.capture_rules¨);

    19 DBMS_RULE_ADM.ADD_RULE(

    20 rule_name => ¨strmadmin.member_cap_ddl¨,

    21 rule_set_name => ¨strmadmin.capture_rules¨);

    22 DBMS_CAPTURE_ADM.CREATE_CAPTURE(

    23 queue_name => ¨strmadmin.streams_queue¨,

    24 capture_name => ¨jssweb_capture¨,

    25 rule_set_name => ¨strmadmin.capture_rules¨);

    26 END;

    27 /

    PL/SQL procedure successfully completed.
3、 Jssweb 端准备实例SCN

  如果是使用dbms_streams_adm包来配置就不需要此步骤,过程会自动进行配置。

    JSSWEB> BEGIN

    2 DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(

    3 schema_name => ¨member¨,

    4 supplemental_logging => ¨keys¨);

    5 END;

    6 /

    PL/SQL procedure successfully completed.
4、 Jssstr 端设置实例SCN

  因为jssweb要复制到jssstr端的usr_user表在jssstr数据库已经存在(虽然改了名叫users),因此我们需要设置初始scn,以让streams知道从何时开始的修改能够应用到目标端。

    JSSSTR> DECLARE

    2 iscn NUMBER;

    3 BEGIN

    4 iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();

    5 DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@jssstr.jss.cn(

    6 source_object_name => ¨member.usr_user¨,

    7 source_database_name => ¨jssweb.jss.cn¨,

    8 instantiation_scn => iscn);

    9 END;

    10 /

    PL/SQL procedure successfully completed.
5、 Jssbak 端从jssweb导入对象

  Jssbak 中的schema:member中没有任何数据,因此在真正复制前,首先需要进行初始化,这里我们选择通过数据泵的方式从jssweb初始化数据到jssbak。

  需要复制的表有三个:dt_tmp、dt_tbl1和dt_tbl2 :

    JSSBAK> SET SERVEROUTPUT ON

    DECLARE

    h1 NUMBER; -- Data Pump job handle

    sscn NUMBER; -- Variable to hold current source SCN

    job_state VARCHAR2(30); -- To keep track of job state

    js ku$_JobStatus; -- The job status from GET_STATUS

    sts ku$_Status; -- The status object returned by GET_STATUS

    job_not_exist exception;

    JSSBAK> 2 3 4 5 6 7 8 pragma exception_init(job_not_exist, -31626);

    9 BEGIN

    10 -- Create a (user-named) Data Pump job to do a table-level import.

    11 h1 := DBMS_DATAPUMP.OPEN(

    12 peration => ¨IMPORT¨,

    13 job_mode => ¨TABLE¨,

    14 remote_link => ¨jssweb.jss.cn¨,

    15 job_name => ¨dp_sync2¨);

    16 -- A metadata filter is used to specify the schema that owns the tables

    17 -- that will be imported.

    18 DBMS_DATAPUMP.METADATA_FILTER(

    19 handle => h1,

    20 name => ¨SCHEMA_EXPR¨,

    21 value => ¨=¨¨MEMBER¨¨¨);

    22 -- A metadata filter is used to specify the tables that will be imported.

    23 DBMS_DATAPUMP.METADATA_FILTER(

    24 handle => h1,

    25 name => ¨NAME_EXPR¨,

    26 value => ¨IN(¨¨DT_TBL1¨¨, ¨¨DT_TBL2¨¨, ¨¨DT_TMP¨¨)¨);

    27 -- Get the current SCN of the source database, and set the FLASHBACK_SCN

    28 -- parameter to this value to ensure consistency between all of the

    29 -- objects included in the import.

    30 sscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@jssweb.jss.cn();

    31 DBMS_DATAPUMP.SET_PARAMETER(

    32 handle => h1,

    33 name => ¨FLASHBACK_SCN¨,

    34 value => sscn);

    35 -- Start the job.

    36 DBMS_DATAPUMP.START_JOB(h1);

    37 -- The import job should be running. In the following loop, the job

    38 -- is monitored until it completes.

    39 job_state := ¨UNDEFINED¨;

    40 BEGIN

    41 WHILE (job_state != ¨COMPLETED¨) AND (job_state != ¨STOPPED¨) LOOP

    42 sts:=DBMS_DATAPUMP.GET_STATUS(

    43 handle => h1,

    44 mask => DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR +

    45 DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS +

    46 DBMS_DATAPUMP.KU$_STATUS_WIP,

    47 timeout => -1);

    48 js := sts.job_status;

    49 DBMS_LOCK.SLEEP(10);

    50 job_state := js.state;

    51 END LOOP;

    52 -- Gets an exception when job no longer exists

    53 EXCEPTION WHEN job_not_exist THEN

    54 DBMS_OUTPUT.PUT_LINE(¨Data Pump job has completed¨);

    55 DBMS_OUTPUT.PUT_LINE(¨Instantiation SCN: ¨ ||sscn);

    56 END;

    57 END;

    58 /

    Data Pump job has completed

    Instantiation SCN: 622293

    PL/SQL procedure successfully completed.
6、 Jssbak 端配置apply进程

    JSSBAK> BEGIN

    2 DBMS_RULE_ADM.CREATE_RULE_SET(

    3 rule_set_name => ¨strmadmin.apply_rules¨,

    4 evaluation_context => ¨SYS.STREAMS$_EVALUATION_CONTEXT¨);

    5 DBMS_RULE_ADM.CREATE_RULE(

    6 rule_name => ¨strmadmin.dt_tbl1_dml_app¨,

    7 condition => ¨ :dml.get_object_owner() = ¨¨MEMBER¨¨ AND ¨ ||

    8 ¨ :dml.get_object_name() = ¨¨DT_TBL1¨¨ AND ¨ ||

    9 ¨ :dml.is_null_tag() = ¨¨Y¨¨ AND ¨ ||

    10 ¨ :dml.get_source_database_name() = ¨¨JSSWEB.JSS.CN¨¨ ¨);

    11 DBMS_RULE_ADM.CREATE_RULE(

    12 rule_name => ¨strmadmin.dt_tbl1_ddl_app¨,

    13 condition => ¨ (:ddl.get_object_owner() = ¨¨MEMBER¨¨ OR ¨ ||

    14 ¨ :ddl.get_base_table_owner() = ¨¨MEMBER¨¨) AND ¨ ||

    15 ¨ :ddl.get_object_name() = ¨¨DT_TBL1¨¨ AND ¨ ||

    16 ¨ :ddl.is_null_tag() = ¨¨Y¨¨ AND ¨ ||

    17 ¨ :ddl.get_source_database_name() = ¨¨JSSWEB.JSS.CN¨¨ ¨);

    18 DBMS_RULE_ADM.CREATE_RULE(

    19 rule_name => ¨strmadmin.dt_tbl2_dml_app¨,

    20 condition => ¨ :dml.get_object_owner() = ¨¨MEMBER¨¨ AND ¨ ||

    21 ¨ :dml.get_object_name() = ¨¨DT_TBL2¨¨ AND ¨ ||

    22 ¨ :dml.is_null_tag() = ¨¨Y¨¨ AND ¨ ||

    23 ¨ :dml.get_source_database_name() = ¨¨JSSWEB.JSS.CN¨¨ ¨);

    24 DBMS_RULE_ADM.CREATE_RULE(

    25 rule_name => ¨strmadmin.dt_tbl2_ddl_app¨,

    26 condition => ¨ (:ddl.get_object_owner() = ¨¨MEMBER¨¨ OR ¨ ||

    27 ¨ :ddl.get_base_table_owner() = ¨¨MEMBER¨¨) AND ¨ ||

    28 ¨ :ddl.get_object_name() = ¨¨DT_TBL2¨¨ AND ¨ ||

    29 ¨ :ddl.is_null_tag() = ¨¨Y¨¨ AND ¨ ||

    30 ¨ :ddl.get_source_database_name() = ¨¨JSSWEB.JSS.CN¨¨ ¨);

    31 DBMS_RULE_ADM.CREATE_RULE(

    32 rule_name => ¨strmadmin.dt_tmp_dml_app¨,

    33 condition => ¨ :dml.get_object_owner() = ¨¨MEMBER¨¨ AND ¨ ||

    34 ¨ :dml.get_object_name() = ¨¨DT_TMP¨¨ AND ¨ ||

    35 ¨ :dml.is_null_tag() = ¨¨Y¨¨ AND ¨ ||

    36 ¨ :dml.get_source_database_name() = ¨¨JSSWEB.JSS.CN¨¨ ¨);

    37 DBMS_RULE_ADM.CREATE_RULE(

    38 rule_name => ¨strmadmin.dt_tmp_ddl_app¨,

    39 condition => ¨ (:ddl.get_object_owner() = ¨¨MEMBER¨¨ OR ¨ ||

    40 ¨ :ddl.get_base_table_owner() = ¨¨MEMBER¨¨) AND ¨ ||

    41 ¨ :ddl.get_object_name() = ¨¨DT_TMP¨¨ AND ¨ ||

    42 ¨ :ddl.is_null_tag() = ¨¨Y¨¨ AND ¨ ||

    43 ¨ :ddl.get_source_database_name() = ¨¨JSSWEB.JSS.CN¨¨ ¨);

    44 DBMS_RULE_ADM.ADD_RULE(

    45 rule_name => ¨strmadmin.dt_tbl1_dml_app¨,

    46 rule_set_name => ¨strmadmin.apply_rules¨);

    47 DBMS_RULE_ADM.ADD_RULE(

    48 rule_name => ¨strmadmin.dt_tbl1_ddl_app¨,

    49 rule_set_name => ¨strmadmin.apply_rules¨);

    50 DBMS_RULE_ADM.ADD_RULE(

    51 rule_name => ¨strmadmin.dt_tbl2_dml_app¨,

    52 rule_set_name => ¨strmadmin.apply_rules¨);

    53 DBMS_RULE_ADM.ADD_RULE(

    54 rule_name => ¨strmadmin.dt_tbl2_ddl_app¨,

    55 rule_set_name => ¨strmadmin.apply_rules¨);

    56 DBMS_RULE_ADM.ADD_RULE(

    57 rule_name => ¨strmadmin.dt_tmp_dml_app¨,

    58 rule_set_name => ¨strmadmin.apply_rules¨);

    59 DBMS_RULE_ADM.ADD_RULE(

    60 rule_name => ¨strmadmin.dt_tmp_ddl_app¨,

    61 rule_set_name => ¨strmadmin.apply_rules¨);

    62 DBMS_APPLY_ADM.CREATE_APPLY(

    63 queue_name => ¨strmadmin.streams_queue¨,

    64 apply_name => ¨jssbak_apply¨,

    65 rule_set_name => ¨strmadmin.apply_rules¨,

    66 apply_captured => true,

    67 source_database => ¨JSSWEB.JSS.CN¨);

    68 END;

    69 /

    PL/SQL procedure successfully completed.
7、 Jssbak 端启动apply进程

    JSSBAK> EXEC DBMS_APPLY_ADM.START_APPLY(¨jssbak_apply¨);

    PL/SQL procedure successfully completed.
8、 Jssstr 端配置apply进程

  传播dt_tmp,dt_tbl1,dt_tbl2三表的修改到jssbak端:

    JSSSTR> BEGIN

    2 DBMS_RULE_ADM.CREATE_RULE_SET(

    3 rule_set_name => ¨strmadmin.pro_to_bak_rules¨,

    4 evaluation_context => ¨SYS.STREAMS$_EVALUATION_CONTEXT¨);

    5 DBMS_RULE_ADM.CREATE_RULE(

    6 rule_name => ¨strmadmin.dt_tbl1_dml_pro¨,

    7 condition => ¨ :dml.get_object_owner() = ¨¨MEMBER¨¨ AND ¨ ||

    8 ¨ :dml.get_object_name() = ¨¨DT_TBL1¨¨ AND ¨ ||

    9 ¨ :dml.is_null_tag() = ¨¨Y¨¨ AND ¨ ||

    10 ¨ :dml.get_source_database_name() = ¨¨JSSWEB.JSS.CN¨¨ ¨);

    11 DBMS_RULE_ADM.CREATE_RULE(

    12 rule_name => ¨strmadmin.dt_tbl1_ddl_pro¨,

    13 condition => ¨ (:ddl.get_object_owner() = ¨¨MEMBER¨¨ OR ¨ ||

    14 ¨ :ddl.get_base_table_owner() = ¨¨MEMBER¨¨) AND ¨ ||

    15 ¨ :ddl.get_object_name() = ¨¨DT_TBL1¨¨ AND ¨ ||

    16 ¨ :ddl.is_null_tag() = ¨¨Y¨¨ AND ¨ ||

    17 ¨ :ddl.get_source_database_name() = ¨¨JSSWEB.JSS.CN¨¨ ¨);

    18 DBMS_RULE_ADM.CREATE_RULE(

    19 rule_name => ¨strmadmin.dt_tbl2_dml_pro¨,

    20 condition => ¨ :dml.get_object_owner() = ¨¨MEMBER¨¨ AND ¨ ||

    21 ¨ :dml.get_object_name() = ¨¨DT_TBL2¨¨ AND ¨ ||

    22 ¨ :dml.is_null_tag() = ¨¨Y¨¨ AND ¨ ||

    23 ¨ :dml.get_source_database_name() = ¨¨JSSWEB.JSS.CN¨¨ ¨);

    24 DBMS_RULE_ADM.CREATE_RULE(

    25 rule_name => ¨strmadmin.dt_tbl2_ddl_pro¨,

    26 condition => ¨ (:ddl.get_object_owner() = ¨¨MEMBER¨¨ OR ¨ ||

    27 ¨ :ddl.get_base_table_owner() = ¨¨MEMBER¨¨) AND ¨ ||

    28 ¨ :ddl.get_object_name() = ¨¨DT_TBL2¨¨ AND ¨ ||

    29 ¨ :ddl.is_null_tag() = ¨¨Y¨¨ AND ¨ ||

    30 ¨ :ddl.get_source_database_name() = ¨¨JSSWEB.JSS.CN¨¨ ¨);

    31 DBMS_RULE_ADM.CREATE_RULE(

    32 rule_name => ¨strmadmin.dt_tmp_dml_pro¨,

    33 condition => ¨ :dml.get_object_owner() = ¨¨MEMBER¨¨ AND ¨ ||

    34 ¨ :dml.get_object_name() = ¨¨DT_TMP¨¨ AND ¨ ||

    35 ¨ :dml.is_null_tag() = ¨¨Y¨¨ AND ¨ ||

    36 ¨ :dml.get_source_database_name() = ¨¨JSSWEB.JSS.CN¨¨ ¨);

    37 DBMS_RULE_ADM.CREATE_RULE(

    38 rule_name => ¨strmadmin.dt_tmp_ddl_pro¨,

    39 condition => ¨ (:ddl.get_object_owner() = ¨¨MEMBER¨¨ OR ¨ ||

    40 ¨ :ddl.get_base_table_owner() = ¨¨MEMBER¨¨) AND ¨ ||

    41 ¨ :ddl.get_object_name() = ¨¨DT_TMP¨¨ AND ¨ ||

    42 ¨ :ddl.is_null_tag() = ¨¨Y¨¨ AND ¨ ||

    43 ¨ :ddl.get_source_database_name() = ¨¨JSSWEB.JSS.CN¨¨ ¨);

    44 DBMS_RULE_ADM.ADD_RULE(

    45 rule_name => ¨strmadmin.dt_tbl1_dml_pro¨,

    46 rule_set_name => ¨strmadmin.pro_to_bak_rules¨);

    47 DBMS_RULE_ADM.ADD_RULE(

    48 rule_name => ¨strmadmin.dt_tbl1_ddl_pro¨,

    49 rule_set_name => ¨strmadmin.pro_to_bak_rules¨);

    50 DBMS_RULE_ADM.ADD_RULE(

    51 rule_name => ¨strmadmin.dt_tbl2_dml_pro¨,

    52 rule_set_name => ¨strmadmin.pro_to_bak_rules¨);

    53 DBMS_RULE_ADM.ADD_RULE(

    54 rule_name => ¨strmadmin.dt_tbl2_ddl_pro¨,

    55 rule_set_name => ¨strmadmin.pro_to_bak_rules¨);

    56 DBMS_RULE_ADM.ADD_RULE(

    57 rule_name => ¨strmadmin.dt_tmp_dml_pro¨,

    58 rule_set_name => ¨strmadmin.pro_to_bak_rules¨);

    59 DBMS_RULE_ADM.ADD_RULE(

    60 rule_name => ¨strmadmin.dt_tmp_ddl_pro¨,

    61 rule_set_name => ¨strmadmin.pro_to_bak_rules¨);

    62 DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(

    63 propagation_name => ¨str_to_bak¨,

    64 source_queue => ¨strmadmin.streams_queue¨,

    65 destination_queue => ¨strmadmin.streams_queue¨,

    66 destination_dblink => ¨jssbak.jss.cn¨,

    67 rule_set_name => ¨strmadmin.pro_to_bak_rules¨);

    68 END;

    69 /

    PL/SQL procedure successfully completed.
9、 Jssstr 端创建名称转换函数

    JSSSTR> CREATE OR REPLACE FUNCTION member.to_users_trans_dml(

    2 p_in_data in ANYDATA)

    3 RETURN ANYDATA IS out_data SYS.LCR$_ROW_RECORD;

    4 tc pls_integer;

    5 BEGIN

    6 -- Typecast AnyData to LCR$_ROW_RECORD

    7 tc := p_in_data.GetObject(out_data);

    8 IF out_data.GET_OBJECT_NAME() = ¨USR_USER¨

    9 THEN

    10 -- Transform. the in_data into the out_data

    11 out_data.SET_OBJECT_NAME(¨USERS¨);

    12 END IF;

    13 -- Convert to AnyData

    14 RETURN ANYDATA.ConvertObject(out_data);

    15 END;

    16 /

    Function created.
10、 Jssstr 端配置apply进程

  注意一点,jssweb.jss.cn中的usr_user表在jssstr端已经更名成了users表,因此此处指定一个context应用前面创建的转换函数。

    JSSSTR> DECLARE

    2 action_ctx_dml SYS.RE$NV_LIST;

    3 action_ctx_ddl SYS.RE$NV_LIST;

    4 ac_name VARCHAR2(30) := ¨STREAMS$_TRANSFORM_FUNCTION¨;

    5 BEGIN

    6 action_ctx_dml := SYS.RE$NV_LIST(SYS.RE$NV_ARRAY());

    7 action_ctx_dml.ADD_PAIR(

    8 ac_name,

    9 ANYDATA.CONVERTVARCHAR2(¨member.to_users_trans_dml¨));

    10 DBMS_RULE_ADM.CREATE_RULE_SET(

    11 rule_set_name => ¨strmadmin.apply_rules¨,

    12 evaluation_context => ¨SYS.STREAMS$_EVALUATION_CONTEXT¨);

    13 DBMS_RULE_ADM.CREATE_RULE(

    14 rule_name => ¨strmadmin.users_dml_app¨,

    15 condition => ¨ :dml.get_object_owner() = ¨¨MEMBER¨¨ AND ¨ ||

    16 ¨ :dml.get_object_name() = ¨¨USR_USER¨¨ AND ¨ ||

    17 ¨ :dml.is_null_tag() = ¨¨Y¨¨ AND ¨ ||

    18 ¨ :dml.get_source_database_name() = ¨¨JSSWEB.JSS.CN¨¨ ¨,

    19 action_context => action_ctx_dml);

    20 DBMS_RULE_ADM.ADD_RULE(

    21 rule_name => ¨strmadmin.users_dml_app¨,

    22 rule_set_name => ¨strmadmin.apply_rules¨);

    23 DBMS_APPLY_ADM.CREATE_APPLY(

    24 queue_name => ¨strmadmin.streams_queue¨,

    25 apply_name => ¨jssstr_apply¨,

    26 rule_set_name => ¨strmadmin.apply_rules¨,

    27 apply_captured => true,

    28 source_database => ¨jssweb.jss.cn¨);

    29 END;

    30 /

    PL/SQL procedure successfully completed.
11、 Jssstr 端启动apply进程

    JSSSTR> EXEC DBMS_APPLY_ADM.START_APPLY(¨jssstr_apply¨);

    PL/SQL procedure successfully completed.
12、 Jssweb 端启动capture进程

    JSSWEB> EXEC DBMS_CAPTURE_ADM.START_CAPTURE(¨jssweb_capture¨);

    PL/SQL procedure successfully completed.
13、 测试

  首先测试jssweb端usr_user表的dml操作是否能够同步到jssstr端:

    JSSWEB> insert into member.usr_user values (20,¨SMITH¨);

    1 row created.

    JSSWEB> commit;

    Commit complete.

    JSSWEB> alter system switch logfile;

    System altered.

    JSSWEB> select * from member.users@jssstr.jss.cn;

    USER_ID USERNAME

    ---------- ------------------------------

    10 ALLEN

    20 SMITH

  然后测试jssweb端其它表的修改,是否能够经由jssstr的传播,并最终在jssbak端应用,分别测试DML和DDL操作:

    JSSWEB> select * from member.dt_tmp;

    ID

    ----------

    10

    JSSWEB> insert into member.dt_tmp values (20);

    1 row created.

    JSSWEB> select count(0) from member.dt_tbl2;

    COUNT(0)

    ----------

    1

    JSSWEB> truncate table member.dt_tbl2;

    Table truncated.

    JSSWEB> alter system switch logfile;

    System altered.

  转到jssbak端查看结果:

    JSSBAK> select * from member.dt_tmp;

    ID

    ----------

    10

    20

    JSSBAK> select * from member.dt_tbl2;

    no rows selected

  全部成功。

======================================

一步一步学Streams(21) 创建单源多向复制环境(2)

一步一步学Streams(20) 创建单源多向复制环境(1)

一步一步学Streams(19) 创建单源单向复制环境

一步一步学Streams(18) 创建tables级复制环境

一步一步学Streams(17) 创建schema级复制环境

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

上一篇: 俺也要卖身~~
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-21

  • 博文量
    640
  • 访问量
    4112922