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

2.16 ALTER EXTRACT

Use ALTER EXTRACT for the following purposes:

  • To change the attributes of an Extract group created with the ADD EXTRACT command.

  • To increment a trail to the next file in the sequence.

  • To upgrade to an integrated capture configuration.

  • To downgrade from an integrated capture configuration.

  • To position any given DB2 for i journal at a specific journal sequence number.

Before using this command, stop the Extract with the STOP EXTRACT group_name command.

Admin Client Syntax

ALTER EXTRACT group-name
[, BEGIN (NOW | yyyy-mm-dd[ hh:mi:[ss[.cccccc]]]} |
   EXTSEQNO sequence-number   
   [, EXTRBA archive-offset-number] [, ADD_EXTRACT_attribute]  | 
   SCN value]
[, DESC
[, UPGRADE INTEGRATED TRANLOG]
[, DOWNGRADE INTEGRATED TRANLOG [THREADS number]]
[, THREAD number]

[, ETROLLOVER]
[, ENCRYPTIONPROFILE encryption-profile-name ]
         [CRITICAL    [ YES | NO ]
         [PROFILE     profile-name             
   |  [AUTOSTART   [ YES | NO ]
         [DELAY       delay-number]
     [AUTORESTART [ YES | NO ]|        
         [RETRIES      retries-number ]|
         [WAITSECONDS  wait-number    ]|
         [RESETSECONDS reset-number   ]|
         [DISABLEONFAILURE [ YES | NO ]     ] ]
          ]

GGSCI Syntax

ALTER EXTRACT group-name
[, BEGIN {NOW | yyyy-mm-dd[ hh:mi:[ss[.cccccc]]]}]
[, START]
[, EXTSEQNO sequence-number]
[, EXTRBA offset-number]
[, TRANLOG LRI LRI_number]
[, UPGRADE INTEGRATED TRANLOG]
[, DOWNGRADE INTEGRATED TRANLOG [THREADS number]]
[, THREAD number]
[, LSN value]
[, SCN value]
[, ETROLLOVER]
[, INFO EXTRACT group-name]

The following ALTER EXTRACT options are supported for DB2 for i to position Extract for a given journal: 

ALTER EXTRACT {BEGIN {NOW | yyyy-mm-dd[ hh:mi:[ss[.cccccc]]]}
[JOURNAL journal_library/journal_name [JRNRCV receiver_library/
  receiver_name]] |
, EOF [JOURNAL journal_library/journal_name
  [JRNRCV receiver_library/receiver_name]] |
, SEQNO sequence_number [JOURNAL journal_library/journal_name 
  [JRNRCV receiver_library/receiver_name]]}
BEGIN {NOW | yyyy-mm-dd[ hh:mi:[ss[.cccccc]]]}
[JOURNAL journal_library/journal_name
[JRNRCV receiver_library/ receiver_name]] |
, EOF [JOURNAL journal_library/journal_name
[JRNRCV receiver_library/receiver_name]] |
, SEQNO sequence_number [JOURNAL journal_library/journal_name
[JRNRCV receiver_library/receiver_name]]

These IBM for i options allow journal-specific Extract positioning after the global start point is issued with ADD EXTRACT. A specific journal position set with ALTER EXTRACT does not affect any global position that was previously set with ADD EXTRACT or ALTER EXTRACT; however a global position set with ALTER EXTRACT overrides any specific journal positions that were previously set in the same Extract configuration.

Note:

SEQNO, when used with a journal in ALTER EXTRACT, is the journal sequence number that is relative to that specific journal, not the system sequence number that is global across journals.
group-name

The name of the Extract group that is to be altered.

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

For all databases except DB2 LUW, NOW specifies the time at which the ALTER 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 abort 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.

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.

ADD_EXTRACT_attribute

You can change any of the attributes specified with the ADD EXTRACT command, except for the following:

  • Altering an Extract specified with the EXTTRAILSOURCE option.

  • Altering the number of RAC threads specified with the THREADS option.

For these exceptions, delete the Extract group and then add it again.

If using the BEGIN option, do not combine other options in the statement. Issue separate statements, for example: 

ALTER EXTRACT finance, BEGIN 2011-01-01
ALTER EXTRACT finance, ETROLLOVER
ALTER EXTRACT finance, SCN 789000

If using the SCN or BEGIN option for Integrated Extract, it requires a DBLOGIN, and the SCN or timestamp value specified cannot be below the outbound server's first SCN or timestamp. To find the outbound server's first SCN, issue the following command: 

INFO EXTRACT group_name, SHOWCH DETAIL

The first SCN value is listed as shown in the following example:

Integrated Extract outbound server first scn: 0.665884 (665884)
EXTSEQNO sequence_number

Valid for a primary Extract in classic capture mode for Oracle, and a data pump Extract. Not supported for an Oracle Extract in integrated mode. Specifies either of the following:

  • sequence number of an Oracle redo log and RBA within that log at which to begin capturing data.

  • the relative byte address within that file at which to begin capturing data. Together these specify the location in the TMF Master Audit Trail (MAT).

  • the file in a trail in which to begin capturing data (for a data pump). Specify the sequence number, but not any zeroes used for padding. For example, if the trail file is \aa000026, you would specify EXTSEQNO 26. By default, processing begins at the beginning of a trail unless this option is used.

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

EXTRBA offset_number

Valid for 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

(PostgreSQL) You can use this option for PostgreSQL to specify the EOF value. DBLOGIN is required.

TRANLOG LRI LRI_number

(DB2 LUW) You can use this option for DB2 LUW systems to specify the LRI record value for the checkpoint transaction log.

For PostgreSQL, DBLOGIN is required for position by EOF.

UPGRADE INTEGRATED TRANLOG

Upgrades the Extract group from classic capture to integrated capture. To support the upgrade, the transaction log that contains the start of the oldest open transaction must be available on the source or downstream mining system.

DOWNGRADE INTEGRATED TRANLOG [THREADS number]

Downgrades the Extract group from integrated capture to classic capture. When downgrading on a RAC system, the THREADS option must be used to specify the number of RAC threads. On a non-RAC system, you can optionally specify THREADS 1 to cause the downgraded classic Extract to run in threaded mode with one thread, which is similar to doing an ADD EXTRACT with THREADS 1 on a non-RAC system.

To support the downgrade, the transaction log that contains the start of the oldest open transaction must be available on the source or downstream mining system..

THREAD number

Valid for classic capture mode. In an Oracle RAC configuration, alters Extract only for the specified redo thread. Only one thread number can be specified.

SCN value

Valid for Oracle. Starts Extract at the transaction in the redo log that has the specified Oracle system change number (SCN). This option is valid for Extract both in classic capture and integrated modes. For Extract in integrated mode, the SCN value must be greater than the SCN at which the Extract was registered with the database. If using the SCN or BEGIN option for Integrated Extract, it requires a DBLOGIN, and the SCN or timestamp value specified cannot be below the outbound server's first SCN or timestamp.

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:
ALTER EXTRACT extsql LSN 0X00000d7e:0000036b:0001
ETROLLOVER

Use for manual recovery situations that require repositioning and regenerating trail files for a primary Extract and when upgrading Oracle GoldenGate from a previous version.

Causes Extract to create a new incarnation of the trail file and increments to the next file in the trail sequence when restarting, requiring readers such as pump or Replicat, to be manually repositioned to the new trail sequence number.

From 19c onwards, during Distribution Server processing:

  • If the source primary Extract is upgraded with target trail file ETROLLOVER, then the Distribution Server automatically detects the source trail file ETROLLOVER and starts reading from the next input trail file. This is the same command as data pump ALTER EXTRACT groupname EXTSEQNO seqno+1 EXTRBA 0.

  • After upgrading the Distribution Server, it automatically performs the ETROLLOVER for the output trail file upon restarting and writes the next trail file properly. This is the same as data pump ALTER EXTRACT groupname ETROLLOVER.

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 encryption profile for the Extract. This name is case sensitive so you must use the exact name that you entered with ADD EXTRACT.
CRITICAL

Indicates if the process is critical for the deployment.

PROFILE
Name of the auto start profile. This name is case sensitive so you must use the exact name that you entered with ADD EXTRACT.
AUTOSTART
Sets the auto start value to YES or NO for the Extract.
RETRIES

The maximum number of tries for restarting the task before aborting 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.

INFO EXTRACT group-name, SHOWCH DETAIL

The first SCN value is listed as shown in the following example: 

Integrated Extract outbound server first scn: 0.665884 (665884)

Examples

The following alters Extract to start processing data from January 1, 2011.

ALTER EXTRACT finance, BEGIN 2011-01-01

The following alters Extract to start processing at a specific location in the trail.

ALTER EXTRACT finance, EXTSEQNO 26, EXTRBA 338

The following alters Extract in an Oracle RAC environment, and applies the new begin point only for redo thread 4.

ALTER EXTRACT accounts, THREAD 4, BEGIN 2011-01-01

The following alters Extract in a SQL Server environment to start at a specific LSN.

ALTER EXTRACT sales, LSN 3454:875:445

The following alters Extract to increment to the next file in the trail sequence.

ALTER EXTRACT finance, ETROLLOVER

The following alters Extract to upgrade to integrated capture.

ALTER EXTRACT finance, UPGRADE INTEGRATED TRANLOG

The following alters Extract to downgrade to classic capture in a RAC environment.

ALTER EXTRACT finance, DOWNGRADE INTEGRATED TRANLOG THREADS 3

The following alters Extract in an Oracle environment to start processing data from source database SCN 778899.

ALTER EXTRACT finance, SCN 778899

The following shows ALTER EXTRACT for an IBM for i journal start point. 

ALTER EXTRACT finance, SEQNO 1234  JOURNAL accts/acctsjrn

The following shows ALTER EXTRACT for an IBM for i journal and receiver start point. 

ALTER EXTRACT finance, SEQNO 1234 JOURNAL accts/acctsjrn JRNRCV accts/jrnrcv0005

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

ALTER EXTRACT extcust, TRANLOG LRI 8066.322711

The following example shows the ALTER EXTRACT options used with PostgreSQL: 
GGSCI> ALTER EXTRACT Extract1, EOF
GGSCI> ALTER EXTRACT <POSTEXT>, LSN 0/156784
GGSCI> ALTER EXTRACT <POSTEXT>, begin 2020-02-18 14:50:43.4230

Parent topic: Common Command Line Interface Commands