For example, source database character set and nchar characterset is WE8ISO8859P15 and target database character set and nchar characterset is UTF8.
Data replication of special characters are not converted correctly or compare/repair are not working correctly.
Parameters are not set correctly
Make sure you have the following set up for correct data replication and compare/repair to function correctly.
Source:
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15
restart shareplex sp_cop
No special parameters need to be set on source for replication to work correctly.
To allow compare repair to work across different character sets, the following parameters must be set on the source machine. In a future version after 7.6, Shareplex will automatically detect this based on post parameter setting and handle it correctly.
sp_ctrl> set param SP_DEQ_CHARACTERSET UTF8
sp_ctrl> set param SP_DEQ_NCHAR_CHARACTERSET UTF8
sp_ctrl> set param SP_DEQ_NLS_LANG_SELECTOR 2
Target:
export NLS_LANG=AMERICAN_AMERICA.UTF8
restart sp_cop and set SP_OPO_CHARACTERSET and SP_OPO_NCHAR_CHARACTERSET to source characterset so that oracle will perform data conversion correctly for replication.
sp_ctrl> stop post
sp_ctrl> set param SP_OPO_CHARACTERSET WE8ISO8859P15
sp_ctrl> set param SP_OPO_NCHAR_CHARACTERSET WE8ISO8859P15
sp_ctrl> start post
There is no need to set any parameters for compare/repair on target.
To verify whether the data is replicated or converted correctly, you can query the data from TOAD.
or dump the column using sqlplus
Source:
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15
sqlplus to source database
select dump(column) from table where keycol=xxxx;
Target:
export NLS_LANG=AMERICAN_AMERICA.UTF8
sqlplus to target database
select dump(column) from table where keycol=xxxx
1. Shareplex does not perform character set conversion, oracle performs character set conversion. You can choose TO USE or SKIP oracle character set conversion.
By setting shareplex parameter SP_OPO_CHARACTERSET to the target database character set, this will bypass oracle character set conversion.
If you want oracle conversion to take place, you should set SP_OPO_CHARACTERSET to the source database character set.
If you have NCHAR, NVARCHAR2, and NCLOB or if you are replicating LOB data and source and target NCHAR charactersets are different, then you must set SP_OPO_NCHAR_CHARACTERSET to source NCHAR characterset on target.
2. Source character set should be a subset of target database character set. If both source and target are superset, then only common characters maybe converted correctly. For example, WE8ISO8859P15 and UTF8 are both superset. Therefore, only common characters will be converted correctly and not all characters in WE8ISO8859P15 maybe converted correctly to UTF8. If you are replicating from US7ASCII to UTF8, then any character with code point above 127 in US7ASCII will fail. When you insert data into US7ASCII database, Oracle actually allow code point 128-255 (no validation) to be saved but it will not be converted correctly to UTF8.
3. You should always use csscan utility to find out if current data on source can be converted correctly to the target database character set.
