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 usingSOURCEISTABLE
, 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 theBEGIN
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 existingTRANLOG
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 theREGISTER 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 theADD EXTRACT
command is issued.For DB2 LUW,
NOW
specifies the time at whichSTART 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 useLRI
orEOF
options wherever possible.Do not use
NOW
for a data pump Extract except to bypass data that was captured to the trail prior to theADD 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
Iftrack_commit_timestamp
isoff
, 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 thetrack_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
Iftrack_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 associatedtransaction 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
orLSN
. 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
0X
nnn
, wherennn
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 letterx
). -
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 activeLRI
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 byEOF
. -
LSN
value
-
Valid for SQL Server, DB2 z/OS, and PostgreSQL.
Specifies the transactionLSN
at which to start capturing data. An alias for this option isEXTLSN
. The specifiedLSN
should exist as a validtran_begin_lsn
found in thecdc.lsn_time_mapping
system table, otherwise the Extract will attempt to position after theLSN
value provided.For PostgreSQL,
LSN
value can behi
orlo
. Set the value ashi
for the entry point of the log file.Lo
is the offset in the log file. TheLSN
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 isEXTLSN
.The specified
LSN
should exist as a validtran_begin_lsn
found in thecdc.lsn_time_mapping
system table, otherwise the Extract will attempt to position after the LSN value provided.ValidLSN
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 theLRI
at which extract can start capturing records from the transaction log. You can use the DB2 utilitydb2logsForRfwd
to obtain theLRI
. This utility providesLRI
ranges present in the DB2 logs.Note that, although Extract might position to a given
LRI
, thatLRI
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 startingLRI
to the Extract report file. -
LOGNUM lognum
-
Valid for MySQL.
This is the log file number. In Microservices Architecture,
ADD EXTRACT
will fail if theLOGNUM
value contains zeroes preceding the value. For example,ADD EXTRACT ext1, TRANLOG, LOGNUM 000001, LOGPOS 0
will fail. Instead, setLOGNUM
to1
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 keywordDESC
or the full wordDESCRIPTION
. -
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 withADD 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 traildirdat\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 namedfinance
on source systemsys
. 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
Parent topic: Common Command Line Interface Commands