DBOPTIONS

Valid For

Extract and Replicat

Description

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

The TARGETDB or SOURCEDB parameter must precede a DBOPTIONS parameter statement and/or the USERIDALIAS statement. Some DBOPTIONS options apply only to Extract or Replicat.

Default

None

Syntax

DBOPTIONS
[ALLOWLOBDATATRUNCATE | NOALLOWLOBDATATRUNCATE]
[ALLOWUNUSEDCOLUMN | NOALLOWUNUSEDCOLUMN]
[BINDCHARFORBITASCHAR]
[CATALOGCONNECT | NOCATALOGCONNECT]
[CONNECTIONPORT port]
[DECRYPTPASSWORD shared_secret ENCRYPTKEY {DEFAULT | key_name}]
[DEFERREFCONST]
[DISABLECOMMITNOWAIT]
[DISABLELOBCACHING]
[EMPTYLOBSTRING 'string']
[FETCHBATCHSIZE records]
[FETCHLOBS | NOFETCHLOBS]
[HOST {DNS_name | IP_address}]
[INTEGRATEDPARAMS(parameter[, ...])]
[LIMITROWS | NOLIMITROWS]
[LOBBUFSIZE bytes]
[LOBWRITESIZE bytes]
[SESSIONPOOLMAX max_value | 
[SESSIONPOOLMIN min_value][SESSIONPOOLINCR increment_value]
[SETTAG [tag_value | NULL] ]
[SHOWINFOMESSAGES]
[SHOWWARNINGS]
[SKIPTEMPLOB | NOSKIPTEMPLOB]
[SPTHREAD | NOSPTHREAD]
[SUPPRESSTRIGGERS | NOSUPPRESSTRIGGERS]
[TDSPACKETSIZE bytes]
[TRANSNAME trans_name]
[TRUSTEDCONNECTION]
[USEODBC | USEREPLICATIONUSER]
[XMLBUFSIZE bytes]
ALLOWUNUSEDCOLUMN | NOALLOWUNUSEDCOLUMN

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.

ALLOWLOBDATATRUNCATE | NOALLOWLOBDATATRUNCATE

Valid for Replicat for DB2 LUW, Sybase, 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.

BINDCHARFORBITASCHAR

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.

CATALOGCONNECT | NOCATALOGCONNECT

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.

CONNECTIONPORT port

Valid for Replicat for multi-daemon MySQL. Specifies the TCP/IP port of the instance to which Replicat must connect.

DECRYPTPASSWORD shared_secret algorithm ENCRYPTKEY {key_name | DEFAULT}

Valid for Extract in classic capture mode (Oracle)

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:

shared_secret

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

algorithm

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, DB2 for i, and SQL/MX.

ENCRYPTKEY key_name

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.

ENCRYPTKEY DEFAULT

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

For more information about configuring Extract to support TDE, see Installing and Configuring Oracle GoldenGate for Oracle Database.

For more information about Oracle GoldenGate encryption options, including ENCKEYS, see Administering Oracle GoldenGate for Windows and UNIX.

DEFERREFCONST

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.

If used with an Oracle version 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.

DISABLECOMMITNOWAIT

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).

DISABLELOBCACHING

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

EMPTYLOBSTRING 'string'

Valid for Replicat for Sybase. Substitutes a string value for empty (zero-length) LOB columns, such as Sybase IMAGE or TEXT values, that are replicated to the target. By default, Oracle GoldenGate sets empty columns to NULL on the target and will abend if the target database does not permit LOB columns to be NULL. This option prevents Replicat from abending.

For 'string' use any string that the column accepts, and enclose the string within single quotes. The default is NULL.

Example:

DBOPTIONS EMPTYLOBSTRING 'empty'
FETCHBATCHSIZE records

Valid for Extract for Oracle, DB2, SQL/MX, Sybase, SQL Server, Sybase, and Teradata. Enables array fetches for initial loads to improve performance, rather than one row at a time. Valid values are 0 through 1000000 records per fetch. 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.

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.

FETCHLOBS | NOFETCHLOBS

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.

INTEGRATEDPARAMS(parameter[, ...])

Valid for Replicat for Oracle. Passes settings for parameters that control the database inbound server within the target Oracle database. Use this option only for an integrated Replicat. For more information about integrated Replicat and a list of supported inbound server parameters, see Installing and Configuring Oracle GoldenGate for Oracle Database.

LIMITROWS | NOLIMITROWS

Valid for Replicat for MySQL, Oracle, SQL Server, and Sybase. 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 and Sybase, 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.

LOBBUFSIZE bytes

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 10485760 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.
LOBWRITESIZE bytes

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.

SHOWINFOMESSAGES

Valid for Extract and Replicat for Sybase. Enables the following Sybase server messages to be printed to the error log.

0: /* General informational message */
5701: /* Changed Database Context */
5703: /* Changed language setting */
5704: /* Changed client character set */
7326: /* Non ANSI Escaping */

Normally, these messages are suppressed because they do not affect Oracle GoldenGate processing.

SHOWWARNINGS

Valid for Extract and Replicat for Sybase. Enables the logging of Sybase server messages with a severity level greater than 10. These messages may be useful for debugging when Sybase performs corrective action that causes data to change.

SESSIONPOOLMAX max_value

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.

SESSIONPOOLMIN min_value

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 also can be used to identify transactions other than those of Replicat.

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. Valid value is any single Oracle Streams tag. A tag value can be up to 2000 hexadecimal digits (0-9 A-F) long. For more information about Streams tags, see Oracle Streams Replication Administrator's Guide

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.

hex_value

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

DBOPTIONS SETTAG 00112233445566778899AABBCCDDEEFF
DBOPTIONS SETTAG 00112233445566778899aabbccddeeff
DBOPTIONS SETTAG 123
NULL

Disables tag-based filtering for the associated Replicat.

SKIPTEMPLOB | NOSKIPTEMPLOB

Valid for Replicat for Oracle database versions 11g and 12c. Controls how LOBs are applied to a target Oracle database. The default of 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 also does not apply to partial LOB operations.

Oracle GoldenGate allocates a minimum 1MB LOB-write chunk size when SKIPTEMPLOB is enabled, and a warning message is issued if a value of less than 1MB is specified for the LOBWRITESIZE option of DBOPTIONS. The LOB write chunk size for each column is adjusted and rounded by the database, and the actual LOB write chunk size may differ from the value of LOBWRITESIZE depending on the LOB storage attribute of the target table and the LOB type.

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

SPTHREAD | NOSPTHREAD

Valid for Extract and Replicat. Creates a separate database connection thread for stored procedures. The default is NOSPTHREAD.

SUPPRESSTRIGGERS | NOSUPPRESSTRIGGERS

Valid for nonintegrated 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 11.2.0.2 and later 11gR2 versions. SUPPRESSTRIGGERS is not valid for 11gR1.

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.

SQLEXEC 'dbms_ddl.set_trigger_firing_property(trigger_owner "trigger_name", FALSE);'

Note:

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.

TDSPACKETSIZE bytes

Valid for Extract and Replicat for Sybase. Sets the TDS packet size for replication to a Sybase target.

Valid values:

  • Sybase version 12.5.4:

    Note:

    This version is de-supported as of Oracle GoldenGate 11.2.1.

    512 to 65024

    Default is 0 for Extract, 512 for Replicat

  • Sybase15 or higher:

    2048 to 65024

    Default is 0 for Extract, 2048 for Replicat

The value must be a multiple of 512. The range of values that are set for the Sybase Adaptive Server max network packet size and additional network memory parameters must support the value that is set with TDSPACKETSIZE.

Note:

The higher the max network packet size value, the more memory (as set with additional network memory) the database server needs to allocate for the network data.

For best performance, choose a server packet size that works efficiently with the underlying packet size on your network. The goals of this procedure are to:

  • Reduce the number of server reads and writes to the network.

  • Reduce unused space in the network packets to increase network throughput.

For example, if your network packet size carries 1500 bytes of data, you can achieve better transfer performance by setting the packet size on the server to 1024 (512 x 2) than by setting it to 1536 (512 x 3).

For optimal performance, start with the following configuration:

DBOPTIONS TDSPACKETSIZE 8192

The DBOPTIONS parameter that contains TDSPACKETSIZE must be placed before the SOURCEDB or TARGETDB parameter in the parameter file.

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.

TRUSTEDCONNECTION

Valid for Extract and Replicat for SQL Server. Causes Oracle GoldenGate to connect by using trusted connection = yes. Contact Oracle Support before using this option.

USEODBC

Valid for Replicat for SQL Server. Configures Replicat to use ODBC to perform DML operations. The default is to use OLE DB. Not valid if USEREPLICATIONUSER is enabled; will cause Replicat to abend.

Note:

Replicat always uses ODBC to connect to the database catalog to obtain metadata.
USEREPLICATIONUSER

Valid for Replicat for SQL Server. Configures Replicat to perform target DML operations as the SQL Server replication user. The replication user is not a SQL Server user or account, but is a property of the database connection. USEREPLICATIONUSER enables the SQL Server NOT FOR REPLICATION flag.

When the replication user is used, the following concerns must be addressed for their effect on data integrity:

  • IDENTITY seeds on the target are not updated. A partitioning scheme is needed to avoid primary key violations unless the target is read-only.

  • Foreign key constraints are not enforced.

  • ON UPDATE CASCADE, ON DELETE CASCADE and triggers are disabled. This is beneficial to Replicat, since it prevents duplicate operations, but may not be appropriate for the target applications and might require modification to the code of the constraint or trigger to ensure data integrity.

  • CHECK constraints are not enforced, so data integrity cannot be certain on the target.

When using USEREPLICATIONUSER, IDENTITY properties and constraints must be set with the 'not for replication' option at the object level within the database. For more information about these considerations, see Installing and Configuring Oracle GoldenGate for SQL Server.

By default, USEREPLICATIONUSER is disabled and the default is to use OLE DB. The use of USEREPLICATIONUSER is only advised if delivery performance must be increased. Not valid if USEODBC is enabled; will cause Replicat to abend.

XMLBUFSIZE bytes

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 10485760 bytes.

Examples

Example 1   
DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3307
Example 2   
DBOPTIONS DECRYPTPASSWORD AACAAAAAAAAAAAIALCKDZIRHOJBHOJUH ENCRYPTKEY DEFAULT
Example 3   
DBOPTIONS TDSPACKETSIZE 2048
Example 4   
DBOPTIONS FETCHBATCHSIZE 2000
Example 5   
DBOOPTION XMLBUFSIZE 2097152