Add an Online Extract

Before you begin adding an Extract, make sure that the following settings are configured:

Add Database Connections
Add TRANDATA
Add a Checkpoint Table
Add Heartbeat Table
Register an Extract from the Admin Client for Oracle and PostgreSQL

Now, you're ready to add an Extract for your deployment.

From the Administration Service Home page, click the + sign next to Extracts. The Add Extract wizard is displayed.
On the Extract Information screen, select the type of Extract. Depending on the database platform, the types of Extracts that are available may be Integrated Extract, Change Data Capture Extract, and Initial Load Extract.

If you need to set up the Downstream Extract for Oracle database, enable the Downstream Extract toggle switch.

Select the type of Extract to create, and specify the following:
- Process Name: Name of the Extract process. The name of the Extract process can be up to 8 characters.
- Description: Description of the Extract process being created
  
  Note:
  To learn about creating initial load Extract, see About Instantiation.
Click Next.
On the Extract Options screen configure the following settings:
- Source Credentials: Specify a domain for the database.
- Alias: Specify the user ID alias used as the database connection for the source login or select from the displayed options.
- (Oracle and PostgreSQL only) Registration Options:
  - CSN: Commit Sequence Number (CSN) value.
  - Share: This drop down is used to define how to share the LogMiner data dictionary. The available options are Automatic, None, and Extract Name. Automatic means that the system decides which Extract to share. None means that the LogMiner data dictionary is not shared. Extract Name means that the LogMiner data dictionary is shared with the specified Extract.
  - Optimized: Enable this option to optimize the Extract registration.
- Extract Trail:
  - Name: Name of the Extract trail file. The name of the trail file can be upto 2 characters.
  - Subdirectory: Directory name of the subdirectory where the Extract trail is stored.
  - Trail Sequence: Sequence number of the trail.
  - Trail Size: Maximum size of the trail file.
  - Encryption Profile: Description of the encryption profile. If you have not created an encryption profile, then the Local Wallet profile would be selected, by default. To know more about creating and applying encryption profiles, see Configure an Encryption Profile.
  - Encryption Algorithm: List of encryption algorithms available for the Extract trail file.
    
    Note:
    For more information on trail file encryption, see Trail File Encryption and Encrypting Trail Files.
Click Next.
If you selected the Downstream Capture on the Extract Information screen, the Downstream Capture options screen is displayed. Configure the downstream mining database connection for the downstream Extract using this screen.
- Mining Credentials: Specify the domain and the user ID alias value in the Domain and Alias boxes.
- No UserID/No Source DB Connection: Enable this toggle switch to set up the mining database connection using Active Data Guard (ADG). The options to enter the ADG Fetch Credential are displayed:
  - Domain: Domain name for the ADG fetch database.
  - Alias: Domain alias for the ADG fetch database.
  Note:
  Downstream Capture with ADG does not support per-PDB Extract.

Click Next. On the Managed Options screen, configure the auto start and auto restart options for the Extract process. The following table provides these options:

The following table provides these options:

Option	Description
Profile Name	Provides the name of the autostart and autorestart profile. You can select the default or custom options. If you have already created a profile, then you can select that profile also. If you select the Custom option, then you can set up a new profile from this section itself.
Critical to deployment health	(Oracle only) Enable this option if the profile is critical for the deployment health. Note: This option only appears while creating the Extract or Replicat and not when you set up the managed processes in the Profiles page.
Auto Start	Enables autostart for the process.
Startup Delay	Time to wait in seconds before starting the process
Auto Restart	Configures how to restart the process if it terminates
Max Retries	Specify the maximum number of retries to try to start the process
Retry Delay	Delay time in trying to start the process
Retries Window	The duration interval to try to start the process
Restart on Failure only	If true the task is only restarted if it fails.
Disable Task After Retries Exhausted	If true then the task is disabled after exhausting all attempts to restart the process.

Click Next. On the Parameter File screen, you can edit the parameter file in the text area to list the table details that you are interested in capturing. Here's a sample Extract parameter file:
```
EXTRACT exte
USERIDALIAS ggeast DOMAIN OracleGoldenGate
EXTTRAIL east/ea
DDL INCLUDE MAPPED
TABLE hr.*;
```
You can select Register Extract in the background to register the Extract in the background asynchronously. This option is required for Oracle and PostgreSQL databases. See Register an Extract from the Admin Client.
Click Create and Run to create and start the Extract. If you select Create, the Extract is created but you need to start it using the Extract options.

You return to either the Administration Service home page or the Extract page where all created Extracts are listed.
To reach the Extracts page, select Extracts from the Administration Service left navigation pane. From the Extracts page, you can perform the following actions:
- View the status and lag details of Extracts.
- From the Actions column:
  - Click the Details icon to view Extract details such as PDB container name, selected encryption profile, auto start and auto restart options. Extract details also expand into specific details about checkpoint, statistics, parameters, cache manager statistics, report, and integrated diagnostics.
  - Click the Start/Stop icon for stopping or starting Extract.
  - Click the Delete icon to delete an Extract.
  - Click the three-dot icon to select option to start Extract with options or alter an Extract CSN value to begin an Extract.
    
    If you need to start the Extract at a specific CSN value or after a specific CSN value, select between At CSN or After CSN and specify the CSN value from where the Extract should start the extraction process.
  - You can alter an Extracts begin options. The begin options for Extract are Begin, Custom Time, and CSN. Begin immediately starts the Extract after Submit, Custom Time starts the Extract at the specified time, and CSN starts the Extract from the specified the commit sequence number (CSN).

Create a Parameter File for Extract

Follow these instructions to create a parameter file for an Extract.

(MySQL only) When setting up Extract on Windows platform if you want to set the TZ environment variable in the Extract parameter file using SETENV, use the following format:
```
SETENV(TZ='tzn [+|-]hh[:mm[:ss]][dzn]')

tzn - Three-letter time-zone name, such as PST. You must specify the correct offset from local time to UTC.
hh - Difference in hours between UTC and local time. Sign (+) optional for positive values.
mm - Minutes. Separated from hh by a colon (:).
ss - Seconds. Separated from mm by a colon (:).
dzn - Three-letter daylight-saving-time zone such as PDT. 
If daylight saving time is never in effect in the locality, set TZ without a value for dzn. The C run-time 
library assumes the United States' rules for implementing the calculation of daylight saving time (DST).
```
The offset is positive if the local time zone is west of the Prime Meridian and negative if it is East. The hour must be between 0 and 24, and the minute and seconds between 0 and 59.
For example, for Pacific Standard Time with automatic Daylight switching:
```
SETENV(TZ='PST8PDT')

For UTC, SETENV(TZ='UTC')

For IST, SETENV(TZ='IST-05:30')
```
Ensure that the environment variable TZ is set to match the database time zone as timestamp in MySQL depends upon correct setting of TZ.

See the following links for reference:

https://learn.microsoft.com/en-us/cpp/c-runtime-library/reference/tzset?view=msvc-170#remarks

https://www.gnu.org/software/libc/manual/html_node/TZ-Variable.html
On the source system, issue the following command:
EDIT PARAMS extract_name

Where:

extract_name is either the name of the Extract that you created with the ADD EXTRACT command or the fully qualified name of the parameter file if you defined an alternate location when you created the group.

Enter the parameters in the order shown in the following table, starting a new line for each parameter statement. Some parameters apply only for certain configurations.

Parameter	Description
`EXTRACT group` group is the name of the Extract group that you created with the `ADD EXTRACT` command.	Configures Extract as an online process with checkpoints.
`[, USERIDALIAS alias options ]`	See `USERIDALIAS`, to specify database credentials.
`ENCRYPTTRAIL algorithm`	Encrypts all trails that are specified after this entry.
`SOURCECATALOG`	Specifies a default container in an Oracle multitenant container database or `SEQUENCE` statements. Enables the use of two-part names (`schema.object`) where three-part names otherwise would be required for those databases. You can use multiple instances of this parameter to specify different default containers or catalogs for different sets of `TABLE` or `SEQUENCE` parameters.
`TABLE [container. \| catalog.]owner.object \| schema.object \| library/file \| library/file(member);`	Specifies the fully qualified name of an object or a fully qualified wildcarded specification for multiple objects. If the database is an Oracle multitenant container database, the object name must include the name of the container or catalog unless `SOURCECATALOG` is used. `schema` is the schema name or a wildcarded set of schemas. `object` is the table name, or a wildcarded set of tables. `library` is the IBM i library name or a wildcarded set of libraries. `file` is the IBM i physical file name or a wildcarded set of physical files. `member` is the IBM i physical file member name or a wildcarded set of member names. When using the IBM i native name format (library/file with optional member) the only valid wildcards are a name with at least one valid character followed by a trailing asterisk (``) or `ALL` which matches any name. Note: The member name is optional, and must be provided if the member names are required to be written in the trail as part of the object name. Without member names all members in a physical file be implicitly merged as a single object in the trail. See Specifying Object Names in Oracle GoldenGate Input guidelines for specifying object names in parameter files.
`SCHEMAEXCLUDE` `TABLEEXCLUDE` `EXCLUDEWILDCARDOBJECTSONLY`	Parameters that can be used in conjunction with one another to exclude specific objects from a wildcard specification in the associated `TABLE` statement.

Enter any appropriate optional Extract parameters listed in the Oracle GoldenGate Parameters.
Save and close the parameter file.

The following sample Extract parameter file explains various configuration parameters and options for Extract:

ADD EXTRACT extract_name
{, datasource} 
{, BEGIN start_point} | {position_point} 
[, PARAMS pathname] 
[, REPORT pathname] 
[, DESC 'description']

extract_name is the name of the Extract group. A group name is required.
datasource is required to specify the source of the data to be extracted. Use one of the following:
- TRANLOG specifies the transaction log as the data source. When using this option for Oracle Enterprise Edition, you must issue the DBLOGIN command as the Extract database user (or a user with the same privileges) before using ADD EXTRACT (and also before issuing DELETE EXTRACT to remove an Extract group).
  
  Use the bsds option for Db2 z/OS to specify the Bootstrap Data Set file name of the transaction log.
- INTEGRATED TRANLOG specifies that this Extract will operate in integrated capture mode to receive logical change records (LCR) from an Oracle Database logmining server. This parameter applies only to Oracle databases.
- EXTTRAILSOURCE trail_name to specify the relative or fully qualified name of a local trail.
BEGIN start_point defines an online Extract group by establishing an initial checkpoint and start point for processing. Transactions started before this point are discarded. Use one of the following:
- NOW to begin extracting changes that are timestamped at the point when the ADD EXTRACT command is executed to create the group or, for Extract in integrated mode, from the time the group is registered with the REGISTER EXTRACT command. Extract needs to be registered for Oracle and PostgreSQL databases only.
  
  Timestamp: The format for specifying an exact timestamp as the begin point. Use a begin point that is later than the time at which replication or logging was enabled.
  The following example shows the repositioning of Extract using a specific timestamp:
```
OGG (http://localhost:11000 ggeast as pdb1@east.oracle.com) 95> dblogin useridalias ggma

Successfully logged into database PDB1.
```
```
OGG (http://localhost:11000 ggeast as ggma@ggeast/PDB1) 96> alter extract exte , begin 2024-05-03T03:48:00Z

2024-05-03T03:50:49Z  INFO    OGG-08100  Extract exte I/O position is altered and reposition to older date and time position 2024-05-03 03:48:00.000000 current date and time position 2024-05-03 03:49:04.000000. 
Duplicate transactions are filtered out. Perform output trail ETROLLOVER if duplicate transaction output is desired, or Extract configuration was updated.
2024-05-03T03:50:49Z  INFO    OGG-08100  Extract altered.
```
position_point specifies a specific position within a specific transaction log file at which to start processing. For the specific syntax to use for your database.
PARAMS pathname is required if the parameter file for this group will be stored in a location other than the dirprm sub-directory of the Oracle GoldenGate directory. Specify the fully qualified name. The default location is recommended.
REPORT pathname is required if the process report for this group will be stored in a location other than the dirrpt sub-directory of the Oracle GoldenGate directory. Specify the fully qualified name. The default location is recommended.
DESC 'description' specifies a description of the group.

Additional Parameter Options for Extract

Learn about additional parameters that may be required for your Extract configuration.

Extract uses a database logmining server in the mining database to mine the redo stream of the source database. You can set parameters that are specific to the logmining server by using the TRANLOGOPTIONS parameter with the INTEGRATEDPARAMS option in the Extract parameter file.

Note:

For detailed information and usage guidance for these parameters, see the "DBMS_CAPTURE_ADM" section in Oracle Database PL/SQL Packages and Types Reference.

The following parameters can be set with INTEGRATEDPARAMS:

CAPTURE_IDKEY_OBJECTS: Controls the capture of objects that can be supported by FETCH. The default for Oracle GoldenGate is Y (capture ID key logical change records).
DOWNSTREAM_REAL_TIME_MINE: Controls whether the logmining server operates as a real-time downstream capture process or as an archived-log downstream capture process. The default is N (archived-log mode). Specify this parameter to use real-time capture in a downstream logmining server configuration. For more information on establishing a downstream mining configuration, see Downstream Extract for Oracle GoldenGate Deployment.
INLINE_LOB_OPTIMIZATION: Controls whether LOBs that can be processed inline (such as small LOBs) are included in the LCR directly, rather than sending LOB chunk LCRs. The default for Oracle GoldenGate is Y (Yes).
MAX_SGA_SIZE: Controls the amount of shared memory used by the logmining server. The shared memory is obtained from the streams pool of the SGA. The default is 1 GB.
PARALLELISM: Controls the number of processes used by the logmining server. The default is 2. For Oracle Standard Edition, this must be set to 1.
TRACE_LEVEL: Controls the level of tracing for the Extract logmining server. For use only with guidance from Oracle Support. The default for Oracle GoldenGate is 0 (no tracing).
WRITE_ALERT_LOG: Controls whether the Extract logmining server writes messages to the Oracle alert log. The default for Oracle GoldenGate is Y (Yes).

See Managing Server Resources.