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

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.

Oracle GoldenGate supports a large number of concurrent Extract and Replicat groups per instance of Oracle GoldenGate deployment, depending on the resources available with the operating system. At the supported level, all groups can be controlled and viewed in full with 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.

GGSCI Syntax

ADD EXTRACT group_name
{, SOURCEISTABLE |
    , TRANLOG [bsds_name | 
    , INTEGRATED TRANLOG |
    , EXTFILESOURCE file_name |
    , EXTTRAILSOURCE trail_name |
[, BEGIN {NOW | yyyy-mm-ddT[ hh:mi:[ss[.cccccc]]]Z}]
[, DESC 'description']
[, EXTSEQNO sequence_number
[, EXTRBA offset_number |
[, EXTRBA relative_byte_address |
[, EOF 
[, LOGNUM lognum]
[, LOGPOS logpos
[, LRI value |
[, LSN value |
[, PAGE data_page, ROW row_ID |
[, PASSIVE]
[, PARAMS file_name]
[, REPORT file_name]
[, RMTNAME passive_Extract_name]
[, SEQNO sequence_number
[, SCN value]
[, SOCKSPROXY {host_name | IP_address}[:port] [PROXYCSALIAS credential_store_alias [PROXYCSDOMAIN credential_store_domain]]]
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 specified tables for an initial load using Oracle GoldenGate. 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 z/OS) You can use the bsds_name option for Db2 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) Extract reads the Oracle redo logs directly. See INTEGRATED TRANLOG for an alternate configuration.

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 initial load Extract file as the data source. Use this option with an Extract Pump that sends initial load file data to a remote, target system.

For file_name, specify the relative or fully qualified path name of the file. For example ./dirdat/initfile.

EXTTRAILSOURCE trail_name

Specifies a CDC Extract trail as the data source. Use this option with an Extract Pump that sends change data capture trails to a remote, target system.

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

BEGIN {NOW | yyyy-mm-ddT[ hh:mi:[ss[.cccccc]]]Z}

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

NOW

NOW specifies the time at which the ADD EXTRACT command is issued.

For Db2 LUW, only commit and end transaction records contain timestamps, so the Extract 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 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

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

EXTRBA archive-offset_number

Valid for Db2 z/OS. 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 Db2 for i, Db2 LUW, MySQL, PostgreSQL, and SQL Server.

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.

For MySQL, it finds the position corresponding to the end of the file and starts reading transactions from there. The EOF position is not exact, if data is continuously written to the binary log.

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.

Positioning to an LSN is precise.

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

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 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 example creates an Extract group named exte that captures database changes from the transaction logs. Extraction starts with records generated at the time when the Extract group was created and started with ADD EXTRACT. 

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

The following creates an integrated Extract group.

ADD EXTRACT extn, INTEGRATED TRANLOG, BEGIN NOW

The following creates a data pump Extract group named extn that reads data from the source trail north\ea. 

ADD EXTRACT extn, EXTTRAILSOURCE north\ea

The following creates an initial-load Extract named extei. 

ADD EXTRACT extn, SOURCEISTABLE

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

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

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

ADD EXTRACT extn, RMTHOST sys, MGRPORT 7800, RMTNAME extw

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

ADD EXTRACT extn TRANLOG SCN 123456
ADD EXTRACT extn INTEGRATED TRANLOG SCN 123456

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

ADD EXTRACT extn DESC 'alias extract' 
RMTHOST server1.dc1.north.example.com 
MGRPORT 7813 
RMTNAME ppmp 
SOCKSPROXY server2.dc1.north.example.com PROXYCSALIAS ggnorthproxy

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

ADD EXTRACT extn, TRANLOG LRI 8066.322711