最近被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.|

全文完

ITPub博客

OGG复制与加密字段的冲突

最新文章