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 }
CACHEMGR {CACHESTATS | CACHEQUEUES | CACHEPOOL n} |
CACHEMGR CACHEFSOPTION { MS_SYNC | MS_ASYNC } |
FORCESTOP |
FORCETRANS transaction_ID [THREAD n] [FORCE] |
GETLAG |
GETTCPSTATS |
LOGEND |
LOGSTATS |
REPORT |
RESUME |
ROLLOVER |
SHOWTRANS [transaction_ID] [THREAD n] [COUNT n]
    [DURATION duration unit] [TABULAR]
    [FILE file_name [DETAIL]] |
SKIPTRANS transaction_ID [THREAD n] [FORCE] |
STATUS |
STOP |
TRACE[2] file_name |
TRACE[2] OFF |
TRACE OFF file_name |
TRACEINIT |
TRANLOGOPTIONS {PREPAREFORUPGRADETOIE | NOPREPAREFORUPGRADETOIE} |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.

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

Returns statistics for virtual memory usage and file caching.

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 [THREAD n] [FORCE]

Valid for MySQL, Oracle, SQL/MX, SQL Server, Sybase.

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 with the exception of SQL/MX. For SQL/MX, the transaction does not have to be the oldest outstanding.

THREAD n

Valid only for Oracle.

Use THREAD n to specify which thread generated the transaction in an Oracle RAC environment if there are duplicate transaction IDs across threads.

FORCE

Valid for Oracle, SQL/MX, SQL Server, Sybase. 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 (see "LAG EXTRACT").

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. See "STATOPTIONS".

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] [THREAD n] [COUNT n]
[DURATION duration unit] [TABULAR] | [FILE file_name [DETAIL]]

Valid for MySQL, Oracle, SQL/MX, SQL Server, Sybase.

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). See Administering Oracle GoldenGate for Windows and UNIX for more information about checkpoints.

  • 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. See Example 1-0 for sample output of SHOWTRANS. To further control output, see the following options.

transaction_ID

Limits the command output to a specific transaction.

THREAD n

Valid only for Oracle.

Constrains the output to open transactions against a specific Oracle RAC thread. For n, use a RAC thread number that is recognized by Extract.

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

For Sybase, which does not put a timestamp on each record, the duration is not always precise and depends on the time information that is stored in the transaction log for the BEGIN and COMMIT records.

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, SQL/MX, Sybase.

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.
SKIPTRANS transaction_ID [THREAD n] [FORCE]

Valid for MySQL, Oracle, SQL/MX, SQL Server, Sybase.

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 will still be 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 with the exception of SQL/MX. For SQL/MX, the transaction does not have to be the oldest outstanding. 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.

THREAD n

Valid only for Oracle.

Use THREAD n to specify which thread generated the transaction in an Oracle RAC environment if there are duplicate transaction IDs. SKIPTRANS specifies the checkpoint index number, not the actual thread number. To specify the correct thread, issue the INFO EXTRACT group_name SHOWCH command, and then specify the READ checkpoint index number that corresponds to the thread number that you want to skip. See the examples for details. See Administering Oracle GoldenGate for Windows and UNIX for more information about checkpoints.

FORCE

Valid for Oracle, SQL/MX, SQL Server, Sybase. 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 JC108XT...
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 {PREPAREFORUPGRADETOIE | NOPREPAREFORUPGRADETOIE}

(Oracle) Valid when upgrading from Classic to Integrated Extract on Oracle RAC.

When upgrading on Oracle RAC from Classic to Integrated Extract, you must set the PREPAREFORUPGRADETOIE option before stopping Classic Extract for the upgrade then wait for the information message in the report file that indicates that the parameter has taken effect before proceeding with the upgrade. For detailed upgrade instructions, see Upgrading Oracle GoldenGate for Windows and UNIX.

PREPAREFORUPGRADETOIE

Set PREPAREFORUPGRADETOIE in the Extract parameter file, which requires a restart of Extract, or you can set it dynamically for a running extract from GGSCI using this command:

SEND EXTRACT extract_name TRANLOGOPTIONS PREPAREFORUPGRADETOIE

NOPREPAREFORUPGRADETOIE

Dynamically turns off the PREPAREFORUPGRADETOIE option if necessary. The default is NOPREPAREFORUPGRADETOIE.

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. See "TRANLOGOPTIONS" for descriptions.

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. See "TRANLOGOPTIONS".

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

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.

A SQL/MX command can be any of the following. The module returns a response to GGSCI. The response can be either ERROR or OK along with a response message.

'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:

'OPENTRANS'

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

Examples

Example 1   
SEND EXTRACT finance, ROLLOVER
Example 2   
SEND EXTRACT finance, STOP
Example 3   
SEND EXTRACT finance, VAMMESSAGE 'control:suspend'
Example 4   
SEND EXTRACT finance, TRANLOGOPTIONS TRANSCLEANUPFREQUENCY 20
Example 5   

This example explains SKIPTRANS. Start with the following SHOWCH output, which shows that thread 2 is at Read Checkpoint #3. See Administering Oracle GoldenGate for Windows and UNIX for more information about checkpoints.

INFO extract 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.

Example 6   
SEND EXTRACT finance, SHOWTRANS COUNT 2
Example 7   

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
Example 8   

The following 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, because it is not known whether operations on configured tables will be added to a transaction at some point in the future.

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 the Oracle GoldenGate software.