6.170 TRANLOGOPTIONS

Valid For

Extract

Description

Use the TRANLOGOPTIONS parameter to control the way that Extract interacts with the transaction log or with the API that passes transaction data, depending on the database or capture mode. You can use multiple TRANLOGOPTIONS statements in the same parameter file, or you can specify multiple options within the same TRANLOGOPTIONS statement, if permissible for those options.

Use a given TRANLOGOPTIONS option only for the database or databases for which it is intended.

Default

None

Syntax

TRANLOGOPTIONS {
[{ACTIVATIONIDPADLEN | DATABASEIDPADLEN | THREADPADLEN | SEQPADLEN | RESETLOGSIDPADLEN} width]
[ADGAPPLYCHECKFREQ seconds | cseconds | mseconds]
[ADGCRETRYCOUNT number]
[ADGTIMEOUT seconds]
[ALLOWTABLECOMPRESSION]
[ALTARCHIVEDLOGFORMAT string] [INSTANCE instance] [THREADID id]
[ALTARCHIVELOGDEST [PRIMARY] [INSTANCE instance] path] 
[ALTLOGDEST path | REMOTE]
[ARCHIVEDLOGONLY]
[{ASMBUFSIZE size | DBLOGREADERBUFSIZE size}]
[ASMUSER SYS@ASM_instance, ASMPASSWORD password
   [algorithm ENCRYPTKEY {key_name | DEFAULT}] 
[ASMUSERALIAS alias [DOMAIN domain]]
[ASYNCTRANSPROCESSING buffer_size]
[BUFSIZE size]
[CHECKPOINTRETENTIONTIME days]
[CHECKTABLELEVELSUPPLOG]
[COMPLETEARCHIVEDLOGONLY | NOCOMPLETEARCHIVEDLOGONLY]
[COMPLETEARCHIVEDLOGTIMEOUT seconds]
[DB2APIRETRY       retry_count]
[DB2ZV11COMPATIBILITYMODE]
[DBLOGREADER]
[DBLOGREADERBUFSIZE size]
[DLFAILOVER_TIMEOUT seconds]
[DISABLESOFTEOFDELAY]
[EXCLUDETAG [tag | NULL] | [EXCLUDETAG +]
[EXCLUDETRANS transaction]
[EXCLUDEUSER user]
[EXCLUDEUSERID Oracle_uid]
[FAILOVERTARGETDESTID n]
[FETCHLOBIFERROR]
[FETCHPARTIALLOB]
[FETCHINLINESFLOB]
[FETCHPARTIALXML]
[FILTERTABLE table]
[FORCEFETCHLOB]
[GETCTASDML | NOGETCTASDML]
[HANDLEDLFAILOVER]
[IGNOREDATACAPTURECHANGES | NOIGNOREDATACAPTURECHANGES]
[IGNOREDIRECTLOADINSERTS]
[INCLUDEAUX (AUX_specification)]
[INCLUDEREGIONID | INCLUDEREGIONIDWITHOFFSET]
[INTEGRATEDPARAMS (ENABLE_PROCEDURAL_REPLICATION Y] [, ...])]
[LOGRETENTION [ENABLED | SR | DISABLED]
[LOGSOURCE platform, [PATHMAP path]]

[MAXWARNEOF seconds]

[MAXAUTOCMTTRANSSIZE (range, default)]
[MINEFROMACTIVEDG | NOMINEFROMACTIVEDG]
[MINEFROMSNAPSHOTSTBY | NOMINEFROMSNAPSHOTSTBY]
[MININGUSER {/ | user}[, MININGPASSWORD password]
   [algorithm ENCRYPTKEY {key_name | DEFAULT}] [SYSDBA]
[MININGUSERALIAS alias [DOMAIN domain]]
[TRANLOGOPTIONS MIXEDENDIAN [ON|OFF] [NODDLCHANGEWARNING]]
[NOFLUSH]
[NOMANAGECDCCLEANUP | MANAGECDCCLEANUP] 
[PATHMAP NFS_mount_point log_path]
[PREPAREFORUPGRADETOIE | NOPREPAREFORUPGRADETOIE]
[PERFORMANCEPROFILE HIGH|MEDIUM]
[PURGEORPHANEDTRANSACTIONS | NOPURGEORPHANEDTRANSACTIONS]
[QUERYTIMEOUT number]
[QUERYRETRYCOUNT number]
[READQUEUESIZE size]
[READTIMEOUT milliseconds]
[REDO_TRANSPORT_LAG_THRESHOLD seconds]
[REQUIRELONGDATACAPTURECHANGES | NOREQUIRELONGDATACAPTURECHANGES]
[SOURCE_OS_TIMEZONE timezone]
[SUPPRESSNOOOPUPDATES]
[TRANSCLEANUPFREQUENCY minutes]

[TRANCOUNT integer]
[TSLOOKUPBEGINLRI | TSLOOKUPENDLRI]
[VALIDATEINLINESFLOB]
[USE_ROOT_CONTAINER_TIMEZONE]
[USENATIVEOBJSUPPORT | NOUSENATIVEOBJSUPPORT]
[USEPREVRESETLOGSID | NOUSEPREVRESETLOGSID]
[VALIDATEINLINESFLOB]
[VAMCOMPATIBILITY {1 | 2 | 3}]
}
[, ...]
{ACTIVATIONIDPADLEN | DATABASEIDPADLEN | THREADPADLEN | SEQPADLEN | RESETLOGSIDPADLEN} width

Valid for Extract in classic capture mode for Oracle.

Specifies the minimum default padding length when Extract forms the archive log name using the format specifiers %A, %D, %T, %S, and %R in the ALTARCHIVELOGFORMAT parameter. When the corresponding number is smaller than the field width, it is zero-padded on the left. Table 6-23 shows the specifier that relates to each option and the default length.

Table 6-23 Archive Log Specifier and Default Padding Length

Option Specifier Default padding length

ACTIVATIONIDPADLEN

%A

8

DATABASEIDPADLEN

%D

8

THREADPADLEN

%T

3 on Windows, 4 on other platforms

SEQPADLEN

%S

5 on Windows, 10 on other platforms

RESETLOGSIDPADLEN

%R

10
ADGAPPLYCHECKFREQ seconds | cseconds | mseconds

Valid for Integrated Extract for Oracle.

Specifies the number of seconds, cseconds, or mseconds 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 minimum is 1 and the maximum is 120 seconds.

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

ADGTIMEOUT seconds

Valid for Extract in classic capture mode for Oracle.

Sets the interval, in seconds, after which Extract times out if v$database.current_scn has not moved past the commit SCN associated with the record for which it needs to process. The default is 30 seconds. The minimum is 5 and the maximum is 2147483646. Supports Extract in classic capture mode when capturing in an Oracle Data Guard environment.

ALLOWTABLECOMPRESSION

Valid for DB2 LUW version 9.5 and 9.7.

Enables Oracle GoldenGate to support tables created with row compression, as long as the tables do not contain LOBs. When this parameter is set, LOB columns are not supported, whether or not the table is compressed. To capture from a source where some tables have row compression and some do not, process the compression-enabled tables with one Extract group and the non-compressed tables with another Extract group.

ALTARCHIVEDLOGFORMAT string [INSTANCE instance] [THREADID id]

Valid for Extract in classic capture mode for Oracle.

Specifies a string that overrides the archive log format of the source database.

In an Oracle RAC environment, use the ALTARCHIVEDLOGFORMAT parameter on each node. To ensure that Extract can differentiate between the log streams, use the INSTANCE or THREADID option. The default log format that is queried from the database for one RAC thread is assumed for all of the other threads if Extract cannot find a log format and nothing is specified with INSTANCE or THREADID.

The TRANLOGOPTIONS statement that includes ALTARCHIVEDLOGFORMAT cannot contain any other TRANLOGOPTIONS options. Use a separate TRANLOGOPTIONS statement to specify other options.

string

Accepts the same specifier as Oracle's parameter LOG_ARCHIVE_FORMAT. Extract uses the supplied format specifier to derive the log file name. Example:

arch_%T.arc
INSTANCE instance

For use with Oracle RAC. Applies ALTARCHIVEDLOGFORMAT to a specific Oracle instance. Extract verifies the supplied input against the database catalog.

Example:

TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT &
INSTANCE rac1 log_%t_%s_%r.arc
THREADID id

For use with Oracle RAC. Specifies the thread number of the instance that has the specified log format.

Example:

TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT &
THREADID 2 log_%t_%s_%r.arc
ALTARCHIVELOGDEST [PRIMARY] [INSTANCE instance]
[THREADID id] path

Valid for Extract in classic capture mode for Oracle.

Points Extract to the archived or backup Oracle transaction logs when they reside somewhere other than the default location. Extract first checks the specified location and then checks the default location.

path

Specifies the fully qualified path to the archived logs in the alternate directory. This directory must be NFS mounted to the node where Oracle GoldenGate is running. Use that mount point for ALTARCHIVELOGDEST.

PRIMARY

Prevents Extract from checking the default log location if it does not find the log in the alternate location. Only the ALTARCHIVELOGDEST path is checked. PRIMARY is the default for an Extract that is running in Archived Log Only (ALO) mode; otherwise, it is optional.

INSTANCE instance

Applies the specified ALTARCHIVELOGDEST behavior to a specific Oracle instance. On RAC, if this option is used, you must specify the ALTARCHIVELOGDEST parameter on each node.

THREADID id

Applies the specified ALTARCHIVELOGDEST behavior to a specific thread number.

ALTLOGDEST path | REMOTE

Valid for MySQL.

Specifies the location of the MySQL log index file. Extract looks for the log files in this location instead of the database default location. ALTLOGDEST can be used when the database configuration does not include the full path name to the logs or when there are multiple MySQL installations on the machine. Extract reads the log index file to find the binary log file that it needs to read. When ALTLOGDEST is used, Extract assumes that the logs and the index are in the same location.

Supply the full path name to the directory. On Windows, enclose the path within double quotes if the path contains any spaces, such as in the following example.

TRANLOGOPTIONS  ALTLOGDEST "C:\Program Files\MySQL\MySQL Server 5.1\log\test.index"

On Linux, use this format:

TRANLOGOPTIONS ALTLOGDEST "/mnt/rdbms/mysql/data/logs/binlog.index"

To capture from a remote server or in case of remote capture, you only need to specify the REMOTE option instead of the index file path on the remote server. For remote capture on both Windows and Linux, specify the following in the Extract parameter file (capture.prm):

TRANLOGOPTIONS ALTLOGDEST REMOTE

For more information on using the REMOTE option, see Setting Logging Parameters.

ARCHIVEDLOGONLY

Valid for Extract in classic capture mode for SQL Server.

ARCHIVEDLOGONLY causes Extract to read from the transaction log backups exclusively. This parameter puts Extract into Archived Log Only mode (ALO) and allows the ability to run the Extract on a different Windows server, other than the database server. ALO mode is incompatible with the ACTIVESECONDARYTRUNCSTIONPOINT parameter. For more information about archived-log only mode, see Requirements Summary for Classic Extract in Archived Log Only (ALO) Mode in Using Oracle GoldenGate for Heterogeneous Databases.

(Oracle) Valid for Extract in classic capture mode.

ARCHIVEDLOGONLY causes Extract to read from the archived logs exclusively, without querying or validating the logs from system views such as v$log and v$archived_log. This parameter puts Extract into Archived Log Only mode (ALO). By default, Extract does not use archived log-only mode even if the database that it connects to is a physical standby database. For more information about archived-log only mode, see Example 2: Capturing from Multiple Sources in Archive-log Only Mode in Using Oracle GoldenGate for Oracle Database.

ASMBUFSIZE size

Valid for Extract in classic capture mode for Oracle.

Controls the maximum size, in bytes, of a read operation into the internal buffer that holds the results of each read of the transaction log. Use this option instead of the DBLOGREADERBUFSIZE option if the source Oracle version is one that is:

  • 11g that is earlier than 11.2.0.2

  • any Oracle 11g R1 version

These versions do not support the newer API that is available in Oracle versions that are supported by the DBLOGREADER option. It is recommended that you use the DBLOGREADER option together with the DBLOGREADERBUFSIZE option if supported by your Oracle version.

Higher values increase extraction speed but cause Extract to consume more memory. Low values reduce memory usage but increase I/O because Extract must store data that exceeds the cache size to disk.

The following are the valid ranges and default sizes, in bytes:

  • Minimum: size of one block in the redo log

  • Maximum: 4 MB

  • Default: 2 MB (2097152)

The value of the BUFSIZE option must always be at least equal to, or greater than, the value of DBLOGREADERBUFSIZE.

ASMUSER SYS@ASM_instance, ASMPASSWORD password [algorithm
ENCRYPTKEY {key_name | DEFAULT}]

Valid for Extract in classic capture mode for Oracle.

Specifies credentials for logging in to an ASM instance to read the transaction logs. Can be used instead of ASMUSERALIAS if an Oracle GoldenGate credential store is not being used.

SYS@ASM_instance

Specifies the ASM instance for the connection string. The user must be SYS.

password

Is the encrypted 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.

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.

ENCRYPTKEY DEFAULT

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

Note:

This parameter does not replace the standard USERID parameter. Both are required in an ASM environment. ASMUSER is not needed if using the DBLOGREADER option to read the logs.

See Encrypting Data with the ENCKEYS Method in Administering Oracle GoldenGatefor more information about password security features.

ASMUSERALIAS alias [DOMAIN domain]

Valid for Extract in classic capture mode for Oracle.

Specifies credentials for logging in to an ASM instance to read the transaction logs. Can be used instead of ASMUSER if an Oracle GoldenGate credential store is being used.

alias

Specifies the alias of the login credential that will be used to log into the ASM instance. This credential must exist in the Oracle GoldenGate credential store. If you are not sure what alias to use, you can inspect the content of the credential store by issuing the INFO CREDENTIALSTORE command..

DOMAIN domain

Specifies the domain that is assigned to the specified alias in the credential store.

For more information about the credential store, see Managing Identities in a Credential Store in Administering Oracle GoldenGate.

Note:

This parameter does not replace the standard USERIDALIAS parameter. Both are required in an ASM environment. ASMUSERALIAS is not needed if using the DBLOGREADER option to read the logs.

ASYNCTRANSPROCESSING buffer_size

Valid for Extract in integrated capture mode for Oracle.

Controls whether integrated capture runs in asynchronous or synchronous processing mode, and controls the buffer size when Extract is in asynchronous mode. The minimum is 1 and the maximum is 1024; the default is 300.

ASYNCTRANSPROCESSING buffer_size

In asynchronous transaction processing mode, there are two threads of control:

  • One thread groups logical change records (LCR) into transactions, does object-level filtering, and does partial rollback processing,

  • The other thread formats committed transactions, performs any user-specified transformations, and writes to the trail file.

The transaction buffer is the buffer between these two threads and is used to transfer work from one thread to the other. The default transaction buffer size is 300 committed transactions, but is adjusted downward by the Oracle GoldenGate memory manager if its cache memory is close to being exhausted.

NOASYNCTRANSPROCESSING

Disables asynchronous processing and causes Extract to operate in synchronous mode. In this mode, one thread performs all capture work.

BUFSIZE size

Valid for DB2 LUW, DB2 z/OS, and Oracle.

Controls the maximum size, in bytes, of the buffers that are allocated to contain the data that is read from the transaction log.

  • For an Oracle source where Extract is processing file-based redo, this parameter also controls the maximum size, in bytes, of a read operation into the buffer.

  • For an Oracle source where Extract is processing ASM redo, TRANLOGOPTIONS with either ASMBUFSIZE or DBLOGREADERBUFSIZE controls the read size, and in both cases BUFSIZE controls the buffer size. This parameter must be equal to, or greater than, the value that is set for ASMBUFSIZE or DBLOGREADERBUFSIZE (depending on which is in use.)

High values increase capture speed but cause Extract to consume more memory. Low values reduce memory usage but increase I/O because Extract must store data that exceeds the cache size to disk.

The following are the valid ranges and default sizes, in bytes:

Oracle:

  • Minimum: 8,192

  • Maximum: 10,000,000

The default buffer size is determined by the source of the redo data:

  • For file-based redo, the default is 1000KB (1024000).

  • For ASM redo, the default is 1000KB (1024000).

  • For DBLOGREADER redo, the default is 2MB (2097152).

  • For Extract in integrated capture mode, the default is 1000KB (1024000).

DB2 LUW:

  • Minimum: 8,192

  • Maximum: 10,000,000

  • Default: 204,800

  • The preceding values must be in multiples of the 4096 page size. Extract will truncate to a multiple if a given value does not meet this requirement.

DB2 z/OS

  • Minimum: 36KB (36864)

  • Maximum: 32MB (33554432)

  • Default: 2MB (2097152)

  • The preceding values must be in multiples of the 4096 page size. Extract will truncate to a multiple if a given value does not meet this requirement.

  • Each Extract uses a fixed 68KB of ECSA on the DB2 z/OS system that the Extract connects to.

CHECKPOINTRETENTIONTIME days

Valid for Extract in integrated mode only for Oracle.

Controls the number of days that Extract retains checkpoints before they are purged. Partial days can be specified using decimal values. For example, 8.25 specifies 8 days and 6 hours. When the checkpoint of an Extract in integrated capture mode is purged, LogMiner data dictionary information for the archived redo log file that corresponds to the checkpoint is purged, and the first_scn value of the capture process is reset to the SCN value corresponding to the first change in the next archived redo log file. The default is seven days and the minimum is 0.00001.

CHECKTABLELEVELSUPPLOG

Valid for Extract in classic capture mode for Oracle.

Causes Extract to send a warning to the report file if it encounters a table for which the ADD TRANDATA command was not issued to create an Oracle GoldenGate supplemental log group. CHECKTABLELEVELSUPPLOG also verifies whether the key columns in any user-defined log groups for the table are the same as, or a superset of, the key columns of the log group that was created with the ADD TRANDATA command. Without key columns, Extract may abend or try to fetch the missing column or columns. By default, CHECKTABLELEVELSUPPLOG verification is disabled.

COMPLETEARCHIVEDLOGONLY | NOCOMPLETEARCHIVEDLOGONLY

Valid for Extract in classic capture mode for Oracle.

Overrides the default Extract processing of archived logs. This parameter applies when copying production (source) archive logs to a secondary database where they will serve as the data source. Some Oracle programs do not build the archive log from the first byte to the last byte in sequential order, but instead may copy the first 500MB, then the last 500MB, and finally the middle 1000MB, for example. If Extract begins reading at the first byte, it will abend when it reaches the break in the byte sequencing. Waiting for the whole file to be written prevents this problem.

Note that Extract starts to read an archive file before it is completely written to disk, but whether or not it starts to capture data before the file is complete depends on whether COMPLETEARCHIVEDLOGONLY or NOCOMPLETEARCHIVEDLOGONLY is used.

COMPLETEARCHIVEDLOGONLY

This is the default in ALO (archived log only) mode. It forces Extract to wait for the archived log to be written to disk completely before starting to process redo data. In regular mode, use it to override the default of NOCOMPLETEARCHIVEDLOGONLY.

NOCOMPLETEARCHIVEDLOGONLY

This is the default in regular mode. Extract starts processing redo data from an archived log immediately when it becomes available, without waiting for it to be written completely to disk. In ALO mode, use it to override the default of COMPLETEARCHIVEDLOGONLY.

COMPLETEARCHIVEDLOGTIMEOUT seconds

Valid for Extract in classic capture mode for Oracle.

Controls the number of seconds that Extract waits, when in COMPLETEARCHIVEDLOGONLY mode, to try again if it cannot validate that a redo log is being completely written to disk. Use this option in conjunction with the COMPLETEARCHIVEDLOGONLY option of TRANLOGOPTIONS. This option is disabled by default, and Extract will abend after ten seconds if it cannot validate that the file is being written to disk. This check is performed by reading the block header from the last block and verifying against the expected sequence number to determine if the last block has been written out. For seconds use any value greater than 0. The default is 20.

DB2APIRETRY number of retries
If Extract receives an error from the DB2 log reading APIdb2ReadLog(), then for certain errors the API call is retried. Use the DB2APIRETRY to change the number of retries. The default number of retries is set to 3. SQL code for which the API is retried is SQLCODE -30108.
DB2ZV11COMPATIBILITYMODE

Valid for Extract for DB2 z/OS.

When using Oracle GoldenGate to extract from DB2 z/OS version 11 in some compatibility modes, the Extract process may not programmatically determine the actual database version and an OGG-00551 or OGG-00804 error occurs. Use this option in your Extract parameter file to manually set the correct database version.

DBLOGREADER

Valid for Extract in classic capture mode for Oracle.

Causes Extract to use a newer API that is available as of Oracle 11.2.0.2 and later 11g R2 versions. This API uses the database server to access the redo and archive logs. DBLOGREADER can be used to mine logs on regular disks and raw disks, and can be used instead of connecting directly to an Oracle ASM instance. The database system must contain the libraries that contain the API modules and must be running. To use this feature, the Extract database user must have SELECT ANY TRANSACTION privilege.

When used, DBLOGREADER enables Extract to use a read size of up to 4 MB in size. This is controlled with the DBLOGREADERBUFSIZE option. The maximum read size when using the default OCI buffer is 28672 bytes. This is controlled by the ASMBUFSIZE option. A larger buffer may improve the performance of Extract when redo rate is high.

When using DBLOGREADER with ASM, do not use the ASMUSER or ASMUSERALIAS and ASMPASSWORD options of TRANLOGOPTIONS. The API uses the user and password specified with the USERID or USERIDALIAS parameter. For more information about using Oracle GoldenGate with ASM, see Mining ASM-stored Logs in Classic Capture Mode in Using Oracle GoldenGate for Oracle Database.

Note:

DBLOGREADERalso can be used when the redo and archive logs are on regular disk or on a raw device.
DBLOGREADERBUFSIZE size

Valid for Extract in classic capture mode for Oracle.

Controls the maximum size, in bytes, of a read operation into the internal buffer that holds the results of each read of the transaction log in ASM. High values increase capture speed but cause Extract to consume more memory. Low values reduce memory usage but increase I/O because Extract must store data that exceeds the cache size to disk.

Use DBLOGREADERBUFSIZE together with the DBLOGREADER option if the source ASM instance is Oracle 11.2.0.2 and later 11g R2 versions. The newer ASM API in those versions provides better performance than the older one. If the Oracle version is not one of those versions, then ASMBUFSIZE must be used.

The following are the valid ranges and default sizes, in bytes:

  • Minimum: size of one block in the redo log

  • Maximum: 4 MB

    On AIX, the maximum buffer size is 1048576; any attempt to read more than this maximum will result in error.

  • Default: 2 MB (2097152)

The default should be sufficient in most cases.

The value of the BUFSIZE option must always be at least equal to, or greater than, the value of DBLOGREADERBUFSIZE.

DLFAILOVER_TIMEOUT seconds

Valid for Extract in integrated mode for Oracle.

Provides a configurable timeout in seconds to allow for standby database reinstatement post-role transition. It is used in conjunction with HANDLEDLFAILOVER to allow Integrated Extract to start up immediately after a role transition. At the end of the timeout period, if the standby database is still not available, then Extract will terminate.

The default is 300 seconds. You can also use centiseconds or milliseconds.

DISABLESOFTEOFDELAY

Valid for Extract only in integrated or classic mode for Oracle and DB2 LUW. .

Use DISABLESOFTEOFDELAY in the Extract parameter file to set that the wait time takes effect when the an EOF status is reported with no records to return.

[EXCLUDETAG [tag | NULL] | [EXCLUDETAG +]

Valid for Extract in integrated or classic mode for Oracle.

Use EXCLUDETAG tag to direct the Extract process to ignore the individual records that are tagged with the specified redo tag. There is no database release limitation for this parameter though not all releases of Oracle Database support tagging. Compare with older versions, new trail file contains tag tokens, which would not introduce problems for older trail readers.

Use EXCLUDETAG + to direct the Extract process to ignore the individual records that are tagged with any redo tag.

To tag the individual records, use the DBOPTIONS parameter with the SETTAG option in the Replicat parameter file. Use these parameters to prevent cycling (loop-back) of Replicat the individual records in a bidirectional configuration or to filter other transactions from capture. The default SETTAG value is 00. Valid value is any single Oracle Streams tag or a plus sign (+). A tag value can be up to 2000 hexadecimal digits (0-9 A-F) long. The dbms_streams.set_tag operation is supported by EXCLUDETAG. For more information about Streams tags, see Oracle Streams Tags in Oracle Streams Replication Administrator's Guide.

EXCLUDETRANS transaction

Valid for Integrated Extract for Oracle.

Specifies the transaction name of the Replicat database user or any other user so that those transactions are not captured by Extract. Use for bi-directional processing to prevent data looping between the databases.

Only valid for Sybase and SQL Server, the default transaction name used by Replicat is ggs_repl. For more information about bidirectional synchronization, see Overview of an Active-Active Configuration in Administering Oracle GoldenGate.

EXCLUDEUSER user

Valie for DB2 LUW, DB2 for z/OS, DB2 for i, and Oracle.

Specifies the name of the Replicat database user, or of any other user, to be used as a filter that identifies transactions that will be subject to the rules of the GETREPLICATES or IGNOREREPLICATES parameter. Typically, this option is used to identify Replicat transactions in a bi-directional or cascading processing configuration, for the purpose of excluding or capturing them. However, it can be used to identify transactions by any other user, such as those of a specific business application.

You can use EXCLUDEUSER and EXCLUDEUSERID in the same parameter file. Do not use wildcards in either parameter.

The user name must be valid. Oracle GoldenGate queries the database to get the associated user ID and maps the numeric identifier back to the user name. For this reason, if the specified user is dropped and recreated while name resolution is set to the default of DYNAMICRESOLUTION, EXCLUDEUSER remains valid. If the same transaction is performed when name resolution is set to NODYNAMICRESOLUTION, EXCLUDEUSER becomes invalid, and Extract must be stopped and then started to make EXCLUDEUSER take effect. See "DYNAMICRESOLUTION | NODYNAMICRESOLUTION" for more information.

  • DB2 z/OS considerations: In DB2 for z/OS, the user is always the primary authorization ID of the transaction, which is typically that of the original RACF user who logged on, but also could be a different authorization ID if changed by a transaction processor or by DB2 exits.

  • Oracle considerations: For an Oracle and DB2 LUW database, multiple EXCLUDEUSER statements can be used. All specified users are considered the same as the Replicat user, in the sense that they are subject to the rules of GETREPLICATES or IGNOREREPLICATES. You must include the IGNOREAPPLOPS parameter for EXCLUDEUSER to operate correctly unlike all other supported databases.

EXCLUDEUSERID Database_uid

Valid for Extract for Oracle.

Specifies the database user ID (uid) of the Replicat database user, or of any other user, to be used as a filter that identifies transactions that will be subject to the rules of the GETREPLICATES or IGNOREREPLICATES parameter.

Usage is the same as that of EXCLUDEUSER.

Oracle_uid is a non-negative integer with a maximum value of 2147483638. There are several system views that can be queried to get the user ID. The simplest one is the ALL_USERS view. Oracle GoldenGate does not validate the user ID. If the user that is associated with the specified user ID is dropped and recreated, a new user ID is assigned; therefore, EXCLUDEUSERID becomes invalid for that user.

FAILOVERTARGETDESTID n

Valid for Extract for Oracle.

Identifies which standby database the Oracle GoldenGate Extract process must remain behind, with regard to not extracting redo data that has not yet been applied to the Oracle Data Guard standby database. To determine the correct value for_FAILOVERTARGETDESTID, the archive_log_destdatabase initialization parameter is used with n being the correct archive log destination identifier. The minimum value is 0, the maximum is 32 and the default 0.

FETCHLOBIFERROR

Valid for Extract in classic capture mode Oracle.

Overrides the Extract default of abending if LOB capture from the redo log results in an error, such as incomplete data. It forces Extract to fetch the LOB from the database if there is an error when reading it from the redo log.

Caution:

If a value gets deleted before the fetch occurs, Extract writes a null to the trail. If a value gets updated before a fetch, Extract writes the updated value. To prevent these inaccuracies, try to keep Extract latency low. See Tuning the Performance of Oracle GoldenGateguidelines in Administering Oracle GoldenGatefor tuning process performance. Also, see FETCHOPTIONS for instructions on setting fetch options.

See also the FORCEFETCHLOB option.

FETCHPARTIALLOB

Valid for Extract in integrated capture mode for Oracle.

Use this option when replicating to a non-Oracle target or in other conditions where the full LOB image is required. It causes Extract to fetch the full LOB object, instead of using the partial change object from the redo record. By default, the database logmining server sends Extract a whole or partial LOB, depending on whether all or part of the source LOB was updated. To ensure the correct snapshot of the LOB, the Oracle Flashback feature must be enabled for the table and Extract must be configured to use it. The Extract FETCHOPTIONS parameter controls fetching and must be set to USESNAPSHOT (the default in the absence of NOUSESNAPSHOT). Without a Flashback snapshot, Extract fetches the LOB from the table, which may be a different image from the point in time when the redo record was generated.

FETCHINLINESFLOB

Use this option with Classic Extract to force fetching on inline SECUREFILE LOBs even when the LOB is not encrypted, not compressed, and not deduplicated. No value is expected after FETCHINLINESFLOB.

FETCHPARTIALXML

Valid for Extract in integrated capture mode Oracle.

Use this option when replicating to a non-Oracle target or in other conditions where the full LOB image is required. It causes Extract to fetch the full XML document, instead of using the partial change image from the redo record. By default, the database logmining server sends Extract a whole or partial XML document, depending on whether all or part of the source XML was updated. To ensure the correct snapshot of the XML, the Oracle Flashback feature must be enabled for the table and Extract must be configured to use it. The Extract FETCHOPTIONS parameter controls fetching and must be set to USESNAPSHOT (the default in the absence of NOUSESNAPSHOT). Without a Flashback snapshot, Extract fetches the XML document from the table, which may be a different image from the point in time when the redo record was generated.

FILTERTABLE table

Valid for Extract for MySQL.

Use this option to specify the fully qualified name of the checkpoint table being used by Replicat. Operations on the checkpoint table will be ignored by the local Extract as a means of preventing data from looping back to the source. For information about creating a checkpoint table, see Creating a Checkpoint Table in Administering Oracle GoldenGate. To specify object names and wildcards correctly, see Using Wildcards in Database Object Names in Administering Oracle GoldenGate.

FORCEFETCHLOB

Valid for Extract in classic and integrated capture modes for Oracle.

Overrides the default behavior of capturing LOB data from the redo log. Causes LOBs to be fetched from the database by default.

Caution:

If a value gets deleted before the fetch occurs, Extract writes a null to the trail. If a value gets updated before a fetch, Extract writes the updated value. To prevent these inaccuracies, try to keep Extract latency low. The Oracle GoldenGate documentation provides guidelines for tuning process performance. Also, see Interactions Between Fetches from a Table and DDL in Using Oracle GoldenGate for Oracle Databasefor instructions on setting fetch options.

GETCTASDML | NOGETCTASDML

Enables Create Table As Select (CTAS) functionality. When GETCTASDML is enabled, CTAS DMLs are sent from LogMiner and replicated on the target. Execution of the CTAS DDL is suppressed on the target. This parameter cannot be enabled while using the DDL metadata trigger. Trail files produced with the CTAS functionality enabled cannot be consumed by a Replicat version lower than 12.1.2.1.0.

Use GETCTASDML to allow CTAS to replay the inserts of the CTAS thus preserving OIDs during replication. This parameter is only supported with Integrated Dictionary and any downstream Replicat must be 12.1.2.1 or greater to consume the trail otherwise, there may be divergence.

HANDLEDLFAILOVER

Valid for integrated Extract only for Oracle.

Controls whether Extract will throttle its writing of trail data based on the apply progress of the Fast Start Failover standby database. It is intended to keep Extract at a safe point behind any data loss failover. When using this for data loss in a Data Guard configuration with Fast Start Failover (FSFO), after a role transition you must set the FAILOVERTARGETDESTID Extract parameter to identify the archive log destination ID to where the standby can be connected.

IGNOREDATACAPTURECHANGES | NOIGNOREDATACAPTURECHANGES

Valid for DB2 LUW

Controls whether or not Extract captures tables for which DATA CAPTURE CHANGES is not set. IGNOREDATACAPTURECHANGES ignores tables for which DATA CAPTURE CHANGES is not set. Use if tables were specified with a wildcard to ensure that processing continues for tables that do have change capture set. A warning is issued to the error log for tables that were skipped. The default is NOIGNOREDATACAPTURECHANGES.

IGNOREDIRECTLOADINSERTS

Valid for Extract in classic capture mode for Oracle.

Causes Extract to ignore all Oracle direct-load INSERTs. The default behavior (without this parameter) is to capture Oracle direct-load INSERTs. This option applies to Oracle logs with log compatibility of Oracle 10g or later.

INCLUDEAUX (AUX_specification)

Directs the Oracle GoldenGate VAMSERV component to capture only the specified AUX trails when reading the audit trail. This parameter can improve performance when you know that some AUX trails will not contain data that is to be captured and can be ignored. With this parameter, you specify only the AUX trails that are to be captured.

AUX_specification is a number that represents the AUX trails to be captured. To specify multiple AUX trails, use a comma-delimited list. For example, the following statement includes AUX trails BB & CC = 1, 2.

TRANLOGOPTIONS INCLUDEAUX (1, 2)

To only include MAT or to exclude all AUX trails, place one space between the parentheses, for example:

TRANLOGOPTIONS INCLUDEAUX ( )
INCLUDEREGIONID | INCLUDEREGIONIDWITHOFFSET

Valid for Extract in either capture mode for Oracle.

These options support the Oracle data type TIMESTAMP WITH TIME ZONE specified as TZR (which represents the time zone region, such as US/Pacific). By default, Extract abends on TIMESTAMP WITH TIME ZONE if it includes a time zone region. These options enable you to handle this timestamp based on the target database type.

When Extract detects that the source data type is TIMESTAMP and there is a region ID mapping token, Replicat applies the timestamp as follows:

  • A TIMESTAMP WITH TIME ZONE with TZR is applied if the target Oracle version supports it.

  • A timestamp with a UTC offset is applied to a non-Oracle database, or to an earlier version of Oracle that does not support TIMESTAMP WITH TIME ZONE with TZR.

INCLUDEREGIONID

Use when replicating from an Oracle source to an Oracle target of the same version or later. When INCLUDEREGIONID is specified, Extract adds a column index and the two-byte TMZ value as a time-zone mapping token and outputs it to the trail in the UTC format of YYYY-MM-DD HH:MI.SS.FFFFFF +00:00.

INCLUDEREGIONIDWITHOFFSET

Use when replicating TIMESTAMP WITH TIME ZONE as TZR from an Oracle source that is v10g or later to an Oracle target that is earlier than 10g, or from an Oracle source to a target that is not an Oracle database. When INCLUDEREGIONIDWITHOFFSET is specified, Extract converts the time zone region value to a time offset that takes Daylight Saving Time into account based on the date and time. The timestamp data is written to the trail in local time in the format of YYYY-MM-DD HH:MI.SS.FFFFFF TZH:TZM, where TZH:TZM is the region ID converted time offset.

LOB_CHUNK_SIZE

Valid for CDC Capture method for Oracle.

LOB_CHUNK_SIZEis a new parameter added for the CDC Capture Method to support large objects. If you have huge LOB data sizes, then you can adjust the LOB_CHUNK_SIZE from the default of 4000 bytes, to a higher value up to 65535 bytes, so that the fecth size is increades, reducing the trips needed to fetch the entire LOB

Example: TRANLOGOPTIONS LOB_CHUNK_SIZE 8000

INTEGRATEDPARAMS (parameter value [, ...])

Valid for Extract in integrated capture mode for Oracle Standard or Enterprise Edition 11.2.0.3 or later.

Passes parameters and values to the Oracle Database logmining server when Extract is in integrated capture mode. The input must be in the form of parameter value, as in:

TRANLOGOPTIONS INTEGRATEDPARAMS (downsream_real_time_mine Y)

Valid parameter specifications and their values are the following:

max_sga_size

Specifies the amount of SGA memory that is used by the database logmining server. Can be a positive integer in megabytes. The default is 1 GB if streams_pool_size is greater than 1 GB; otherwise, it is 75% of streams_pool_size.

parallelism

Specifies the number of processes supporting the database logmining server. Can be a positive integer. The default is 2.

downstream_real_time_mine

Specifies whether or not integrated capture mines a downstream mining database in real-time mode. A value of Y specifies real-time capture and requires standby redo logs to be configured at the downstream mining database. A value of N specifies capture from archived logs shipped to the downstream mining database. The default is N.

enable_procedural_replication

Enables procedural replication at capture. Procedural replication is disabled by default. A value of Y enables procedural replication. Once this option is turned on for an Extract, it remains on. The parameter value can not be toggled back.

LOGRETENTION [ENABLED [DAYS n] | SR | DISABLED]

Valid for Extract in classic capture mode for Oracle Enterprise Edition.

Specifies whether or not Oracle Recovery Manager (RMAN) retains the log files that Extract needs for recovery. When you use the REGISTER EXTRACT command, the logs are retained from the time that the command is issued, based on the current database SCN. The logs are retained until manually deleted. This parameter does not enable or disable RMAN within the database itself.

Other information about LOGRETENTION:

  • If the Oracle flash recovery storage area is full, RMAN will purge the archive logs even when needed by Extract. This limitation exists so that the requirements of Extract (and other Oracle replication components) do not interfere with the availability of redo to the database.

  • The database user that is assigned to Extract and specified with the USERID or USERIDALIAS parameter must have certain privileges, which are the same as those required for the DBLOGIN parameter.

  • LOGRETENTION makes use of an underlying (but non-functioning) Oracle Streams Capture process; thus, it requires the database to be the Enterprise Edition of Oracle version 11g or higher. Oracle Standard Edition and Express Edition do not support this feature. The LOGRETENTION feature can operate concurrently with other Streams installations.

Note:

To support RMAN log retention on Oracle RAC, you must download and install the database patch that is provided in BUGFIX 11879974, before you add the Extract groups.

ENABLED [DAYS n]

Enables the log-retention feature. This is the default, except when Extract for an Oracle database is in Archived Log Only (ALO) mode. Extract must be registered with the database by using the REGISTER EXTRACT command with the LOGRETENTION option.

By default, ENABLED honors the SCN of the Bounded Recovery checkpoint and retains the logs up to and including that point. This checkpoint represents the log file of the oldest open non-persisted transaction. In the unlikely event that a problem with Bounded Recovery affects the persisted data, the logs that are required to reprocess the oldest open transaction must be available.

You can use the DAYS option to retain the logs for a specific number of days, from 1 to 365 days as a whole number. The default for DAYS is 7 days.

To be more conservative, you can use the SR option instead. See "BR" for more information about the Bounded Recovery feature.

SR

Enables the log-retention feature, but retains logs up to and including the SCN of the log that is required for Extract to revert to standard (normal) recovery mode. In normal mode, Extract needs access to the log that contains the oldest open transaction that it had in memory. Using SR is a conservative measure that retains more logs than would be retained in Bounded Recovery mode (the default), but it ensures data availability in case Bounded Recovery fails. Extract must be registered with the database by using the REGISTER EXTRACT command with the LOGRETENTION option.

DISABLED

Disables the log-retention feature. This is the default setting when Extract for an Oracle source is operating in Archived Log Only (ALO) mode, but you can override this if needed. If you used the REGISTER EXTRACT command to register Extract, use the UNREGISTER EXTRACT command to unregister the associated Extract group from the database after disabling log retention.

LOGSOURCE platform, [PATHMAP path]

Valid for Extract in classic capture mode for Oracle.

Specifies the operating system and (optionally) the path name when the redo and/or archived logs are stored on a platform other than the one which is hosting the database. When LOGSOURCE is used, put the entire TRANLOGOPTIONS statement on one line. Do not use ampersand (&) line terminators to split it into multiple lines.

platform

Specifies the platform that hosts the redo or archived logs. Valid values are:

  • AIX

  • HPUX

  • LINUX

  • MVS

  • SOLARIS

  • VMS

  • WINDOWS

  • S390

To maintain correct data alignment, the specified platform and the platform that Extract is running on must have the same endian order and bit width (as in 32-bit or 64-bit). The following are compatible endian platforms:

  • Big endian: AIX, HPUX, MVS, SOLARIS, S290

  • Little endian: LINUX, VMS, WINDOWS

For example when running Extract on HPUX, a LOGSOURCE platform setting of AIX is valid but LINUX is not.

PATHMAP path

Specifies the path to the logs.

MAXWARNEOF seconds

Valid for Extract in classic capture mode for Oracle.

Specifies the number of seconds that Extract waits for a new log file to become available before generating a warning message. Extract generates only one warning message for a given sequence number. If MAXWARNEOF is not specified, Extract waits for one hour by default. A value of 0 omits the warning no matter how long Extract waits. The minimum value is 0 and the default is 3600.

MAXAUTOCMTTRANSSIZE (range, default)

Valid for DB2 for i only

Provides the range of the maximum autocommited transaction size.

DB2 for i autocommited records (journal entry has CCID equal to 0) do not have a commit record in the journal and therefore Oracle GoldenGate must create an implicit transaction to include these records in the trail. The default allows for multiple records to be included in a single transaction at the expense of accuracy of the indicated IO Time for each record because the IO time is based on the commit for the transaction. This improves the overall efficiency of handling these records.

This parameter sets the maximum number of records that will be included in an implicitly created transaction, but the number could be less if any other type of entry is seen in the journal before the maximum is reached. This behavior avoids issues with overlap for checkpoints on records that belong to explicitly committed records.

Setting the value for this parameter to 1 will provide an accurate IO time for each record in the trail for records that are autocommitted (have a CCID of 0 in the journal entry), at the potential expense of throughput for the Extract. The value of this parameter also affects the maximum potential size of a cached transaction for these records in memory. Setting it to a lower value causes the transaction memory to be lower if the Extract is able to store the maximum number of entries per implicit transaction. By definition there can only be one such implicit transaction in memory at any given time since any other transaction records will cause an immediate commit to the trail of any records in an implicit transaction already in memory.

The default range is between 1-10000 and the default value is 1000.

MINEFROMACTIVEDG

Valid for Extract in classic capture mode for Oracle.

Specifies that Extract is allowed to mine redo from an Active Data Guard instance. Without this parameter set, Extract will abend with an error. Supports Extract in classic capture mode when capturing in an Oracle Data Guard environment. MINEFROMACTIVEDG does not support DBLOGREADER,it only supports ASMUSER for reading the redo logs in the ASM storage.

MININGUSER {/ | user} [, MININGPASSWORD password]
[algorithm ENCRYPTKEY {key_name | DEFAULT}] [SYSDBA]]

Valid for Extract in integrated capture mode for Oracle.

Specifies login credentials for Extract to log in to a downstream Oracle mining database to interact with the logmining server. Can be used instead of the MININGUSERALIAS option if an Oracle GoldenGate credential store is not being used.

This user must:

  • Have the privileges granted in dbms_goldengate_auth.grant_admin_privilege.

  • Be the user that issues the MININGDBLOGIN or MININGDBLOGINALIAS and REGISTER EXTRACT or UNREGISTER EXTRACT commands for the Extract group that is associated with this MININGUSERALIAS.

  • Not be changed while Extract is in integrated capture mode.

/

Directs Oracle GoldenGate to use an operating-system login for Oracle, not a database user login. Use this argument only if the database allows authentication at the operating-system level. Bypassing database-level authentication eliminates the need to update Oracle GoldenGate parameter files if application passwords frequently change.

To use this option, the correct user name must exist in the database, in relation to the value of the Oracle OS_AUTHENT_PREFIX initialization parameter. The value set with OS_AUTHENT_PREFIX is concatenated to the beginning of a user's operating system account name and then compared to the database name. Those two names must match.

When OS_AUTHENT_PREFIX is set to ' ' (a null string), the user name must be created with IDENTIFIED EXTERNALLY. For example, if the OS user name is ogg, you would use the following to create the database user:

CREATE USER ogg IDENTIFIED EXTERNALLY;

When OS_AUTHENT_PREFIX is set to OPS$ or another string, the user name must be created in the format of:

OS_AUTHENT_PREFIX_value OS_user_name

For example, if the OS user name is ogg, you would use the following to create the database user:

CREATE USER ops$ogg IDENTIFIED BY oggpassword;
user

Specifies the name of the mining database user or a SQL*Net connect string.

password

The user's password. Use when database authentication is required to specify the password for the database user. If the password was encrypted by means of the ENCRYPT PASSWORD command, supply the encrypted password; otherwise, use the clear-text password. If the password is case-sensitive, type it that way. If either the user ID or password changes, the change must be made in the Oracle GoldenGate parameter files, including the re-encryption of the password if necessary.

algorithm

Specifies the encryption algorithm that was used to encrypt the password with ENCRYPT PASSWORD. Can be one of:

AES128
AES192
AES256
BLOWFISH
ENCRYPTKEY {key_name | DEFAULT}

Specifies the encryption key that was specified with ENCRYPT PASSWORD.

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

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

SYSDBA

Specifies that the user logs in as sysdba.

For more information about Oracle GoldenGate security options, see Configuring Oracle GoldenGate Security in Administering Oracle GoldenGate.

MINEFROMSNAPSHOTSTBY | NOMINEFROMSNAPSHOTSTBY

Valid for Oracle.

Controls whether or not Oracle GoldenGate can capture from redo that is archived by a snapshot standby database. MINEFROMSNAPSHOTSTBY enables Extract to run on a snapshot standby in classic capture mode or in integrated capture mode in an upstream configuration; running in a downstream configuration is not supported because the snapshot standby database does not ship its redo logs to another database.

The default is NOMINEFROMSNAPSHOTSTBY, which prevents Extract from capturing from a database that is a snapshot. Extract cannot run on a physical standby database and will abend if its source snapshot database is converted to a physical database.

MININGUSERALIAS alias

Valid for Extract in integrated capture mode for Oracle.

Specifies the alias for the login credentials that Extract uses to log in to a downstream Oracle mining database to interact with the logmining server. Can be used instead of MININGUSER if an Oracle GoldenGate credential store is being used.

This alias must be:

  • Associated with a database user login credential that is stored in the local Oracle GoldenGate credential store. This user must have the privileges granted in dbms_goldengate_auth.grant_admin_privilege.

  • The user that issues the MININGDBLOGIN or MININGDBLOGINALIAS and REGISTER EXTRACT or UNREGISTER EXTRACT commands for the Extract group that is associated with this MININGUSERALIAS.

This alias and user must not be changed while Extract is in integrated capture mode.

TRANLOGOPTIONS MIXEDENDIAN [ON|OFF] 
Valid for DB2 LUW valid for primary Extract

Oracle GoldenGate Extract for DB2 LUW supports cross endian capture where the database and Oracle GoldenGate are running on different byte order servers. Detection of byte order is automatic for DB2 LUW database version 10.5. If you need to disable auto-detection on DB2 LUW 10.5, then you can override it by specifying this parameter. For version 10.1 the parameter must be used in the Extract parameter file for the cross-endian capture.

Syntax:

TRANLOGOPTIONS MIXEDENDIAN [ON|OFF] 

ON: If this is set, then the Extract assumes that the database and Oracle GoldenGate are running on servers with a different byte order and necessary byte reversal conversion is performed.

OFF: If this is set, then the Extract assumes that the database and Oracle GoldenGate are running on servers with the same byte order and no byte order reversal conversion is performed.

NOFLUSH

Valid for DB2 z/OS.

Inhibits the flushing of log buffers.

NOMANAGECDCCLENUP | MANAGECDCCLEANUP

Valid for SQL Server.

When enabling Supplemental Logging for SQL Server tables for the CDC Capture method, data in the CDC staging tables and other CDC system tables need to be purged. Use either the Oracle GoldenGate CDC Cleanup job or the SQL Server CDC Cleanup job to purge these tables. By default, when Extract starts MANAGECDCCLEANUP is enabled and instructs Extract to check for the existence of the Oracle GoldenGate CDC Cleanup job and this is the recommended approach. By default, Extract requires that the Oracle GoldenGate CDC Cleanup job be created using the ogg_cdc_cleanup_setup.bat program or the Extract cannot start.

The NOMANAGECDCCLEANUP option instructs Extract not to check for the existence of the Oracle GoldenGate CDC Cleanup job.

PATHMAP NFS_mount_point log_path

Valid for Extract in classic capture mode for Oracle.

Specifies the location of the redo and/or archived logs when they are stored on a system other than the one which is hosting the database. More than one PATHMAP statement can be used. When PATHMAP is used, put the entire TRANLOGOPTIONS statement on one line. Do not use ampersand (&) line terminators to split it into multiple lines.

PATHMAP can be used with the LOGSOURCE option if the system is a different platform from the one that hosts the database.

NFS_mount_point

Specifies the NFS mount point of the remote system where the logs are stored.

log_path

The path to the logs on the remote system. The path must follow the mount point specification.

PREPAREFORUPGRADETOIE | NOPREPAREFORUPGRADETOIE

Valid when upgrading from Classic to Integrated Extract on Oracle RAC for Oracle.

When upgrading on Oracle RAC from Classic to Integrated Extract, you must set the PREPAREFORUPGRADETOIE option before stopping Classic Extract for the upgrade then wait for the information message in the report file that indicates that the parameter has taken effect before proceeding with the upgrade. For detailed upgrade instructions, see Upgrading Oracle GoldenGate.

PREPAREFORUPGRADETOIE

Set PREPAREFORUPGRADETOIE in the Extract parameter file, which requires a restart of Extract, or you can set it dynamically for a running extract from GGSCI using this command:

SEND EXTRACT extract_name TRANLOGOPTIONS PREPAREFORUPGRADETOIE

NOPREPAREFORUPGRADETOIE

Dynamically turns off the PREPAREFORUPGRADETOIE option if necessary. The default is NOPREPAREFORUPGRADETOIE.

PERFORMANCEPROFILE HIGH|MEDIUM
Valid for Extract in Integrated Capture mode.

Tunes Integrated Capture. It can be set to HIGH and MEDIUM (default). It helps achieve better performance by grouping the parameters that affect performance. Once the performance profile is set up, this option automatically configures the relevant parameters, to achieve the desired throughput and latency.

PURGEORPHANEDTRANSACTIONS | NOPURGEORPHANEDTRANSACTIONS

Valid for Extract in classic capture mode for Oracle.

Controls the purging of orphaned transactions that occur when an Oracle RAC node fails and Extract cannot capture the rollback.

PURGEORPHANEDTRANSACTIONS

Purges orphaned transactions. A transaction is verified as orphaned before purging by comparing its startup time with the node's startup time; if the transaction started earlier, it is purged.

NOPURGEORPHANEDTRANSACTIONS

The default. Orphaned transactions are not purged.

QUERYTIMEOUT number

Valid for SQL Server.

Specifies how long queries to SQL Server will wait for results before reporting a timeout error message. This option takes an integer value to represent the number of seconds. The default query timeout value is 30 seconds. The minimum value is 0 seconds (infinite timeout). The maximum is 2147483645 seconds.

The following example instructs SQL Server to wait 60 seconds for results before timing out.

TRANLOGOPTIONS QUERYTIMEOUT 60
QUERYRETRYCOUNT number

Valid for Extract for SQL Server.

Specifies how many times to retry a query to obtain table metadata after timeouts. Timeouts can occur for a long-running transaction that has created any table. The system tables become locked and prevent Extract's query from completing.

The default is one retry after a 30-second wait, after which the process abends if the retry fails. QUERYRETRYCOUNT can be specified to retry multiple times at 30-second intervals, according to the input value that is supplied. If all of the retries fail, Extract abends with the normal connection error message. The minimum (0) is infinite, maximum is 1000, and default is 1.

The following example causes Extract to attempt its query four times at 30-second intervals:

TRANLOGOPTIONS QUERYRETRYCOUNT 4
READQUEUESIZE size

Valid for MySQL.

Specifies the internal queue size, in bytes, for transaction data. It can be increased to improve performance. Valid values are integers from 1 through 214748364750000. The default is 256 bytes; start with the default and evaluate performance before adjusting upward.

REDO_TRANSPORT_LAG_THRESHOLD seconds
Valid for Integrated Extract in Downstream Mining Mode.

Monitors the network latency between a source database and target database when redo logs are shipped. If the latency exceeds the specified threshold then a warning appears in the report file and a subsequent information message appears when the lag drops to the normal level.

The default threshold value is 10 seconds.

For more information, see Configuring Redo Transport from Source to Downstream Mining Database

REQUIRELONGDATACAPTURECHANGES | NOREQUIRELONGDATACAPTURECHANGES

Valid for DB2 LUW.

Controls the response of Extract when DATA CAPTURE is set to NONE or to CHANGES without INCLUDE LONGVAR COLUMNS and the parameter file includes any of the following Oracle GoldenGate parameters that require the presence of before images for some or all column values: GETBEFOREUPATES, NOCOMPRESSUPDATES, and NOCOMPRESSDELETES. Both of those DATA CAPTURE settings prevent the logging of before values for LONGVAR columns. If those columns are not available to Extract, it can affect the integrity of the target data.

REQUIRELONGDATACAPTURECHANGES

Extract abends with an error.

NOREQUIRELONGDATACAPTURECHANGES

Extract issues a warning but continues processing the data record.

SOURCE_OS_TIMEZONE timezone

Valid for Extract in integrated capture mode for Oracle.

Specifies the system time zone of the source database. The system time zone of a database is usually given by the default time zone of its operating system, and can also be overridden by setting the TZ environment variable when the database is started. You should specify this option only if the source database and the Extract process use different system time zones. For example, in a downstream capture deployment where the source database and the Extract process run on different servers in different time zones.

You can specify the value of this option in a time zone region name or a UTC offset form and you must use the same form used by the source database. For example, if the source database uses a region name form like America/New_York, then you must specify America/New_York, US/Eastern, or EST5EDT. Alternately, if the source database uses a UTC offset form like -05:00, then you must use the syntax (GMT)[+|-]hh[:mm]. For example, GMT-05:00 or -5.

SUPPRESSNOOOPUPDATES

Valid for Extract on Oracle Database 12 c (12.2) and later.

You can control whether no-op updates are filtered or not in Integrated Extract. The default is no suppression.

TRANSCLEANUPFREQUENCY minutes

Valid for Extract in classic capture mode for Oracle.

Specifies an interval, in minutes, after which Oracle GoldenGate scans for orphaned transactions, and then scans again to delete them. The initial scan marks transactions considered to be orphaned. The second scan confirms they are orphaned, and they are deleted. Valid values are from 1 to 43200 minutes. Default is 10 minutes.

TRANCOUNT
Valid for SQL Server.

Allows you to specify a number of transactions to be fetched from the SQL Server tables at a time. It takes an integer value to represent the number of transactions. The minimum value is 1, maximum is 100, and the default is 10.

Example:

TRANLOGOPTIONS TRANCOUNT 20

This example instructs Extract to fetch 20 transactions at a time from CDC enabled tables.

[TSLOOKUPBEGINLRI | TSLOOKUPENDLRI]

Valid for DB2 LUW v10.1 and later.

When you specify an LRI range using these parameters, Extract looks for the timestamp specified in the ADD or ALTER EXTRACT command within this range. This helps Extract to optimize the look up process for a particular timestamp in the database transaction log. The TSLOOKUPBEGINLRI parameter is mandatory while TSLOOKUPENDLRI is optional. Specifying only TSLOOKUPENDLRI without TSLOOKUPBEGINLRI is invalid. For example:

TRANLOGOPTIONS TSLOOKUPBEGINLRI 75200.666197, TSLOOKUPENDLRI  75207.666216 
TRANLOGOPTIONS TSLOOKUPBEGINLRI 75200.666197

If the provided timestamp falls between the given LRI ranges or the provided timestamp falls after the TSLOOKUPBEGINLRI LRI timestamp then Extract starts from a record with timestamp equal to or nearest less than the provided timestamp.

If the provided timestamp falls before TSLOOKUPBEGINLRI LRI timestamp, Extract is started from the specified TSLOOKUPBEGINLRI LRI. If the provided timestamp falls after TSLOOKUPENDLRI timestamp, then Extract abends. If you only specify TSLOOKUPENDLRI, then an informational message is displayed and Extract starts from a record with timestamp equal or nearest less than the provided timestamp.

USENATIVEOBJSUPPORT | NOUSENATIVEOBJSUPPORT

Valid for Extract in integrated capture mode for Oracle.

Integrated Capture adds redo-based capture for User Defined Type (UDT) and ANYDATA data types. It is enabled by default and can only be enabled if the source database version is 12.1.0.1 or greater and the source database compatibility is 12.0.0.0.0 or greater. Replicat from Oracle GoldenGate release 12.1.2.1.0 must be used. If a source or target database of release 12.1.0.1 is used, the Streams patch for bug 18038108 must be installed on the database. To use Native Support, all of your Oracle databases and Oracle GoldenGate instances must be release 12.1.0.1 or greater to be compatible.

If redo-based capture is enabled but a UDT contains an unsupported attribute, Integrated Capture retries to capture the UDT using fetch. For limitations of support for capture, see XML Data Types in Using Oracle GoldenGate for Oracle Database. If you create object tables by using a CREATE TABLE AS SELECT (CTAS) statement, Integrated Capture must be configured to capture DML from CTAS operation in order to fully support object tables. For CTAS use information, see How Oracle GoldenGate Handles Derived Object Names in Using Oracle GoldenGate for Oracle Database

The default is USENATIVEOBJSUPPORT if supported.

USE_ROOT_CONTAINER_TIMEZONE

This parameter is for a CDB environment. Each PDB in a CDB can use a different database time zone. If the database time zone is available, Extract tries to get the time zone of a PDB from Integrated Dictionary. The time zone extraction requires a patch on the mining database. If the patch is not available, Extract sends a query to the PDB to get the time zone. If the database patch or a connection to the PDB is not available, and this parameter is specified, Extract assumes that the PDB database time zone is the same as the root container database time zone.

USEPREVRESETLOGSID | NOUSEPREVRESETLOGSID

Valid for Extract in classic capture mode for Oracle.

Specifies that Extract will take the previous RESETLOG id as the current branch. The default is NOUSEPREVRESETLOGSID. Supports Extract in classic capture mode when capturing in an Oracle Data Guard environment.

VALIDATEINLINESFLOB

Use this option with Classic Extract to validate inline SECUREFILE LOBs when it extracts out the LOB data from the redo column value. No value is expected after VALIDATEINLINESFLOB.

VAMCOMPATIBILITY {3}

Valid for MySQL, SQL Server, and Teradata.

Ensures the VAM module and the VAM API are using the same version of the column metadata structure. As new features are added to the VAM API, the column metadata needs enhancing with new attributes. When this occurs a new version is created, adding the new column metadata attributes to the existing ones. All database implementations other than Teradata are required to update to the latest version after a new version is added. The minimum is 1, the maximum is 1 and the default is 1.

1

A value of one means the original VAM API metadata structure is being used. This structure was originally created for Teradata, which is a separate implementation from the other databases, and only Teradata still uses this level. To maintain backwards compatibility with Extract, it may be necessary to manually set VAMCOMPATIBILITY to 1 if running an earlier version of a TAM module against later releases of Extract that contain VAM versioning. Extract abends with a message to the report file if VAMCOMPATIBILITY 1 is required and not set.

2

This VAM version added column metadata attributes for SQL Server. This value is set internally by the VAM module, so setting it manually with TRANLOGOPTIONS is not required.

3

This is the current version level. All databases other than Teradata (including SQL Server) use this level and set it internally in the VAM module, so setting it manually with TRANLOGOPTIONS is not required.

Examples

Example 1   

The following specifies the location of the Oracle archived logs.

TRANLOGOPTIONS ALTARCHIVELOGDEST /fs1/oradata/archive/log2
Example 2   

The following Oracle example filters for two users (one by name and one by user ID). The transactions generated by these users will be handled according to the GETREPLICATES or IGNOREREPLICATES rules, and a new transaction buffer size is specified.

TRANLOGOPTIONS EXCLUDEUSER ggsrep, EXCLUDEUSERID 90, BUFSIZE 100000
Example 3   

The following shows how to deal with transaction logs that are on a platform other than the one which hosts the database.

Note:

The following statement spans multiple lines only because of space constraints in this documentation.

TRANLOGOPTIONS, LOGSOURCE VMS, PATHMAP DKA200:[RDBMS.ORACLE.ORA9201I.64.ADMIN.GGS.ARCH]
/net/deltan/uservol1/RDBMS.DIR/ORACLE.DIR/ORA9201I.DIR/
64.DIR/admin.DIR/ggs.DIR/ARCH.dir PATHMAP DKA200:[RDBMS.ORACLE.ORA9201I.64.ORADATA.GGS] 
/net/deltan/uservol1/rdbms.dir/oracle.dir/ora9201I.DIR/
64.dir/oradata.dir/ggs.dir
Example 4   

The following example supplies ASM credentials by specifying the alias asm1 in the asmdomain domain in the Oracle GoldenGate credential store.

TRANLOGOPTIONS ASMUSERALIAS asm1 DOMAIN asmdomain
Example 5   

The following is an example of how to specify the padding width when Extract forms the archive log name using the format specifiers %T, %S, and %R in the ALTARCHIVELOGFORMAT parameter.

TRANLOGOPTIONS ALTARCHIVELOGFORMAT ARC_%S_%R.%T
TRANLOGOPTIONS SEQPADLEN 12, RESETLOGSIDPADLEN 12, THREADPADLEN 5
Example 6   

The following are examples of how to use tag specifiers with EXCLUDETAG.

TRANLOGOPTIONS EXCLUDETAG 00
TRANLOGOPTIONS EXCLUDETAG +
TRANLOGOPTIONS EXCLUDETAG 0952
Example 7   

The following is an example of how to use the TRANLOGOPTIONS FAILOVERTARGETDESTID Extract parameter.

TRANLOGOPTIONS FAILOVERTARGETDESTID 2

SQL> show parameters log_archive_dest 

NAME TYPE VA 
LUE 
------------------------------------ ----------- -- 
---------------------------- 
log_archive_dest_1 string location=USE_DB_RECOVERY_FILE_DEST, 
valid_for=(ALL_LOGFILES, ALL_ROLES) 
. 
log_archive_dest_2 string service="ggs2d", ASYNC NOAFFIRM delay=0 optional 
compression =disable max_failure=0 max_connections=1 reopen=300 
db_unique_name="GGS2D" net_timeout=30, 
valid_for=(online_logfile,all_roles)

It would be set to 2 because that is the Standby database Oracle GoldenGate should stay behind. The first entry (log_archive_dest_1) is for the local archive logs for that database, and the second is for the standby database.00