Extract commands

1.2 Extract commands

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.

1.2.1 ADD EXTRACT

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: See "Specifying an Alternative Parameter or Report File".
PROCESS process_name: See "Specifying an Alternative Process".
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, BASE24 TFL/PTLF, or Connex Advantage 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 : Use the NOCREATE option of SOURCE to specify that the trail is not created. If the CREATE option or no value is specified, the trail 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, ACIPTLFX, or ADVANTAGE.
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 asFINANCEOracle 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:

PARAMS param_file_name: Supplies an alternative parameter file name. Enter the fully qualified path name for the parameter file.
REPORT report_name: Supplies an alternative report file name. Enter the fully qualified path name for the parameter file.

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

1.2.2 ALTER EXTRACT

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.

1.2.3 CLEANUP EXTRACT

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.

Syntax

CLEANUP EXTRACT group_name [, SAVE count ]

group_name: An Extract group name or wildcard specification, such as * or FIN*.
SAVE count: Save the last option run records instead of just the last record.

1.2.4 DELETE EXTRACT

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.

Syntax

DELETE EXTRACT group_name [!]

group_name: An Extract group name or wildcard specification, such as * or FIN*.
!: (exclamation point) Deletes trail files associated with each group without prompting the operator.

1.2.5 INFO EXTRACT

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.

1.2.6 KILL EXTRACT

Use KILL EXTRACT to force Extract to stop immediately. Try STOP EXTRACT first because it also performs cleanup. Using the Oracle GoldenGate commands STOP or KILL is preferred to stopping processes from TACL. Manager automatically restarts processes that are stopped from TACL.

Syntax

KILL EXTRACT group_name

group_name: The group name. You can use wildcards to kill a set of groups.

1.2.7 LAG EXTRACT

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.

Syntax

LAG EXTRACT {group_name | process_name}

group_name: The group name, as in: LAG EXTRACT FINANCE
process_name: The process name, as in: LAG EXTRACT $GGE00

1.2.8 SEND EXTRACT

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
}

group_name: A running Extract group. If the group specified is not running, an error is returned.

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: See "Rollover Oracle GoldenGate trails".
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 `AUDITEND` returns "All audit processed," more data must be processed before it can be assumed that secondary databases are synchronized.
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 `SEND` `REPORT` options.
TCP/IP statistics	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:

FORCESTOP: Terminates the process with a STOP operation.
STOP: Terminates the run gracefully. This command is preferable to stopping from TACL, which results in an ABEND status.

Processing rollbacks

Process rollback records with:

GETROLLBACKS: Retrieves rollback records. Use this command only before extracting changes during an initial-load phase.
IGNOREROLLBACKS: Ignores rollback records. Use this command after completing your initial load.

1.2.9 START EXTRACT

Use START EXTRACT to start Extract. GGSCI routes the START request to Manager to start and monitor the process.

Syntax

START EXTRACT group_name
[, FILTERRESTART | NOFILTERRESTART]

group_name: The name of the Extract group. You can use wildcards to specify a set of group names, such as, * or *FIN*.

FILTERRESTART | NOFILTERRESTART: NOFILTERRESTART causes Extract to ignore transactions that it has already processed to the output trails. Use only when Extract is to re-process data and you are confident that likely duplicated transactions in the trail that would normally cause Replicat to abend are accounted for.
The default is FILTERRESTART.

1.2.10 STATUS EXTRACT

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.

1.2.11 STOP EXTRACT

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.