SEND EXTRACT

Use SEND EXTRACT to communicate with a running Extract process. The request is processed as soon as Extract is ready to accept commands from users.

Syntax

SEND EXTRACT group_name, {
BR {BRINTERVAL interval | 
   BRSTART | 
   BRSTOP | 
   BRCHECKPOINT {IMMEDIATE | IN n{M|H} | AT yyyy-mm-dd hh:mm[:ss]]}} |
BR BRFSOPTION { MS_SYNC | MS_ASYNC }
BR BRSTATS
BR BRSTATUS
CACHEMGR {CACHESTATS {CACHEALL | CACHEMINIMAL | CACHESUPERPOOL | CACHECURRENTPOOL} | CACHEQUEUES | CACHEPOOL n} |
CACHEMGR CACHEFSOPTION { MS_SYNC | MS_ASYNC } |
FORCESTOP |
FORCETRANS transaction_ID [FORCE] |
GETLAG |
GETPARAMINFO [parameter_name] [FILE output_file] |
GETTCPSTATS |
LOGEND |
LOGSTATS |
REPORT |
RESUME |
ROLLOVER |
SHOWTRANS [transaction_ID] [COUNT n]
    [DURATION duration unit] [TABULAR]
    [FILE file_name [DETAIL]] [ALL]|
SKIPTRANS transaction_ID [FORCE] |
STATUS |
STOP |
TRACE[2] file_name |
TRACE[2] OFF |
TRACE OFF file_name |
TRACEINIT |
TRANLOGOPTIONS INTEGRATEDPARAMS(parameter_specification) |
TRANLOGOPTIONS {TRANLOGOPTIONS {PURGEORPHANEDTRANSACTIONS | NOPURGEORPHANEDTRANSACTIONS} |
TRANLOGOPTIONS TRANSCLEANUPFREQUENCY minutes |
VAMMESSAGE 'Teradata_command' |
VAMMESSAGE {'ARSTATS' | 'INCLUDELIST [filter]' | 'FILELIST [filter]'| 'EXCLUDELIST [filter]'} |
VAMMESSAGE 'OPENTRANS'
}
group_name

The name of the Extract group or a wildcard (*) to specify multiple groups. For example, T* sends the command to all Extract processes whose group names start with T. If an Extract is not running, an error is returned.

BR {BRINTERVAL interval | BRSTART | BRSTOP |
BRCHECKPOINT {IMMEDIATE | IN n {H|M} | AT yyyy-mm-dd[ hh:mm[:ss]]}}

Sends commands that affect the Bounded Recovery mode of Extract.

BRINTERVAL interval

Sets the time between Bounded Recovery checkpoints. Valid values are from 20 minutes to 96 hours specified as M for minutes or H for hours, for example 20M or 2H. The default interval is 4 hours.

BRSTART

Starts Bounded Recovery. This command should only be used under direction of Oracle Support.

BRSTOP

Stops Bounded Recovery for the run and for recovery. Consult Oracle Support before using this option. In most circumstances, when there is a problem with Bounded Recovery, it turns itself off.

BRCHECKPOINT {IMMEDIATE | IN n{H|M} | AT yyyy-mm-dd[ hh:mm[:ss]]}}

Sets the point at which a bounded recovery checkpoint is made. IMMEDIATE issues the checkpoint immediately when SEND EXTRACT is issued. IN issues the checkpoint in the specified number of hours or minutes from when SEND EXTRACT is issued. AT issues the checkpoint at exactly the specified time.

BR BRFSOPTION {MS_SYNC | MS_ASYNC}

Performs synchronous/asynchronous writes of the mapped data in Bounded Recovery.

MS_SYNC

Bounded Recovery writes of mapped data are synchronized for I/O data integrity completion.

MS_ASYNC

Bounded Recovery writes of mapped data are initiated or queued for servicing.

BR BRSTATS
Provides details on each of the object pools, which were persisted by BR. This includes the following details:
  • Object sizes through life of the Extract group

  • Object ages through the life of Extract group

  • Extant object sizes as of the most recent BCP

  • Extant object ages as of the most recent BCP

BR BRSTATUS

Returns status for the Bounded Recovery mode of Extract.

It shows the following:

  • Current settings of the BR parameter.

  • Current status of the Bounded Recovery, if one was performed, with current checkpoint interval, timestamps for the next and last checkpoints, and the total and outstanding number of objects and sizes when BR is in progress.

  • Start and end sequence, RBA, SCN, and timestamp for the Bounded Recovery checkpoint positions (per redo thread).

CACHEMGR {CACHESTATS | CACHEQUEUES | CACHEPOOL n}

Returns statistics about the Oracle GoldenGate memory cache manager. CACHESTATS should only be used as explicitly directed by Oracle Support.

CACHESTATS {CACHEALL | CACHEMINIMAL | CACHESUPERPOOL | CACHECURRENTONLY}

CACHESTATS returns statistics for virtual memory usage and file caching.

CACHEALL

Returns all COM statistics and is the default option.

CACHESUPERPOOL

Super pool statistics. Returns statistics about all object pools.

CACHECURRENTONLY

Returns statistics only for the run time.

CACHEMINIMAL

Returns condensed (minimal) version of COM stats whereas CACHEALL returns all statistics.

CACHEQUEUES

Returns statistics for the free queues only.

CACHEPOOL n

Returns statistics for the specified object pool only.

CACHEMGR CACHEFSOPTION {MS_SYNC | MS_ASYNC}

Performs synchronous or asynchronous writes of the mapped data in the Oracle GoldenGate memory cache manager.

FORCESTOP

Forces Extract to stop, bypassing any notifications. This command will stop the process immediately.

FORCETRANS transaction_ID ] [FORCE]

Valid for Db2 LUW, Db2 IBM, MySQL, Oracle, and SQL Server.

Forces Extract to write a transaction specified by its transaction ID number to the trail as a committed transaction. FORCETRANS does not commit the transaction to the source database. It only forces the existing data to the trail so that it is processed (with an implicit commit) by Replicat. You can repeat FORCETRANS for other transactions in order of their age. Note that forcing a transaction to commit to the trail (and therefore the target database) may cause data discrepancies if the transaction is rolled back by the source user applications.

After using FORCETRANS, wait at least five minutes if you intend to issue SEND EXTRACT with FORCESTOP. Otherwise, the transaction will still be present.

If FORCETRANS is used immediately after Extract starts, you might receive an error message that asks you to wait and then try the command again. This means that no other transactions have been processed yet by Extract. Once another transaction is processed, you will be able to force the transaction to trail.

transaction_ID

The ID of the transaction. Get the transaction ID number with SHOWTRANS or from an Extract runtime message. Extract ignores any data added to the transaction after this command is issued. A confirmation prompt must be answered unless FORCE is used. To use FORCETRANS, the specified transaction must be the oldest one in the list of transactions shown with SHOWTRANS.

FORCE

Valid for Oracle and SQL Server. Not valid for MySQL.

Use FORCE to bypass the confirmation prompt.

GETLAG

Determines a true lag time between Extract and the data source. Returns the same results as LAG EXTRACT.

GETPARAMINFO [parameter_name] [FILE output_file]

Use GETPARAMINFO to query runtime parameter values of a running instance, including Extract, Replicat, and Manager. You can query for a single parameter or all parameters and send the output to the console or a text file

parameter_name

The default behavior is to display all parameters in use, meaning those parameters that have ever been queried by the application, parameters, and their current values. If you specify a particular parameter, then the output is filtered by that name.

FILE output_file

The name of the text file that your output is redirected to.

GETTCPSTATS

Displays statistics about network activity between Extract and the target system. The statistics include:

  • Local and remote IP addresses.

  • Inbound and outbound messages, in bytes and bytes per second.

  • Number of receives (inbound) and sends (outbound). There will be at least two receives per inbound message: one for the length and one or more for the data.

  • Average bytes per send and receive.

  • Send and receive wait time: Send wait time is how long it takes for the write to TCP to complete. The lower the send wait time, the better the performance over the network. Receive wait time is how long it takes for a read to complete. Together, the send and receive wait times provide a rough estimate of network round trip time. These are expressed in microseconds.

  • Status of data compression (enabled or not).

  • Uncompressed bytes and compressed bytes: When compared (uncompressed to compressed), these comprise the compression ratio, meaning how many bytes there were before and after compression. You can compare the compression ratio with the bytes that are being compressed per second to determine if the compression rate is worth the cost in terms of resource and network consumption.

The TCPBUFSIZE option of RMTHOST and RMTHOSTOPTIONS controls the size of the TCP buffer for uncompressed data. What actually enters the network will be less than this size if compression is enabled. GETTCPSTATS shows post-compression throughput.

LOGEND

Confirms whether or not Extract has processed all of the records in the data source.

LOGSTATS

Valid only for Oracle.

Instructs Extract to issue a report about the statistics that are related to the processing of data from the Oracle redo log files. Extract uses an asynchronous log reader that reads ahead of the current record that Extract is processing, so that the data is available without additional I/O on the log files. The processing is done through a series of read/write queues. Data is parsed by a producer thread at the same time that additional data is being read from the log file by a reader thread. Thus, the reason for the term "read-ahead" in the statistics.

The statistics are:

  • AsyncReader.Buffersn: There is a field like this for each buffer queue that contains captured redo data. It shows the size, the number of records in it, and how long the wait time is before the data is processed. These statistics are given for write operations and read operations on the queue.

  • REDO read ahead buffers: The number of buffers that are being used to read ahead asynchronously.

  • REDO read ahead buffer size: The size of each buffer.

  • REDO bytes read ahead for current redo: Whether read-ahead mode is on or off for the current redo log file (value of ON or OFF).

  • REDO bytes read: The number of bytes read from all redo log files that are associated with this instance of Extract.

  • REDO bytes read ahead: The number of bytes that were processed by the read-ahead mechanism.

  • REDO bytes unused: The number of read-ahead bytes that were subsequently dropped as the result of Extract position changes or stale reads.

  • REDO bytes parsed: The number of bytes that were processed as valid log data.

  • REDO bytes output: The number of bytes that were written to the trail file (not including internal Oracle GoldenGate overhead).

REPORT

Generates an interim statistical report to the Extract report file. The statistics that are displayed depend upon the configuration of the STATOPTIONS parameter when used with the RESETREPORTSTATS | NORESETREPORTSTATS option.

RESUME

Resumes (makes active) a process that was suspended by an EVENTACTIONS SUSPEND event. The process resumes normal processing from the point at which it was suspended.

ROLLOVER

Causes Extract to increment to the next file in the trail when restarting. For example, if the current file is ET000002, the current file will be ET000003 after the command executes. A trail can be incremented from 000001 through 999999, and then the sequence numbering starts over at 000000.

SHOWTRANS [transaction_ID] [COUNT n]
[DURATION duration unit] [TABULAR] | [FILE file_name [DETAIL]] [ALL]

Valid for Db2 IBM, Db2 LUW, Db2 z/OS, MySQL, Oracle, and SQL Server.

Displays information about open transactions. SHOWTRANS shows any of the following, depending on the database type:

  • Process checkpoint (indicating the oldest log needed to continue processing the transaction in case of an Extract restart).

  • Transaction ID

  • Extract group name

  • Redo thread number

  • Timestamp of the first operation that Oracle GoldenGate extracts from a transaction (not the actual start time of the transaction)

  • System change number (SCN)

  • Redo log number and RBA

  • Status (Pending COMMIT or Running). Pending COMMIT is displayed while a transaction is being written after a FORCETRANS was issued.

Without options, SHOWTRANS displays all open transactions that will fit into the available buffer. However, it doesn't display the output user name sometimes for an open active transaction because the user name is not provided in the begin record from transaction log.

See the examples for sample output of SHOWTRANS. To further control output, see the following options.

transaction_ID

Limits the command output to a specific transaction.

COUNT n

Constrains the output to the specified number of open transactions, starting with the oldest one. Valid values are 1 to 1000.

DURATION duration unit

Restricts the output to transactions that have been open longer than the specified time, where:

duration is the length of time expressed as a whole number.

unit is one of the following to express seconds, minutes, hours, or days:

S|SEC|SECS|SECOND|SECONDS
M|MIN|MINS|MINUTE|MINUTES
H|HOUR|HOURS
D|DAY|DAYS
TABULAR

Valid only for Oracle.

Generates output in tabular format similar to the default table printout from SQL*Plus. The default is field-per-row.

FILE file_name [DETAIL]

Valid only for Oracle and SQL Server. Not valid for MySQL.

Forces Extract to write the transaction information to the specified file. There is no output to the console.

For Oracle, you can write a hex and plain-character dump of the data by using FILE with DETAIL. This dumps the entire transaction from memory to the file. Viewing the data may help you decide whether to skip the transaction or force it to the trail.

Note:

Basic detail information is automatically written to the report file at intervals specified by the WARNLONGTRANS CHECKINTERVAL parameter.
[ALL]

This option allows showing all the transaction in COM. It is useful when detecting potential issues with committed transactions that are remaining in COM.

SKIPTRANS transaction_ID [FORCE]

Valid for Db2 LUW, Db2 IBM, MySQL, Oracle, and SQL Server.

Forces Extract to skip the specified transaction, thereby removing any current data from memory and ignoring any subsequent data. A confirmation prompt must be answered unless FORCE is used. After using SKIPTRANS, wait at least five minutes if you intend to issue SEND EXTRACT with FORCESTOP. Otherwise, the transaction is still present. Note that skipping a transaction may cause data loss in the target database.

Note:

To use SKIPTRANS, the specified transaction must be the oldest one in the list of transactions shown with SHOWTRANS. You can repeat the command for other transactions in order of their age.
transaction_ID

The transaction ID number. Get the ID number with SHOWTRANS or from an Extract runtime message.

FORCE

Valid for Oracle and SQL Server. Not valid for MySQL.

Use FORCE to bypass the prompt that confirms your intent to skip the transaction.

STATUS

Returns a detailed status of the processing state, including current position and activity. Possible processing status messages on the Current status line are:

  • Delaying – waiting for more data

  • Suspended – waiting to be resumed

  • Processing data – processing data

  • Starting initial load – starting an initial load task

  • Processing source tables – processing data for initial load task

  • Reading from data source – reading from the data source, such as a source table or transaction log

  • Adding record to transaction list – adding a record to the file memory transaction list

  • At EOF (end of file) – no more records to process

In addition to the preceding statuses, the following status notations appear during an Extract recovery after an abend event. You can follow the progress as Extract continually changes its log read position over the course of the recovery.

  • In recovery[1] – Extract is recovering to its checkpoint in the transaction log.

  • In recovery[2] – Extract is recovering from its checkpoint to the end of the trail.

  • Recovery complete – The recovery is finished, and normal processing will resume.

STOP

Stops Extract. If there are any long-running transactions (based on the WARNLONGTRANS parameter), the following message will be displayed:

Sending STOP request to EXTRACT EXTE...
There are open, long-running transactions. Before you stop Extract, make the archives containing data for those transactions available for when Extract restarts. To force Extract to stop, use the SEND EXTRACT group, FORCESTOP command.
Oldest redo log file necessary to restart Extract is:
Redo Thread 1, Redo Log Sequence Number 150, SCN 31248005, RBA 2912272.
TRACE[2] {file_name | OFF}

Turns tracing on and off. Tracing captures information to the specified file to reveal processing bottlenecks. Contact Oracle Support for assistance if the trace reveals significant processing bottlenecks.

TRACE

Captures step-by-step processing information.

TRACE2

Identifies code segments rather than specific steps.

file_name

Specifies the name of the file to which the trace information is written. If a trace is already running when SEND EXTRACT is issued with TRACE, the existing trace file is closed and the trace is resumed to the new file specified with file_name.

OFF

Turns off tracing.

TRACE OFF file_name

Turns tracing off only for the specified trace file.

TRACEINIT

Resets tracing statistics back to 0 and then starts accumulating statistics again. Use this option to track the current behavior of processing, as opposed to historical.

TRANLOGOPTIONS INTEGRATEDPARAMS(parameter_specification)

(Oracle) Supports an integrated Extract. Sends a parameter specification to the database inbound server while Extract is running in integrated mode. Only one parameter specification can be sent at a time with this command. You can send multiple parameter changes, issue multiple SEND EXTRACT commands.

To preserve the continuity of processing, the parameter change is made at a transaction boundary.

TRANLOGOPTIONS {PURGEORPHANEDTRANSACTIONS | NOPURGEORPHANEDTRANSACTIONS}

Valid for Oracle RAC. Enables or disables purging of orphaned transactions that occur when a node fails and Extract cannot capture the rollback.

TRANLOGOPTIONS TRANSCLEANUPFREQUENCY minutes

Valid for Oracle RAC. Specifies the interval, in minutes, after which Oracle GoldenGate scans for orphaned transactions and then re-scans to confirm and delete them. Valid values are from 1 to 43200 minutes. Default is 10 minutes.

VAMMESSAGE 'Teradata_command'
VAMMESSAGE { 'ARSTATS' | 'INCLUDELIST [filter]' | 'EXCLUDELIST [filter ]' }

Sends a command to the capture API that is used by Extract.

A Teradata command can be any of the following:

'control:terminate'

Stops a replication group. Required before dropping or altering a replication group in Teradata.

'control:suspend'

Suspends a replication group. Can be used when upgrading Oracle GoldenGate.

'control:resume'

Resumes a replication group after it has been suspended.

'control:copy database.table'

Copies a table from the source database to the target database.

'ARSTATS'

Displays TMF audit reading statistics.

'FILELIST [filter]'

Displays the list of tables for which Extract has encountered data records in the audit trail that match the selection criteria in the TABLE parameters. The filter option allows use of a wildcard pattern to filter the list of tables returned. GETFILELIST can also be used in the same manner.

'EXCLUDELIST [filter]'

Displays the list of tables for which Extract has encountered data records in the audit trail that do not match the selection criteria in the TABLE parameters. The filter option allows use of a wildcard pattern to filter the list of tables returned. Certain system tables that are implicitly excluded will always be present in the list of excluded tables.

A SQL Server command can be the following:

VAMMESSAGE 'OPENTRANS'

Prints a list of open transactions with their transaction ID, start time, first LSN, and the number of operations they contain.

Examples

SEND EXTRACT exte, ROLLOVER
SEND EXTRACT exte, STOP
SEND EXTRACT exte, VAMMESSAGE 'control:suspend'
SEND EXTRACT exte, TRANLOGOPTIONS TRANSCLEANUPFREQUENCY 20

This example explains SKIPTRANS. Start with the following SHOWCH output, which shows that thread 2 is at Read Checkpoint #3.

INFO exte SHOWCH
Read Checkpoint #3
Oracle RAC Redo Log
Startup Checkpoint (starting position in the data source):
Thread #: 2
Sequence #: 17560
RBA: 65070096
Timestamp: 2011-07-30 20:04:47.000000
SCN: 1461.3499051750 (6278446271206)
Redo File: RAC4REDO/sss11g/onlinelog/group_4.292.716481937

Therefore, SKIPTRANS should be: SKIPTRANS xid THREAD 3.

SEND EXTRACT exte, SHOWTRANS COUNT 2

The following shows the default output of SHOWTRANS.

Oldest redo log file necessary to restart Extract is:
Redo Thread 1, Redo Log Sequence Number 148, SCN 30816254, RBA 17319664
------------------------------------------------------------
XID                 : 5.15.52582
Items               : 30000
Extract             : JC108XT
Redo Thread         : 1
Start Time          : 2011-01-18:12:51:27
SCN                 : 20634955
Redo Seq            : 103
Redo RBA            : 18616848
Status              : Running
------------------------------------------------------------
XID                 : 7.14.48657
Items               : 30000
Extract             : JC108XT
Redo Thread         : 1
Start Time          : 2011-01-18:12:52:14
SCN                 : 20635145
Redo Seq            : 103
Redo RBA            : 26499088
Status              : Running

The following example shows SHOWTRANS output with TABULAR in effect (view is truncated on right).

XID         Items  Extract   Redo Thread  Start Time
5.15.52582  30000  JC108XT       1            2011-01-18:12:52:14

Dumping transaction memory at 2011-01-21 13:36:54.
Record #1:
Header (140 bytes):
       0: 0000 0A4A 0000 FFFF 0000 0000 0057 6C10        ...J.........Wl.
      16: 02FF 3F50 FF38 7C40 0303 4141 414E 5A77        ..?P.8|@..AAANZw
      32: 4141 4641 4141 4B6F 4941 4144 0041 4141        AAFAAAKoIAAD.AAA
      48: 4E5A 7741 4146 4141 414B 6F49 4141 4400        NZwAAFAAAKoIAAD.
      64: 4141 414E 5A77 414A 2F41 4142 7A31 7741        AAANZwAJ/AABz1wA
      80: 4141 0041 4141 4141 4141 4141 4141 4141        AA.AAAAAAAAAAAAA
      96: 4141 4141 4100 0000 0140 FF08 0003 0000        AAAAA....@......
     112: 0000 0000 0000 70FF 0108 FFFF 0001 4A53        ......p.......JS
     128: 554E 2E54 4355 5354 4D45 5200                  UN.TCUSTMER.

Data (93 bytes):
       0: 2C00 0400 0400 0000 0100 0200 0300 0000        ,...............
      16: 0000 0000 0800 0000 1800 0000 2000 0400        ............ ...
      32: 1000 0600 0200 0000 284A 414E 456C 6C6F        ........(JANEllo
      48: 6352 4F43 4B59 2046 4C59 4552 2049 4E43        cROCKY FLYER INC
      64: 2E44 454E 5645 5220 6E43 4F20 7365 7400        .DENVER nCO set.
      80: 0000 0000 0000 0C00 0000 0000 00              ..............

When analyzing the summary output of SHOWTRANS, understand that it shows all currently running transactions on the database (as many as will fit into a predefined buffer). Extract must track every open transaction, not just those that contain operations on tables configured for Oracle GoldenGate.

The Items field of the SHOWTRANS output shows the number of operations in the transaction that have been captured by Oracle GoldenGate so far, not the total number of operations in the transaction. If none of the operations are for configured tables, or if only some of them are, then Items could be 0 or any value less than the total number of operations.

The Start Time field shows the timestamp of the first operation that Oracle GoldenGate extracts from a transaction, not the actual start time of the transaction itself.

Note:

Command output may vary somewhat from the examples shown due ongoing enhancements of Oracle GoldenGate.
The following example shows sending BR request to Extract exte
SEND exte BR BRSTATUS

Output displays the following:

Bounded Recovery Parameter:
Options    = _BRDEBUG _NOBRCLEANUP _BRFORCE_ASSERT
BRINTERVAL = 40SECONDS
BRDIR      = /home/mpopeang/ogg_test/
Bounded Recovery Status: IN PROGRESS
Checkpoint interval = 40SECONDS
Next checkpoint  = 2020-01-15 21:10:47
Last checkpoint# = 49
Last checkpoint  = 2020-01-15 21:10:07
Total objects    = 65
Total size       = 426 MB
Outstanding objects = 58
Outstanding size    = 384 MB
Object pool 1: p12733_extr:RECOVERY: COMPLETE: start:SeqNo: 1580, 
RBA: 793460, SCN: 0.664178312(664178312), Timestamp: 2020-01-15 20:35:45.000000, 
Thread: 1, end=SeqNo:1580, 
RBA: 793460, SCN: 0.664178312 (664178312), Timestamp: 2020-01-15 20:35:45.000000, 
Thread: 1, complete=SeqNo: 1580, RBA: 793460, 
SCN:0.664178312 (664178312), Timestamp: 2020-01-15 20:35:45.000000, Thread: 1 at 2020-01-15 20:38:52.435830
CHECKPOINT: start=SeqNo: 1637, RBA: 10182312, SCN: 0.669567539 (669567539),
Timestamp: 2020-01-15 21:09:59.000000, Thread: 1, end=SeqNo: 1637, 
RBA:10182312, SCN: 0.669567539 (669567539), Timestamp: 2020-01-15 21:09:59.000000, 
Thread: 1

The following example provides details on each of the object pools persisted by BR:

SEND exte BR BRSTATS
Output:

Object pool #0, instance: 1, id: p12733_extr 
Object sizes through life of Extract group:
Sizes in bytes            :         POs
  512K      to        1M-1         :         13
    1M      to        2M-1         :         37
    4M      to        8M-1         :        398
    8M      to       16M-1         :          2
   16M      to       32M-1         :         10
Object ages through life of Extract group:
duration: BCP intervals in the life of the PO
  duration                        0:        230
  duration         30 to         39:        230
Extant object sizes as of most recent BCP:
Sizes in bytes                   :        POs
          512K      to        1M-1        :          8
            1M      to        2M-1        :         17
            4M      to        8M-1        :        200
           16M      to       32M-1        :          5
Extant object ages as of most recent BCP:
duration: BCP intervals in the life of the PO
  duration         30 to            39:        230