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

 

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.