--------------------------------------
1.在源上创建一个队列
--------------------------------------
BEGIN
dbms_streams_adm.set_up_queue(
    queue_table => 'STRMADMIN.O11203_CTAIS2_CAPT$1',
    storage_clause => NULL,
    queue_name => 'STRMADMIN.O11203_CTAIS2_CAPQ$1',
    queue_user => '');
END;
/

--------------------------------------
2.创建一个Propagation
--------------------------------------

BEGIN
   DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
       propagation_name    => 'O11203_CTAIS2_PRO$1',
       source_queue        => 'STRMADMIN.O11203_CTAIS2_CAPQ$1',
       destination_queue   => 'STRMADMIN.QUERY_CTAIS2_APPQ$1',
       destination_dblink => 'QUERY',
       queue_to_queue      => TRUE);
END;
/

--------------------------------------
3.为Propagation添加一个rule
--------------------------------------
BEGIN
dbms_streams_adm.add_table_propagation_rules(
   table_name             => 'CTAIS2.T_STREAMS',
   streams_name           => 'O11203_CTAIS2_PRO$1',
   source_queue_name      => 'STRMADMIN.O11203_CTAIS2_CAPQ$1',
   destination_queue_name => 'STRMADMIN.QUERY_CTAIS2_APPQ$1@QUERY',
   include_dml            => TRUE,
   include_ddl            => TRUE,
   include_tagged_lcr     => FALSE,
   source_database        => NULL,
   inclusion_rule         => TRUE,
   and_condition          => NULL,
   queue_to_queue         => TRUE);
END;
/

--------------------------------------
4.先DISABLE掉这个Propagation JOB
--------------------------------------
BEGIN
dbms_aqadm.disable_propagation_schedule(
    queue_name => 'STRMADMIN.O11203_CTAIS2_CAPQ$1',
    destination => 'QUERY',
    destination_queue => 'STRMADMIN.QUERY_CTAIS2_APPQ$1');
END;
/

--------------------------------------
5.创建Capture
--------------------------------------
BEGIN
   DBMS_CAPTURE_ADM.CREATE_CAPTURE(
       queue_name       => 'STRMADMIN.O11203_CTAIS2_CAPQ$1',
       capture_name     => 'O11203_CTAIS2_CAP$1',
       start_scn        => NULL,
       source_database => NULL,
       first_scn        => NULL);
END;
/

--------------------------------------
6.为Capture添加一个rule
--------------------------------------
BEGIN
   DBMS_STREAMS_ADM.ADD_TABLE_RULES(
       table_name          => 'CTAIS2.T_STREAMS',
       streams_type        => 'CAPTURE',
       streams_name        => 'O11203_CTAIS2_CAP$1',
       queue_name          => 'STRMADMIN.O11203_CTAIS2_CAPQ$1',
       include_dml         => TRUE,
       include_ddl         => TRUE,
       include_tagged_lcr => FALSE,
       source_database     => NULL,
       inclusion_rule      => TRUE);
END;
/

这个动作会自动执行DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('CTAIS2.T_STREAMS','KEYS');
这个动作会自动设置初始化的SCN
CAPTURE对于这个对象，从该初始化SCN开始捕获

select * from DBA_CAPTURE_PREPARED_TABLES

TABLE_OWNER                    TABLE_NAME                            SCN
------------------------------ ------------------------------ ----------
TIMESTAMP SUPPLEME SUPPLEME SUPPLEME SUPPLEME
--------- -------- -------- -------- --------
CTAIS2                         T_STREAMS                         2285482
04-JUN-13 IMPLICIT IMPLICIT IMPLICIT NO

--------------------------------------
7.启动捕获进程
--------------------------------------
BEGIN
dbms_capture_adm.start_capture(
    capture_name => 'O11203_CTAIS2_CAP$1');
END;
/

--------------------------------------
8.在目标上执行初始化动作
--------------------------------------
impdp strmadmin/oracle NETWORK_LINK=o11203 tables=CTAIS2.T_STREAMS STREAMS_CONFIGURATION=YES FLASHBACK_SCN=2285482

STREAMS_CONFIGURATION默认就是YES
如果STREAMS_CONFIGURATION为YES，FLASHBACK_SCN可以不指定
如果STREAMS_CONFIGURATION为NO，FLASHBACK_SCN指定为源上初始化SCN后的SCN

Apply进程，从该SCN开始应用，低于该INSTANTIATION_SCN和IGNORE_SCN的LCR将忽略

如果STREAMS_CONFIGURATION为YES，就不需要执行下面步骤，设置目标上的SCN
如果为NO，需要设置instantiation_scn为FLASHBACK_SCN
BEGIN
dbms_apply_adm.set_table_instantiation_scn(
    source_object_name=> 'CTAIS2.T_STREAMS',
    source_database_name => 'o11203',
    instantiation_scn => 2285482,
    apply_database_link => NULL);
END;
/

select * from dba_apply_instantiated_objects

--------------------------------------
9.目标上创建队列
--------------------------------------
BEGIN
dbms_streams_adm.set_up_queue(
    queue_table => 'STRMADMIN.QUERY_CTAIS2_APPT$1',
    storage_clause => NULL,
    queue_name => 'STRMADMIN.QUERY_CTAIS2_APPQ$1',
    queue_user => '');
END;
/

--------------------------------------
10.创建APPLY
--------------------------------------
BEGIN
   DBMS_APPLY_ADM.CREATE_APPLY(
       queue_name                            => 'STRMADMIN.QUERY_CTAIS2_APPQ$1',
       apply_name                         => 'QUERY_CTAIS2_APP$1',
       message_handler                => NULL,
       ddl_handler             => NULL,
       apply_user              => 'CTAIS2',
       apply_database_link     => NULL,
       apply_tag               => HEXTORAW('5'),
       apply_captured          => TRUE,
       precommit_handler       => NULL,
       negative_rule_set_name => NULL,
       source_database         => 'O11203');
END;
/

--------------------------------------
11.为APPLY添加RULE
--------------------------------------
BEGIN
   DBMS_STREAMS_ADM.ADD_TABLE_RULES(
       table_name          => 'CTAIS2.T_STREAMS',
       streams_type        => 'APPLY',
       streams_name        => 'QUERY_CTAIS2_APP$1',
       queue_name          => 'STRMADMIN.QUERY_CTAIS2_APPQ$1',
       include_dml         => TRUE,
       include_ddl         => TRUE,
       include_tagged_lcr => FALSE,
       source_database     => NULL,
       inclusion_rule      => TRUE);
END;
/

--------------------------------------
12.启动APPLY
--------------------------------------
BEGIN
dbms_apply_adm.start_apply('QUERY_CTAIS2_APP$1');
END;
/

--------------------------------------
13.启动源上的Propagation JOB
--------------------------------------
BEGIN
dbms_aqadm.enable_propagation_schedule(
    queue_name => 'STRMADMIN.O11203_CTAIS2_CAPQ$1',
    destination => 'QUERY',
    destination_queue => 'STRMADMIN.QUERY_CTAIS2_APPQ$1');
END;
/

ITPub博客

STREAMS笔记(1) step by step 创建一个Streams复制环境

最新文章