OGG同步字符集从US7ASCII to ZHS16GBK故障解决
系统环境
源端：
aix 5.3
oracle 9.2.0.7 for rac
IP 192.168.124.1
ogg 11.1.1.1.2
字符集 US7ASCII
目标端：
Red hat 6.5
oracle 11.2.0.4
iP 192.168.17.188
Ogg 12.1.2.1.0
字符集 ZHS16GBK
1.初始化（略）
注意：1.使用exp/imp初始化 需要手动修改DMP文件头字符集为ZHS16GBK。
2.如果使用SCN导入导出错，可以不用SCN而直接配合handlecollisions参数保证数据正确
3.嫌exp/imp初始化麻烦可以使用OGG直接初始化
2.源端配置以操作
GGSCI (GCQ_FWSK_DB_01) 97> view params ex1
extract ex1
exttrail /backup/ex
setenv (NLS_LANG=AMERICAN_AMERICA.US7ASCII)
userid oggadmin,password oggadmin123
gettruncates
reportcount every 30 minutes,rate
--setenv (NLS_LANG = AMERICAN_AMERICA.US7ASCII)
tranlogoptions ,dblogreaderbufsize 524288
eofdelay 3
tranlogoptions bufsize 524288
flushsecs 3
tranlogoptions logretention disabled
discardfile dirrpt/ex1.dsc,append,megabytes 1024
discardrollover AT 3:00
warnlongtrans 2h,checkinterval 3m
dynamicresolution
dboptions allowunusedcolumn
fetchoptions nousesnapshot
fetchoptions fetchpkupdatecols
--tranlogoptions convertucs2clobs
--threadoptions maxcommitpropagationdelay 60000 iolatency 60000
table htjs.cb_czrz;
GGSCI (GCQ_FWSK_DB_01) 98> view params dp1
extract dp1
userid oggadmin,password oggadmin123
rmthost 192.168.17.188,mgrport 7809,compress
passthru
numfiles 5000
dynamicresolution
rmttrail dirdat/rx
table htjs.cb_czrz;
GGSCI (GCQ_FWSK_DB_01) 89> start ex1
Sending START request to MANAGER ...
EXTRACT EX1 starting
GGSCI (GCQ_FWSK_DB_01) 90> info all
Program Status Group LagTime Since Chkpt
MANAGER RUNNING
EXTRACT ABENDED DP100:00:00 01:56:08
EXTRACT RUNNING EX100:23:19 00:00:39
GGSCI (GCQ_FWSK_DB_01) 95> start dp1
Sending START request to MANAGER ...
EXTRACT DP1 starting
GGSCI (GCQ_FWSK_DB_01) 96> info all
Program Status Group LagTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP100:00:00 00:00:00
EXTRACT RUNNING EX100:18:08 00:00:01
3.目标端配置以及操作
GGSCI (cktstest2 as oggadmin@cktstest) 85> start rt2
Sending START request to MANAGER ...
REPLICAT RT2 starting
GSCI (cktstest2 as oggadmin@cktstest) 86> view params rt2
replicat rt2
setenv (NLS_LANG=AMERICAN_AMERICA.US7ASCII)
userid oggadmin,password oggadmin123
--setenv (NLS_LANG = AMERICAN_AMERICA.US7ASCII)
SOURCECHARSET US7ASCII
sourcedefs dirdef/cb.prm
dboptions deferrefconst
gettruncates
report AT 06:00
reportcount every 30 minutes,rate
reportrollover AT 02:00
reperror default,abend
handlecollisions
allownoopupdates
discardfile dirrpt/repsa.dsc,append,megabytes 1024M
--REPLACEBADCHAR SKIP NOWARNING
discardrollover AT 02:00
assumetargetdefs
map htjs.cb_czrz,target test.cb_czrz;
日志：
...............
-06-15 10:35:03 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, rt2.prm: REPLICAT RT2 started.
-06-15 10:35:03 INFO OGG-03507 Oracle GoldenGate Delivery for Oracle, rt2.prm: The source database character set is us7ascii as specified by SOURCECHARSET.
-06-15 10:35:03 INFO OGG-01020 Oracle GoldenGate Delivery for Oracle, rt2.prm: Processed extract process RESTART_ABEND record at seq 3, rba 1110 (aborted 0 records).
-06-15 10:35:03 INFO OGG-06505 Oracle GoldenGate Delivery for Oracle, rt2.prm: MAP resolved (entry htjs.cb_czrz): map "HTJS"."CB_CZRZ",target test.cb_czrz.
-06-15 10:35:04 INFO OGG-06511 Oracle GoldenGate Delivery for Oracle, rt2.prm: Using following columns in default map by name: SWJG_DM, BSYF, BS_NSRSBH, CZLX, CZSM, CZSJ, CZY_DM, CZY_MC, KPJH.
-06-15 10:35:04 INFO OGG-06510 Oracle GoldenGate Delivery for Oracle, rt2.prm: Using the following key columns for target table TEST.CB_CZRZ: CZSJ, CZY_DM, SWJG_DM.
-06-15 10:35:04 INFO OGG-03010 Oracle GoldenGate Delivery for Oracle, rt2.prm: Performing implicit conversion of column data from character set us7ascii to zhs16gbk.
-06-15 10:35:04 ERROR OGG-03517 Oracle GoldenGate Delivery for Oracle, rt2.prm: Conversion from character set us7ascii of source column CZSM to character set zhs16gbk of target column CZSM failed because the source column contains a character that is not available in the target character set.
-06-15 10:35:04 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rt2.prm: PROCESS ABENDING.
4.出现问题描述
问题1. 中文Conversion失败 ERROR OGG-03517或者中文乱码。
问题2. lag 抽取进程 延时很长,一直追不上redo. info ex1 很久不变（但很长时候也会变）
问题1解决方法 方法a.在复制进程修改参数如下 --map htjs.cb_czrz,target test.cb_czrz;
map htjs.cb_czrz,target test.cb_czrz COLCHARSET(PASSTHRU,CZSM,CZY_MC);
不转换指定的含中文列，因为ZHS16GBK 是 US7ASCII的超集
方法b.在复制进程填加如下参数：
SOURCECHARSET PASSTHRU 后面测试发现这相参数也是解决此问题（直接插入数据）。
方法c.在复制进程修改如下参数： setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK) 不用a,b 两参数也行（OGG11中没有a,b两参数）
特别注意在终端sqlplus里查询验证数据前的步骤是：
1.设置终端比如（XSHELL）字符集为Chinses Simplified(GBK)
2.设置查询客户端字符集export NLS_LANG= AMERICAN_AMERICA.ZHS16GBK
3.进入sqlplus查询数据 sqlplus user/passwd
问题2解决方法
在抽取进程增加参数如下
tranlogoptions rawdeviceoffset 0
aix raw设备专用。
5.新增加问题ogg-01028
-06-15 17:34:08 ERROR OGG-01028 Oracle GoldenGate Capture for Oracle, ex1.prm: encountered commit SCN 3610.2269893813 (15507101832373) that is not greater than the highest SCN already processed 3610.2269894305 (15507101832865) Redo Thread 1 (1) xid 29.16.1480870 (0x001d.0010.001698a6), starting seq.rba 40192.62254608, scn 3610.2269893812 (15507101832372), commit seq.rba 40192.62254608 commit timestamp -06-15 17:34:00.000000.
-06-15 17:34:10 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, ex1.prm: PROCESS ABENDING.
解决方法在抽取进程增加参数如下
threadoptions maxcommitpropagationdelay 60000 iolatency 60000
由于rac时钟不同步引起, 因为OGG是通过本地时间和事物提交时间来做关键决策
7.完整配置信息
GGSCI (GCQ_FWSK_DB_01) 1> view params ex1
extract ex1
exttrail /backup/ex
setenv (NLS_LANG=AMERICAN_AMERICA.US7ASCII)
userid oggadmin,password oggadmin123
gettruncates
tranlogoptions rawdeviceoffset 0
reportcount every 30 minutes,rate
--setenv (NLS_LANG = AMERICAN_AMERICA.US7ASCII)
--tranlogoptions ,dblogreaderbufsize 524288
eofdelay 3
--tranlogoptions bufsize 524288
flushsecs 3
--tranlogoptions logretention disabled
discardfile dirrpt/ex1.dsc,append,megabytes 1024
discardrollover AT 3:00
warnlongtrans 2h,checkinterval 3m
dynamicresolution
dboptions allowunusedcolumn
--trace dirtmp/ext.trc
--trace2 dirtmp/ext.trc2
fetchoptions nousesnapshot
fetchoptions fetchpkupdatecols
--tranlogoptions convertucs2clobs
threadoptions maxcommitpropagationdelay 60000 iolatency 60000
table htjs.cb_czrz;
GGSCI (GCQ_FWSK_DB_01) 2> view params dp1
extract dp1
userid oggadmin,password oggadmin123
rmthost 192.168.17.188,mgrport 7809,compress
passthru
numfiles 5000
dynamicresolution
rmttrail dirdat/rx
table htjs.cb_czrz;
GGSCI (cktstest2) 1> view params rt2
replicat rt2
setenv (NLS_LANG=AMERICAN_AMERICA.US7ASCII)
--setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid oggadmin,password oggadmin123
--SOURCECHARSET US7ASCII
--sourcedefs dirdef/cb.prm
dboptions deferrefconst
--dboptions_noautomaticsequenceflush
gettruncates
report AT 06:00
reportcount every 30 minutes,rate
reportrollover AT 02:00
reperror default,abend
handlecollisions
allownoopupdates
discardfile dirrpt/repsa.dsc,append,megabytes 1024M
--REPLACEBADCHAR SKIP NOWARNING
discardrollover AT 02:00
assumetargetdefs
map htjs.cb_czrz,target test.cb_czrz COLCHARSET(PASSTHRU,CZSM,CZY_MC);
--map htjs.cb_czrz,target test.cb_czrz COLCHARSET(PASSTHRU,*);
--map htjs.cb_czrz,target test.cb_czrz;
