Valid For

Extract and Replicat


Use the DBOPTIONS parameter to specify database options. This is a global parameter, applying to all TABLE or MAP statements in the parameter file. Some DBOPTIONS options apply only to Extract or Replicat.

The DBOPTIONS parameter can be placed anywhere in the parameter file irrespective of other parameters.




[HOST {DNS_name | IP_address}]
[INTEGRATEDPARAMS(parameter[, ...])]
[SESSIONPOOLMAX max_value | 
[SESSIONPOOLMIN min_value][SESSIONPOOLINCR increment_value]
[SETTAG [tag_value | NULL] ]
[SOURCE_DB_NAME src_dbase_global_name]
[TRANSNAME trans_name]

Valid for Extract for Oracle. Controls whether Extract abends when it encounters a table with an unused column.

The default is ALLOWUNUSEDCOLUMN. When Extract encounters a table with an unused column, it continues processing and generates a warning.When using this parameter, either the same unused column must exist on the target or a source definitions file for the table must be specified to Replicat, so that the correct metadata mapping can be performed.

NOALLOWUNUSEDCOLUMN causes Extract to abend on unused columns.


Valid for Replicat for DB2 LUW and MySQL. ALLOWLOBDATATRUNCATE prevents Replicat from abending when replicated LOB data is too large for a target CHAR, VARCHAR, BINARY or VARBINARY column and is applicable to target LOB columns only. or replicat of DB2 LUW, ALLOWLOBDATATRUNCATE prevents Replicat from abending when replicated LOB data is too large for a target LOB column. The LOB data is truncated to the maximum size of the target column without any further error messages or warnings.

NOALLOWLOBDATATRUNCATE is the default and causes Replicat to abend with an error message if the replicated LOB is too large.


Valid for PostgreSQL.

Use DBOPTIONS ALLOWNONSTANDARDINTERVALDATA in the Extract parameter file to capture the mixed sign interval data (or any other format of interval data, which is not supported by Oracle GoldenGate) as a string (not as standard interval data). When this option is used, the format of the interval data that gets written to the trail and gets applied into the target CHAR column is as follows:

year-component-sign years-months days-component-sign days hour-component-sign hours:minutes:seconds.fractional_seconds

For example, +1026-9 +0 +0:0:22.000000 should be interpreted as 1026 years 9 months 0 days 0 hours 0 minutes 22 seconds. -0-0 -0 -8 should be interpreted as 0 years 0 months 0 days -8 hours. +1-3 +0 +3:20 should be interpreted as 1 years 3 months 0 days 3 hours 20 minutes.

In case of Replicat, if the source interval data was captured using DBOPTIONS ALLOWNONSTANDARDINTERVALDATA and written as a string to the trail, the corresponding source column is allowed to be mapped to either a char or a binary type column on the target.


Valid for DEFGEN, Extract, and Replicat for DB2 for i. Allows columns that are defined as CHAR or VARCHAR with CCSID 65535, or CHAR and VARCHAR FOR BIT DATA to be treated as if the field had a normal translatable encoding. The encoding is picked up from the job CCSID. When this option is in effect, DEFGEN does not indicate that the field is binary in the defs file.


Valid for Extract and Replicat for ODBC databases. By default, Oracle GoldenGate creates a new connection for catalog queries, but you can use NOCATALOGCONNECT to prevent that. On DB2 for z/OS, NOCATALOGCONNECT prevents Oracle GoldenGate from attempting multiple connections when the MVS DB2 initialization parameter mvsattachtype is set to CAF. Because CAF mode does not support multiple connections, it is possible that Oracle GoldenGate may issue commit locks on the system catalog tablespaces until it receives the commit for its open connection. To prevent commit locks, Oracle GoldenGate recommends using RRSAF (mvsattachtype=RRSAF), which supports multiple connections.


Valid for Replicat for multi-daemon MySQL. Specifies the TCP/IP port of the instance to which Replicat must connect. The minimum value is 1 and the default value is 3306.

DECRYPTPASSWORD shared_secret algorithm ENCRYPTKEY {key_name | DEFAULT}

Valid for Extract in classic capture mode (Oracle).

Integrated Extract does not require this option, and automatically handles encrypted data.

Specifies the shared secret (password) that decrypts the TDE key, which decrypts redo log data that was encrypted with Oracle Transparent Data Encryption (TDE). The TDE key is first encrypted in the Oracle server by using the shared secret as a key, and then it is delivered to Extract, which decrypts it by using the same shared secret. The shared secret must be created in the Oracle Wallet or Hardware Security Module by the Oracle Server Security Officer. The only other person who should know the shared secret is the Oracle GoldenGate Administrator.

To use the decryption options, you must first generate the encrypted shared secret with the ENCRYPT PASSWORD command in GGSCI and create an ENCKEYS file.

Parameter options:


Is the encrypted shared secret (password) that is copied from the ENCRYPT PASSWORD command results.


Specifies the encryption algorithm that was used to encrypt the password: AES128, AES192, AES256, or BLOWFISH. AES is not supported on DB2 on z/OS.


Specifies the logical name of a user-created encryption key in the ENCKEYS lookup file. Use if ENCRYPT PASSWORD was used with the KEYNAME key_name option. Requires an ENCKEYS file to be created on the local system.


Directs Oracle GoldenGate to use a random key. Use if ENCRYPT PASSWORD was used with the KEYNAME DEFAULT option.

For more information about configuring classic Extract to support TDE, see Configuring Classic Capture for TDE Support in Using Oracle GoldenGate for Oracle Database.

For more information about Oracle GoldenGate encryption options, including ENCKEYS, see Encrypting Data with the ENCKEYS Method in the Administering Oracle GoldenGate.


Valid for nonintegrated Replicat for Oracle. Sets constraints to DEFERRABLE to delay the checking and enforcement of cascade delete and cascade update referential integrity constraints by the Oracle target database until the Replicat transaction is committed. At that point, if there are constraint violations, an error is generated. Integrated Replicat does not require disabling of referential constraints on the target system.

You can use DEFERREFCONST instead of disabling the constraints on the target tables or setting them to DEFERRED. When used, DEFERREFCONST defers both DEFERABLE and NOT DEFERABLE constraints. DEFERREFCONST applies to every transaction that is processed by Replicat. DEFERREFCONST parameter works on Oracle and up.

If used with an Oracle Database release that does not support this functionality, DEFERREFCONST is ignored without returning a notification to the Oracle GoldenGate log. To handle errors on the commit operation, you can use REPERROR at the root level of the parameter file and specify the TRANSDISCARD or TRANSEXCEPTION option.


Do not to use with DEFERREFCONST coordinated Replicat because there is no way to guarantee that related rows in parent and child tables are processed by same thread 


Valid for Replicat for Oracle. Disables the use of asynchronous COMMIT by Replicat. An asynchronous COMMIT statement includes the NOWAIT option.

When DISABLECOMMITNOWAIT is used, Replicat issues a standard synchronous COMMIT (COMMIT with WAIT option).


Valid for nonintegrated Replicat for Oracle. Disables Oracle's LOB caching mechanism. By default, Replicat enables Oracle's LOB caching mechanism.


Valid for Oracle. Enables automatic per table instantiation CSN filtering on tables imported using Oracle data pump or manually instantiated using the SET_INSTANTIATION_CSN command.


Valid for Extract for Oracle, DB2 for i, DB2 z/OS, PostgreSQL, and SQL Server. Enables array fetches for initial loads to improve performance, rather than one row at a time. It is used for Initial Load Extract.

Valid values for Oracle, DB2 for i, DB2 z/OS, and SQL Server are 0 through 1000000 records per fetch. Valid values for DB2 LUW are 1 through 1000000 records per fetch; zero (0) is not a valid value.

The default is 1000. Performance slows when batch size gets very small or very large. If the table contains LOB data, Extract reverts to single-row fetch mode, and then resumes batch fetch mode afterward.


Valid for Integrated Extract for Oracle. Specifies the number of seconds that Extract waits between each fetch check for the ADG to catch up. A low number improves latency though increases the number of queries of current_scn from v$database. The default is 3 seconds; the maximum is 120 seconds.


Valid for Extract for DB2 for z/OS and DB2 for LUW. Suppresses the fetching of LOBs directly from the database table when the LOB options for the table are set to NOT LOGGED. With NOT LOGGED, the value for the column is not available in the transaction logs and can only be obtained from the table itself. By default, Oracle GoldenGate captures changes to LOBs from the transaction logs. The default is FETCHLOBS.


Valid for Integrated Extract for Oracle. Specifies the number of times that Extract tries before it reports ADG progress or the reason for no progress when waiting for the ADG to catch up. This value is multiplied with FETCHCHECKFREQ to determine approximately how often the ADG progress is reported.


Valid for Integrated Extract for Oracle. Specifies the number of seconds that Extract after which it will abend when ADG makes no progress. No progress can be because the MRP is not running or because it is not applying redo changes. When this occurs, the ADG database should be examined. The default is 30 seconds; valid values are 0 - 4294967295 (ub4 max value) seconds. NOFETCHTIMEOUT means never timeout (the same as FECHTIMEOUT 0) and seconds cannot be specified with it.


For trail fie formats of Oracle GoldenGate19c, if FORCE_XML_ESCAPE_CONVERSION is enabled, Replicat will escape the linefeed characters for the character types in the ANYDATA columns. If this parameter is enabled for a trail file with a format of 19.1 or higher, it is ignored because Extract already performs the linefeed escape.

This parameter only affects ANYDATA columns that are fetched in Classic Extract or when NOUSENATIVEOBJSUPPORT is turned on for Integrated Extract.


This parameter option doesn't affect ANYDATA columns retrieved from the database by Logminer in native mode.

HOST {DNS_name | IP_address}

Valid for Replicat for multi-daemon MySQL. Specifies the DNS name or IP address of the system that hosts the instance to which Replicat must connect.

INTEGRATEDPARAMS(parameter[, ...])

Valid for Integrated Replicat for Oracle. Passes settings for parameters that control the database inbound server within the target Oracle database.

You can use the commit_serialization option with INTEGRATEDPARAMS for integrated Replicat but not for parallel Replicat in integrated mode. Setting internal database parameters for integrated Extract is done using TRANLOGOPTIONS INTEGRATEDPARAMS.

For more information about integrated Replicat and a list of supported inbound server parameters, see Choosing Capture and Apply Modes in Using Oracle GoldenGate for Oracle Database.


Valid for Replicat for MySQL, Oracle, and SQL Server. LIMITROWS prevents multiple rows from being updated or deleted by the same Replicat SQL statement when the target table does not have a primary or unique key.

LIMITROWS is the default. LIMITROWS and NOLIMITROWS apply globally to all MAP statements in a parameter file.

For MySQL, LIMITROWS uses a LIMIT 1 clause in the UPDATE or DELETE statement.

For Oracle targets, LIMITROWS (the default) must be used. It uses either WHERE ROWNUM = 1 or AND ROWNUM = 1 in the WHERE clause.

For SQL Server, LIMITROWS uses a SET ROWCOUNT 1 clause before the UPDATE or DELETE statement.

NOLIMITROWS permits multiple rows to be updated or deleted by the same Replicat SQL statement.


Valid for Extract for Oracle. Determines the memory buffer size in bytes to allocate for each embedded LOB attribute that is in an Oracle object type. Valid values are from 1024 and 104857600 bytes. The default is 1048576 bytes.

If the length of embedded LOB exceeds the specified LOBBUFSIZE size, an error message similar to the following is generated:

GGS ERROR    ZZ-0L3  Buffer overflow, needed: 2048, allocated: 1024.

Valid for nonintegrated Replicat for Oracle. Specifies a fragment size in bytes for each LOB that Replicat writes to the target database. The LOB data is stored in a buffer until this size is reached. Because LOBs must be written to the database in fragments, writing in larger blocks prevents excessive I/O. The higher the value, the fewer I/O calls that are made by Replicat to the database server to write the whole LOB to the database.

Specify a multiple of the Oracle LOB fragment size. A given value will be rounded up to a multiple of the Oracle LOB fragment size, if necessary. The default LOB write size is 32k if DBOPTIONS NOSKIPTEMPLOB is specified, or 1MB if DBOPTIONS SKIPTEMPLOB is specified. Valid values are from 2,048 bytes to 2,097,152 bytes (2MB).

By default, Replicat enables Oracle's LOB caching mechanism. To disable Oracle's LOB caching, use the DISABLELOBCACHING option of DBOPTIONS.


Valid for Extract in integrated mode for Oracle. Sets a maximum value for the number of sessions in the OCI Session Pool, which is used by Extract for fetching from a container database. The default value is 10 sessions. Must be specified before the USERID or USERIDALIAS parameter; otherwise will be ignored and the default will be used.


Valid for Extract in integrated mode for Oracle. Sets a minimum value for the number of sessions in the OCI Session Pool, which is used by Extract for fetching from a container database. The default value is 2 sessions. Must be specified before the USERID or USERIDALIAS parameter; otherwise will be ignored and the default will be used.

SESSIONPOOLINCR increment_value

Valid for Extract in integrated mode for Oracle. Sets a value for the number of incremental sessions that can be added to the OCI Session Pool, which is used by Extract for fetching from a container database. The default value is 2 sessions. Must be specified before the USERID or USERIDALIAS parameter; otherwise will be ignored and the default will be used.

SETTAG [tag_value | NULL

Valid for Replicat for Oracle. Sets the value for an Oracle redo tag that will be used to identify the transactions of the associated Replicat in the redo log. A redo tag can also be used to identify transactions other than those of Replicat. This parameter is recommended over EXCLUDEUSER and TRACETABLE.

Use this option to prevent cycling (loop-back) of Replicat the individual records in a bi-directional configuration or to filter other transactions from capture. The default SETTAG value is 00 and is limited to 2K bytes. A valid value is any single Oracle Streams tag. A tag value can be up to 2000 hexadecimal digits (0-9 A-F) long.

Transactions in the redo that are marked with the specified tag can be filtered by an Extract that has the TRANLOGOPTIONS parameter with the EXCLUDETAG option set to the tag_value. Use tag-based filtering to prevent cycling (loop-back) of Replicat transactions in a bi-directional configuration or to filter other transactions from capture. For more information, see TRANLOGOPTIONS.

You can disable the tagging of DDL by using the DDLOPTIONS parameter with the NOTAG option.


A hexadecimal value from 0 through F. The default value is 00. The following are valid examples:

DBOPTIONS SETTAG 00112233445566778899aabbccddeeff

Disables tag-based filtering for the associated Replicat.


Valid for Replicat for Oracle Database versions 11g and 12c. Controls how LOBs are applied to a target Oracle database. The default of SKIPTEMPLOB .

SKIPTEMPLOB improves performance by directly writing LOB data to the target LOB column. Replicat creates a SQL statement with an empty LOB value and returns the LOB locator to the bind variable. After the SQL statement is executed successfully, the LOB data is written directly to the LOB column using the returned LOB locator.

NOSKIPTEMPLOB uses a temporary LOB in the SQL statement. Replicat declares a bind variable within SQL statement and associates a temporary LOB, then writes to the temporary LOB. The Oracle Database applies the LOB column data from the temporary LOB.

SKIPTEMPLOB applies to INSERT and UPDATE operations that contain LOB data. It does not apply if the table has a functional index with a LOB column, if the LOB data is NULL, empty, or stored inline. It does not apply to partial LOB operations.

SKIPTEMPLOB causes Replicat to generate/perform 1 DML+ n LOB_WRITE (piece-wise) operations when updating/inserting a row with LOB columns. However, SKIPTEMPLOB should not be used with FETCHPARTIALLOB (an Extract Parameter) because it results in excessive fetching.

NOSKIPTEMPLOB is provided for backward compatibility; otherwise the default of SKIPTEMPLOB should be retained.

SOURCE_DB_NAME src_dbase_global_name

Valid for Oracle. Indicates the Global Name of the Trail Source Database. It is used to query the relevant instantiation information when DBOPTIONS ENABLE_INSTANTIATION_FILTERING is enabled. This option is optional for instantiation filtering in a 12.2. trail file with metadata enabled.

When the source has no DOMAIN, do not specify a DOMAIN for the downstream database.


Valid for Extract and Replicat. Not valid for Oracle and MySQL.

Creates a separate database connection thread for using SQLEXEC to execute stored procedures. The default is NOSPTHREAD.

With this option enabled, the sql_mode variable is set to to 'ANSI_QUOTES' (set sql_mode = 'ANSI_QUOTES'). Treat the double quotes (") as an identifier quote character (like the ` quote character) and not as a string quote character. You can still use ` to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotes (") to quote literal strings, because it is interpreted as an identifier.

For more information, see Server SQL Modes.


Valid for DB2 LUW 10.1 FixPack 2 and greater replication of temporal table.

Use SUPPRESSTEMPORALUPDATES to replicate system-period and bitemporal tables along with associated history tables. Oracle GoldenGate replicates the row begin, row end, and transaction start id columns along with the other columns of the table. You must ensure that the database instance has the execute permission to run the SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY¿ stored procedure at the apply side.

By default, Oracle GoldenGate does not replicate row begin, row end, and transaction start id columns. To preserve the original values of these columns, implement one of the followings options.

  • Add extra timestamp columns in the target temporal table and map the columns accordingly.

  • Use a non-temporal table at the apply side and map the columns accordingly.

Replication in Heterogeneous Environment:

In heterogeneous environments where there is no temporal tables at the apply side, you need to set the row begin, row end and transaction start id columns value. These source columns will have timestamp values that the target database may not support. You should first use the map conversion functions to convert these values into the format that target database supports, and then map the columns accordingly. For example, MySQL has a DATETIME range from `1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999¿'. You cannot replicate a DB2 LUW timestamp value of `0001-01-01-¿ to MySQL. To replicate such values you must convert this value into the MySQL DATETIME format. For example, if a system-period or bitemporal table has the following timestamp column:


Then to replicate this column into MySQL, you would use the function colmap() as follows:

map <source_schema>.<source_table>, target <target_schema>.<target_table> 
colmap(sys_start= @IF( ( @NUMSTR( @STREXT(sys_start,1,4))) > 1000, sys_start, 

Initial Load of Temporal Table:

Oracle GoldenGate supports initial load of temporal table as usual.

Take into account the following considerations with temporal table:

  • Replication between system-period and application-period temporal table is not supported.

  • Replication from a non-temporal table to a temporal table is not supported.

  • Replication of system-period, bi-temporal tables, and SUPPRESSTEMPORALUPDATES with the INSERTALLRECORDS parameter is not supported.

  • If any unique index is created for application-period temporal table using BUSINESS_TIME WITHOUT OVERLAPS for the target table, then the same unique index must be created for the source table.

  • Bidirectional replication between temporal tables is advised only with the default.

  • CDR is supported only with SUPPRESSTEMPORALUPDATES. There is no CDR support in bidirectional replication.

  • By default, there are inconsistencies in row begin, row end, and transaction start id columns of the temporal tables when the source and target databases operate with different time zones. These timestamp columns of system-period and bitemporal tables are automatically populated by the respective database managers and will have values as per the respective time zones of the databases.

  • Using the default with GETUPDATEBEFORES is in the replicate parameter file, you cannot use the row begin, row end, and transaction start id columns in any delta calculations. For example, taking before and after image of such columns in any kind of calculations is not possible. These columns can be used in delta calculations using SUPPRESSTEMPORALUPDATES.


Valid for Integrated Replicat and Classic Replicat for Oracle. Controls whether or not triggers are fired during the Replicat session. Provides an alternative to manually disabling triggers. (Integrated Replicat does not require disabling of triggers on the target system.)

SUPPRESSTRIGGERS is the default and prevents triggers from firing on target objects that are configured for replication with Oracle GoldenGate. SUPPRESSTRIGGERS is valid for Oracle Database 12c, 11g (, and later 11g R2 releases. SUPPRESSTRIGGERS is not valid for 11g R1.

To allow a specific trigger to fire, you can use the following SQLEXEC statement in the Replicat parameter file, where trigger_owner is the owner of the trigger and trigger_name is the name of the trigger.



Once this SQLEXEC is executed with FALSE, the trigger will continue to fire until the command is run again with a setting of TRUE.

NOSUPPRESSTRIGGERS allows target triggers to fire. To use [NO]SUPPRESSTRIGGERS, the Replicat user must have the privileges granted through the dbms_goldengate_auth.grant_admin_privilege package. This procedure is part of the Oracle database installation. See the database documentation for more information.

The USERID or USERIDALIAS parameter must precede a DBOPTIONS statement that contains SUPPRESSTRIGGERS or NOSUPPRESSTRIGGERS.

TRANSNAME trans_name

Valid for Replicat for SQL Server. Allows an individual Replicat to use a specific transaction name that is specified in the parameter file. The trans_name is the name of the transaction that the Replicat uses for target DML transactions and overrides the default ggs_repl transaction name when used.


Valid for Replicat for SQL Server.

Configures Replicat to use ODBC to perform DML operations if using the SQL Server Native Client 11.0 driver. This parameter has no effect when using the SQL Server ODBC drivers. USEODBC cannot be used in conjunction with USEREPLICATIONUSER and will cause the Replicat to abend.

This parameter option is not allowed for SQL Server for Linux because Oracle GoldenGate for Linux only uses ODBC.


Valid for Replicat for SQL Server.

Configures the Replicat connection to support the NOT FOR REPLICATION option if enabled for identity columns, triggers, and foreign key and check constraints.

Enabling USEREPLICATIONUSER has the following effect:

  • IDENTITY inserts into tables with an Identity column that have the NOT FOR REPLICATION option enabled, do not set the SET IDENTITY_INSERT clause nor execute SELECT IDENT_CURRENT statements for multimaster implementations, resulting in improved performance.

  • Foreign key and check constraints are not enforced when enabled with the NOT FOR REPLICATION option.

  • Suppresses trigger execution on the target table if enabled with the NOT FOR REPLICATION option. Use this configuration if Replicat is configured to replicate transactions from the source table with the trigger, and the recipient table of the trigger operation..

  • For Replicat connections using a Microsoft ODBC driver, install the Microsoft OLE DB Driver 18 for SQL Server to support the USEREPLICATIONUSER option:


For the USEREPLICATIONUSER option to be effective, Identity columns, Triggers, and Foreign Key and Check constraints must be enabled within the table using the SQL Server NOT FOR REPLICATION option. For more information about these considerations, see Understanding What’s Supported for SQL Server in Using Oracle GoldenGate for Heterogeneous Databases.

USEREPLICATIONUSER is disabled by default and cannot be used in conjunction with the USEODBC option.

This parameter option is not allowed for SQL Server for Linux.


By default, the DB2 for i Extract converts all text data to UTF-8 for non-DBCS data and UTF-16 for DBCS data. Using this option causes the Extract to store all text data in the trail in its native character encoding for non-DBCS data. Currently, DBCS (GRAPHIC/VARGRAPHIC/DBCLOB) data continues to be converted to UTF-16 whether this parameter is provided or not. For CCSID values that are not supported by Oracle GoldenGate, the Extract converts the data to UTF-8 for non-DBCS data and UTF-16 for DBCS data to ensure compatibility for all Replicats.


Valid for Extract for Oracle. Sets the size of the memory buffer that stores XML data that was extracted from the sys.xmltype attribute of a SDO_GEORASTER object type. The default is 1048576 bytes (1MB). If the data exceeds the default buffer size, Extract will abend. If this occurs, increase the buffer size and start Extract again. The valid range of values is 1024 to 104857600 bytes.


Example 1   
Example 2   
Example 3   
Example 4   
Example 5