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 | GETPARAMINFO [parameter_name] [FILE output_file] | 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 | TRANSLOGOPTIONS INTEGRATEDPARAMS(parameter_specification) | 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.
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.
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").
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.Buffers
n
: 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
] [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. 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.
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.
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. For a list of supported inbound server parameters, see Installing and Configuring Oracle GoldenGate for Oracle Database.
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.
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
]' }
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:
Examples
SEND EXTRACT finance, ROLLOVER
SEND EXTRACT finance, STOP
SEND EXTRACT finance, VAMMESSAGE 'control:suspend'
SEND EXTRACT finance, TRANLOGOPTIONS TRANSCLEANUPFREQUENCY 20
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
.
SEND EXTRACT finance, 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 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.