STREAMS筆記(10) 同步捕獲

同步捕獲時11g的新特性，不需要開啟歸檔，不需要supplemental logging，類似以觸發器的機制
不同於以前的CDC和物化檢視日誌，每個物件就要建立一張日誌表，同步捕獲將資料放入persistent佇列中

--------------------------------------
1.源庫上建立Propagation
--------------------------------------
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;
/

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;
/

BEGIN
dbms_streams_adm.add_global_propagation_rules(
   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;
/

BEGIN
dbms_aqadm.disable_propagation_schedule(
    queue_name => 'STRMADMIN.O11203_CTAIS2_CAPQ$1',
    destination => 'QUERY',
    destination_queue => 'STRMADMIN.QUERY_CTAIS2_APPQ$1');
END;
/

--------------------------------------
2.目標庫上建立Apply
--------------------------------------
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;
/

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            => false,
       precommit_handler       => NULL,
       negative_rule_set_name => NULL,
       source_database         => 'O11203');
END;
/

BEGIN
    DBMS_STREAMS_ADM.ADD_GLOBAL_RULES(
        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;
/

--------------------------------------
3.源上建立同步捕獲
--------------------------------------
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name         => 'CTAIS2.TEST1',
streams_type       => 'sync_capture',
streams_name       => 'sync01_capture',
queue_name         => 'STRMADMIN.O11203_CTAIS2_CAPQ$1',
include_tagged_lcr => FALSE);
END;
/

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name         => 'CTAIS2.TEST2',
streams_type       => 'sync_capture',
streams_name       => 'sync01_capture',
queue_name         => 'STRMADMIN.O11203_CTAIS2_CAPQ$1',
include_tagged_lcr => FALSE);
END;
/

SQL> select * from DBA_SYNC_CAPTURE_PREPARED_TABS;

TABLE_OWNER                    TABLE_NAME                            SCN
------------------------------ ------------------------------ ----------
TIMESTAMP
---------
CTAIS2                         TEST1                             2617134
10-JUN-13

CTAIS2                         TEST2                             2617154
10-JUN-13

SQL> select * from DBA_SYNC_CAPTURE_TABLES;

TABLE_OWNER                    TABLE_NAME                     ENA
------------------------------ ------------------------------ ---
CTAIS2                         TEST2                          YES
CTAIS2                         TEST1                          YES

--------------------------------------
4.初始化資料
--------------------------------------
impdp strmadmin/oracle TABLES=ctais2.test1,ctais2.test2 NETWORK_LINK=o11203

SQL> select * from DBA_APPLY_INSTANTIATED_OBJECTS;

SOURCE_DATABASE
--------------------------------------------------------------------------------
SOURCE_OBJECT_OWNER            SOURCE_OBJECT_NAME             SOURCE_OBJE
------------------------------ ------------------------------ -----------
INSTANTIATION_SCN IGNORE_SCN
----------------- ----------
APPLY_DATABASE_LINK
--------------------------------------------------------------------------------
O11203
CTAIS2                         TEST2                          TABLE
          2618594          0

O11203
CTAIS2                         TEST1                          TABLE
          2618488          0

--------------------------------------
5.啟動
--------------------------------------
BEGIN
dbms_apply_adm.start_apply('QUERY_CTAIS2_APP$1');
END;
/

BEGIN
dbms_aqadm.enable_propagation_schedule(
    queue_name => 'STRMADMIN.O11203_CTAIS2_CAPQ$1',
    destination => 'QUERY',
    destination_queue => 'STRMADMIN.QUERY_CTAIS2_APPQ$1');
END;
/

SQL> select * from DBA_SYNC_CAPTURE;

CAPTURE_NAME                   QUEUE_NAME
------------------------------ ------------------------------
QUEUE_OWNER                    RULE_SET_NAME
------------------------------ ------------------------------
RULE_SET_OWNER                 CAPTURE_USER
------------------------------ ------------------------------
SYNC01_CAPTURE                 O11203_CTAIS2_CAPQ$1
STRMADMIN                      RULESET$_143
STRMADMIN                      STRMADMIN

SQL> ;
1* select * from DBA_STREAMS_RULES where rule_Set_name='RULESET$_143'
SQL> /

STREAMS_TYPE STREAMS_NAME                   RULE_SET_OWNER
------------ ------------------------------ ------------------------------
RULE_SET_NAME                  RULE_OWNER
------------------------------ ------------------------------
RULE_NAME
------------------------------
RULE_CONDITION
--------------------------------------------------------------------------------
RULE_SET STREAM SCHEMA_NAME                    OBJECT_NAME
-------- ------ ------------------------------ ------------------------------
SUBSET
------
DML_CONDITION
--------------------------------------------------------------------------------
INC
---
SOURCE_DATABASE
--------------------------------------------------------------------------------
RUL MESSAGE_TYPE_OWNER             MESSAGE_TYPE_NAME
--- ------------------------------ ------------------------------
MESSAGE_RULE_VARIABLE
------------------------------
ORIGINAL_RULE_CONDITION
--------------------------------------------------------------------------------
SAM
---
SYNC_CAPTURE SYNC01_CAPTURE                 STRMADMIN
RULESET$_143                   STRMADMIN
TEST1142
(((:dml.get_object_owner() = 'CTAIS2' and :dml.get_object_name() = 'TEST1')) and
POSITIVE TABLE CTAIS2                         TEST1

NO

DML

(((:dml.get_object_owner() = 'CTAIS2' and :dml.get_object_name() = 'TEST1')) and
:dml.is_null_tag() = 'Y' )
YES

SYNC_CAPTURE SYNC01_CAPTURE                 STRMADMIN
RULESET$_143                   STRMADMIN
TEST2144
(((:dml.get_object_owner() = 'CTAIS2' and :dml.get_object_name() = 'TEST2')) and
POSITIVE TABLE CTAIS2                         TEST2

NO

DML

(((:dml.get_object_owner() = 'CTAIS2' and :dml.get_object_name() = 'TEST2')) and
:dml.is_null_tag() = 'Y' )
YES

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-763703/，如需轉載，請註明出處，否則將追究法律責任。

STREAMS筆記(10) 同步捕獲

相關文章