ogg的字符集分析淺談

我們所熟知oracle的字符集一旦建立完畢後最好不要修改，關於oracle goldengate的字符集問題還是需要注意的，因為如果目標端和源端字符集不一致，而有些字元無法在目標端表示ogg可能無法保證資料一致性。


源庫字符集：
SQL> select value from v$nls_parameters where parameter='NLS_CHARACTERSET';


VALUE
----------------------------------------------------------------
AL32UTF8


如果這裡小魚在源端設定SETENV（NLS_LANG=“AMERICAN_AMERICA.ZHS16GBK”）去指定源端客戶端的字符集
GGSCI (dg01) 21> view params exiaoyu


extract exiaoyu
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
SETENV (ORACLE_SID="xiaoyu")
userid ogg,password ogg
dynamicresolution
gettruncates
report at 2:00
reportrollover at 3:00
warnlongtrans 3h,checkinterval 10m
exttrail ./dirdat/dd
table xiaoyu.*;
table xiaoyugg.*;


來看看對應的extract程式的報告，發現此時ogg發覺源端客戶端的NLS_LANG變數和源端資料庫字符集不一致，從而選擇源端資料庫字符集，並沒有根據extract程式引數中的SETENV指定。
GGSCI (dg01) 52> view report exiaoyu


** Running with the following parameters **
***********************************************************************


2013-06-04 04:50:27 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
extract exiaoyu
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
Set environment variable (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
SETENV (ORACLE_SID="xiaoyu")
Set environment variable (ORACLE_SID=xiaoyu)
userid ogg,password ***


2013-06-04 04:50:28 INFO OGG-03500 WARNING: NLS_LANG environment variable does not match database character set, or not set. Using database character set value of AL32UTF8.


[oracle@ogg 11.2]$ oggerr 3500
03500, 00000, "WARNING: NLS_LANG environment variable does not match database character set, or not set. Using database character set value of {0}"
// *{0}: nls_charset (String)
// *Cause: The NLS_LANG environment variable is not set to the same as the
// database character set. Oracle GoldenGate is using the database
// character set.
// *Action: None
看來源端設定NLS_LANG跟oracle database的字符集不一致時，ogg還是會選擇oracle database的字符集，而忽略掉extract的程式引數SETEVN NLS_LANG


接下來測試目標端：
這裡也指定SETENV（NLS_LANG=”AMERICAN_AMERICA.ZHS16GBK”）
GGSCI (ogg.single) 15> view params rxiaoyu


replicat rxiaoyu
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
SETENV (ORACLE_SID="xiaoyu")
userid ogg,password ogg
assumetargetdefs
gettruncates
report at 2:00
reportrollover at 3:00
discardfile ./dirrpt/discard_rxiaoyu.dsc,append,megabytes 100
map xiaoyu.xiaoyu10,target xiaoyu.xiaoyu10,filter(@getenv("transaction","csn")>1074454806);
map xiaoyu.*,target xiaoyu.*;
map xiaoyugg.*,target ogg.*;


觀察目標端的replicat程式，發現ogg選擇了程式引數中SETENV（NLS_LANG=“AMERICAN_AMERICA.ZHS16GBK”）
GGSCI (ogg.single) 17> view report rxiaoyu
。。。
2013-06-05 03:14:14 WARNING OGG-03504 NLS_LANG character set ZHS16GBK on the target is different from the source database character set AL32UTF8. Replication may not be valid if the source data has an incompatible character for the target NLS_LANG character set


此時ogg給出的提示需要在replicat程式中正確設定SETENV NLS_LANG變數，這裡源端傳遞的是AL32UTF8字符集，目標端透過replicat程式引數SETENV NLS_LANG指定的是ZHS16GBK，而ogg也採用了replicat程式的引數，並沒有選擇源端的字符集。
[oracle@ogg 11.2]$ oggerr 3504
03504, 00000, "NLS_LANG character set {0} on the target is different from the source database character set {1}. Replication may not be valid if the source data has an incompatible character for the target NLS_LANG character set."
// *{0}: nls_lang_charset (String)
// *{1}: src_db_charset (String)
// *Cause: The NLS_LANG environment variable on the target is set to a
// different character set than the character set of the source
// database.
// *Action: Set the NLS_LANG environment variable on the target to the
// character set of the source database that is shown in the message.
// You can use the SETENV parameter in the Replicat parameter file to
// set it for the Replicat session.


而ogg報出的3504警告是為了提醒目標端字符集和源端不一致，可能會引起replicat程式異常，這裡ogg也推薦在replicat程式中設定NLS_LANG使目標端和源端一致。


那麼對於字符集對ogg的影響就是源端和目標端，如果源端和目標端database字符集一直，這裡在程式中直接採用一致的SETENV NLS_LANG都等於預設的資料庫字符集即可，而對於源端和目標端字符集不一致的，則需要在目標端手動指定replicat程式引數SETENV NLS_LANG等於源端字符集，當然對於最後在資料庫中資料行小魚認為還是需要再次轉化成目標端oracle database的字符集。（ogg也是一個同步複製產品，其技術原理依然不能脫離oracle database）