Use Extract commands to create and manage Extract groups. The Extract process captures operations and sends the data to the target system. The Extract process maintains checkpoints to provide a starting point for subsequent runs, provides run history information, and displays the audit trails required for a given Extract group.
Process names, parameter files, and report files take system-assigned default values. Oracle GoldenGate Software recommends using the default names. If your installation requires different names see Changing Default Component Names.
Use ADD EXTRACT
to add Extract groups, allowing change records to be processed from run to run without data loss.
Using ADD EXTRACT
options you can perform the operations that are summarized in "ADD EXTRACT options summary".
Syntax
ADD EXTRACT group_name { [, BEGIN time |, AUDSEQNO seq_num, AUDRBA rba] | [[, SOURCE trail_name {BEGIN time |, EXTSEQNO seq_num, EXTRBA rba}] | [, LOGTRAILSOURCE trail_name {BEGIN time |, EXTSEQNO seq_num, EXTRBA rba}] | [, SOURCEISTABLE]] | [, FILETYPE file_type file_name] } [, CPU primary_cpu] [, BACKUPCPU backup_cpu] [, PRI priority] [, PROCESS process_name] [, PROGRAM program_name] [, PARAMS param_file_name] [, REPORT report_name] [, DESC "text"]
ADD EXTRACT options summary
group_name
The group name.
SOURCE
trail_name
| LOGTRAILSOURCE
trail_name
| SOURCEISTABLE
| FILETYPE
file_type, file_name
The default source for ADD EXTRACT
is the TMF
audit trail. For information on other data sources see "Specifying the Data Source".
BEGIN
time
| , {AUDSEQNO
seq_num
, AUDRBA
rba
| EXTSEQNO
seq_num
, EXTRBA
rba
}To specify a begin time or starting point in an audit trail or an Oracle GoldenGate trail, see "Specifying a Starting Point".
CPU
cpu
BACKUPCPU
cpu
PRI
priority
To specify the CPUs, see "Assigning CPUs".
DESC
"text
"See "Describing the Group".
PARAMS
param_file_name
REPORT
report_name
PROCESS
process_name
PROGRAM
program_name
The name of the object file to run. See "Executing user exits".
Example
The following example creates an Extract group called DISTRIB
that:
Begins at midnight on May 1, 2010
Runs in CPU 9
at priority 170
with an assigned backup CPU in case the primary fails
ADD EXTRACT DISTRIB, BEGIN 2010-05-01 00:00, CPU 9, BACKUPCPU 7, PRI 170
Specifying the Data Source
The default ADD EXTRACT
source is a TMF audit trail. If your source is not the audit trail, you can specify an alternative source. Valid sources are:
A local Oracle GoldenGate trail
An Oracle GoldenGate Logger trail
An entry-sequenced or BASE24 TFL
/PTLF
file
Data captured directly from a file or table for one-time processes, such as initial synchronization
Using a Local Oracle GoldenGate Trail
A local Oracle GoldenGate trail is specified by SOURCE
trail_name
. The following example identifies the data source as a local Oracle GoldenGate trail, and specifies a sequence number in the trail at which to begin extracting data.
ADD EXTRACT FINANCE, SOURCE \LA.$D1.GGSDAT.AA, EXTSEQNO 26
Not Creating the : Use the NOCREATE
option of SOURCE
to specify that the trail is not created. If the CREATE
option or no value is specified, the is created.
Using the Logger Trail
A Logger trail is specified by LOGTRAILSOURCE
trail_name
, as in:
ADD EXTRACT FINANCE, LOGTRAILSOURCE $DATA2.GLOGGGL.AA
Using a File
An entry-sequenced or ACI file source is specified by FILETYPE
file_type
file_name
, as in:
ADD EXTRACT DISTRIB, FILETYPE ENTRY $DATA5.GGSDAT.FL1234
For file_name
, enter one of: ENTRY, ACITLF, ACIPTLF, ACITLFX
, or ACIPTLFX
Include the ALTINPUT
and RANGE
parameters in the Extract parameter file when capturing directly from a sequence of files
For One-time Processing
Initial synchronization or other one-time tasks are specified by SOURCEISTABLE (
or SOURCEISFILE
for an Enscribe file),
as in:
ADD EXTRACT GROUP1, SOURCEISTABLE
When you configure Extract for a task, you must include a corresponding SOURCEISTABLE
parameter in the Extract parameter file.
SOURCEISTABLE
does not maintain checkpoints unless RESTARTCHECKPOINTS
is used.
Specifying a Starting Point
You can specify a trail file sequence number and relative byte address as a starting point within an audit trail or local Oracle GoldenGate trail. However, it is more typical to specify a starting point using BEGIN
with a date and time, which is the preferred method.
BEGIN
time
Determines when Extract begins processing data in the audit trail. The time
options are: NOW
, or a date and time as yyyy-mm-dd [hh:mi:[ss[.cccccc]]]
.
AUDSEQNO
seq_num
Identifies the TMF audit trail file sequence number at which to begin extracting data
AUDRBA
rba
Specifies that processing begin at the specified relative byte address.
EXTSEQNO
seq_num
Identifies the Oracle GoldenGate trail file sequence number at which to begin extracting data.
EXTRBA
rba
Specifies that processing begin at the specified relative byte address.
Example
ADD EXTRACT ORDERS, BEGIN NOW
Assigning CPUs
When you add an Extract group you can specify primary and backup CPUs and a process priority.
CPU
cpu
The primary CPU in which Extract runs. The default is the CPU in which Manager runs.
BACKUPCPU
cpu
An alternative CPU on which Extract runs if the primary CPU becomes unavailable.
PRI
priority
The NonStop priority for the process. This defaults to the NonStop priority assigned to the TACL process underlying the ADD
.
Example
This example assigns both the primary and backup CPUs and a priority.
ADD EXTRACT DISTRIB, BEGIN 2010-05-01 00:00, LOGTRAILSOURCE $DATA2.GLOGGGL.AA, CPU 9, BACKUPCPU 7, PRI 170
Specifying an Alternative Process
The default process name is $GGSnn
, where nn
represents the sequence of the process. Oracle GoldenGate recommends that you use the default, however, if you must specify an alternative process, you can do so with the PROCESS
process_name
option.
Example
ADD EXTRACT FINANCE, BEGIN 2010-05-01 00:00, PROCESS $GGE07
Specifying an Alternative Parameter or Report File
Oracle GoldenGate recommends that you use the default parameter and report names, however, if you must specify an alternative name, use the options described here. Alternatively, you can change the default names globally from the GLOBALS
parameter file using ADD DEFINE
. See the parameter summary for GLOBALS
on "GLOBALS Parameters Summary". Also see Changing Default Component Names.
The default parameter file name is GGS_volume
.GGSPARM
.group_name
, where group_name
represents a group, such as FINANCE.
The default report file name is GGS_volume
.GGSRPT
.rpt_name
, where rpt_name
represents the group name, such as FINANCE.
Oracle GoldenGate creates an entry-sequenced file to hold each group's run results, and by default, the report name is the same as the group name.
To change the default names:
Example
These examples change the default parameter file and report names.
ADD EXTRACT FINANCE, BEGIN 2010-05-01 00:00, PARAMS $DATA01.NEWPARM.FINANCE ADD EXTRACT FINANCE, BEGIN 2010-05-01 00:00, REPORT $PROD.NEWRPT.FINANCE
Describing the Group
Use the DESC
"text
" option to describe an Extract group.
Example
ADD EXTRACT ET24AT2, LOGTRAILSOURCE GGSLOG.LT, DESC "T24 data pump for ATM transactions to IBM in Seattle"
Executing user exits
You can create and run your own routines by compiling them into an object file and binding this to the Extract program using the TACL macro named BINDEXIT
. For more information, see Creating User Exits.
When you are ready to call the user exit, launch the Extract object that has the bound routines with the PROGRAM
program_name
option. Manager uses that program when starting the process.
Example
ADD EXTRACT GROUP1, BEGIN NOW, CPU 1, PRI 150, PROGRAM $DATA.GGS.FINEXIT1
Use ALTER EXTRACT
primarily to change attributes of the CPU
, PRIORITY
or BACKUPCPU
options. You can use ALTER EXTRACT
to change attributes of the options you specified with ADD EXTRACT
, but you should consider the following:
Use caution when changing the BEGIN
values previously set with ADD EXTRACT
. Since the BEGIN
option checkpoints the starting point in the source, changing it may cause duplicate or missing records.
You can change EXTRAILSOURCE
or LOGTRAILSOURCE
settings with ALTER
EXTRACT
, but Oracle GoldenGate recommends deleting and re-adding the group instead.
Syntax
ALTER EXTRACT group_name [, ETROLLOVER] [, ETPURGE] [, option ]
group_name
The group name.
ETROLLOVER
Causes Extract to increment and write to the next file in the trail sequence when restarting. For example, if the current file is ET000002
, the current file will be ET000003
when Extract
restarts.
ETPURGE
Causes old trails to be purged before the new one is created. Valid only when ETROLLOVER
is specified.
option
In addition to the above described options, you can specify any appropriate ADD EXTRACT
option.
Use CLEANUP EXTRACT
to delete old run history records for a group. This command keeps the last run record, enabling processing to resume from the correct position.
For example: CLEANUP EXTRACT FINANCE
deletes the run history records for the FINANCE
group, and keeps the last run record. You can also specify a quantity of records to save, as in: CLEANUP EXTRACT * SAVE 5
, saving the last five run records.
Use DELETE
EXTRACT
to delete an Extract group and its associated checkpoints. Use this when the TMF configuration changes, or when you no longer require the group.
When you delete an Extract group, Oracle GoldenGate deletes both the group and the metadata that controls the group's trail. By default it retains all the files currently in the trail. If you want to delete the trail files, you must use the exclamation point (!) in the DELETE
EXTRACT
statement or manually purge the files.
Use INFO EXTRACT
to retrieve processing history for an Extract group. You can specify reporting options to obtain:
Status of the process
The process run history
A process lag report
Detailed historical checkpoints
Only processes that are running, or stopped
Information about tasks
Syntax
INFO EXTRACT group_name [, BRIEF | DETAIL] [, LAG number SECONDS | MINUTES | HOURS] [, SHOWCH] [, UP | DOWN] [, TASKS | ALLPROCESSES] [, PROGRAM]
group_name
An Extract group name or wildcard specification, such as *
or FIN*
.
BRIEF
Reports:
Status of the process (STARTING, RUNNING, STOPPED
or ABENDED
).
An approximation of the time and byte lag between the associated source and Extract processing.
DETAIL
Reports:
Process run history, which includes starting and stopping points within the audit.
Run history for trails.
Process parameters established by the ADD
EXTRACT
command.
LAG
number
SECONDS
| MINUTES
| HOURS
Restricts the display to groups that are a specified time interval behind. This helps spot critical conditions. The lag returned by this command is approximate. For precise information, use LAG EXTRACT
. Lag measures both bytes behind and time behind. For more information about how Oracle GoldenGate reports lag, see Changing Default Component Names.
SHOWCH
Shows detailed historical checkpoints.
UP | DOWN
Shows processes that are either running, (UP
) or not (DOWN
). Specify either UP
or DOWN
.
TASKS | ALLPROCESSES
Shows information about either tasks or all processes that are running. Specify either TASKS
or ALLPROCESSES
.
PROGRAM
Displays the name and location of the object that is running.
Use LAG EXTRACT
to determine Extract's relative position in the audit trail. This command estimates the lag behind the source database more precisely than INFO EXTRACT
.
For more information about how Oracle GoldenGate reports lag, see Changing Default Component Names.
To determine lag for local processes, specify the group name. To determine lag for remote processes, specify the remote process name.
Use SEND EXTRACT
to communicate with a running Extract process. Using SEND EXTRACT
options, you can perform a variety of operations that are summarized in "SEND EXTRACT options summary".
Syntax
SEND EXTRACT group_name { ARCLOSECATALOG | AUDITEND | STATUS | GETTCPSTATS | RESETTCPSTATS | REPORT [time_option [RESET | FILE name | TABLE name]] | ROLLREPORT | GETEXTARSTATS | RESETEXTARSTATS | GETARSTATS, [MAT | AUXnn] | RESETARSTATS, [MAT | AUXnn] | GETTRANSINFO | GETARPROCESS | GETARPARAMS, [MAT | AUXnn] | GETARFILELIST, [MAT | AUXnn]] GETARFILESTATS, [FILE | MAT | MINRECS | RESET | QUIET | NOPARTITIONS] | GETAREXCLUDELIST, [FILE | MAT | AUXnn] | CLEAREXCLUDELIST | ROLLOVER | LAGSTATS option | LAGSNAPSHOT | LAGREPORTON | LAGREPORTOFF | LAGOFF | FORCESTOP | STOP | GETROLLBACKS | IGNOREROLLBACKS }
SEND EXTRACT options summary
AUDITEND | STATUS | REPORT | GETTCPSTATS | RESETTCPSTATS
See "Obtaining process reports".
ARCLOSECATALOG | GETEXTARSTATS | RESETEXTARSTATS | GETARSTATS | RESETARSTATS |
GETTRANSINFO | GETARPROCESS | GETARPARAMS | GETARFILELIST | GETARFILESTATS |
GETAREXCLUDELIST | CLEAREXCLUDELIST
See "Managing the Audserv program".
ROLLREPORT
See "Opening a new report file".
ROLLOVER
LAGSTATS
option
See "Obtaining lag reports".
FORCESTOP | STOP
See "Stopping the process".
GETROLLBACKS | IGNOREROLLBACKS
See "Processing rollbacks".
Example
SEND EXTRACT FINANCE, STOP SEND EXTRACT MANUFACT, ROLLOVER
Obtaining process reports
You can generate reports for:
Report | Option | Description |
---|---|---|
End of audit trail |
AUDITEND |
Queries the Extract process to determine whether all records in the audit trails have been processed. This command indicates whether more Extract and Replicat activity must occur before a scheduled switch between databases. Until |
Processing status |
STATUS |
Returns a detailed status of the processing state, including current position and activity. |
Processing statistics |
REPORT |
Generates an interim statistical report to the report file, including the number of inserts, updates, and deletes. Refer to "SEND REPORT" for detail on |
GETTCPSTATS |
Retrieves TCP/IP statistics, such as the quantity and byte length of inbound and outbound messages, the number of messages received and sent, wait times, process CPU time, and byte transmit averages. Time accumulates when Extract is waiting on a socket send or receive and all times are reported in microseconds. |
|
TCP/IP statistics type |
RESETTCPSTATS |
Resets the TCP/IP statistics so the next report displays fresh statistics. |
Example
The first example uses the AUDITEND
option to report on the end of an audit trail. The second example specifies the STATUS
option to return details of the processing state.
SEND EXTRACT FINANCE, AUDITEND SEND EXTRACT FINANCE, STATUS
Opening a new report file
To close the current report file and open a new one, specify the ROLLREPORT
option. ROLLREPORT
renames the current file by appending a number to the end of the report name (such as EXTACCT0
), then opens a new report file with the original name.
Managing the Audserv program
SEND
EXTRACT
supplies the following options for determining the status of Audserv operations.
ARCLOSECATALOG
Instructs Audserv to close its opens on the SQL Catalog.
GETEXTARSTATS
Retrieves information about Audserv activity. Information returned includes: first and last record timestamp, first and last read timestamp, bytes processed, commits, and other processing statistics.
RESETEXTARSTATS
Resets the report generated by GETEXTARSTATS
.
GETARSTATS, [MAT | AUXnn]
Retrieves audit trail statistics from Audserv.
RESETARSTATS, [MAT | AUXnn]
Resets the report generated by GETARSTATS
.
GETTRANSINFO
Retrieves information from Extract's pending transaction table.
GETARPROCESS
Retrieves the process names of Audserv processes.
GETARPARAMS, [MAT | AUXnn]
Retrieves Audserv run-time parameters.
GETARFILELIST, [MAT | AUXnn]
Retrieves the Audserv file list.
GETARFILESTATS, [FILE | MAT | MINRECS | RESET | QUIET | NOPARTITIONS]
Retrieves Audserv file level statistics.
GETAREXCLUDELIST, [FILE | MAT | AUXnn]
Retrieves the contents of the Audserv exclude list.
CLEAREXCLUDELIST
Clears the Audserv exclude list.
Rollover Oracle GoldenGate trails
The ROLLOVER
option closes the current trail and opens the next trail in the sequence.
Obtaining lag reports
SEND
EXTRACT
supplies options for generating a variety of lag reports.
LAGSTATS
option
Retrieves and optionally reports lag statistics. The options are the same as those for the LAGSTATS
parameter. See additional LAGSTATS
information "LAGSTATS".
The SEND EXTRACT
LAGSTATS
specification replaces any previous LAGSTATS
entry.
LAGSNAPSHOT
Writes a current statistics report to the screen and to the report file. To generate this report, specify either the LAGSTATS
parameter in the parameter file, or issue SEND
EXTRACT
group_name
, option
.
LAGREPORTON
Generates a report for each lag interval.
LAGREPORTOFF
Turns off automatic reporting, but continues to retrieve data.
LAGOFF
Turns off lag statistics.
Stopping the process
You can stop the current process with:
Use STATUS EXTRACT
to determine if Extract groups are running. A report displays to the Extract process's home terminal.
Syntax
STATUS EXTRACT group_name
[, DETAIL] | [,TASKS | ALLPROCESSES]
group_name
The name of the group. You can use wildcards to specify a set of group names, such as, *
or *FIN*
.
DETAIL
When you specify DETAIL
, (STATUS EXTRACT *, DETAIL
) the audit trails required by the group are also listed. Output consists of the locations of required audit trails, whether they are on disk or tape, and whether the trails still exist.
DETAIL
is useful for determining whether audit must be restored from tape before the group is run and which groups are causing Manager to tie up TMF resources.
TASKS | ALLPROCESSES
Determine either the tasks or all processes that are running. Specify either TASKS
or ALLPROCESSES
.
Use STOP EXTRACT
to stop Extract gracefully. Use STOP
when you are changing the process configuration and to prevent Manager from automatically restarting the process.
Syntax
STOP EXTRACT group_name [, WAIT [seconds] | ATEND |!]
group_name
The name of the Extract group. You can use wildcards to specify a set of group names, such as, *
or *FIN*
.
WAIT
seconds
GGSCI waits for Extract to terminate before issuing the next prompt. If seconds
is specified, GGSCI waits that many seconds before returning control to the user. If you do not specify WAIT
, GGSCI issues the next prompt immediately.
ATEND
Instructs Extract to stop when it reaches end-of-file for the last sequence of audit trails. If the application that updates the source database is brought down first, this ensures that Extract processed all relevant database updates before stopping.
If Extract is reading data from an Oracle GoldenGate trail instead of TMF audit trails, ATEND
causes Extract to terminate when end-of-file is reached for the last sequence of the trails.
!
(Exclamation point) Stops Extract immediately, even in the middle of a transaction. Use this option to terminate long running transactions. As with ATEND
, a grouped transaction is rolled back but the individual transactions are replayed, if the trail is available.