Troubleshooting OGG-03517 errors

Troubleshooting OGG-03517 errors

Environment Introduction:

Source end: sybase Database, server Character Set iso_1, data Character Set cp936;

Target end: Oracle11g, Character Set AL32UTF8.

Replicat process error:

ERROR OGG-03517 Conversion from character set zhs16gbk of source column CYXM to character set UTF-8 of target column CYXM failed because the source column contains a character that is not available in the target character set.

Solution Process:

At first, I encountered this problem. What's the ghost? Is the character conversion error in the CYXM field? No, logdump looked at the data and went to the source database to see the data. Goldengate is crazy. All right, I will use the parameter SOURCECHARSET PASSTHRU based on previous experiences. A restart is good. The process does not report errors, and the database data is garbled ~ Haha. Go to the official introduction, the original sourcecharset passthru this parameter is to I declare the source character set parameters (ZHS16GBK) removed, directly to the source server character set (ISO-8859-1) data inserted in, no wonder it will be garbled. It seems that this parameter cannot be used. I have searched du Niang for many times (not using Google yet). The reason for this problem may be:

1. The relationship between the cp936 Character Set of sybase and the zhs16gbk Character Set of oracle is a superset and a subset;

2. Encoding Problems exist in zhs16gbk and UTF-8 character conversion.

Therefore, I have no solutions for character conversion. But I suddenly thought, Can I skip this character conversion error? Go to the Goldengate group and ask:

Guangzhou-tan () 11:28:33 Excuse me, the replicat process of Goldengate is set to have an error record in the discart file, but this time a OGG-03517 error occurs, it is Abend directly, the error report is a field character set Conversion error in this record. I checked that it should be the reason for the Chinese Character Set superset and subset. However, currently, Oracle's Character Set supports only Chinese characters, so I want it to record the discard file without Abend when it encounters a similar error. Can it be implemented? Wind () 11:30:24 Yes Guangzhou-tan () 11:30:34 How can I set it? PONG () 11:30:38 You can use this parameter. Wind () 11:33:45 Reperror (-03517, discard) Guangzhou-tan () 11:35:46 @ Can I write multiple reperror parameters in the wind? Wind () 11:36:06 Yes. Guangzhou-tan () 11:36:21 Reperror (OGG-03517, discard) Reperror (Default, discard) Write like this? Wind () 11:36:23 Only two errors are allowed. Wind () 11:36:59 Reperror (-03517, discard) Write this statement Guangzhou-tan () 11:37:42 Whether to write two reperror or one reperror Wind () 11:37:53 Two Write different error numbers. Guangzhou-tan () 11:38:16 Thank you @ wind @ PONG

Then I tried to use the REPERROR parameter to capture this error through the discard file, so that the REPLICATE process will not be ABEND. The REPERROR usage is as follows:

REPERROR { ({DEFAULT | DEFAULT2 | SQL_error | user_defined_error},{ABEND | DISCARD | EXCEPTION | IGNORE |RETRYOP [MAXRETRIES n] |TRANSABORT [, MAXRETRIES] [, DELAYSECS n | DELAYCSECS n] |TRANSDISCARD |TRANSEXCEPTION}) |RESET }

After adding the prm file to the REPERROR parameter, I happily started the replicat process. Then I went to du Niang and found that REPERROR can capture errors, but OGG errors cannot be captured ~~~~. Then I searched again, and finally found it in the oracle official documentation:

REPLACEBADCHAR Valid Extract and Replicat Description UseREPLACEBADCHARParameter to control the response of the process when a valid code point does not exist for either the source or target character set when mapping character-type columns. by default, the check for invalid code points is only available med when the source and target databases have different character sets, and the default response is to abend. you can useFORCECHECKOption to force the process to check for invalid code points when the source and target databases have the same character set.REPLACEBADCHARApplies globally. Default ABORT Syntax REPLACEBADCHAR {ABORT | SKIP | ESCAPE | SUBSTITUTE string | NULL | SPACE} [FORCECHECK] [NOWARNING]

My god, I seem to have seen the savior. If you do, you will be used.

I add the following to the prm file:

REPLACEBADCHAR SKIP NOWARING

Purpose: When a character conversion error occurs for some fields, skip this record and do not issue the WARING information.

Note: although the official explanation says: if you use the skip option, data inconsistency may occur. However, after using this parameter, the process does not report an error and the data is consistent. I wonder why it looks like this. However, at least not because of this character conversion problem, the process can synchronize data in all tables in real time. However, you still need to pay attention to the table with incorrect character conversion to check whether the data is consistent. If the data is consistent, no bird is needed. If they are inconsistent, you can only troubleshoot the problem again and find a solution.

By the way, as to why I use the NOWARING option, I like it. I'm happy!

 

Direct Solution (people who don't want to read me a lot of nonsense ):

REPLACEBADCHAR SKIP NOWARING