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.txt TABLEEXCLUDE ：定義所需要排除的表。如果在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, compress RMTTRAIL 指定寫入到目標端的哪個隊列。 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 100m NUMFILES 定義本extract為最大多少張表預留空間，缺省為500，超過500張表需要設定一個比實際表數略大的值 numfiles 3000 PURGEOLDEXTRACTS 同mgr進程，可以設置自動刪除隊列，建議在mgr設置 REPORTROLLOVER 設定切換一個日志的時間和間隔 reportrollover at 02:00 TRANSMEMORY 設定GoldenGate的抽取進程能夠使用的內存大小。如下參數指定本進程最大只能占用2G內存，其中每個事務最大占用內存不能超過500M，如果超過則使用指定目錄作為虛擬內存，該目錄下的單個文件大小為4G，最多只能在該目錄下占用8G空間作為緩存。 transmemory directory (/backup/goldengate/dirtmp,8G,4G),ram 2G,transram 500M DBOPTIONS 指定對於某種特定數據庫所需要的特殊參數。 [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中使用*匹配時排除掉指定的表，類似於於源端的tablexclude mapexclude CTAIS2.JC_GY_SWWSWH MAP 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 10 HANDLECOLLISIONS 自動過濾重復時段的數據沖突，用於不能停機執行初始化。打開該參數後不會將數據錯誤報到discard文件中。 DYNAMICRESOLUTION 使replicat動態解析表的結構，加快啟動速度。缺省為每次啟動解析所有要復制表的結構 SQLEXEC 調用存儲過程或者執行sql語句。可以將返回值作為依據進行過濾條件，或者用戶改變session變量。 GROUPTRANSOPS 將小的交易合並成為一個大的交易進行提交，減少提交次數，降低系統IO消耗。 MAXTRANSOPS 將大交易拆分，每若干條紀錄提交一次 maxtransops 1000 BATCHSQL 針對批處理中針對某個表的大批量重復操作進行優化，提高批處理的處理速度。 DBOPTIONS 定義與數據庫類型相關的特殊處理方式。 NUMFILES 定義進程中表的最大數據量，缺省為 500. PURGEOLDEXTRACTS 定義自動刪除隊列，一般建議在mgr進程配置。
錯誤定位時經常使用下面三個參數： nodynsql
nobinarychars
showsyntax
DYNSQL | NODYNSQL Valid for Replicat Use 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 DYNSQL Syntax DYNSQL | NODYNSQL
BINARYCHARS | NOBINARYCHARS Valid for Extract and Replicat Use 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 BINARYCHARS Syntax BINARYCHARS | NOBINARYCHARS
SHOWSYNTAX Valid for Replicat Use 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 SHOWSYNTAX 1. In the Replicat parameter file, include the following parameters in the order shown here, each on its own line: ? NOBINARYCHARS ? NODYNSQL ? SHOWSYNTAX NOTE 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 None Syntax SHOWSYNTAX

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

tranlogoptions logretention disabled tranlogoptions dblogreader DBLOGREADER ： (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 option The 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。