1. Command Line Interface Reference for Oracle GoldenGate
  2. Common Command Line Interface Commands
  3. ADD EXTRACT

2.4 ADD EXTRACT

Use ADD EXTRACT to create an Extract group. Unless a SOURCEISTABLE task or an alias Extract is specified, ADD EXTRACT creates an online group that uses checkpoints so that processing continuity is maintained from run to run.

For DB2 for i, this command establishes a global start point for all journals and is a required first step. After issuing the ADD EXTRACT command, you can then optionally position any given journal at a specific journal sequence number by using the ALTER EXTRACT command with an appropriate journal option.

You need to provide TRANLOG as the source to configure Extract. See Enabling Supplemental Logging PostgreSQL in Using Oracle GoldenGate for Heterogeneous Databases.

Oracle GoldenGate supports up to 5,000 concurrent Extract and Replicat groups per instance of Oracle GoldenGate Manager. At the supported level, all groups can be controlled and viewed in full with GGSCI commands such as the INFO and STATUS commands. Oracle GoldenGate recommends keeping the combined number of Extract and Replicat groups at the default level of 300 or below in order to manage your environment effectively.

This command cannot exceed 500 bytes in size for all keywords and input, including any text that you enter for the DESC option.

Admin Client Syntax

ADD EXTRACT group-name
|             ( SOURCEISTABLE |
|             ( [ INTEGRATED ] TRANLOG )
|               ( BEGIN     ( NOW | yyyy-mm-ddthh:mm.ssZ ) |
|                 EXTSEQNO  archive-sequence-number 
                [ EXTRBA archive-offset-number ] |
                SCN       scn
|               ) )
|             [ DESC        description    ]
|             [ THREADS     threads-number ]
|             [ CRITICAL    [ YES | NO ]     ]
|             [ ENCRYPTIONPROFILE     encryption-profile-name]
|             [ PROFILE
|                 [ AUTOSTART   [ YES | NO ]
|                       [ DELAY     delay-number   ] ]
|                 [ AUTORESTART [ YES | NO ]
|                       [ RETRIES          retries-number ]
|                       [ WAITSECONDS      wait-number    ]
|                       [ RESETSECONDS     reset-number   ]
|                       [ DISABLEONFAILURE [ YES | NO ] ] ] ]
|             [ LOGNUM lognum]
|             [ LOGPOS logpos

GGSCI Syntax

ADD EXTRACT group_name
{, SOURCEISTABLE |
    , TRANLOG [bsds_name | 
    , INTEGRATED TRANLOG |
    , EXTFILESOURCE file_name |
    , EXTTRAILSOURCE trail_name |
[, BEGIN {NOW | yyyy-mm-dd[ hh:mi:[ss[.cccccc]]]}]
[, START]
[, EXTSEQNO sequence_number
[, EXTRBA offset_number |
[, EOF 
[, LSN value |
[, EXTRBA relative_byte_address |
[, LRI value |
[, PAGE data_page, ROW row_ID |
[, SEQNO sequence_number
[, SCN value]
[, THREADS n]
[, PASSIVE]
[, PARAMS file_name]
[, REPORT file_name]
[, DESC 'description']
[, SOCKSPROXY {host_name | IP_address}[:port] [PROXYCSALIAS credential_store_alias [PROXYCSDOMAIN credential_store_domain]]]
[, RMTNAME passive_Extract_name]
[, LOGNUM lognum]
[, LOGPOS logpos
group_name

The name of the Extract group. The name of an Extract group can contain up to eight characters, see Choosing Names for Processes and Files.

SOURCEISTABLE

Creates an Extract task that extracts entire records from the database for an initial load using the Oracle GoldenGate direct load method or the direct bulk load to SQL*Loader method. If SOURCEISTABLE is not specified, ADD EXTRACT creates an online change-synchronization process, and one of the other data source options must be specified. When using SOURCEISTABLE, do not specify any service options. Task parameters must be specified in the parameter file, see Instantiating Oracle GoldenGate with an Initial Load.

TRANLOG [bsds_name]

Use this option for all databases. TRANLOG requires the BEGIN option.

(DB2 on z/OS) You can use the bsds_name option for DB2 on a z/OS system to specify the Bootstrap Data Set file name of the transaction log, though it is not required and is not used. You do not need to change existing TRANLOG parameters.

(Oracle) As of Oracle Standard or Enterprise Edition 11.2.0.3, this mode is known as classic capture mode. Extract reads the Oracle redo logs directly. See INTEGRATED TRANLOG for an alternate configuration.

(PostgreSQL) Use GGSCI to add Extract with TRANLOG as source.

INTEGRATED TRANLOG

(Oracle) Adds Extract in integrated capture mode. In this mode, Extract integrates with the database logmining server, which passes logical change records (LCRs) directly to Extract. Extract does not read the redo log. Before using INTEGRATED TRANLOG, use the REGISTER EXTRACT command.

EXTFILESOURCE file_name

Specifies an Extract file as the data source. Use this option with a secondary Extract group (data pump) that acts as an intermediary between a primary Extract group and the target system.

For file_name, specify the relative or fully qualified path name of the file, for example \extfile.

EXTTRAILSOURCE trail_name

Specifies a trail as the data source. Use this option with an Extract Pump that acts as an intermediary between a primary Extract group and the target system.

For trail_name, specify the relative or fully qualified path name of the trail, for example \aa.

BEGIN {NOW | yyyy-mm-ddthh:mm.ssZ}

Specifies a timestamp in the data source at which to begin processing.

NOW

For all databases except DB2 LUW, NOW specifies the time at which the ADD EXTRACT command is issued.

For DB2 LUW, NOW specifies the time at which START EXTRACT takes effect. It positions to the first record that approximately matches the date and time. This is because the only log records that contain timestamps are the commit and end transaction records, so the starting position can only be calculated relative to those timestamps. This is a limitation of the API that is used by Oracle GoldenGate. It must be noted that positioning by timestamp is not accurate and can also take a long time. It is recommended to use LRI or EOF options wherever possible.

Do not use NOW for a data pump Extract except to bypass data that was captured to the trail prior to the ADD EXTRACT statement.

yyyy-mm-ddthh:mm:ssZ

A date and time (timestamp) in the given form. For example, 2017-07-14T14:54:45Z.

yyyy-mm-dd[ hh:mi:[ss[.cccccc]]]

A date and time (timestamp) in the given form. For an Oracle Extract in integrated mode, the timestamp value must be greater than the timestamp at which the Extract was registered with the database.

Positioning by timestamp in a SQL Server transaction log is affected by the following characteristics of SQL Server:

  • The timestamps recorded in the SQL Server transaction log use a 3.3333 microsecond (ms) granularity. This level of granularity may not allow positioning by time between two transactions, if the transactions began in the same 3.3333 ms time interval.

  • Timestamps are not recorded in every SQL Server log record, but only in the records that begin and commit the transaction, as well as some others that do not contain data.

  • SQL Server timestamps are not from the system clock, but instead are from an internal clock that is specific to the individual processors in use. This clock updates several times a second, but between updates it could get out of sync with the system clock. This further reduces the precision of positioning by time.

  • Timestamps recorded for log backup files may not precisely correspond to times recorded inside the backup (however this imprecision is less than a second).

Positioning to an LSN is precise.

Postitioning by timestamp in PostgreSQL includes the following scenarios:

  • Scenario 1

    If track_commit_timestamp is off, the following output will be displayed when the Extract process starts irrespective of what positioning method is used:
    2020-04-29 02:15:54 INFO OGG-01517 Position of first record processed LSN: 0/2222C20, Jan 1, 1970 12:00:00 PM.

  • Scenario 2

    If the track_commit_timestamp is enabled before Extract is registered then the correct timestamp will be displayed once the records are pushed in the source database as mentioned in the following example: 
    2020-04-29 02:19:07 INFO OGG-01515 Positioning to begin time Apr 29,2020 2:18:38 AM.

  • Scenario 3

    If track_commit_timestamp is enabled after the Extract is registered, then there may be chances that the older records are available in the log for which the commit timestamp is not built up with the associated transaction ID. In that case, if Extract does not get the timestamp then it will fallback using the default timestamp mentioned in scenario 1. The output will be similar to the following:
    020-04-29 01:55:07 INFO OGG-01517 Position of first record processed LSN: 0/221D028, Jan 1, 1970 12:00:00 PM.

  • Past timestamp cannot be specified if the replication slot has moved away.

START

Valid for PostgreSQL.

Adds an Extract without mentioning BEGIN NOW or LSN. Extract will start from the replication slot restart position automatically.

EXTSEQNO sequence-number

Valid for a primary Extract in classic capture mode for Oracle. Not supported for an Extract in integrated mode. Specifies the sequence number of an Oracle redo log at which to begin capturing data. Contact Oracle Support before using this option.

Contact Oracle Support before using this option. For more information, go to http://support.oracle.com.

EXTRBA archive-offset_number

Valid for DB2 z/OS and Oracle Database. Specifies the relative byte address within a transaction log at which to begin capturing data.

The required format is 0Xnnn, where nnn is a 1 to 20 digit hexadecimal number (the first character is the digit zero, and the second character can be upper or lower case letter x).

EOF

Valid for SQL Server, DB2 for i, DB2 LUW, PostgreSQL. Configures processing to start at the end of the log files (or journals) that the next record will be written to. Any active transactions will not be captured.

For DB2 LUW, it configures processing to start at the active LRI value in the log files. The active LRI is the position at the end of the log files that the next record will be written to. Any active transactions will not be captured.

For PostgreSQL, DBLOGIN is required for position by EOF.

LSN value

Valid for SQL Server, DB2 z/OS, and PostgreSQL.

Specifies the transaction LSN at which to start capturing data. An alias for this option is EXTLSN. The specified LSN should exist as a valid tran_begin_lsn found in the cdc.lsn_time_mapping system table, otherwise the Extract will attempt to position after the LSN value provided.

For PostgreSQL, LSN value can be hi or lo. Set the value as hi for the entry point of the log file. Lo is the offset in the log file. The LSN position should lie between the replication slot restart position and write ahead log current location. If the position specified itself exists between the mentioned range then Extract will throw an error.

(SQL Server) specifies the transaction LSN at which to start capturing data. An alias for this option is EXTLSN.

The specified LSN should exist as a valid tran_begin_lsn found in the cdc.lsn_time_mapping system table, otherwise the Extract will attempt to position after the LSN value provided.

Valid LSN specification consists of the following:

  • Colon separated hex string (8:8:4) padded with leading zeroes and 0X prefix, as in 0X00000d7e:0000036b:0001

  • Colon separated decimal string (10:10:5) padded with leading zeroes, as in 0000003454:0000000875:00001

  • Colon separated hex string with 0X prefix and without leading zeroes, as in 0Xd7e:36b:1

  • Colon separated decimal string without leading zeroes, as in 3454:875:1

  • Decimal string, as in 3454000000087500001

You can find the minimum LSN available by querying the following:

SELECT min([tran_begin_lsn]) FROM [cdc].[lsn_time_mapping] with (nolock) where tran_id <>
      0x00

Example:

ADD EXTRACT extsql TRANLOG, LSN 0X00000d7e:0000036b:0001

LRI value

Valid for DB2 LUW. Specifies a start position in the transaction logs when Extract starts.

You can use the LRI option for DB2 LUW systems to specify the LRI at which extract can start capturing records from the transaction log. You can use the DB2 utility db2logsForRfwd to obtain the LRI. This utility provides LRI ranges present in the DB2 logs.

Note that, although Extract might position to a given LRI, that LRI might not necessarily be the first one that Extract will process. There are numerous record types in the log files that Extract ignores, such as DB2 internal log records. Extract will report the actual starting LRI to the Extract report file.

LOGNUM lognum

Valid for MySQL.

This is the log file number. In Microservices Architecture, ADD EXTRACT will fail if the LOGNUM value contains zeroes preceding the value. For example, ADD EXTRACT ext1, TRANLOG, LOGNUM 000001, LOGPOS 0 will fail. Instead, set LOGNUM to 1 for this example to succeed.

LOGPOS logpos
This is is an event offset value within the log file that identifies a specific transaction record. Event offset values are stored in the header section of a log record. To position at the beginning of a binlog file, set the LOGPOS as 0.
SEQNO sequence_number

Valid for DB2 for i. Starts capture at, or just after, a system sequence number, which is a decimal number up to 20 digits in length.

SCN value

Valid for Oracle.

Starts Extract at the transaction in the redo log that has the specified Oracle system change number (SCN). For Extract in integrated mode, the SCN value must be greater than the SCN at which the Extract was registered with the database. For more information, see REGISTER EXTRACT.

PARAMS file_name

Specifies the full path name of an Extract parameter file in a location other than the default of dirprm within the Oracle GoldenGate directory.

REPORT file_name

Specifies the full path name of an Extract report file in a location other than the default of dirrpt within the Oracle GoldenGate directory.

THREADS n

Valid for Oracle classic capture mode.

Specifies the number of producer threads that Extract maintains to read redo logs.

Required in an Oracle RAC configuration to specify the number of producer threads. These are the Extract threads that read the different redo logs on the various RAC nodes. The value must be the same as the number of nodes from which you want to capture redo data.

PASSIVE

(Classic Architecture only) Specifies that this Extract group runs in passive mode and can only be started and stopped by starting or stopping an alias Extract group on the target system. Source-target connections will be established not by this group, but by the alias Extract from the target.

This option can be used for a regular Extract group or a data-pump Extract group. It should only be used by whichever Extract on the source system is the one that will be sending the data across the network to a remote trail on the target.

DESC 'description'

Specifies a description of the group, such as 'Extracts account_tab on Serv1'. Enclose the description within single quotes. You may use the abbreviated keyword DESC or the full word DESCRIPTION.

ENCRYPTIONPROFILE

Specifies the name of the Oracle GoldenGate encryption profile associated with the specific client.

CRITICAL

Indicates if the process is critical for the deployment.

PROFILE

Name of the auto start profile.

AUTOSTART

Specifies whether the managed process has to be started automatically when the Administration ServerAdministration Service starts. The default value is YES.

RETRIES

The maximum number of tries for restarting the task before canceling retry efforts. This is optional.

WAITSECONDS

The duration (in seconds) in which the retries are counted.

RESETSECONDS

Resets the duration in which the retries are counted.

DISABLEONFAILURE

If set to TRUE, then the task is disabled when the number of retries is exhausted.

SOCKSPROXY {host_name | IP_address}[:port] [PROXYCSALIAS credential_store_alias [PROXYCSDOMAIN credential_store_domain]

Use for an alias Extract. Specifies the DNS host name or IP address of the proxy server. You can use either one to define the host though you must use the IP address if your DNS server is unreachable. If you are using an IP address, use either an IPv6 or IPv4 mapped address, depending on the stack of the destination system. You must specify the PROXYCSALIAS. In addition, you can specify the port to use, and the credential store domain.

RMTNAME passive_extract_name

(Classic Architecture only) Use for an alias Extract. Specifies the passive Extract name, if different from that of the alias Extract.

Examples

The following creates an Extract group named finance that extracts database changes from the transaction logs. Extraction starts with records generated at the time when the group was created with ADD EXTRACT. 

ADD EXTRACT finance, TRANLOG, BEGIN NOW
In the following example, which is run from the Admin Client, an Extract group name finance is created to get the database changes from the transaction logs beginning from the specified time.
ADD EXTRACT finance, TRANLOG, BEGIN 2020-08-02T06:05:30.000Z

The following creates an Extract group named finance that extracts database changes from Oracle RAC logs. Extraction starts with records generated at the time when the group was created. There are four RAC instances, meaning there will be four Extract threads. 

ADD EXTRACT finance, TRANLOG, BEGIN NOW, THREADS 4

The following creates an integrated Extract group.

ADD EXTRACT finance, INTEGRATED TRANLOG, BEGIN NOW

The following creates an Extract group named finance that interfaces with MySQL in either maximum performance or maximum protection mode. 

ADD EXTRACT finance, TRANLOG, BEGIN NOW

The following creates a data-pump Extract group named epump that reads data from the source trail dirdat\lt. 

ADD EXTRACT finance, EXTTRAILSOURCE dirdat\lt

The following creates an initial-load Extract named load. 

ADD EXTRACT load, SOURCEISTABLE

In Classic Architecture, the following creates a passive Extract group named finance that extracts database changes from the transaction logs. 

GGSCI> ADD EXTRACT finance, TRANLOG, BEGIN NOW, PASSIVE

The following creates an alias Extract group named finance. The alias Extract is associated with a passive extract named finance on source system sys. The Manager on that system is using port 7800. 

ADD EXTRACT finance, RMTHOST sys, MGRPORT 7800, RMTNAME finance

The following examples create and position Extract at a specific Oracle system change number (SCN) in the redo log.

ADD EXTRACT finance TRANLOG SCN 123456
ADD EXTRACT finance INTEGRATED TRANLOG SCN 123456

The following example creates an alias Extract specifying the host to use. 

ADD EXTRACT apmp desc 'alias extract' RMTHOST lc01abc MGRPORT 7813 RMTNAME 
ppmp SOCKSPROXY lc02def:3128 PROXYCSALIAS proxyAlias

The following example creates an Extract on a DB2 LUW system.

ADD EXTRACT extcust, TRANLOG LRI 8066.322711
The following example creates an Extract on a PostgreSQL system.
ADD EXTRACT Extract1, TRANLOG, BEGIN NOW
The following example creates an Extract with the autostart option using Admin Client.
OGG (https://localhost:15000 Atlanta) 5> add extract EXT_DEMO tranlog begin now autostart yes
2019-11-20T23:22:54Z  
INFO    OGG-08100  EXTRACT added.

Parent topic: Common Command Line Interface Commands