goldengate一些參數整理，goldengate整理

goldengate一些參數整理，goldengate整理
manager參數：AUTOSTART：指定在mgr啟動時自動啟動那些進程.AUTOSTART ER *AUTOSTART extract extsz 
AUTORESTART：指定在mgr可以定時重啟那些進程。可以在網路中斷等故障恢複後自動重起，避免人工幹預.AUTORESTART ER *, WAITMINUTES 5, RETRIES 3
DYNAMICPORTLIST：指定GoldenGate可以使用那些連接埠接受extract發送過來的資料.DYNAMICPORTLIST 7840-7850
PORT：制定GoldenGate的mgr進程使用哪個 TCP/IP連接埠偵聽請求.PORT 7809
LAGCRITICAL：指定認為超過此時間即為嚴重錯誤的延遲最大值，如果延遲達到此時間值則會在ggserr.log裡面寫入一條error資訊LAGCRITICALMINUTES 10 
LAGREPORT：指定在ggserr.log中報告延遲的時間間隔.LAGREPORTHOURS 1 
PURGEOLDEXTRACTS :定義自動刪除過時的隊列以節省硬碟空間。一般按照兩個規則來刪除：首先，要滿足檢查點要求，沒有使用過的隊列不能刪除，保證無資料丟失；其次，可以保留一定的天數。只有當已經使用過且超過設定的天數後的隊列會被自動刪除。 purgeoldextracts /backup/goldengate/dirdat/*,usecheckpoints, minkeepdays 7

抽取參數：CHECKPARAMS：如果加入該參數，表示下次運行只是檢查一下文法，並不實際運行進程。注意：該命令只能檢查一些簡單文法，並不能保證參數檔案是完全正確的。COMMENT：注釋行，也可以用兩個中劃線--代替.--checkparams表示本行已經被注釋掉 EXTRACT ：定義抽取進程的名字 OBEY ：可以將外部檔案的內容包含到本參數檔案中來。用於將一些可以重複利用的參數檔案部分內容隔離出來，便於統一修改。Obey tables.txtTABLEEXCLUDE ：定義所需要排除的表。如果在table裡面定義了使用萬用字元，那麼可以使用該參數定義排除掉其中的部分表。如：tableexclude ctais2.TMP_*;tableexclude ctais2.TEMPTAB; GETUPDATEAFTERS | IGNOREUPDATEAFTERS 是否在隊列中寫入後影像，預設複製 GETUPDATEBEFORES | IGNOREUPDATEAFTERS 是否在隊列中寫入前影像，預設不複製 GETUPDATES | IGNOREUPDATES 是否複製update操作，預設複製GETDELETES | IGNOREDELETES 是否複製delete操作，預設複製GETINSERTS | IGNOREINSERTS 是否複製insert操作，預設複製GETRUNCATES| IGNORETRUNCATES 是否複製truncate操作，預設不複製.RMTHOST 指定目標系統及其GoldenGate Manager進程的連接埠號碼，也用於定義是否使用壓縮排行傳輸。rmthost  99.16.1.12,mgrport 7809, compressRMTTRAIL 指定寫入到目標端的哪個隊列。 EXTTRAIL 指定寫入到本地的哪個隊列。 SQLEXEC 在extract運行時首先運行一個sql語句。sqlexec "Alter session set constraints=deferred" PASSTHRU 禁止extract與資料庫互動，適用於Data Pump傳輸進程（dpeXX）GETENV | SETENV 針對extract進程設定系統內容變數。 setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK ) REPORT 定義自動定時報告。 REPORT AT 01:59 STATOPTIONS 定義每次使用stat時統計數字是否需要重設REPORTCOUNT 報告已經處理的紀錄條數統計數字TLTRACE 開啟對於資料庫日誌的追蹤記錄檔 TRACE/TRACE2 開啟對GoldenGate進程的追蹤記錄檔，一般用於調試。 DISCARDFILE 定義discard檔案位置，如果處理中有紀錄出錯會寫入到此檔案中discardfile /oradata/goldengate/repkj.dsc,append,megabytes 100mNUMFILES 定義本extract為最大多少張表預留空間，預設為500，超過500張表需要設定一個比實際表數略大的值numfiles 3000PURGEOLDEXTRACTS 同mgr進程，可以設定自動刪除隊列，建議在mgr設定REPORTROLLOVER 設定切換一個日誌的時間和間隔 reportrollover at 02:00TRANSMEMORY 設定GoldenGate的抽取進程能夠使用的記憶體大小。如下參數指定本進程最大隻能佔用2G記憶體，其中每個事務最大佔用記憶體不能超過500M，如果超過則使用指定目錄作為虛擬記憶體，該目錄下的單個檔案大小為4G，最多隻能在該目錄下佔用8G空間作為緩衝。transmemory directory (/backup/goldengate/dirtmp,8G,4G),ram 2G,transram 500MDBOPTIONS  指定對於某種特定資料庫所需要的特殊參數。[SOURCEDB,] USERID ,PASSWORD 指定所要登陸的資料庫名稱，使用者名稱和密碼。對於oracle無需指定sourcedb，直接指定使用者名稱和密碼即可。Userid goldengate, password goldengate TRANLOGOPTIONS 指定在解析資料庫日誌時所需要的特殊參數。例如，對於裸裝置，可能需要加入下列參數tranlogoptions rawdeviceoffset 0 tranlogoptions altarchivelogdest instance sidname /arch  --指定歸檔日誌所在WARNLONGTRANS 指定對於超過一定時間的長交易可以在ggserr.log裡面寫入警告資訊。例如，每隔30分鐘檢查一次長交易，對於超過12個小時的進行警示：--warnlongtrans 12h, checkintervals 30m

複製參數：CHECKPARAMS 檢查參數文法然後停止COMMENT 注釋行REPLICAT 定義進程名稱USERID 指定登陸目標資料庫的使用者名稱和密碼OBEY 將外部檔案包含到參數檔案中ASSUMETARGETDEFS 假定兩端資料結構一致使用此參數SOURCEDEFS 假定兩端資料結構不一致，使用此參數指定源端的資料結構定義檔案。該檔案需要由GoldenGate工具產生MAPEXCLUDE 用於使用在map中使用*匹配時排除掉指定的表，類似于于源端的tablexcludemapexclude CTAIS2.JC_GY_SWWSWHMAP ctais2.* ,TARGET ctais2.*; 
GETDELETES | IGNOREDELETES 是否複製delete操作，預設為複製GETUPDATES |IGNOREUPDATES  是否複製update操作，預設為複製GETINSERTS | IGNOREINSERTS 是否複製insert操作，預設為複製GETUPDATEAFTERS | IGNOREUPDATEAFTERS 是否讀取後影像，預設為讀取GETUPDATEBEFORES | IGNOREUPDATEBEFORES 是否讀取前影像，預設為不讀取GETTRUNCATES | IGNORETRUNCATES 是否複製truncate操作，預設為不複製allownoopupdates 允許執行無實際變化的update。例如，update a=a會紀錄一條update，但是沒有後影像，無法正確構築where語句REPERROR 定義出錯以後replicat的響應，一般可以定義為兩種：Abend，即一旦出現錯誤即停止複製，此為預設配置；Discard，出現錯誤後繼續複製，只把錯誤的資料放到discard檔案中。 DISCARDFILE 定義出錯資料的輸出檔案。當資料出現錯誤後，可以用於尋找錯誤原因 discardfile /oradata/goldengate/dirrpt/repsz.dsc,append, megabytes 10HANDLECOLLISIONS 自動過濾重複時段的資料衝突，用於不能停機執行初始化。開啟該參數後不會將資料錯誤判到discard檔案中。 DYNAMICRESOLUTION 使replicat動態解析表的結構，加快啟動速度。預設為每次啟動解析所有要複製表的結構 SQLEXEC 調用預存程序或者執行sql語句。可以將返回值作為依據進行過濾條件，或者使用者改變session變數。GROUPTRANSOPS 將小的交易合并成為一個大的交易進行提交，減少提交次數，降低系統IO消耗。MAXTRANSOPS 將大交易拆分，每若干條紀錄提交一次 maxtransops 1000BATCHSQL 針對批處理中針對某個表的大批量重複操作進行最佳化，提高批處理的處理速度。 DBOPTIONS 定義與資料庫類型相關的特殊處理方式。NUMFILES 定義進程中表的最大資料量，預設為 500.PURGEOLDEXTRACTS 定義自動刪除隊列，一般建議在mgr進程配置。
錯誤定位時經常使用下面三個參數：nodynsql
nobinarychars
showsyntax
 DYNSQL | NODYNSQLValid for ReplicatUse the DYNSQL and NODYNSQL parameters to control the way that SQL statements are formed. With NODYNSQL, Replicat uses literal SQL statements with the bind variables resolved. With DYNSQL, the default, Replicat uses dynamic SQL to compile a statement once, and then execute it many times with different bind variables. ● Statement with DYNSQL:UPDATE <table> ... WHERE ID = :B● Statement with NODYNSQL: UPDATE <table> ... WHERE ID = ‘1234’In most environments, using DYNSQL yields the best efficiency and most throughput. However, in isolated instances, using NODYNSQL has proven faster and more efficient. Try NODYNSQL only if Replicat throughput appears unsatisfactory.Do not use DYNSQL when replicating to target databases that do not support dynamic SQL.When using NODYNSQL, you must also use the NOBINARYCHARS parameter。Oracle GoldenGate for MySQL does not support LOB replication in NODYNSQL mode. Default DYNSQLSyntax DYNSQL | NODYNSQL
BINARYCHARS | NOBINARYCHARSValid for Extract and ReplicatUse BINARYCHARS and NOBINARYCHARS to control whether character data is treated as binary data or null-terminated strings.BINARYCHARS, the default, maintains data the way it was entered in the source table. This ensures proper processing in cases when a column in the source or target database is defined as a character column and it is possible that binary characters could be entered into that column. BINARYCHARS is not compatible with the BULKLOAD parameter (direct-bulk load); use NOBINARYCHARS.NOBINARYCHARS can cause Oracle GoldenGate to interpret a binary character to be the end of the data in that column. If there is more data after the binary data, it is not processed by Oracle GoldenGate, compromising data integrity. NULL characters cause this to happen, as well as any character defined with the DELIMITER option of FORMATASCII. Unless there is good reason to use NOBINARYCHARS, leaving the default set to BINARYCHARS is recommended so that data is maintained the way it was entered in the source table. Before using NOBINARYCHARS, contact Oracle Support.BINARYCHARS and NOBINARYCHARS are table-specific. One parameter remains in effect for all subsequent TABLE or MAP statements until the other is encountered.Default BINARYCHARSSyntax BINARYCHARS | NOBINARYCHARS
SHOWSYNTAXValid for ReplicatUse the SHOWSYNTAX parameter to start an interactive session where you can view each Replicat SQL statement before it is applied. By viewing the syntax of SQL statements that failed, you might be able to diagnose the cause of the problem. For example, you could find out that the WHERE clause is using a non-indexed column.Requirements for using SHOWSYNTAX● The first time that you use SHOWSYNTAX, request guidance from an Oracle Support analyst. It is a debugging parameter and can cause unwanted results if used improperly. It requires manual intervention, so automated processing is suspended, and it slows down processing, which can cause backlogs and latency.● To use SHOWSYNTAX, Replicat must be started from the command shell of the operating system. Do not use SHOWSYNTAX if Replicat is started through GGSCI. ● Use SHOWSYNTAX in a test environment. Create duplicates of your Replicat groups and target tables so that the production environment is not affected. Using SHOWSYNTAX1. In the Replicat parameter file, include the following parameters in the order shown here, each on its own line:❍ NOBINARYCHARS❍ NODYNSQL❍ SHOWSYNTAXNOTE NOBINARYCHARS is an undocumented parameter that causes Oracle GoldenGate to treat binary data as a null-terminated string. Contact Oracle Support before using it. NODYNSQL causes Replicat to use literal SQL statements instead of using dynamic SQL with bind variables. 2. From the Oracle GoldenGate home directory, start Replicat from the command shell of the operating system using the syntax shown here. Do not specify a reportfile option. Output must go to screen.replicat paramfile dirprm/<Replicat_name>.prm 3. The first SQL statement is displayed with some prompts.❍ Choose Keep Displaying (the default) to execute the current statement and display the next one.❍ Choose Stop Display to resume normal processing and stop printing SQL statements to screen.4. When finished viewing syntax, remove SHOWSYNTAX, NOBINARYCHARS, and NODYNSQL from the parameter file.Default NoneSyntax SHOWSYNTAX

11.1版本的OGG Bounded Recovery BR參數可能會有很多問題，最好關閉，然後配合作業系統監控長事務，定時殺掉長事務，避免出現問題。然後配置CACHESZIE為一個值，避免影響系統效能。

tranlogoptions logretention disabledtranlogoptions dblogreaderDBLOGREADER ：(Oracle) Valid for Extract in classic capture mode.Causes Extract to use a newer ASM API that is available as of Oracle 10.2.0.5 and later 10g R2 versions, and Oracle 11.2.0.2 and later 11g R2 versions (but not in Oracle 11g R1 versions). This API uses the database server to access the redo and archive logs, instead of connecting directly to the Oracle ASM instance. The database must contain the libraries that contain the API modules and must be running.To use this feature, the Extract database user must have SELECT ANY TRANSACTIONprivilege. When used, DBLOGREADER enables Extract to use a read size of up to 4 MB in size. This is controlled with the DBLOGREADERBUFSIZE optionThe maximum read size when using the default OCI buffer is 28672 bytes. This is controlled by the ASMBUFSIZE option.A larger buffer may improve the performance of Extract when redo rate is high. When using DBLOGREADER, do not use the ASMUSER and ASMPASSWORD options of TRANLOGOPTIONS. The API uses the user and password specified with the USERID parameter。