db2平台下oracle goldengate配置支持truncate操作测试记录
1、测试环境:
os:suse linux 10
db:db2 9.7.5
gg:goldengate 11.1.1.0.0
2、测试目的:
源端执行truncate操作,目的端进程报错(参见本文附录部分内存),即默认不支持truncate操作;
测试源端加上参数GETTRUNCATES、目的端加上参数IGNORETRUNCATES后,会忽略掉源端的truncate操作,进程不再报错;
即支持truncate操作:源端执行truncate,目的端也需要执行一次truncate,确保两端数据一致。
测试通过后,调整生产环境中的goldengate配置。
3、测试时间:
2013年3月1日
4、测试记录:
4.1、源端插入4条记录:
db2inst1@test2:~> db2 "insert into db2inst1.test14 values('1','test')"
DB20000I The SQL command completed successfully.
db2inst1@test2:~> db2 "insert into db2inst1.test14 values('2','test')"
DB20000I The SQL command completed successfully.
db2inst1@test2:~> db2 "insert into db2inst1.test12 values('1','test')"
DB20000I The SQL command completed successfully.
db2inst1@test2:~> db2 "insert into db2inst1.test12 values('2','test')"
DB20000I The SQL command completed successfully.
4.2、正常GG同步到目的端:
db2inst1@test1:~> db2 "select * from db2inst1.test14"
ID NAME
----------- ----------
1 test
2 test
2 record(s) selected.
db2inst1@test1:~> db2 "select * from db2inst1.test12"
ID NAME
----------- ----------
1 test
2 test
2 record(s) selected.
4.3、添加GG参数支持truncate操作:
源端进程停止后编辑添加
GETTRUNCATES
目的端进程停止后编辑添加
IGNORETRUNCATES
4.4、源端执行truncate操作
db2 "LOAD FROM /dev/null OF DEL REPLACE INTO db2inst1.test14 NONRECOVERABLE"
db2 "LOAD FROM /dev/null OF DEL REPLACE INTO db2inst1.test12 NONRECOVERABLE"
check约束(先父表后子表)
db2inst1@test2:~> db2 "SET INTEGRITY FOR db2inst1.test14 IMMEDIATE CHECKED"
DB20000I The SQL command completed successfully.
db2inst1@test2:~> db2 "SET INTEGRITY FOR db2inst1.test12 IMMEDIATE CHECKED"
DB20000I The SQL command completed successfully.
4.5、启动进程
GGSCI (test2) 11> start extdatat
Sending START request to MANAGER ...
EXTRACT EXTDATAT starting
GGSCI (test2) 12> start dpedatat
Sending START request to MANAGER ...
EXTRACT DPEDATAT starting
GGSCI (test2) 13> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPEDATAT 00:00:00 00:07:55
EXTRACT RUNNING EXTDATAT 00:11:54 00:00:05
REPLICAT STOPPED REPGT 00:00:00 1726:51:43
GGSCI (test1) 15> start reptest
Sending START request to MANAGER ...
REPLICAT REPTEST starting
GGSCI (test1) 16> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPEGT 00:00:00 1726:52:44
EXTRACT STOPPED EXTGT 00:00:00 1726:52:49
REPLICAT RUNNING REPTEST 00:08:29 00:00:01
4.6、目的端检查数据:
db2inst1@test1:~> db2 "select * from db2inst1.test14"
ID NAME
----------- ----------
1 test
2 test
2 record(s) selected.
db2inst1@test1:~> db2 "select * from db2inst1.test12"
ID NAME
----------- ----------
1 test
2 test
2 record(s) selected.
4.7、目的端执行truncate
db2inst1@test1:~> db2 "LOAD FROM /dev/null OF DEL REPLACE INTO db2inst1.test14 NONRECOVERABLE"
db2inst1@test1:~> db2 "LOAD FROM /dev/null OF DEL REPLACE INTO db2inst1.test12 NONRECOVERABLE"
db2inst1@test1:~> db2 "SET INTEGRITY FOR db2inst1.test14 IMMEDIATE CHECKED"
DB20000I The SQL command completed successfully.
db2inst1@test1:~> db2 "SET INTEGRITY FOR db2inst1.test12 IMMEDIATE CHECKED"
DB20000I The SQL command completed successfully.
db2inst1@test1:~>
4.8、检查数据
db2inst1@test2:~> db2 "select * from db2inst1.test14"
ID NAME
----------- ----------
0 record(s) selected.
db2inst1@test2:~> db2 "select * from db2inst1.test12"
ID NAME
----------- ----------
0 record(s) selected.
db2inst1@test1:~> db2 "select * from db2inst1.test14"
ID NAME
----------- ----------
0 record(s) selected.
db2inst1@test1:~> db2 "select * from db2inst1.test12"
ID NAME
----------- ----------
0 record(s) selected.
db2inst1@test1:~>
5、测试结果:
源端加上参数GETTRUNCATES、目的端加上参数IGNORETRUNCATES后,会忽略掉源端的truncate操作,进程不再报错。
即支持truncate操作:源端执行truncate,目的端也执行一次truncate,确保两端数据一致。
6、附录:报错信息
2013-03-01 09:12:24 WARNING OGG-01425 Missing context item number 2, 'ErrSQLErrorCode', for message issued from line 15544 of '/mn
t/ecloud/workspace/Build_OpenSys_r11.1.1.0.0_078_[37283]/perforce/src/app/er/rep.c'.
2013-03-01 09:12:24 WARNING OGG-01425 Missing context item number 3, 'ErrSQLErrorText', for message issued from line 15544 of '/mn
t/ecloud/workspace/Build_OpenSys_r11.1.1.0.0_078_[37283]/perforce/src/app/er/rep.c'.
2013-03-01 09:12:24 WARNING OGG-01004 Aborted grouped transaction on 'DB2INST1.TEST14', Database error 0 ().
2013-03-01 09:12:24 WARNING OGG-01003 Repositioning to rba 1479 in seqno 14.
2013-03-01 09:12:24 WARNING OGG-01151 Error mapping from DB2INST1.TEST14 to DB2INST1.TEST14.
2013-03-01 09:12:24 WARNING OGG-01003 Repositioning to rba 1479 in seqno 14.
Source Context :
SourceModule : [er.main]
SourceID : [/mnt/ecloud/workspace/Build_OpenSys_r11.1.1.0.0_078_[37283]/perforce/src/app/er/rep.c]
SourceFunction : [take_rep_err_action]
SourceLine : [15780]
ThreadBacktrace : [9] elements
: [/home/db2inst1/gg/replicat(CMessageContext::AddThreadContext()+0x26) [0x70a036]]
: [/home/db2inst1/gg/replicat(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x7b2) [0
x700ad2]]
: [/home/db2inst1/gg/replicat(_MSG_ERR_MAP_TO_TANDEM_FAILED(CSourceContext*, DBString<777> const&, DBStrin
g<777> const&, CMessageFactory::MessageDisposition)+0x9b) [0x6ac89b]]
: [/home/db2inst1/gg/replicat() [0x550e93]]
: [/home/db2inst1/gg/replicat() [0x5557d7]]
: [/home/db2inst1/gg/replicat() [0x559eaa]]
: [/home/db2inst1/gg/replicat(main+0xe9c) [0x55bedc]]
: [/lib64/libc.so.6(__libc_start_main+0xe6) [0x7facd9836c36]]
: [/home/db2inst1/gg/replicat(__gxx_personality_v0+0x19a) [0x4ce81a]]
2013-03-01 09:12:24 ERROR OGG-01296 Error mapping from DB2INST1.TEST14 to DB2INST1.TEST14.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21256317/viewspace-1063726/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21256317/viewspace-1063726/