STREAM过滤指定DDL语句

2010年因为需要评估STREAM 和 GOLDENGATE某些功能，主要是测试STREAM对过滤指定DDL语句的功能
本次测试是过滤DROP PARTITION 语句，源库进行DROP 历史PARTITION 但是目标库保留历史PARTITION

环境说明
平台  REDHAT AS 5 32-BIT + ORACLE 10.2.0.4 32-BIT

              IP            HOSTNAME&ORACLE_SID     TNSNAME 
源库      192.168.128.21        DG1                  STREAM1
目标库    192.168.128.22        DG2                  STREAM2

STREAM 配置 单向复制，将源库的TT SCHEMA 复制到 目标库
 （标有源  目标 的 下面的脚本都在标注的相应库运行）
简单操作步骤
以下步骤两台机器全要配置
create tablespace tbs_streams 
datafile '/oracle/oradata/DG1/tbs_streams_01.dbf' size 50m  AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE                                                                                                                               
SEGMENT SPACE MANAGEMENT AUTO ;

create tablespace tbs_streams 
datafile '/oracle/oradata/DG2/tbs_streams_01.dbf' size 50m  AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE                                                                                                                               
SEGMENT SPACE MANAGEMENT AUTO ;

execute dbms_logmnr_d.set_tablespace('tbs_streams'); 


create user strmadm identified by admin default tablespace tbs_streams temporary tablespace temp; 


grant connect,resource,dba,aq_administrator_role to strmadm; 

exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADM');


配置源、目标库数据库的TNS ：

STREAM2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.22)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DG2)
    )
  )

STREAM1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.128.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DG1)
    )
  )



源库
alter database add supplemental log data; 

源 
    以strmadmin身份
conn strmadm/admin
SQL> create database link stream2  connect to strmadm  identified by admin using 'stream2';
目标库
    以strmadmin身份
conn strmadm/admin
SQL> create database link stream1 connect to strmadm  identified by admin  using 'stream1';

源  创建队列
    以strmadmin身份
begin 
dbms_streams_adm.set_up_queue( 
queue_table => 'DG1_queue_table', 
queue_name => 'DG1_queue'); 
end; 
/ 

PL/SQL procedure successfully completed.

目标库  创建队列
    以strmadmin身份
begin   
dbms_streams_adm.set_up_queue( 
queue_table => 'DG2_queue_table', 
queue_name => 'DG2_queue'); 
end; 
/ 

PL/SQL procedure successfully completed.

源 创建捕获进程
    以strmadmin身份
begin 
dbms_streams_adm.add_schema_rules( 
schema_name => 'TT', 
streams_type => 'capture', 
streams_name => 'capture_DG1', 
queue_name => 'strmadm.DG1_queue', 
include_dml => true, 
include_ddl => true, 
include_tagged_lcr => false, 
source_database => null, 
inclusion_rule => true); 
end; 
/ 

PL/SQL procedure successfully completed.

目标库
初始目标库数据
[oracle@DG2 admin]$ impdp strmadm/admin NETWORK_LINK=stream1 SCHEMAS=tt
Job "STRMADM"."SYS_IMPORT_SCHEMA_01" successfully completed at 16:33:06


源
创建传播进程
    以strmadmin身份，登录主数据库。 
begin 
dbms_streams_adm.add_schema_propagation_rules( 
schema_name => 'TT', 
streams_name => 'DG1_to_DG2', 
source_queue_name => 'strmadm.DG1_queue', 
destination_queue_name => 'strmadm.DG2_queue@STREAM2', 
include_dml => true, 
include_ddl => true, 
include_tagged_lcr => false, 
source_database => 'DG1', 
inclusion_rule => true); 
end; 
/ 

PL/SQL procedure successfully completed.



目标库库
创建APPLY 进程
    以strmadmin身份
begin 
dbms_streams_adm.add_schema_rules( 
schema_name => 'TT', 
streams_type => 'apply', 
streams_name => 'apply_DG2', 
queue_name => 'strmadm.DG2_queue', 
include_dml => true, 
include_ddl => true, 
include_tagged_lcr => false, 
source_database => 'DG1', 
inclusion_rule => true);
end; 
/ 
PL/SQL procedure successfully completed.

创建过滤DROP PARTITION 的DDL HANDLER PROCEDURE
    以strmadmin身份
CREATE OR REPLACE PROCEDURE apply_ddl_handler(in_any IN sys.anydata)
AUTHID current_user AS
ddllcr sys.lcr$_ddl_record; -- the ddl lcr
res NUMBER;
ddl_txt CLOB := NULL; -- original ddl text
command_type VARCHAR2(30);
-- current schema at the time the original ddl was executed
cur_schema VARCHAR2(30);
-- variables to store the ddl text in varchar2 format
ddl_vc2_orig VARCHAR2(32000);
ddl_vc2_upper VARCHAR2(32000);
ddl_modified VARCHAR2(32000);
-- object name information
tab_owner VARCHAR2(30);
tab_name VARCHAR2(30);
tab_full_name VARCHAR2(65);
-- used for counting rows
cnt NUMBER;
drop_loc NUMBER;
part_loc NUMBER;
table_exists EXCEPTION;
PRAGMA exception_init(table_exists, -942);
BEGIN
-- get the ddl lcr
res := in_any.getobject(ddllcr);

-- get some info from the LCR
command_type := ddllcr.get_command_type();
cur_schema := ddllcr.get_current_schema();
tab_owner := ddllcr.get_object_owner();
tab_name := ddllcr.get_object_name();
tab_full_name := '"' || tab_owner || '"."' || tab_name || '"';

-- check for ALTER TABLE
IF command_type = 'ALTER TABLE' THEN
dbms_lob.createtemporary(ddl_txt, true, dbms_lob.call);
ddllcr.get_ddl_text(ddl_txt);

-- only check for DROP PARTITION if the DDL text is not too big
IF dbms_lob.getlength(ddl_txt) <= 32000 THEN
ddl_vc2_orig := ddl_txt;
dbms_lob.freetemporary(ddl_txt);
ddl_vc2_upper := NLS_UPPER(ddl_vc2_orig);

-- possible DROP PARTITION
drop_loc := INSTR(ddl_vc2_upper, ' DROP ');
part_loc := INSTR(ddl_vc2_upper, ' PARTITION ');

-- Check that the words DROP and PARTITION exist in the ddl_text AND that
-- the words are within 10 characters of each other
-- This allows for some common typing variations
IF drop_loc > 0
AND part_loc > 0 and (part_loc-drop_loc) <=10 THEN
BEGIN
null;

-- done processing, so return
RETURN;
EXCEPTION 
WHEN others THEN
RAISE;
-- done processing, so return
RETURN;
END;
END IF;
END IF;
END IF;

-- free temp lob
IF dbms_lob.istemporary(ddl_txt) = 1 THEN
dbms_lob.freetemporary(ddl_txt);
END IF;

-- execute all lcrs that fall through
ddllcr.execute();
EXCEPTION WHEN others THEN
IF dbms_lob.istemporary(ddl_txt) = 1 THEN
dbms_lob.freetemporary(ddl_txt);
END IF;
RAISE;
END;
/


应用DDL HANDLER 过滤DROP PARTITION 操作
    以strmadmin身份
BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => 'apply_DG2',
ddl_handler => 'strmadm.apply_ddl_handler');
END;
/
开启APPLY 进程
    以strmadmin身份
begin 
dbms_apply_adm.start_apply( 
apply_name => 'apply_DG2'); 
end; 
/ 

源
开启CAPTURE进程
    以strmadmin身份
begin 
dbms_capture_adm.start_capture( 
capture_name => 'capture_DG1'); 
end; 
/ 

至此单身SCHEMA 复制已经配置完成

测试
最先测试我们关注的与PARITION 操作相关的功能
源库：
创建分区表
SQL> CREATE TABLE TT.TCTAUDLG(
  2    AC_DATE  NUMBER(8) default 0 not null,
  3    JRN_NO   NUMBER(11) default 0 not null,
  4    IN_TIM   CHAR(17) default ' ' not null,
  5    OUT_TIM  CHAR(17) default ' ' not null,
  6    TR_STS   CHAR(1) default ' ' not null,
  7    MSG_CODE CHAR(6) default ' ' not null
  8  ) 
  9  PARTITION BY RANGE (ac_date)
 10  (PARTITION TCTAUDLG_20091122 VALUES LESS THAN (20091122) TABLESPACE USERS,
 11   PARTITION TCTAUDLG_20091130 VALUES LESS THAN (20091130) TABLESPACE USERS,
 12   PARTITION TCTAUDLG_20091207 VALUES LESS THAN (20091207) TABLESPACE USERS,
 13   PARTITION TCTAUDLG_20091215 VALUES LESS THAN (20091215) TABLESPACE USERS
 14  );

Table created.
插入数据，为了方便显示测试结果，每个分区INSERT 进一条记录，当删除掉该分区时，应该此条记录不存在
SQL> Insert into TT.TCTAUDLG
  2     (AC_DATE, JRN_NO, IN_TIM, OUT_TIM, TR_STS, 
  3      MSG_CODE)
  4   Values
  5     (20091121, 0, '                 ', 'PARTITION1122    ', ' ', 
  6      '      ');

1 row created.

SQL> Insert into TT.TCTAUDLG
  2     (AC_DATE, JRN_NO, IN_TIM, OUT_TIM, TR_STS, 
  3      MSG_CODE)
  4   Values
  5     (20091124, 0, '                 ', 'PARTITION1130    ', ' ', 
  6      '      ');

1 row created.

SQL> Insert into TT.TCTAUDLG
  2     (AC_DATE, JRN_NO, IN_TIM, OUT_TIM, TR_STS, 
  3      MSG_CODE)
  4   Values
  5     (20091206, 0, '                 ', 'PARTITION1207    ', ' ', 
  6      '      ');
COMMIT;
1 row created.

SQL> commit;


SQL> SELECT * FROM TCTAUDLG;

   AC_DATE     JRN_NO IN_TIM            OUT_TIM           T MSG_CO
---------- ---------- ----------------- ----------------- - ------
  20091121          0                   PARTITION1122
  20091124          0                   PARTITION1130
  20091206          0                   PARTITION1207

目标库
查看数据是否同步
SQL> SELECT * FROM TCTAUDLG;

   AC_DATE     JRN_NO IN_TIM            OUT_TIM           T MSG_CO
---------- ---------- ----------------- ----------------- - ------
  20091121          0                   PARTITION1122
  20091124          0                   PARTITION1130
  20091206          0                   PARTITION1207
已经同步

现测试DROP PARTITION  结果应该是在源库删除一个分区后，目标库仍然保留此分区
源库
SQL> ALTER TABLE TT.TCTAUDLG DROP partition TCTAUDLG_20091122; 

Table altered.

SQL> SELECT * FROM TCTAUDLG;

   AC_DATE     JRN_NO IN_TIM            OUT_TIM           T MSG_CO
---------- ---------- ----------------- ----------------- - ------
  20091124          0                   PARTITION1130
  20091206          0                   PARTITION1207

检查目标库是否TCTAUDLG_20091122分区还保留
SQL> SELECT * FROM TCTAUDLG;

   AC_DATE     JRN_NO IN_TIM            OUT_TIM           T MSG_CO
---------- ---------- ----------------- ----------------- - ------
  20091121          0                   PARTITION1122
  20091124          0                   PARTITION1130
  20091206          0                   PARTITION1207
  
  
  以上结果显示 在源库删除分区TCTAUDLG_20091122后，目标库还保留着，结果正是我们所要的。