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后,目标库还保留着,结果正是我们所要的。
STREAM过滤指定DDL语句
最新推荐文章于 2023-02-25 16:41:05 发布