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. Some DBOPTIONS options apply only to Extract or Replicat.
The DBOPTIONS parameter can be placed anywhere in the parameter file irrespective of other parameters.
Default
None
Syntax
DBOPTIONS [ALLOWLOBDATATRUNCATE | NOALLOWLOBDATATRUNCATE] [ALLOWUNUSEDCOLUMN | NOALLOWUNUSEDCOLUMN] [BINDCHARFORBITASCHAR] [CATALOGCONNECT | NOCATALOGCONNECT] [CONNECTIONPORT port] [DECRYPTPASSWORD shared_secret ENCRYPTKEY {DEFAULT | key_name}] [DEFERREFCONST] [DISABLECOMMITNOWAIT] [DISABLELOBCACHING] [DRIVER SQLNCLI11] [ENABLE_INSTANTIATION_FILTERING] [EMPTYLOBSTRING 'string'] [FETCHBATCHSIZE records] [FETCHCHECKFREQ seconds] [FETCHLOBS | NOFETCHLOBS] [FETCHRETRYCOUNT number] [FECHTIMEOUT seconds | NOFECHTIMEOUT] [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] [SOURCE_DB_NAME src_dbase_global_name] [SPTHREAD | NOSPTHREAD] [SQLMODE] [SUPPRESSTEMPORALUPDATES] [SUPPRESSTRIGGERS | NOSUPPRESSTRIGGERS] [TDSPACKETSIZE bytes] [TRANSNAME trans_name] [USEODBC | USEREPLICATIONUSER] [XMLBUFSIZE bytes]
ALLOWUNUSEDCOLUMN | NOALLOWUNUSEDCOLUMNValid 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 | NOALLOWLOBDATATRUNCATEValid 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.
BINDCHARFORBITASCHARValid 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 | NOCATALOGCONNECTValid 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 portValid 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)
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_secretIs the encrypted shared secret (password) that is copied from the ENCRYPT PASSWORD command results.
algorithmSpecifies the encryption algorithm that was used to encrypt the password: AES128, AES192, AES256, or BLOWFISH. AES is not supported on DB2 on z/OS, and DB2 for i.
ENCRYPTKEY key_nameSpecifies 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 DEFAULTDirects 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 Configuring Capture in Integrated Modein Using Oracle GoldenGate for Oracle Database.
For more information about Oracle GoldenGate encryption options, including ENCKEYS, see Administering Oracle GoldenGate.
DEFERREFCONSTValid 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 is valid for Oracle Database 12c, 11g (11.2.0.2), and later 11g R2 releases.
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.
Note:
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
DISABLECOMMITNOWAITValid 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).
DISABLELOBCACHINGValid for nonintegrated Replicat for Oracle. Disables Oracle's LOB caching mechanism. By default, Replicat enables Oracle's LOB caching mechanism.
DRIVER SQLNCLI11Valid for SQL Server, Classic and CDC Extract only (Replicat is not supported). Sets the SQL Server VAM to use the SQLNCLI11 driver for use with communication. If the SQLNCLI11 driver is not available, the SQLOLEDB is used. It is required when TLS 1.2 is configured for your database environment.
ENABLE_INSTANTIATION_FILTERINGValid 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.
FETCHBATCHSIZE recordsValid for Extract for Oracle, DB2 for i, DB2 z/OS, SQL Server, and Teradata. Enables array fetches for initial loads to improve performance, rather than one row at a time.
Valid values for Oracle, DB2 for i, DB2 z/OS, SQL Server, and Teradata 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.
FETCHCHECKFREQ secondsValid 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.
FETCHLOBS | NOFETCHLOBSValid 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.
FETCHRETRYCOUNT numberValid 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.
FECHTIMEOUT seconds | NOFECHTIMEOUTValid 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.
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 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 Choosing Capture and Apply Modes in Using Oracle GoldenGate for Oracle Database.
LIMITROWS | NOLIMITROWSValid 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.
LOBBUFSIZE bytesValid 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.
LOBWRITESIZE bytesValid 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.
SESSIONPOOLMAX max_valueValid 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_valueValid 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_valueValid 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 | NULLValid 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 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. 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.
SKIPTEMPLOB | NOSKIPTEMPLOBValid 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_nameValid 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.
SPTHREAD | NOSPTHREADValid for Extract and Replicat for SQL Server. Creates a separate database connection thread for stored procedures. The default is NOSPTHREAD.
SQLMODEsql_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.
SUPPRESSTEMPORALUPDATESValid 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-00.00.00.000000000000¿ 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:
SYS_START -------------------------------- 0001-01-01-00.00.00.000000000000
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, '1000-01-01 00.00.00.000000'));
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.
SUPPRESSTRIGGERS | NOSUPPRESSTRIGGERSValid 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 Database 12c, 11g (11.2.0.2), 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.
SQLEXEC 'DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY('"S1"','"MY_TRIGGER"',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.
TRANSNAME trans_nameValid 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.
USEODBCValid for Replicat for SQL Server. Configures Replicat to use ODBC to perform DML operations. The default is to use OLE DB. USEODBC cannot be used in conjunction with USEREPLICATIONUSER and will cause the Replicat to abend.
Note:
Replicat always uses ODBC to connect to the database catalog to obtain metadata.
USEREPLICATIONUSERValid for Replicat for SQL Server. Configures the Replicat connection to honor the NOT FOR REPLICATIONproperty, which is set on objects within the database, such as Identity columns, Foreign Key columns, Check constraints, and Triggers.
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 Understanding What’s Supported for SQL Server in Using Oracle GoldenGate for Heterogeneous Databases.
By default, USEREPLICATIONUSER is disabled. The use of USEREPLICATIONUSER is only advised if delivery performance must be increased. USEREPLICATIONUSER cannot be used in conjunction with USEODBC and will cause the Replicat to abend.
XMLBUFSIZE bytesValid 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.