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
| 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 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. 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_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, and DB2 for i.
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 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.
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. 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
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.
DRIVER SQLNCLI11
Valid 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_FILTERING
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.
FETCHBATCHSIZE
records
Valid 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
seconds
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.
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
.
FETCHRETRYCOUNT
number
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.
FECHTIMEOUT
seconds
| NOFECHTIMEOUT
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.
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 | NOLIMITROWS
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.
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 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
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
.
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 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 | 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
.
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.
SPTHREAD | NOSPTHREAD
Valid for Extract and Replicat for SQL Server. Creates a separate database connection thread for stored procedures. The default is NOSPTHREAD
.
SQLMODE
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.
SUPPRESSTEMPORALUPDATES
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-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 | 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 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_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.
USEODBC
Valid 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.
USEREPLICATIONUSER
Valid for Replicat for SQL Server. Configures the Replicat connection to honor the NOT FOR REPLICATION
property, 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
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 104857600 bytes.