ITPub博客

首页 > Linux操作系统 > Linux操作系统 > GoldenGate同步DML功能测试

GoldenGate同步DML功能测试

原创 Linux操作系统 作者:db_wjw 时间:2012-03-21 17:35:46 0 删除 编辑
GoldenGate同步DML功能测试:

GoldenGate官方文档中明确列出的不支持的数据类型(这些类型没有做测试):
ANYDATA ANYDATASET ANYTYPE BFILE BINARY_INTEGER MLSLABEL ORDDICOM PLS_INTEGER TIMEZONE_ABBR URITYPE UROWID

在本测试中,对包含各种数据类型(基本囊括除上述不支持的类型外的所有类型)的对表做DML操作(包含insert、update、delete和merge),看源端的修改是否可以通过GoldenGate同步到目标数据库中,同时也对分区表做了测试。

结果发现没有什么问题,同步正常,但会延迟10秒左右。

本文不涉及GoldenGate相关操作。

测试过程如下:

1、创建表:

将下列语句保存为SQL脚本,在源数据库端和目标数据库端分别执行:
-- create table
-- number type
create table test11(id number,name int);
create table test12(id number,name decimal);
create table test13(id number,name float);
create table test14(id number,name real);
create table test15(id number,name number);
create table test16(id number,name binary_float);
create table test17(id number,name binary_double);
-- character type
create table test21(id number,name char(10));
create table test22(id number,name varchar2(10));
create table test23(id number,name varchar(10));
create table test24(id number,name nchar(10));
create table test25(id number,name nvarchar2(10));
create table test26(id number,name long);
create table test27(id number,name rowid);
-- binary type
create table test31(id number,name raw(10));
create table test32(id number,name long raw);
-- time type
create table test41(id number,name date);
create table test42(id number,name timestamp);
create table test43(id number,name timestamp with time zone);
create table test44(id number,name timestamp with local time zone);
create table test45(id number,name interval year to month);
create table test46(id number,name interval day to second);
-- large object type
Create table test51(id number,name clob);
Create table test52(id number,name nclob);
Create table test53(id number,name blob);
-- varray type
Create type varray_name_type as varray(2) of varchar2(10);
/
Create table test54(id number,name varray_name_type);
-- nested table type
Create type name_type as object(first_name varchar2(10),last_name varchar2(10));
/
Create type nested_table_name_type as table of name_type;
/
Create table test55(id number,name nested_table_name_type) nested table name store as nested_name;
-- XML type
Create table test56(id number,name sys.xmltype);


2、插入数据:

将下列语句保存为SQL脚本,在源数据库端执行,检查目标端数据变化情况:
insert into test11 values(1,11111);
insert into test12 values(1,11111);
insert into test13 values(1,11111);
insert into test14 values(1,11111);
insert into test15 values(1,11111);
insert into test16 values(1,30.5f);
insert into test17 values(1,48.7d);

insert into test21 values(1,'a');
insert into test22 values(1,'a');
insert into test23 values(1,'a');
insert into test24 values(1,'a');
insert into test25 values(1,'a');
insert into test26 values(1,'AAAAAAAAABBBBBBBBB');
insert into test27 values(1,'AAAAAAAAABBBBBBBBB');

insert into test31 values(1,'1111100000');
insert into test32 values(1,'1111100000');

insert into test41 values(1,sysdate);
insert into test42 values(1,sysdate);
insert into test43 values(1,sysdate);
insert into test44 values(1,sysdate);
insert into test45 values(1,interval '30' year);
insert into test46 values(1,interval '30' day);

insert into test51 values(1,empty_clob());
update test51 set name='hello this is a test' where id=1;
insert into test52 values(1,empty_clob());
update test52 set name='how do you do' where id=1;
insert into test53 values(1,empty_blob());
update test53 set name='1111100000' where id=1;

insert into test54 values(1,varray_name_type('hello','hi'));
insert into test55 values(1,nested_table_name_type(name_type('Steve','White'),Name_type('John','Black')));
insert into test56 values(1,sys.xmltype.createXML('on'));
commit;

3、更新数据:

将下列语句保存为SQL脚本,在源数据库端执行,检查目标端数据变化情况:
update test11 set name=22222 where id=1;
update test12 set name=22222 where id=1;
update test13 set name=22222 where id=1;
update test14 set name=22222 where id=1;
update test15 set name=22222 where id=1;
update test16 set name=40.5f where id=1;
update test17 set name=58.7d where id=1;

update test21 set name='b' where id=1;
update test22 set name='b' where id=1;
update test23 set name='b' where id=1;
update test24 set name='b' where id=1;
update test25 set name='b' where id=1;
update test26 set name='BBBBBBBBBAAAAAAAAA' where id=1;
update test27 set name='BBBBBBBBBAAAAAAAAA' where id=1;

update test31 set name='0000011111' where id=1;
update test32 set name='0000011111' where id=1;

update test41 set name=sysdate-1 where id=1;
update test42 set name=sysdate-1 where id=1;
update test43 set name=sysdate-1 where id=1;
update test44 set name=sysdate-1 where id=1;
update test45 set name=interval '60' year where id=1;
update test46 set name=interval '60' day where id=1;

update test51 set name='this is a test' where id=1;
update test52 set name='do you do' where id=1;
update test53 set name='0000011111' where id=1;

update test54 set name=varray_name_type('hi','hello') where id=1;
update test55 set name=nested_table_name_type(name_type('White','Steve'),Name_type('Black','Jone')) where id=1;
update test56 set name=sys.xmltype.createXML('come') where id=1;
commit;

4、删除数据:

将下列语句保存为SQL脚本,在源数据库端执行,检查目标端数据变化情况:
delete from test11 where id=1;
delete from test12 where id=1;
delete from test13 where id=1;
delete from test14 where id=1;
delete from test15 where id=1;
delete from test16 where id=1;
delete from test17 where id=1;

delete from test21 where id=1;
delete from test22 where id=1;
delete from test23 where id=1;
delete from test24 where id=1;
delete from test25 where id=1;
delete from test26 where id=1;
delete from test27 where id=1;

delete from test31 where id=1;
delete from test32 where id=1;

delete from test41 where id=1;
delete from test42 where id=1;
delete from test43 where id=1;
delete from test44 where id=1;
delete from test45 where id=1;
delete from test46 where id=1;

delete from test51 where id=1;
delete from test52 where id=1;
delete from test53 where id=1;
delete from test54 where id=1;
delete from test55 where id=1;
delete from test56 where id=1;
commit;

5、merge语句

1)在源数据库端和目标数据库端分别用下列语句创建test_from表和test_to表:
create table test_from(id number,name varchar2(10));
create table test_to(id number,name varchar2(10));
2)在源端数据库执行下列操作:
insert into test_from values(1,'a');
insert into test_from values(2,'b');
insert into test_to values(2,'c');
commit;
3)在源数据库端和目标数据库端检查test_from和test_to表数据情况:
select * from test_from;
select * from test_to;
4)在源数据库端执行下列操作:
merge into test_to using test_from on (test_from.id=test_to.id) when matched then update set test_to.name=test_from.name when not matched then insert values(test_from.id,test_from.name);
commit;
5)在源数据库端和目标数据库端检查test_from和test_to表数据情况:
select * from test_from;
select * from test_to;

6、分区表DML测试

1)在源数据库端和目标数据库端分别用下列语句创建test_partition_table表:
create table test_partition_table(id number,week_no number(2)) partition by range (week_no) (partition jan values less than(4),partition feb values less than(8),partition others values less than(maxvalue)) enable row movement;
2)在源数据库端执行下列DML操作,每一次commit时都查看目标端数据库数据变化情况:
insert into test_partition_table values(1,2);
insert into test_partition_table values(2,6);
insert into test_partition_table values(3,10);
commit;
update test_partition_table set week_no=3 where id=1;
commit;
delete from test_partition_table where id=1;
commit;
3)测试row movement场景,在源数据库端执行,查看目标数据库端数据变化情况:
update test_partition_table set week_no=9 where id=2;
commit;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26194851/viewspace-719192/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-08-21

  • 博文量
    96
  • 访问量
    442226