STREAMS筆記(1) step by step 建立一個Streams複製環境

--------------------------------------
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部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-762991/，如需轉載，請註明出處，否則將追究法律責任。

STREAMS筆記(1) step by step 建立一個Streams複製環境

相關文章