OGG同步字符集从US7ASCII to ZHS16GBK故障解决

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       Lag           Time Since Chkpt


MANAGER     RUNNING                                           
EXTRACT     ABENDED     DP1         00:00:00      01:56:08    
EXTRACT     RUNNING     EX1         00: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       Lag           Time Since Chkpt


MANAGER     RUNNING                                           
EXTRACT     RUNNING     DP1         00:00:00      00:00:00    
EXTRACT     RUNNING     EX1         00: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;

日志：

...............

2017-06-15 10:35:03  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, rt2.prm:  REPLICAT RT2 started.
2017-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.
2017-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).
2017-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.
2017-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.
2017-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.
2017-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.
2017-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.
2017-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
2017-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 2017-06-15 17:34:00.000000.
2017-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;