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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8242091/viewspace-763703/