莫名奇妙的乱码就这样产生了

一个客户那里做ORACLE Goldengate同步数据时发现了一个奇怪的事情，在源库oracle 10.2中数据正常，使用OGG同步到目标库（也是ORACLE 10.2）后，部分数据出现乱码，比如一共同步了100张表，其中大概十几张表出现乱码，而且这十几张表不是全部数据都乱码，比如一张表有100条数据，那么其中有几十条出现乱码，这个乱码用sqlplus是看不到的，使用JAVAUE或者PLSQL DEVELOPER等工具可以看到，例如，使用JAVAUE（OGG的一套JAVA API借口，客户自己用这个开发了类似于OGG DATAPUMP进程的工具）可以看到SQL语句为：

SELECT COUNT(*) FROM EOS.HS_FLMX WHERE PCXH = '2221191'
and XH = '1' and BNO = '1' and YY = '2012' and XMLBID = '1' and XMDM = '051510'
and YWLX = '05^G' and ZJLX = '01^H' and OCID = '141   '
and RQ = '2012-04-13:00:00:00'
and JF is null  and DF = '480.00'
and ZY = '三大方法' and FJ = '2' and YWXH = '607070' and PZID = '181542'
and JBWD = '141^Q' and ZCOCID = '151^R' and ZROCID = '141^S'and WLFZX = '150^T'
and DM = '201090101' and MM = '4' and CZWD is null
and SNCODE = '051510'

正如上面红色标注的地方，出现乱码的列并非中文，而是数字。。。。。。

于是，我对于源库和目标库都进行了dump：

源库查询结果，貌似一切正常：

 1,  SELECT ocid,DUMP(OCID,1016) FROM EOS.FL WHERE rownum<=10

1 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30   

2 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
3 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
4 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
5 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
6 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
7 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
8 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
9 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
10 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30

 

2,  SELECT ocid,DUMP(OCID,1016) FROM EOS.FL WHERE ocid NOT LIKE '%      ' and rownum<=20

1 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
2 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
3 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
4 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
5 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
6 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
7 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
8 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
9 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
10 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
11 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
12 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
13 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
14 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
15 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
16 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
17 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
18 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
19 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30
20 100 Typ=96 Len=3 CharacterSet=ZHS16GBK: 31,30,30

 

3,  SELECT ocid,DUMP(OCID,10) FROM EOS.FL WHERE ocid NOT LIKE '%      ' and rownum<=20

1 100 Typ=96 Len=3: 49,48,48
2 100 Typ=96 Len=3: 49,48,48
3 100 Typ=96 Len=3: 49,48,48
4 100 Typ=96 Len=3: 49,48,48
5 100 Typ=96 Len=3: 49,48,48
6 100 Typ=96 Len=3: 49,48,48
7 100 Typ=96 Len=3: 49,48,48
8 100 Typ=96 Len=3: 49,48,48
9 100 Typ=96 Len=3: 49,48,48
10 100 Typ=96 Len=3: 49,48,48
11 100 Typ=96 Len=3: 49,48,48
12 100 Typ=96 Len=3: 49,48,48
13 100 Typ=96 Len=3: 49,48,48
14 100 Typ=96 Len=3: 49,48,48
15 100 Typ=96 Len=3: 49,48,48
16 100 Typ=96 Len=3: 49,48,48
17 100 Typ=96 Len=3: 49,48,48
18 100 Typ=96 Len=3: 49,48,48
19 100 Typ=96 Len=3: 49,48,48
20 100 Typ=96 Len=3: 49,48,48

 

 

4,  SELECT ocid,DUMP(OCID,10) FROM EOS.FL WHERE rownum<=10

1 100 Typ=96 Len=3: 49,48,48
2 100 Typ=96 Len=3: 49,48,48
3 100 Typ=96 Len=3: 49,48,48
4 100 Typ=96 Len=3: 49,48,48
5 100 Typ=96 Len=3: 49,48,48
6 100 Typ=96 Len=3: 49,48,48
7 100 Typ=96 Len=3: 49,48,48
8 100 Typ=96 Len=3: 49,48,48
9 100 Typ=96 Len=3: 49,48,48
10 100 Typ=96 Len=3: 49,48,48

目标端查询结果：

SELECT ocid,DUMP(OCID,1016) FROM EOS.FL WHERE rownum<=10
正常数据：说明表中存在部分正常数据
1 400       Typ=96 Len=9 CharacterSet=AL32UTF8: 34,30,30,20,20,20,20,20,20
2 400       Typ=96 Len=9 CharacterSet=AL32UTF8: 34,30,30,20,20,20,20,20,20
3 400       Typ=96 Len=9 CharacterSet=AL32UTF8: 34,30,30,20,20,20,20,20,20
4 400       Typ=96 Len=9 CharacterSet=AL32UTF8: 34,30,30,20,20,20,20,20,20
5 400       Typ=96 Len=9 CharacterSet=AL32UTF8: 34,30,30,20,20,20,20,20,20
6 400       Typ=96 Len=9 CharacterSet=AL32UTF8: 34,30,30,20,20,20,20,20,20
7 400       Typ=96 Len=9 CharacterSet=AL32UTF8: 34,30,30,20,20,20,20,20,20
8 400       Typ=96 Len=9 CharacterSet=AL32UTF8: 34,30,30,20,20,20,20,20,20
9 400       Typ=96 Len=9 CharacterSet=AL32UTF8: 34,30,30,20,20,20,20,20,20
10 400       Typ=96 Len=9 CharacterSet=AL32UTF8: 34,30,30,20,20,20,20,20,20

SELECT ocid,DUMP(OCID,1016) FROM EOS.FL WHERE ocid NOT LIKE '%      '
有问题的数据，请注意，第二列每个三位数后面都有一个类似于惊叹号的东东
1 100 Typ=96 Len=9 CharacterSet=AL32UTF8: 31,30,30,13,0,a,0,0,0    查询ASCII码对照表：13代表；00代表' '（空格）； 0a代表换行
2 100 Typ=96 Len=9 CharacterSet=AL32UTF8: 31,30,30,13,0,a,0,0,0
3 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
4 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
5 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
6 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
7 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
8 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
9 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
10 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
11 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
12 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
13 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
14 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
15 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
16 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
17 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
18 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
19 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
20 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
21 200 Typ=96 Len=9 CharacterSet=AL32UTF8: 32,30,30,13,0,a,0,0,0
22 140 Typ=96 Len=9 CharacterSet=AL32UTF8: 31,34,30,13,0,a,0,0,0
23 140 Typ=96 Len=9 CharacterSet=AL32UTF8: 31,34,30,13,0,a,0,0,0

SELECT ocid,DUMP(OCID,10) FROM EOS.FL WHERE ocid NOT LIKE '%      '
1 100 Typ=96 Len=9: 49,48,48,19,0,10,0,0,0
2 100 Typ=96 Len=9: 49,48,48,19,0,10,0,0,0
3 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
4 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
5 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
6 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
7 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
8 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
9 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
10 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
11 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
12 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
13 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
14 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
15 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
16 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
17 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
18 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
19 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
20 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
21 200 Typ=96 Len=9: 50,48,48,19,0,10,0,0,0
22 140 Typ=96 Len=9: 49,52,48,19,0,10,0,0,0
23 140 Typ=96 Len=9: 49,52,48,19,0,10,0,0,0

SELECT ocid,DUMP(OCID,10) FROM EOS.FL WHERE rownum<=10
1 400       Typ=96 Len=9: 52,48,48,32,32,32,32,32,32
2 400       Typ=96 Len=9: 52,48,48,32,32,32,32,32,32   32是10进制的空格
3 400       Typ=96 Len=9: 52,48,48,32,32,32,32,32,32
4 400       Typ=96 Len=9: 52,48,48,32,32,32,32,32,32
5 400       Typ=96 Len=9: 52,48,48,32,32,32,32,32,32
6 400       Typ=96 Len=9: 52,48,48,32,32,32,32,32,32
7 400       Typ=96 Len=9: 52,48,48,32,32,32,32,32,32
8 400       Typ=96 Len=9: 52,48,48,32,32,32,32,32,32
9 400       Typ=96 Len=9: 52,48,48,32,32,32,32,32,32
10 400       Typ=96 Len=9: 52,48,48,32,32,32,32,32,32

 

这样，问题就奇怪了，我尝试抛开OGG，做两个测试：

1，EXP/IMP重新导一遍这张表

2，使用dblink直接CTAS这张表

结果发现，使用上述两个方法，结果同样是目标端出现部分数据是乱码，这个问题跟OGG没有关系！！

 

这是一个很老的系统，我无法给出合理的解释，但是感觉这个跟客户那里的历史数据有关系，猜测和疑问：

（1）或许之前使用某种工具操纵过数据（尽管还是解释不同，但是感觉这个可能就是原因，问题应该是发生在不同工具作用下的结果，或者从其他什么地方将数据导入ORACLE，而正常数据是后面在ORACLE中自然产生的）

（2）在源库中字符集是ZHS16GBK，目标库为AL32UTF8。有问题的表基本都是CHAR类型，且实际数值都是数字，比如假设源库的有问题的表某列定义为CHAR(3)），到目标库后通过字符集实际的转换后，变为CHAR(9)，这里开始我也不明白为什么CHAR3会变为CHAR9才能将数据入库（扩大了3倍，而不是正常的扩大1.5倍），后来我明白了，在源端其实还有看不见的控制字符在某个我也想不到的地方起作用，因此就有了上述所有的奇怪问题。。。

 

目前，我只能找到规律，类似有这种问题的数据，基本上CHAR(1)会在目标库上扩大为CHAR(3)才可以，其中第一个字符是正常的，后面跟着1个控制字符（也就是所谓的乱码）和1个空格；如果是CHAR3，那么需要CHAR(9)才能入库，其中后面跟着3个控制字符和3个空格；且这个规律不是全部数据（有部分数据结果正常），只是表中部分数据有这个问题。。。

 

没有时间再继续纠缠这个问题了，但是总算把OGG洗干净了，客户也认可他们的数据的确可能有隐患。。。