Oracle GoldenGate常用参数详解

146次阅读

共计 52134 个字符，预计需要花费 131 分钟才能阅读完成。

OGG（Oracle GoldenGate）包括 Manager 进程、Extract 进程、Pump 进程（可以看做特殊的 extract）、Replicat 进程等，需要设置的内容包括 Manager、Extract、Replicat、DEFGEN 等，本文从这几个方面介绍相关的可用参数，方面学习和工作之用。

介绍

所有的 GoldenGate 进程均有参数文件
Manager
Extract
Replicat
Utilities

所有参数均有缺省配置
实际应用只需对小部分参数进行配置

所有参数文件均放在 ./dirprm 目录下
缺省通过进程名进行查找

一、全局参数

MGRSERVNAME Specifies the name of the Manager process when it is installed as a Windows service.

CHECKPOINTTABLE Specifies a default checkpoint table.

GGSCHEMA Specifies the name of the schema that contains the database objects that support DDL synchronization for Oracle.

DDLTABLE Specifies a non-default name for the DDL history table that supports DDL synchronization for Oracle.

MARKERTABLE Specifies a non-default name for the DDL marker table that supports DDL synchronization for Oracle.

OUTPUTFILEUMASK Specifies a umask that can be used by Oracle GoldenGate processes to create trail files and discard files.

SYSLOG Filters the types of Oracle GoldenGate messages that are written to the system logs.

UNLOCKEDTRAILFILES Workaround for problems with trail files that remain locked by Collector if there is a network outage that affects the target system.

二、Manager 进程相关参数

1、常用参数：

PORT 制定 GoldenGate 的 mgr 进程使用哪个 TCP/IP 端口侦听请求.
示例：PORT 7809

DYNAMICPORTLIST 指定 GoldenGate 可以使用那些端口接受 extract 发送过来的数据.
示例：DYNAMICPORTLIST 7840-7850

AUTOSTART 指定在 mgr 启动时自动启动那些进程.
示例：AUTOSTART ER *
AUTOSTART extract extsz

AUTORESTART 指定在 mgr 可以定时重启那些进程。可以在网络中断等故障恢复后自动重起，避免人工干预.
示例：AUTORESTART ER *, WAITMINUTES 5, RETRIES 3 – 每隔 5 分钟尝试启动一次，尝试 3 次

LAGREPORT 指定在 ggserr.log 中报告延迟的时间间隔.
示例：LAGREPORTHOURS 1 – 每隔一小时检查一次传输延迟情况

LAGCRITICAL 指定认为超过此时间即为严重错误的延迟最大值，如果延迟达到此时间值则会在 ggserr.log 里面写入一条 error 信息
示例：LAGCRITICALMINUTES 45 – 传输延时超过 45 分钟将写入警告日志

LAGINFO 指定一个延迟时限，如果延迟达到此时间值则会在 ggserr.log 里面写入一条 info 信息
示例：LAGINFOMINUTES 30 – 传输延时超过 30 分钟将写入错误日志

PURGEOLDEXTRACTS 定义自动删除过时的队列以节省硬盘空间。
一般按照两个规则来删除：首先，要满足检查点要求，没有使用过的队列不能删除，保证无数据丢失；其次，可以保留一定的天数。只有当已经使用过且超过设定的天数后的队列会被自动删除。示例：purgeoldextracts /backup/goldengate/dirdat/*,usecheckpoints, minkeepdays 7

2、完整示例：

源端 Manager 配置参数示例：

GGSCI> view params mgr

PORT 7839 – 通信端口 7839。源端和目标端需要保持一致。
DYNAMICPORTLIST 7840-7914 – 动态端口列表的范围从 7840 到 7914。当制定端口被占用或者出现通信故障，管理进程将会从列表中选择下一个端口尝试连接，避免通信端口的单点故障。
AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 7 – 当提取进程中断后尝试自动重启，每隔 7 分钟尝试启动一次，尝试 5 次。
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 10 – 定期清理 dirdat 路径下的本地队列（local trail）。保留期限 10 天，过期后自动删除。从而控制队列文件的目录不会增长过大。
LAGREPORTHOURS 1 – 每隔一小时检查一次传输延迟情况
LAGINFOMINUTES 30 – 传输延时超过 30 分钟将写入错误日志
LAGCRITICALMINUTES 45 – 传输延时超过 45 分钟将写入警告日志

目标端 Manager 配置参数示例：

GGSCI 4> view params mgr

PORT 7839 – 通信端口 7839。生产端和灾备端需要保持一致

USERID goldengate, PASSWORD YGGJBFJGIH, ENCRYPTKEY default
DYNAMICPORTLIST 7840-7914 – 动态端口列表的范围从 7840 到 7914。当制定端口被占用或者出现通信故障，管理进程将会从列表中选择下一个端口尝试连接，避免通信端口的单点故障。
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3 – 当提取进程中断后尝试自动重启，每隔 7 分钟尝试启动一次，尝试 5 次。
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 5 – 定期清理 dirdat 路径下的远程队列（remote trail）。保留期限 5 天，过期后自动删除。从而控制队列文件的目录不会增长过大。
–PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10 – 删除 DDL 历史表，最小保存 7 天，最大保存 10 天。由于此项目没有开启 DDL，所以该参数被注释。
–PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10 – 删除 MARKER 历史表，最小保存 7 天，最大保存 10 天。此项目没有使用该参数，所以该参数被注释。
LAGREPORTHOURS 1 – 每隔一小时检查一次传输延迟情况
LAGINFOMINUTES 30 – 传输延时超过 30 分钟将写入错误日志
LAGCRITICALMINUTES 45 – 传输延时超过 45 分钟将写入警告日志

参考：真实环境的 OracleGoldenGate 配置参数详解 — http://blog.itpub.net/29475508/viewspace-1297083/

3、完整可选参数列表

（1）通用参数：

COMMENT | — Allows insertion of comments in a parameter file.## 允许在参数文件中插入注释信息
SYSLOG Filters the types of Oracle GoldenGate messages that are written to the system logs.

（2）端口管理参数：

DYNAMICPORTLIST Specifies the ports that Manager can dynamically allocate.
## 指定 Manager 可以动态分配的端口列表（这些端口将用来接受 extract 发送过来的数据）
示例：DYNAMICPORTLIST 7840-7850

DYNAMICPORTREASSIGNDELAY Specifies a time to wait before assigning a port number that was previously assigned.
## 当要分配的端口被占用时，指定在再次分配前需要等待的时间

PORT Establishes the TCP/IP port number on which Manager listens for requests.
## 指定 Manager 监听请求所使用的 TCP/IP 端口号，示例：PORT 7809

（3）进程管理参数：

AUTORESTART Specifies processes to be restarted by Manager after a failure.
## 指定 Manager 可（定时）重启哪些进程（可以在网络中断等故障恢复后自动重起，避免人工干预）
示例：AUTORESTART ER *, WAITMINUTES 5, RETRIES 3 – 每隔 5 分钟尝试启动一次，尝试 3 次

AUTOSTART Specifies processes to be started when Manager starts.
## 指定 Manager 进程启动时，自动启动哪些进程
示例：AUTOSTART ER *
AUTOSTART extract extsz

BOOTDELAYMINUTES Determines how long after system boot time Manager delays until performing main processing activities. This parameter supports Windows.

UPREPORT Determines how often process heartbeat messages are reported.
## 确定心跳检测信息多久报告一次

（4）事件管理参数：

DOWNCRITICAL Reports processes that stopped gracefully or abnormally.

DOWNREPORT Controls the frequency for reporting stopped processes.

LAGCRITICAL Specifies a lag threshold that is considered critical and generates a warning to the error log.
指定认为超过此时间即为严重错误的延迟最大值，如果延迟达到此时间值则会在 ggserr.log 里面写入一条 error 信息
示例：LAGCRITICALMINUTES 45 – 传输延时超过 45 分钟将写入警告日志

LAGINFO Specifies a lag threshold at which an informational message is reported to the error log.
指定一个延迟时限，如果延迟达到此时间值则会在 ggserr.log 里面写入一条 info 信息
示例：LAGINFOMINUTES 30 – 传输延时超过 30 分钟将写入错误日志

LAGREPORT Sets an interval for reporting lag time to the error log
指定在 ggserr.log 中报告延迟的时间间隔.
示例：LAGREPORTHOURS 1 – 每隔一小时检查一次传输延迟情况

（5）数据库登陆：

SOURCEDB Specifies a data source name as part of the login information.
示例：SOURCEDB mydb
SOURCEDB mydb, USERID ggs, PASSWORD ggs123

USERID Provides login information for Manager when it needs to access the database.
示例：USERID /
USERID ggs, PASSWORD ggs123
USERID ggs@ora1.ora, PASSWORD ggs123
USERID data1
USERID ggs, PASSWORD AACAAAAAAAAAAAIALCKDZIRHOJBHOJUH, ENCRYPTKEY superx128
USERID ggs, PASSWORD AACAAAAAAAAAAAIALCKDZIRHOJBHOJUH, ENCRYPTKEY default

（6）维护相关参数：

CHECKMINUTES Determines how often Manager cycles through maintenance activities.

PURGEDDLHISTORY Purges rows from the Oracle DDL history table when they are no longer needed.

PURGEMARKERHISTORY Purges Oracle marker table rows that are no longer needed.

PURGEOLDEXTRACTS Purges trail data that is no longer needed.## 自动删除过时的队列以节省硬盘空间。
一般按照两个规则来删除：
首先，要满足检查点要求，没有使用过的队列不能删除，保证无数据丢失；
其次，可以保留一定的天数。只有当已经使用过且超过设定的天数后的队列会被自动删除。
示例：purgeoldextracts /backup/goldengate/dirdat/*,usecheckpoints, minkeepdays 7

PURGEOLDTASKS Purges Extract and Replicat tasks after a specified period of time.

STARTUPVALIDATIONDELAY[CSECS] Sets a delay time after which Manager checks that processes are still running after startup.

参考：Oracle GoldenGate Windows and UNIX Reference Guide 11g Release 1(11.1.1) E17791-01

Oracle Golden Gate 系列八 — GG 参数文件说明 http://blog.csdn.net/tianlesoftware/article/details/6970183

二、extract 进程相关参数

1、常用参数

抽取进程有以下方面参数:

Checkpoint
复制目标
Local System
Multiple Remote Systems
One to many GoldenGate Trails
数据源
Which Tables
Which Rows and Columns
Which Operations
数据影射和变换

table 参数：

Syntax:
TABLE <file name>
[, WHERE (<where condition>)]
[, FILTER (<expression>)]
[, KEYCOLS (<key column specification>)]
[, COLS (<column>) | [, <column>]) ]
[, COLSEXCEPT (<column>) | [, <column>]) ]
[, FETCHCOLS (<column>) | [, <column>]) ]
[, FETCHCOLSEXCEPT (<column>) | [, <column>]) ]
[, SQLEXEC <clause>]
[, FETCHBEFOREFILTER]
[, SQLPREDICATE“WHERE <clause>”]
[, COLMAP (<column mapping specification>)]
[, <record type filter>, <record type filter>, …]
;

注意：TABLE 的结尾有一个分号。

2、完整示例

参数文件举例：日志抽取进程

extract extsz
userid goldengate, password *******
–REPORT AT 01:59
–reportrollover at 02:00
–transmemory directory (/backup/goldengate/dirtmp,8G,4G),ram 2G,transram 500M
tranlogoptions rawdeviceoffset 0
–warnlongtrans 12h, checkintervals 30m
exttrail /backup/goldengate/dirdat/sz

dynamicresolution
numfiles 2000

tableexclude ctais2.TMP_*;
tableexclude ctais2.BAK_*;
tableexclude ctais2.MLOG$_*;
tableexclude ctais2.RUPD$_*;
tableexclude ctais2.KJ_*;

table ctais2.*;

参数文件举例：传输进程

extract dpesz
setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)
passthru
REPORT AT 01:59
reportrollover at 02:00
transmemory directory (/backup/goldengate/dirtmp,8G,4G), ram 2G,transram 500M
–threadoptions iolatencydelay 3000
rmthost 99.16.1.12,mgrport 7809, compress
rmttrail /oradata/goldengate/dirdat/sz
dynamicresolution
numfiles 3000
table ctais2.*;

示例 3：

extract extl – 抽取进程名
userid ddw,password ddw – 连接本机 DB 的帐号密码
rmthost 192.168.0.44, mgrport 7801 – 目标数据库服务器地址和 GG 服务端口号
rmttrail d:\tools\GG\gg10g\dirdat\rl – 远程队列的位置
dynamicresolution – 优化参数，动态分析表结构
gettruncates – 抓取 truncate 数据
table ddw.aatest; – 需要抽取的表，可以使用通配符

3、全部可选参数

（1）通用参数

CHECKPARAMS Verifies parameter file syntax.
如果加入该参数，表示下次运行只是检查一下语法，并不实际运行进程。注意：该命令只能检查一些简单语法，并不能保证参数文件是完全正确的。

COMMENT | — Denotes comments in a parameter file. ## 注释行，也可以用两个中划线 – 代替.–checkparams 表示本行已经被注释掉

ETOLDFORMAT Generates trails in a format that is compatible with Replicat versions prior to Oracle GoldenGate version 6.0.

GETENV Retrieves variables that were set with the SETENV parameter.
SETENV Specifies a value for a UNIX environment variable from within the GGSCI interface.
针对 extract 进程设定系统环境变量。
示例：SETENV (NLS_LANG=”AMERICAN_AMERICA.UTF8″) – 设置字符集环境变量为 UTF8

OBEY Processes parameter statements contained in a different parameter file.
可以将外部文件的内容包含到本参数文件中来。用于将一些可以重复利用的参数文件部分内容隔离出来，便于统一修改。Obey tables.txt

RECOVERYOPTIONS Controls the recovery mode of the Extract process.

TCPSOURCETIMER |
NOTCPSOURCETIMER Adjusts timestamps of records transferred to other systems when those systems reflect different times.

TRACETABLE |
NOTRACETABLE Causes Extract to ignore database changes generated by Replicat during bidirectional synchronization. Supports Oracle

（2）处理方法 Processing method

BEGIN Specifies when to begin a processing run.

DSOPTIONS Specifies Extract processing options when a Teradata Access Module (TAM) is being used.

END Specifies when to end a processing run.

EXTRACT Defines an Extract group as an online process. ## 定义抽取进程的名字

GETAPPLOPS |
IGNOREAPPLOPS Controls whether or not operations from all processes except Replicat are written to a trail or file.

GETREPLICATES |
IGNOREREPLICATES Controls whether or not replicated operations are captured by an Extract on the same system.

PASSTHRU | Controls whether tables will be processed by a data-pump Extract in pass-through mode or whether data definitions will be required
NOPASSTHRU 禁止 extract 与数据库交互，适用于 Data Pump 传输进程（dpeXX）。
说明：PASSTHRU 直通模式用在两边表名、列名一致，可以直接映射的情况，不需要额外配置；
NOPASSTHRU 普通模式可以配置表名列名自定义映射，可以加 FILTER、transformation 等，需要配置一个数据定义文件（data-definitions file）
RMTTASK Creates a processing task on a remote system.

SOURCEISTABLE Extracts entire records from source tables.

SPECIALRUN Specifies a one-time processing task that does not checkpoint from run to run.

VAM Indicates that a Teradata Access Module (TAM) is being used to provide transactional data to the Extract process.

（3）数据库登陆 Database login

SOURCEDB Specifies the data source as part of the login information.

USERID Specifies database connection information.

[SOURCEDB,] USERID ,PASSWORD 指定所要登陆的数据库名称，用户名和密码。对于 oracle 无需指定 sourcedb，直接指定用户名和密码即可。
Userid goldengate, password goldengate

（4）数据匹配和映射 Selecting and mapping data

ASCIITOEBCDIC Converts ASCII text to EBCDIC for DB2 on z/OS systems running UNIX System Services.

COLMATCH Establishes global column-mapping rules.

COMPRESSDELETES |
NOCOMPRESSDELETES Controls whether Oracle GoldenGate writes only the key or all columns to the trail for delete operations.

COMPRESSUPDATES |
NOCOMPRESSUPDATES Causes only primary key columns and changed columns to be logged for updates.

DDL Enables and filters the capture of DDL operations.

DDLSUBST Enables string substitution in DDL processing.

FETCHOPTIONS Controls certain aspects of the way that Oracle GoldenGate fetches data.
示例：FETCHOPTIONS NOUSESNAPSHOT – 不会从闪回日志中获取数据
FETCHOPTIONS FETCHPKUPDATECOLS
– 当使用了 HANDLECOLLISIONS 时，请使用该参数。复制进程出现丢失 update 记录（missing update）并且更新的是主键，update 将转换成 insert。由于插入的记录可能不是完整的行，若要保证完整需要加入此参数

GETDELETES | IGNOREDELETES Controls the extraction of delete operations. 是否复制 delete 操作，缺省复制

GETINSERTS | IGNOREINSERTS Controls the extraction of insert operations. 是否复制 insert 操作，缺省复制

GETTRUNCATES |IGNORETRUNCATES Controls the extraction of truncate statements. 是否复制 truncate 操作，缺省不复制.

GETUPDATEAFTERS | IGNOREUPDATEAFTERS Controls the extraction of update after images.## 是否在队列中写入后影像，缺省复制

GETUPDATEBEFORES |IGNOREUPDATEBEFORES Controls the extraction of update before images. 是否在队列中写入前影像，缺省不复制

GETUPDATES | IGNOREUPDATES Controls the extraction of update operations 是否复制 update 操作，缺省复制

REPLACEBADCHAR Replaces invalid character values with another value.

SEQUENCE Specifies sequences for synchronization.

TABLE for Extract Specifies tables for extraction and controls column mapping and conversion.

TABLEEXCLUDE Excludes tables from the extraction process.
定义所需要排除的表。如果在 table 里面定义了使用通配符，那么可以使用该参数定义排除掉其中的部分��。如：
tableexclude ctais2.TMP_*;
tableexclude ctais2.TEMPTAB;

TARGETDEFS Specifies a file containing target table definitions for target databases that reside on the NonStop platform.

TRIMSPACES |
NOTRIMSPACES Controls whether trailing spaces are trimmed or not when mapping CHAR to VARCHAR columns.

WILDCARDRESOLVE Defines rules for processing wildcard table specifications in a TABLE statement.

（5）数据转发 Routing data

EXTFILE Specifies an extract file to which extracted data is written on the local system.

EXTTRAIL Specifies a trail to which extracted data is written on the local system.
指定写入到本地的哪个队列。
示例：EXTTRAIL ./dirdat/ya – 本地队列文件路径

RMTFILE Specifies an extract file to which extracted data is written on a remote system.

RMTHOST Specifies the target system and Manager port number.
指定目标系统及其 GoldenGate Manager 进程的端口号，也用于定义是否使用压缩进行传输。
示例：rmthost 99.16.1.12,mgrport 7809, compress

RMTTRAIL Specifies a trail to which extracted data is written on a remote system.
指定写入到目标端的哪个队列

（6）数据格式化 Formatting data

FORMATASCII Formats extracted data in external ASCII format.

FORMATSQL Formats extracted data into equivalent SQL statements.

FORMATXML Formats extracted data into equivalent XML syntax.

NOHEADERS Prevents record headers from being written to the trail.

（7）自定义处理 Custom processing

CUSEREXIT Invokes a user exit routine during processing.

INCLUDE Invokes a macro library.

MACRO Defines an Oracle GoldenGate macro.

MACROCHAR Defines a macro character other than the default of #.

SQLEXEC Executes a stored procedure or query during Extract processing.
在 extract 运行时首先运行一个 sql 语句。示例：sqlexec “Alter session set constraints=deferred”

VARWIDTHNCHAR |
NOVARWIDTHNCHAR Controls whether length information is written to the trail for NCHAR columns.

（8）报告信息 Reporting

CMDTRACE Displays macro expansion steps in the Extract report file.

LIST | NOLIST Displays or suppresses the listing of macros in the report file.

REPORT Schedules a statistical report.
定义自动定时报告。REPORT AT 01:59

STATOPTIONS Specifies information to include in statistical displays.
定义每次使用 stat 时统计数字是否需要重置

REPORTCOUNT Reports the number of records processed. 报告已经处理的纪录条数统计数字。
示例：REPORTCOUNT EVERY 30 MINUTES, RATE
– 每隔 30 分钟报告一次从程序开始到现在的抽取进程或者复制进程的事物记录数，并汇报进程的统计信息

TRACE/TRACE2 Shows Extract processing information to assist in revealing processing bottlenecks.
打开对 GoldenGate 进程的跟踪日志，一般用于调试。

WARNLONGTRANS Defines a long-running transaction and controls the frequency of checking for and reporting them
指定对于超过一定时间的长交易可以在 ggserr.log 里面写入警告信息。例如，每隔 30 分钟检查一次长交易，对于超过 12 个小时的进行告警：
示例：warnlongtrans 12h, checkintervals 30m
WARNLONGTRANS 2h, CHECKINTERVAL 3m – 每隔 3 分钟检查一下大事务，超过 2 小时还没结束的进行报告

（9）错误处理 Error handling

DDLERROR Controls error handling for DDL extraction.

DISCARDFILE Contains records that could not be processed.
定义 discard 文件位置，如果处理中有纪录出错会写入到此文件中。
示例 1：discardfile /oradata/goldengate/repkj.dsc,append,megabytes 100m
示例 2：DISCARDFILE ./dirrpt/extya.dsc, APPEND, MEGABYTES 1024
– 将执行失败的记录保存在 discard file 中，该文件位于./dirrpt/extya.dsc, 大小为 1024MB。文件中已经包含记录的话，再后面继续追加，不删除之前的记录

（10）协调 Tuning

ALLOCFILES Controls the number of incremental memory structures allocated when the value of NUMFILES is reached.

BR Controls the Bounded Recovery feature of Extract.

CACHEMGR Controls the virtual memory cache manager.
示例：CACHEMGR CACHESIZE 8000MB, CACHEDIRECTORY /goldengate/temp, CACHEDIRECTORY /goldengate/temp2
在 /goldengate/temp,/goldengate/temp2 目录设置虚拟内存

CHECKPOINTSECS Controls how often Extract writes a checkpoint.

DBOPTIONS Specifies database options. 指定对于某种特定数据库所需要的特殊参数
示例：DBOPTIONS ALLOWUNUSEDCOLUMN
– 当抽取进程遇到一个没有使用的字段时只生成一个警告，进程会继续执行而不会被异常终止（abend）

DDLOPTIONS Specifies DDL processing options.

DYNAMICRESOLUTION |
NODYNAMICRESOLUTION Suppresses the metadata lookup for a table until Extract encounters transactional data for it. Makes Extract start faster when there are numerous tables specified for synchronization.
示例：DYNAMICRESOLUTION – 有时候开启 OGG 进程的时候较慢，可能是因为需要同步的表太多，OGG 在开启进程之前会将需要同步的表建立一个记录并且存入到磁盘中，这样就需要耗费大量的时间。使用该参数来解决此问题。

EOFDELAY | EOFDELAYCSECS Determines how long Extract delays before searching for more data to process.

FLUSHSECS | FLUSHCSECS Determines the amount of time that record data remains buffered before being written to the trail.

FUNCTIONSTACKSIZE Controls the size of the memory stack that is used for processing Oracle GoldenGate functions.

GROUPTRANSOPS Controls the number of records that are sent to the trail in one batch.

LOBMEMORY Controls the amount of memory and temporary disk space available for caching transactions that contain LOB s.

MAXFETCHSTATEMENTS Controls the maximum number of prepared queries that Extract can use to fetch data from the database.

NUMFILES Controls the initial allocation of memory dedicated to storing information about tables to be processed by Oracle GoldenGate.
定义本 extract 为最大多少张表预留空间，缺省为 500，超过 500 张表需要设定一个比实际表数略大的值。
示例：numfiles 3000

RMTHOSTOPTIONS Specifies connection attributes other than host information for a TCP/IP connection used by a passive Extract group.

THREADOPTIONS Controls aspects of the way that Extract operates in an Oracle Real Application Cluster environment. Supports Oracle.
示例：THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENCY 60000
RAC 集群中的所有节点必须同步系统时钟。GoldenGate 通过比较本地系统的时间和事务提交的时间点来做出关键决策。可以通过 NTP 来不同系统时间。所有节点上的 COMPATIBLE 参数设置也必须相同。注：这个参数在 Oracle11.2 版本后就不再使用了。

TRANLOGOPTIONS Supplies log-processing options.
指定在解析数据库日志时所需要的特殊参数。例如，对于裸设备，可能需要加入下列参数：
示例 1：tranlogoptions rawdeviceoffset 0
示例 2：TRANLOGOPTIONS CONVERTUCS2CLOBS
– 指定在解析数据库日志时所需要的特殊参数
– 本参数只用在 extract 端 UTF 字符类型，并且 11.1.1 前处理 CLOB 才需要:
示例 3：–tranlogoptions asmuser sys@asm, asmpassword AACAAAAAAAAAAGAIF, ENCRYPTKEY default

示例 4：TRANLOGOPTIONS altarchivelogdest primary instance sgpmdb1 /sgpmdb/arch altarchivelogdest instance sgpmdb2 /sgpmdb/arch 指定 RAC 环境下每个节点的归档日志地址

TRANSMEMORY Controls the amount of memory and temporary disk space available for caching uncommitted transaction data.
设定 GoldenGate 的抽取进程能够使用的内存大小。如下参数指定本进程最大只能占用 2G 内存，其中每个事务最大占用内存不能超过 500M，如果超过则使用指定目录作为虚拟内存，该目录下的单个文件大小为 4G，最多只能在该目录下占用 8G 空间作为缓存：
示例：transmemory directory (/backup/goldengate/dirtmp,8G,4G),ram 2G,transram 500M

（11）维护 Maintenance

DISCARDROLLOVER Controls how often to create a new discard file.
示例：DISCARDROLLOVER AT 3:00 – 为了防止 discard file 被写满，每天 3：00 做一次文件过期设定

PURGEOLDEXTRACTS Purges obsolete trail files. 同 mgr 进程，可以设置自动删除队列，建议在 mgr 设置

REPORTROLLOVER Specifies when to create new report files.
设定切换一个日志的时间和间隔。示例：reportrollover at 02:00

ROLLOVER Specifies the way that trail files are aged.

（12）安全性 Security

DECRYPTTRAIL Decrypts data in a trail or extract file. 解密 trail 文件或 extract 文件数据

ENCRYPTTRAIL | NOENCRYPTTRAIL Controls encryption of data in a trail or extract file. 加密 trail 或 extract 文件的数据

更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2017-09/146727p2.htm

三、replicat 相关参数

1、常用参数

replicat 提供如下方面参数:

Checkpoint
数据影射关系
Optional row-level selection criteria
Optional column mapping facilities
Optional transformation services
Optional Stored Procedure or SQL query execution
错误处理机制
其它可选参数

参数举例：

replicat repsz
setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)
userid goldengate, password *****
sqlexec “Alter session set constraints=deferred”
REPORT AT 01:59
reportrollover at 02:00
–handlecollisions
reperror default,discard
discardfile /oradata/goldengate/dirrpt/repsz.dsc,append, megabytes 10
–grouptransops 100
–batchsql
assumetargetdefs
allownoopupdates
dynamicresolution
numfiles 3000
–mapexclude CTAIS2.JC_GY_SWWSWH
–map CTAIS2.SHOULIXINXI, target CTAIS2.SHOULIXINXI, keycols (SHOULIBIANHAO);
MAP ctais2.* ,TARGET ctais2.*;

Map 参数：

MAP <source table>, TARGET <target table>
[, EXCEPTIONSONLY]
[, COLMAP (<column mapping specification>)]
[, KEYCOLS (<column list>)]
[, WHERE (<where condition>)]
[, FILTER (<expression>)]
;

注意 map 语句必须以一个分号结束；

2、完整参数示例：

示例 1：

GGSCI 6> view params repsa

REPLICAT repsa
SETENV (NLS_LANG = “American_America.UTF8”) – 设置字符集环境变量为 UTF8
SETENV (Oracle_SID = “xxxx”) – 如果系统中存在多个数据库有时候会用参数 SETENV 设置 ORACLE_HOME、ORACLE_SID 等
USERID goldengate, PASSWORD AACAAAAAAAAAAAJAPJYATHVIMGMAOFHIUCUEPFYGGJBFJGIH, ENCRYPTKEY default
–SQLEXEC “ALTER SESSION SET CONSTRAINTS=DEFERRED”
DBOPTIONS DEFERREFCONST – 约束延迟设置。在复制进程的事物被提交之前，延迟级联删除、级联更新时的校验和实施。
GETTRUNCATES – 不捕获生产端 truncate table 的操作。
REPORT AT 06:00 – 每天早上 6 点报告
REPORTCOUNT EVERY 30 MINUTES, RATE – 每隔 30 分钟报告一次从程序开始到现在的抽取进程或者复制进程的事物记录数，并汇报进程的统计信息
REPORTROLLOVER AT 02:00 – 为了防止 report file 被写满，每天 2：00 做一次文件过期设定
REPERROR DEFAULT, ABEND – 除了特殊指定的 REPERROR 语句，报告所有复制期间出现的错误，回滚非正常中断的事物和进程。
–HANDLECOLLISIONS – 当灾备端已经存在数据的情况下，解决复制过程中出现的冲突。如果要重新做初始化，可以删除 drop 灾备端数据库后再 rman 恢复，这样做的话就不需要该参数了。
####ALLOWNOOPUPDATES – 当生产端有某些列但是目标表却没有，或者复制进程中配置了 COLSEXCEPT 参数在这些情况下，当生产端对那些列进行更新，目标表将不发生任何变化
assumetargetdefs – 使用 ASSUMETARGETDEFS 参数时，用 MAP 语句中指定的生产库源表和灾备端目标表具有相同的列结构。它指示的 Oracle GoldenGate 不在生产端查找源表的结构定义。
DISCARDFILE ./dirrpt/repsa.dsc, APPEND, MEGABYTES 1024M – 将执行失败的记录保存在 discard file 中，该文件位于./dirrpt/extya.dsc, 大小为 1024MB。文件中已经包含记录的话，再后面继续追加，不删除之前的记录。
DISCARDROLLOVER AT 02:00 – 为了防止 discard file 被写满，每天 2：00 做一次文件过期设定
—Schema 名称涉及敏感字符，在此统一使用 Schema 来代替
—table 名称涉及敏感字符，在此统一使用 table 来代替
map Schema1.table1, target Schema1.table1;
map Schema1.table2, target Schema1.table2;
map Schema2.table1, target Schema2.table1;
map Schema2.table2, target Schema2.table2;

示例 2：

replicat repl – 复制进程名
userid ddw,password ddw – 目标数据库的帐号密码
assumetargetdefs – 两台数据库数据结构一致则使用此参数
reperror default,discard – 如果复制数据出错则忽略，
discardfile D:\repsz.dsc,append,megabytes 100 – 错误信息写入 XXXX 文件，追加模式，最大 100m
gettruncates – 复制 truncate 操作
map ddw.aatest, target ddw.aatest_1; – 映射关系，注意 target 前必须留一个空格
–map ddw.aatest, target ddw.aatest_1, keycols (name), where (TYPE1 = “1”)
– 加两横杠在配置文件中进行注释；keycols 指定唯一键；where 筛选数据

3、全部可选参数

（1）通用参数

CHECKPARAMS Verifies parameter file syntax. 检查参数语法然后停止。

COMMENT | — Denotes comments in a parameter file. 注释行。

GETENV Retrieves variables that were set with the SETENV parameter.
SETENV Specifies a value for a UNIX environment variable from within the GGSCI interface.
示例：SETENV (NLS_LANG = “American_America.UTF8”) – 设置字符集环境变量为 UTF8
示例：SETENV (ORACLE_SID = “xxxx”) – 如果系统中存在多个数据库有时候会用参数 SETENV 设置 ORACLE_HOME、ORACLE_SID 等

OBEY Processes parameter statements contained in a different parameter file. 将外部文件包含到参数文件中。

TRACETABLE | NOTRACETABLE Specifies a trace table to which Replicat adds a record whenever it updates the target database. Supports Oracle.

（2）Processing method

BEGIN Specifies a starting point for Replicat processing. Required when SPECIALRUN is specified.

BULKLOAD Loads data directly into the interface of the Oracle SQL*Loader utility.

END Specifies a stopping point for Replicat processing. Required when using SPECIALRUN .

GENLOADFILES Generates run and control files that are compatible with a database load utility.

REPLICAT Specifies a Replicat group for online change synchronization. 定义进程名称。示例：replicat repsz

SPECIALRUN Used for one-time processing that does not require checkpointing from run to run.

（3）Database login

TARGETDB Specifies the data source as part of the login information.

USERID Specifies database connection information. 指定登陆目标数据库的用户名和密码。
示例：Userid goldengate, password 123456
USERID goldengate, PASSWORD AACAAAAAAAAAAAJAPJYATHVIMGMAOFHIUCUEPFYGGJBFJGIH, ENCRYPTKEY default

（4）Selecting, converting, and mapping data

ALLOWDUPTARGETMAP |
NOALLOWDUPTARGETMAP Allows the same source-target MAP statement to appear more than once in the parameter file.

ASCIITOEBCDIC Converts incoming ASCII text to EBCDIC for DB2 on z/OS systems running UNIX System Services.

ASSUMETARGETDEFS Assumes that source and target tables have the same column structure. 假定两端数据结构一致使用此参数。
示例：assumetargetdefs – 使用 ASSUMETARGETDEFS 参数时，用 MAP 语句中指定的生产库源表和灾备端目标表具有相同的列结构。它指示的 Oracle GoldenGate 不在生产端查找源表的结构定义。

COLMATCH Establishes global column-mapping rules.

DDL Enables and filters the capture of DDL operations.

DDLSUBST Enables string substitution in DDL processing.

GETDELETES | IGNOREDELETES Controls the replication of delete operations. 是否复制 delete 操作，缺省为复制。

GETINSERTS | IGNOREINSERTS Controls the replication of insert operations. 是否复制 insert 操作，缺省为复制。

GETUPDATEAFTERS | IGNOREUPDATEAFTERS Controls the replication of update after images. 是否读取后影像，缺省为读取

GETUPDATEBEFORES | IGNOREUPDATEBEFORES Controls the replication of update before images. 是否读取前影像，缺省为不读取。

GETUPDATES | IGNOREUPDATES Controls the replication of update operations. 是否复制 update 操作，缺省为复制。

GETTRUNCATES | IGNORETRUNCATES Includes or excludes the replication of TRUNCATE statements. 是否复制 truncate 操作，缺省为不复制。
示例：GETTRUNCATES – 不捕获生产端 truncate table 的操作。

INSERTALLRECORDS Inserts a new record into the target table for every change operation made to a record.

INSERTDELETES | NOINSERTDELETES Converts deletes to inserts.

INSERTMISSINGUPDATES |
NOINSERTMISSINGUPDATES Converts an update to an insert when the target row does not exist.

INSERTUPDATES | NOINSERTUPDATES Converts updates to inserts.

MAP for Replicat Specifies a relationship between one or more source and target tables and controls column mapping and conversion.

MAPEXCLUDE Excludes tables from being processed by a wildcard specification supplied in MAP statements.
用于使用在 map 中使用 * 匹配时排除掉指定的表，类似于于源端的 tablexclude。
示例：mapexclude CTAIS2.JC_GY_SWWSWH
MAP ctais2.* ,TARGET ctais2.*;

REPLACEBADCHAR Replaces invalid character values with another value.

REPLACEBADNUM Replaces invalid numeric values with another value.

SOURCEDEFS Specifies a file that contains source data definitions created by the DEFGEN utility.
假定两端数据结构不一致，使用此参数指定源端的数据结构定义文件。该文件需要由 GoldenGate 工具产生。

SPACESTONULL | NOSPACESTONULL Controls whether or not a target column containing only spaces is converted to NULL .

TABLE for Replicat Specifies a table or tables for which event actions are to take place when a row satisfies the given filter criteria.

TRIMSPACES | NOTRIMSPACES Controls whether trailing spaces are trimmed or not when mapping CHAR to VARCHAR columns.

UPDATEDELETES | NOUPDATEDELETES Changes deletes to updates.

USEDATEPREFIX Prefixes data values for DATE data types with a DATE literal, as required by Teradata databases.

USETIMEPREFIX Prefixes data values for TIME datatypes with a TIME literal, as required by Teradata databases.

USETIMESTAMPPREFIX Prefixes data values for TIMESTAMP datatypes with a TIMESTAMP literal, as required by Teradata databases.

（5）Routing data

EXTFILE Defines the name of an extract file on the local system that contains data to be replicated. Used for one-time processing.

EXTTRAIL Defines a trail containing data to be replicated. Used for one-time processing.

（6）Custom processing

CUSEREXIT Invokes a user exit routine during processing.

DEFERAPPLYINTERVAL Specifies a length of time for Replicat to wait before applying replicated operations to the target database.

INCLUDE References a macro library in a parameter file.

MACRO Defines an Oracle GoldenGate macro.

SQLEXEC Executes a stored procedure or query during Replicat processing.
调用存储过程或者执行 sql 语句。可以将返回值作为依据进行过滤条件，或者用户改变 session 变量。
示例：SQLEXEC “ALTER SESSION SET CONSTRAINTS=DEFERRED”

（7）Reporting

CMDTRACE Displays macro expansion steps in the report.

LIST | NOLIST Displays or suppresses the listing of macros in the report file.

REPORT Schedules a statistical report at a specified date or time.
示例：REPORT AT 06:00 – 每天早上 6 点报告

REPORTCOUNT Reports the number of records processed.
示例：REPORTCOUNT EVERY 30 MINUTES, RATE
– 每隔 30 分钟报告一次从程序开始到现在的抽取进程或者复制进程的事物记录数，并汇报进程的统计信息

SHOWSYNTAX Causes Replicat to print its SQL statements to the report file.

STATOPTIONS Specifies information to include in statistical displays.

TRACE/TRACE2 Shows Replicat processing information to assist in revealing bottlenecks.

（8）Error handling

CHECKSEQUENCEVALUE |
NOCHECKSEQUENCEVALUE Controls whether or not Replicat verifies that a target sequence value is higher than the one on the source and corrects any disparity that it finds.

DDLERROR Controls error handling for DDL replication.

DISCARDFILE Contains records that could not be processed.
定义出错数据的输出文件。当数据出现错误后，可以用于查找错误原因。
示例：discardfile /oradata/goldengate/dirrpt/repsz.dsc,append, megabytes 10
DISCARDFILE ./dirrpt/repsa.dsc, APPEND, MEGABYTES 1024M – 将执行失败的记录保存在 discard file 中，该文件位于./dirrpt/extya.dsc, 大小为 1024MB。文件中已经包含记录的话，再后面继续追加，不删除之前的记录。

HANDLECOLLISIONS | 自动过滤重复时段的数据冲突，用于不能停机执行初始化。打开该参数后不会将数据错误报到 discard 文件中。
NOHANDLECOLLISIONS Handles errors for duplicate and missing records.
示例：–HANDLECOLLISIONS
– 当灾备端已经存在数据的情况下，解决复制过程中出现的冲突。如果要重新做初始化，可以删除 drop 灾备端数据库后再 rman 恢复，这样做的话就不需要该参数了。

HANDLETPKUPDATE Prevents constraint errors associated with replicating transient primary key updates.

OVERRIDEDUPS |
NOOVERRIDEDUPS Overlays a replicated insert record onto an existing target record whenever a duplicate-record error occurs.

RESTARTCOLLISIONS | Controls whether or not Replicat applies
NORESTARTCOLLISIONS HANDLECOLLISIONS logic after Oracle GoldenGate has abended because of a conflict.

REPERROR Determines how Replicat responds to database errors.
定义出错以后 replicat 的响应，一般可以定义为两种：
Abend，即一旦出现错误即停止复制，此为缺省配置；
Discard，出现错误后继续复制，只把错误的数据放到 discard 文件中。
示例：REPERROR DEFAULT, DISCARD
REPERROR DEFAULT, ABEND – 除了特殊指定的 REPERROR 语句，报告所有复制期间出现的错误，回滚非正常中断的事物和进程。

REPFETCHEDCOLOPTIONS Determines how Replicat responds to operations for which a fetch from the source database was required.

SQLDUPERR Specifies the database error number that indicates a duplicate record. Use with OVERRIDEDUPS .

WARNRATE Determines how often database errors are reported.

（9）Tuning

ALLOCFILES Controls the amount of incremental memory that is allocated when the amount of memory specified with NUMFILES is reached.

BATCHSQL Increases the throughput of Replicat processing by arranging similar SQL statements into arrays and applying them at an accelerated rate. 针对批处理中针对某个表的大批量重复操作进行优化，提高批处理的处理速度。

CHECKPOINTSECS Controls how often Replicat writes a checkpoint when checkpoints are not being generated as the result of transaction commits.

DBOPTIONS Specifies database options. 定义与数据库类型相关的特殊处理方式。
示例：DBOPTIONS DEFERREFCONST – 约束延迟设置。在复制进程的事物被提交之前，延迟级联删除、级联更新时的校验和实施。

DDLOPTIONS Specifies DDL processing options.

DYNAMICRESOLUTION | 使 replicat 动态解析表的结构，加快启动速度。缺省为每次启动解析所有要复制表的结构。
NODYNAMICRESOLUTION Makes Replicat start faster when there is a large number of tables specified for synchronization.

DYNSQL | NODYNSQL Causes Replicat to use literal SQL statements rather than a compile-once, execute-many strategy.

EOFDELAY | EOFDELAYCSECS Determines how many seconds Replicat delays before looking for more data to process.

FUNCTIONSTACKSIZE Controls the size of the memory stack that is used for processing Oracle GoldenGate functions.

GROUPTRANSOPS Controls the number of records that are grouped into a Replicat transaction.
将小的交易合并成为一个大的交易进行提交，减少提交次数，降低系统 IO 消耗。示例：grouptransops 100

INSERTAPPEND |
NOINSERTAPPEND Controls whether or not Replicat uses an APPEND hint when applying INSERT operations to Oracle target tables.

MAXDISCARDRECS Limits the number of discarded records reported to the discard file.

MAXSQLSTATEMENTS Controls the number of prepared SQL statements that can be used by Replicat.

MAXTRANSOPS Divides large source transactions into smaller ones on the target system.
将大交易拆分，每若干条纪录提交一次。示例：maxtransops 1000

NUMFILES Controls the initial allocation of memory that is dedicated to storing information about tables to be processed by Oracle GoldenGate.
定义进程中表的最大数据量，缺省为 500.

RETRYDELAY Specifies the delay between attempts to retry a failed SQL operation.

TRANSACTIONTIMEOUT Specifies a time interval after which Replicat will commit its open target transaction and roll back any incomplete source transactions that it contains, saving them for when the entire source transaction is ready to be applied.

TRANSMEMORY Controls the amount of memory and temporary disk space available for caching uncommitted transaction data.

WILDCARDRESOLVE Alters the rules by which wildcard specifications in a MAP statement are resolved.

（10）Maintenance

DISCARDROLLOVER Specifies when to create new discard files.
示例：DISCARDROLLOVER AT 02:00 – 为了防止 discard file 被写满，每天 2：00 做一次文件过期设定

PURGEOLDEXTRACTS Purges obsolete trail files. 定义自动删除队列，一般建议在 mgr 进程配置。

REPORTROLLOVER Specifies when to create new report files.
示例：REPORTROLLOVER AT 02:00 – 为了防止 report file 被写满，每天 2：00 做一次文件过期设定

（11）Security

DECRYPTTRAIL Decrypts data in a trail or extract file.

补充：

allownoopupdates 参数含义：允许执行无实际变化的 update。例如，update a= a 会纪录一条 update，但是没有后影像，无法正确构筑 where 语句。
ALLOWNOOPUPDATES – 当生产端有某些列但是目标表却没有，或者复制进程中配置了 COLSEXCEPT 参数在这些情况下，当生产端对那些列进行更新，目标表将不发生任何变化

四、定义文件参数

DEFSFILE Identifies the name of the file to which DEFGEN writes the definitions

SOURCEDB Specifies the data source as part of the login information.

TABLE for DEFGEN Identifies a table for which you want to capture a definition.

USERID Specifies database connection information.

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-09/146727.htm