Add an Extract

Extract Information Screen

After logging into the Microservices home page, click the + sign in the Create column to open the Add Extracts wizard.

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 Instantiate.

Click Next.

Register Extract Screen - PostgreSQL and Yugabyte Databases

Note:

Oracle GoldenGate Extract for Oracle, different PostgreSQL flavors, and Yugabyte Database need to be registered. However, the configuration options to register the Extract are displayed on different screens while adding the Extract.

Note:

Also see the Guidelines and Limitations for PGOUTPUT Plugin Type.

On the Register Extract screen, configure the following options and click Register:

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.
(All PostgreSQL flavors) Plugin Type: Select the logical decoding plugin type pgoutput. The pgoutput is default for all PostgreSQL flavors.

For details about failover replication slot configuration for PostgreSQL 17 and higher, see Failover Support for Oracle GoldenGate with PostgreSQL 17 and higher.
(Yugabyte Database only) Plugin Type: Select the logical decoding plugin type yboutput. The YBOUTPUT is default for Yugabyte Database.

Click Next after the Extract registration is successful.

A message displays that the Extract is registered successfully.

Click Next.

Register Extract for PostgreSQL from the Admin Client

An Extract for PostgreSQL must be registered with the database and be granted a reserved replication slot. Replication slots are allocated through the database configuration setting max_replication_slots and can be configured as discussed in Database Configuration.

Follow these instructions to register an Extract. Extract registration must be done prior to creating an Extract. See REGISTER EXTRACT in the Command Line Interface Reference for Oracle GoldenGate for more information.

Connect to the deployment, then connect to the credential alias for the source database.

CONNECT https://remotehost:srvmgrport DEPLOYMENT
          deployment_name AS deployment_user PASSWORD deployment_password

OGG (https://remotehost:16000postgresql_source)> DBLOGIN USERIDALIAS alias

Register the Extract, which internally creates a replication slot for the Extract. Extract names cannot be more than 8 alpha-numeric characters.
```
REGISTER EXTRACT extname
```
To explicitly register Extract with the pgoutput plugin, you must specify the pgoutput plugin type:
```
REGISTER EXTRACT extname PGPLUGINTYPE pgoutput;
```

You can also register an Extract from the Oracle GoldenGate MA web interface. See Add an Extract.

Guidelines and Limitations for PGOUTPUT Plugin Type

Guidelines when configuring PGOUTPUT plugin type

Follow these guidelines when setting the plugin type for PostgreSQL:

The publication(s) must be created before registering the Extract process.
Publication names must not contain special characters: comma (,), single quote ('), and double quotes (").
Multiple publication names can be specified as a comma-separated list within publication_names. For example:
```
TRANLOGOPTIONS STREAMINGOPTIONS (publication_names="my*123,my_publication2,123 pub,my_publication";)
```
For details, see TRANLOGOPTIONS STREAMINGOPTIONS parameter.
The final publication name in the list must end with a semicolon (;).

Limitations

The following restrictions apply while selecting a plugin type for PostgreSQL:

Timezone (TZ) environment variable: The WAL sender process always transmits commit timestamps in UTC.If the OGG session timezone differs from UTC, discrepancies may occur when positioning by timestamp in the CDC Extract process. To prevent this issue, users must set the environment variable TZ=UTC at the deployment layer. This limitation applies specifically to the pgoutput plugin.
Multiple truncates: Multiple truncate operations are only supported in pgoutput plugin. The test_decoding plugin type does not allow multiple truncates.
Tables with unique indexes but no Primary Key: If a table does not have primary keys but contains unique indexes, then the replica identity must use indexes or it must be set to FULL.
Publication Deletion Impact on CDC Extract: If a publication is deleted while the CDC Extract is still running, there is a risk of replication slot corruption, causing it to stop functioning. In such cases, the impacted replication slot is not usable, and recreating the same publication will not resolve the issue. The recommended solution is to create a new publication and associate it with a new replication slot to restore CDC functionality.
Tables eligible for publication: Only persistent base tables and partitioned tables can be included in a publication. The following object types cannot be part of a publication:
- Temporary tables
- Unlogged tables
- Foreign tables
- Materialized views
- Regular views
Bi-Directional Replication Support with pgoutput Plugin: When using the pgoutput plugin for bidirectional replication, the checkpoint table must be created and included in the publication list before starting the CDC Extract process. Additionally, the replication slot should be created only after the checkpoint table has been created and added to the publication.
Column-level publication limitation: Specifying a subset of columns for a table in a publication is not supported for CDC replication. You must include the entire table in the publication. Attempting to add only partial columns may lead to errors or data discrepancies in the CDC stream.

Extract Options Screen

Note:

This screen provides options to register the Extract for Oracle database. If you are adding an Extract for PostgreSQL, then the Extract registration is completed on the Register Extract Screen.

On the Extract Options screen configure the following settings:

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 Apply the OKV 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.
- (Oracle only)Registration Options: Specify the following options to register an Extract for Oracle database.
  
  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.

Register Extract for Oracle from the Admin Client

Follow these instructions to register an Extract. Extract registration must be done prior to creating an Extract.

Ensure that you are connected with the database using the DBLOGIN command.

See REGISTER EXTRACT in the Command Line Interface Reference for Oracle GoldenGate for more information.

Using the Admin Client, connect to the deployment, then connect to the credential alias for the source database.
```
OGG> CONNECT https://remotehost:srvmgrport DEPLOYMENT deployment_name AS deployment_user PASSWORD deployment_password
```
When running the CONNECT command, the command prompt changes from "not connected" to "https://servername:port deployment_name", as shown in the following example:
```
OGG (https://pdbeast.vcn.oracle.com:16000depl_east)>
```

Connect to the database using the DBLOGIN command:

OGG (https://remotehost:portoracle_source)> DBLOGIN USERIDALIAS alias

Register the Extract. The Extract names cannot be more than 8 alpha-numeric characters.
```
OGG (https://remotehost:portoracle_source)> REGISTER EXTRACT extname DATABASE
```

You can also register an Extract in the background while creating an Extract from the Oracle GoldenGate MA web interface. See Add an Extract for details.

Downstream Capture Screen

Valid for Oracle only.

If you selected the Downstream Capture on the Extract Information screen, then 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.

Managed Options Screen

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.

Extract Parameter File Screen

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.*;

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.

Create a Parameter File for Extract

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

(MySQL only) When running Oracle GoldenGate for MySQL on a Windows server and on a Linux server (for OGG version lower than 23.10.x) which is on a remote host from the database server, ensure that their time zones are the same and if not, use the SETENV(TZ) parameter within the Extract and set it to the time zone of the database server.

For TZ format and more details, refer to the SETENV parameter in the Parameters and Functions Reference Guide.

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.