最近被OGG折磨夠嗆。遇到一個小問題，引發出一系列的思考。這個問題就是OGG對加密欄位的複製。
環境是這樣的，源端AIX6.1作業系統，ORACLE10.2.0.4.4雙節點RAC，OGG11.2.1.0.1版本；目標端HPUX11.31作業系統，ORACLE10.2.0.4.8雙節點RAC，OGG11.2.1.0.20。
目標端的OGG版本比源端版本高很多，是因為開始部署安裝時採用的同版本11.2.1.0.1會出現大量的ORA-01403錯誤。再確認部署步驟沒有問題後，嘗試更換該版本，問題立刻解決。回頭來看HP版版本的11.2.1.0.1，只能用bug太多來定義了。
換了新版本後，遇到了新問題，就是開篇提到的加密欄位。源庫的一張表中包含了採用oracle演算法的中文加密欄位。這個欄位在目標端會引起異常，導致replicat程式abend。從源端檢視該欄位，使用sqlplus或是toad檢視該列都是亂碼。目標端出現的卻是ORA-29275，除了加密欄位，其它欄位可以正常顯示。遇到這個問題首先想到了環境變數nls_lang或者是資料庫字符集不一致引起的。為此反覆檢視，反覆確認，多次調整環境變數...abend依舊。
後來在錯誤日誌中看到了ORA-01403錯誤，感覺有些詫異。因為源庫要修改的這條記錄，在目標端是能查詢到的。而不是通常遇到的那種，update，delete失敗是因為目標端的確沒有where字句中定位的記錄。沉思過後，為這張表加入了主鍵。從開發團隊的角度來看，一個只有一條資料的參數列不需要主鍵，但是從DBA的角度來講，主鍵對OGG複製是安全與效率的保障。和oracle的演算法不同，ogg在沒有主鍵的情況下，標識資料記錄的唯一性會將該條記錄所有列的資料合併成一個巨大的集合，將這個集合作為key傳送到目標端做資料比對。這個集合中包含了加密欄位，該加密欄位無法被識別，出現了比對錯誤，導致ORA-01403。

分析過後採取如下行動：
1，源端表新增主鍵
2，刪除原有的trandata
3，重新為該表新增trandata
4，基於當前SCN為該表做一次初始化（expdp-impdp）
5，重啟複製程式，問題解決！

重新初始化後，發下了一個細節。查詢目標端該表已經不會出現ORA-29275。關於這個錯誤多數原因是字符集或者環境變數引起的。但是我們的操作都提前設定了環境變數，沒有問題。如果非要找原因的話，有可能是對加密欄位，誇OS平臺遷移會出現意外吧？！嗯，暫且這麼說吧。

下面是遇到問題的錯誤日誌：
(零)環境介紹
源： AIX + ORACLE 10.2.0.4.4 RAC(2) + OGG 11.2.1.0.1
目標： HP + ORACLE 10.2.0.4.8 RAC(2) + OGG 11.2.1.0.20

問題描述：
源庫中symbols.FM_PARAMETER表中包含加密欄位report_info,同步到目標端dbp.FM_PARAMETER時表錯。

錯誤日誌等資訊如下：

（一）ggserr.log 報錯資訊

2015-07-16 08:33:52 INFO    OGG-00996 Oracle GoldenGate Delivery for Oracle, rt2.prm: REPLICAT RT2 started.
2015-07-16 08:33:52 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rt2.prm: No unique key is defined for table 'FM_PARAMETER'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2015-07-16 08:33:52 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rt2.prm: Aborted grouped transaction on 'DBP.FM_PARAMETER', Database error 1403 (OCI Error ORA-01403: no data found, SQL <UPDATE /*+ RESTRICT_ALL_REF_CONS */ "DBP"."FM_PARAMETER" SET "SETUP_PHASE" = :a42,"SYSTEM_PHASE" = :a43,"ERROR_LOG" = :a44,"AUDIT_LOG" = :a45,"OL_DEL_CHECK" = :a46,"DIR_DELIMITER" = :a47,"PROD_PATH" = :a48,"RPT_PATH" = :a49,"UTIL_PATH" = :a50,"SAVE_PATH" = :a51,"MSG_PATH" = :a52,"SPOOL_PATH" = :a53,"PROC_PATH" = :a54,"SYMBOLS_OWNER" = :a55,"BCK_GRND_PREFIX" = :a56,"BCK_GRND_SUFFIX" = :a57,"FORE_GRND_PREFIX" = :a58,"FORE_GRND_SUFFIX" = :a59,"PRINT_COM_1" = :a60,"PRINT_COM_2" = :a61,"RESTRICT_MENU" = :a62,"RPT_SERVER" = :a63,"VERSION_NO" = :a64,"MACHINE_NAME" = :a65,"PREVIEW_PATH" = :a66,"ONLINE_PATH_NB" = :a67,"BATCH_PATH_NB" = :a68,"ONLINE_PATH_STR_B" = :a69,"BATCH_PATH_STR_B" = :a70,"REP_NAME_STR_B" = :a71,"REP_NAME_STR_NB" = :a72,"DIST_PHYSICAL_PATH" = :a73,"DIST_VIRTUAL_PATH" = :a74,"CONTINUOUS_RUN" = :a75,"COORDINATOR_REFRESH" = :a76,"REPORT_INFO" = :a77,"LOCAL_SERVER_PRINT_IND" = :a78,"GL_PATH" = :a79,"AML_PATH" = :a80,"MAX_EXTRACT_TIME" = :a81,"FRESH_TIME" = :a82,"SPLIT_EXTRACT_TIME" = :a83 WHERE "SETUP_PHASE" = :b0 AND "SYSTEM_PHASE" = :b1 AND "ERROR_LOG" = :b2 AND "AUDIT_LOG" = :b3 AND "OL_DEL_CHECK" = :b4 AND "DIR_DELIMITER" = :b5 AND "PROD_PATH" = :b6 AND "RPT_PATH" = :b7 AND "UTIL_PATH" = :b8 AND "SAVE_PATH" = :b9 AND "MSG_PATH" = :b10 AND "SPOOL_PATH" = :b11 AND "PROC_PATH" = :b12 AND "SYMBOLS_OWNER" = :b13 AND "BCK_GRND_PREFIX" = :b14 AND "BCK_GRND_SUFFIX" = :b15 AND "FORE_GRND_PREFIX" = :b16 AND "FORE_GRND_SUFFIX" = :b17 AND "PRINT_COM_1" = :b18 AND "PRINT_COM_2" is NULL AND "RESTRICT_MENU" = :b20 AND "RPT_SERVER" = :b21 AND "VERSION_NO" = :b22 AND "MACHINE_NAME" = :b23 AND "PREVIEW_PATH" = :b24 AND "ONLINE_PATH_NB" is NULL AND "BATCH_PATH_NB" is NULL AND "ONLINE_PATH_STR_B" is NULL AND "BATCH_PATH_STR_B" is NULL AND "REP_NAME_STR_B" is NULL AND "REP_NAME_STR_NB" is NULL AND "DIST_PHYSICAL_PATH" is NULL AND "DIST_VIRTUAL_PATH" is NULL AND "CONTINUOUS_RUN" = :b33 AND "COORDINATOR_REFRESH" .
2015-07-16 08:33:52 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rt2.prm: Repositioning to rba 19384119 in seqno 2.
2015-07-16 08:33:52 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rt2.prm: SQL error 1403 mapping SYMBOLS.FM_PARAMETER to DBP.FM_PARAMETER OCI Error ORA-01403: no data found, SQL <UPDATE /*+ RESTRICT_ALL_REF_CONS */ "DBP"."FM_PARAMETER" SET "SETUP_PHASE" = :a42,"SYSTEM_PHASE" = :a43,"ERROR_LOG" = :a44,"AUDIT_LOG" = :a45,"OL_DEL_CHECK" = :a46,"DIR_DELIMITER" = :a47,"PROD_PATH" = :a48,"RPT_PATH" = :a49,"UTIL_PATH" = :a50,"SAVE_PATH" = :a51,"MSG_PATH" = :a52,"SPOOL_PATH" = :a53,"PROC_PATH" = :a54,"SYMBOLS_OWNER" = :a55,"BCK_GRND_PREFIX" = :a56,"BCK_GRND_SUFFIX" = :a57,"FORE_GRND_PREFIX" = :a58,"FORE_GRND_SUFFIX" = :a59,"PRINT_COM_1" = :a60,"PRINT_COM_2" = :a61,"RESTRICT_MENU" = :a62,"RPT_SERVER" = :a63,"VERSION_NO" = :a64,"MACHINE_NAME" = :a65,"PREVIEW_PATH" = :a66,"ONLINE_PATH_NB" = :a67,"BATCH_PATH_NB" = :a68,"ONLINE_PATH_STR_B" = :a69,"BATCH_PATH_STR_B" = :a70,"REP_NAME_STR_B" = :a71,"REP_NAME_STR_NB" = :a72,"DIST_PHYSICAL_PATH" = :a73,"DIST_VIRTUAL_PATH" = :a74,"CONTINUOUS_RUN" = :a75,"COORDINATOR_REFRESH" = :a76,"REPORT_INFO" = :a77,"LOCAL_SERVER_PRINT_IND" = :a78,"GL_PATH" = :a79,"AML_PATH" = :a80,"MAX_EXTRACT_TIME" = :a81,"FRESH_TIME" = :a82,"SPLIT_EXTRACT_TIME" = :a83 WHERE "SETUP_PHASE" = :b0 AND "SYSTEM_PHASE" = :b1 AND "ERROR_LOG" = :b2 AND "AUDIT_LOG" = :b3 AND "OL_DEL_CHECK" = :b4 AND "DIR_DELIMITER" = :b5 AND "PROD_PATH" = :b6 AND "RPT_PATH" = :b7 AND "UTIL_PATH" = :b8 AND "SAVE_PATH" = :b9 AND "MSG_PATH" = :b10 AND "SPOOL_PATH" = :b11 AND "PROC_PATH" = :b12 AND "SYMBOLS_OWNER" = :b13 AND "BCK_GRND_PREFIX" = :b14 AND "BCK_GRND_SUFFIX" = :b15 AND "FORE_GRND_PREFIX" = :b16 AND "FORE_GRND_SUFFIX" = :b17 AND "PRINT_COM_1" = :b18 AND "PRINT_COM_2" is NULL AND "RESTRICT_MENU" = :b20 AND "RPT_SERVER" = :b21 AND "VERSION_NO" = :b22 AND "MACHINE_NAME" = :b23 AND "PREVIEW_PATH" = :b24 AND "ONLINE_PATH_NB" is NULL AND "BATCH_PATH_NB" is NULL AND "ONLINE_PATH_STR_B" is NULL AND "BATCH_PATH_STR_B" is NULL AND "REP_NAME_STR_B" is NULL AND "REP_NAME_STR_NB" is NULL AND "DIST_PHYSICAL_PATH" is NULL AND "DIST_VIRTUAL_PATH" is NULL AND "CONTINUOUS_RUN" = :b33 AND "COORDINATOR_REFRESH" = :b34 A.
2015-07-16 08:33:52 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rt2.prm: Repositioning to rba 19384119 in seqno 2.
2015-07-16 08:33:52 ERROR   OGG-01296 Oracle GoldenGate Delivery for Oracle, rt2.prm: Error mapping from SYMBOLS.FM_PARAMETER to DBP.FM_PARAMETER.
2015-07-16 08:33:52 ERROR   OGG-01668 Oracle GoldenGate Delivery for Oracle, rt2.prm: PROCESS ABENDING.

（二）目標端SQL PLUS報錯資訊
SYS@oradbp1 >select * from dbp.fm_parameter;
ERROR:
ORA-29275: partial multibyte character

no rows selected

（三）discard 報錯資訊，其中REPORT_INFO列為加密欄位
Problem replicating SYMBOLS.FM_PARAMETER to DBP.FM_PARAMETER
Record not found
Mapping problem with compressed key update record (target format)...
*
SETUP_PHASE = Y
SYSTEM_PHASE = INP
ERROR_LOG = Y
AUDIT_LOG = Y
OL_DEL_CHECK = Y
DIR_DELIMITER = /
PROD_PATH = /dbdata/beasym/prod/
RPT_PATH = /dbdata/beasym/rep/
UTIL_PATH = /dbdata/beasym/symutil/
SAVE_PATH = /dbdata/beasym/preview/
MSG_PATH = /dbdata/beasym/msg/
SPOOL_PATH = /dbdata/beasym/spool/
PROC_PATH = null
SYMBOLS_OWNER = SYMBOLS
BCK_GRND_PREFIX = null
BCK_GRND_SUFFIX = null
FORE_GRND_PREFIX = N
FORE_GRND_SUFFIX = N
PRINT_COM_1 = COPY/A
PRINT_COM_2 = NULL
RESTRICT_MENU = N
RPT_SERVER = http://192.168.162.30:8100
VERSION_NO = 10
MACHINE_NAME = http://192.168.164.86:7778
PREVIEW_PATH = /sympreview1/
ONLINE_PATH_NB = NULL
BATCH_PATH_NB = NULL
ONLINE_PATH_STR_B = NULL
BATCH_PATH_STR_B = NULL
REP_NAME_STR_B = NULL
REP_NAME_STR_NB = NULL
DIST_PHYSICAL_PATH = NULL
DIST_VIRTUAL_PATH = NULL
CONTINUOUS_RUN = Y
COORDINATOR_REFRESH = 10
REPORT_INFO = <Raw Data>
000000: 6a 1b 09 81 03 87 80 6e e7 ed 54 85 2c 2f cb f2 |j......n..T.,/..|
000010: ee 42 af d8 36 e7 78 12 86 ae 5c e6 bf 6f 65 56 |.B..6.x...\..oeV|
000020: 3a 9a 68 67 35 b3 90 be f0 cb a0 8d 1b 85 4e 0f |:.hg5.........N.|
000030: f2 00 a5 e0 3c 79 b6 ae 6b b1 dc ba 26 da 55 c6 |....<y..k...&.U.|
000040: b3 07 0f d4 a1 51 96 76 f7 57 cc a8 83 58 65 e8 |.....Q.v.W...Xe.|

全文完

OGG複製與加密欄位的衝突

相關文章