Add an Extract

Learn about the prerequisites and considerations for adding an Extract.

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

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

Considerations for creating an Extract when previous Extract trail files exist

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:

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:

Click Next after the Extract registration is successful.

A message displays that the Extract is registered successfully.

Click Next.

Register Extract for PostgreSQL and Yugabyte Databases 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.

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

    CONNECT http*s*://*remotehost*:*srvmgrport* DEPLOYMENT

          *deployment_name* AS *deployment_user* PASSWORD *deployment_password*
    OGG (https://*remotehost*:*16000**postgresql_source*)> DBLOGIN USERIDALIAS *alias*

  2. 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:

Limitations

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

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:

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](/pls/topic/lookup?ctx=en/database/goldengate/core/26/coredoc&id=GCLIR-GUID-5E42DB9E-EFDF-40C0-89D7-88413496B8A3) in the Command Line Interface Reference for Oracle GoldenGate for more information.

  1. 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*:*16000**depl_east*)>

  2. Connect to the database using the DBLOGIN command:

    OGG (https://*remotehost*:*port**oracle_source*)> DBLOGIN USERIDALIAS *alias*

  3. Register the Extract. The Extract names cannot be more than 8 alpha-numeric characters.

    OGG (https://*remotehost*:*port**oracle_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.

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.

  1. (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

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

  3. 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](/pls/topic/lookup?ctx=en/database/goldengate/core/26/coredoc&id=GWURF-GUID-530D7773-B66D-4499-A509-EF5BB9CE9B05), 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.

  4. Enter any appropriate optional Extract parameters listed in the Oracle GoldenGate Parameters.

  5. 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']

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:

See Managing Server Resources.