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.

db2平臺下oracle goldengate配置支援truncate操作測試記錄

相關文章