6 Configuring Extract

This chapter contains instructions for configuring the Oracle GoldenGate capture process to capture transaction data.

When Extract is running from a remote system, Oracle GoldenGate automatically enables cross endian interoperability. This implies that if the endian value where Extract is running is different from the endian value where the Oracle database is running, then the cross endian support is automatically enabled. For cross endian Extract to work, the compatibility parameter of the source database must be 11.2.0.4 or higher.

Topics:

6.1 Prerequisites for Configuring Extract

You must adhere to the guidelines provided in this topic before configuring an Extract.

The guidelines for configuring an Extract in integrated mode are:
  1. Preparing the Database for Oracle GoldenGate.

  2. Establishing Oracle GoldenGate Credentials.

  3. Choosing Different Replicat Modes with Extract.

  4. For Microservices Architecture, the deployment needs to be up and running for the Extract process. To know more about starting a deployment, see How to Start or Stop Deployments and Services.

    For Classic Architecture, Manager must be started prior to adding or starting Extract.

  5. Additionally, review the guidelines in Administering Oracle GoldenGate.

6.2 What to Expect from these Instructions

These instructions show you how to configure Extract parameter (configuration) file for the primary Extract, which captures transaction data from the data source, and for a data-pump Extract, which propagates captured data that is stored locally in a trail from the source system to the target system.

Your business requirements probably will require a more complex topology, but this procedure forms a basis for the rest of your configuration steps.

By performing these steps, you can:

  • get the basic configuration files established.

  • build upon them later by adding more parameters as you make decisions about features or requirements that apply to your environment.

  • use copies of them to make the creation of additional parameter files faster than starting from scratch.

6.3 Configuring Primary Extract

The mining database from which the primary Extract captures log change records from the logmining server, can be either local or downstream from the source database.

These steps configure the primary Extract to capture transaction data from either location. See Configuring a Downstream Mining Database and Example Downstream Mining Configuration for more information about capturing from a downstream mining database.

Note:

One Extract group is generally sufficient to capture from a single database or multiple pluggable databases within a multitenant container database. See Configuring Oracle GoldenGate in a Multitenant Container Database. You can also choose per-PDB capture mode when working in an Autonomous Data Warehouse (ADW) or cloud environment. See Configuring Extract to Capture from an Autonomous Database.

  1. In GGSCI, Admin Client, or REST API client on the source system, create the Extract parameter file.
    EDIT PARAMS name

    Where: name is the name of the primary Extract.

    Note:

    To learn about using Oracle GoldenGate microservices to perform this task, see How to Add Extracts.
  2. Enter the Extract parameters in the order shown, starting a new line for each parameter statement. Examples are shown for a regular database, a multitenant container database, and downstream deployments for both non-CDB and multitenant databases. The difference between the two is whether you must use two-part or three-part object names in the TABLE and SEQUENCE specifications. See the basic parameters for primary Extract for more information and parameter descriptions.

    Basic parameters for Extract mining a non-mulitenant database

    EXTRACT financep
    USERIDALIAS c##_alias
    DDL INCLUDE MAPPED
    EXTTRAIL /ggs/dirdat/lt
    SEQUENCE hr.employees_seq;
    TABLE hr.*;

    Basic parameters for Extract capturing from a multitenant database

    EXTRACT financep
    USERIDALIAS c##_alias
    DDL INCLUDE MAPPED 
    EXTTRAIL /ggs/dirdat/lt
    TABLE test.ogg.tab1;
    SEQUENCE hr.employees_seq;
    TABLE hr.*;
    TABLE sales.*;
    TABLE acct.*;
    

    Basic parameters for Extract where the mining database is a downstream database and is a non-CDB database

    EXTRACT financep
    USERIDALIAS c##_alias 
    TRANLOGOPTIONS MININGUSERALIAS c##_alias 
    TRANLOGOPTIONS INTEGRATEDPARAMS (DOWNSTREAM_REAL_TIME_MINE Y)
    LOGALLSUPCOLS
    UPDATERECORDFORMAT COMPACT
    DDL INCLUDE MAPPED
    ENCRYPTTRAIL AES192
    EXTTRAIL /ggs/dirdat/lt
    SEQUENCE hr.employees_seq;
    TABLE hr.*;
    

    Basic parameters for the primary Extract where the mining database is a downstream database and is a multitenant container database

    EXTRACT financep
    USERIDALIAS tiger1 
    TRANLOGOPTIONS MININGUSERALIAS tiger2 
    TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 164, & 
       DOWNSTREAM_REAL_TIME_MINE y)
    LOGALLSUPCOLS
    UPDATERECORDFORMAT COMPACT
    DDL INCLUDE MAPPED SOURCECATALOG pdb1 INCLUDE MAPPED SOURCECATALOG pdb2
    ENCRYPTTRAIL AES192EXTTRAIL /ggs/dirdat/lt
    TABLE test.ogg.tab1;
    SOURCECATALOG pdb1
    SEQUENCE hr.employees_seq;
    TABLE hr.*;
    SOURCECATALOG pdb2
    TABLE sales.*;
    TABLE acct.*;
    
    Parameter Description
    EXTRACT group

    group is the name of the Extract group. For more information, see Reference for Oracle GoldenGate.

    USERIDALIAS alias

    Specifies the alias of the database login credential of the user that is assigned to Extract. This credential must exist in the Oracle GoldenGate credential store.

    LOGALLSUPCOLS

    Writes all supplementally logged columns to the trail, including those required for conflict detection and resolution and the scheduling columns required to support integrated Replicat. (Scheduling columns are primary key, unique index, and foreign key columns.) You configure the database to log these columns with GGSCI commands. See Establishing Oracle GoldenGate Credentials.

    UPDATERECORDFORMAT COMPACT

    Combines the before and after images of an UPDATE operation into a single record in the trail. This parameter is valid for Oracle Databases version 12c and later to support Replicat in integrated mode. Although not a required parameter, UPDATERECORDFORMAT COMPACT is a best practice and significantly improves Replicat performance.

    TRANLOGOPTIONS MININGUSERALIAS alias

    Specifies connection information for the logmining server at the downstream mining database, if being used.

    MININGUSERALIAS specifies the alias of the Extract user for the downstream mining database. This is the user that you created in Configuring a Downstream Mining Database. The credential for this user must be stored in the Oracle GoldenGate credential store.

    Use MININGUSERALIAS only if the database logmining server is in a different database from the source database; otherwise just use USERIDALIAS. When using MININGUSERALIAS, use it in addition to USERIDALIAS, because credentials are required for both databases.

    TRANLOGOPTIONS [INTEGRATEDPARAMS (parameter[, ...])]

    Optional, passes parameters to the Oracle Database that contains the database logmining server. Use only to change logmining server parameters from their default settings. See Additional Parameter Options for Extract.

    TRANLOGOPTIONS CHECKPOINTRETENTIONTIME days

    Optional, controls the number of days that Extract retains checkpoints before purging them automatically. Partial days can be specified using decimal values. For example, 8.25 specifies 8 days and 6 hours. For more information, see Reference for Oracle GoldenGate.

    DDL include_clause

    Required if replicating DDL operations. See Configuring DDL Support for more information.

    ENCRYPTTRAIL algorithm

    Encrypts the local trail.

    EXTTRAIL pathname

    Specifies the path name of the local trail to which the primary Extract writes captured data.

    SOURCECATALOG container

    Use this parameter when the source database is a multitenant container database. Specifies the name of a pluggable database that is to be used as the default container for all subsequent TABLE and SEQUENCE parameters that contain two-part names. This parameter enables you to use two-part object names (schema.object) rather than three-part names (container.schema.object). It remains in effect until another SOURCECATALOG parameter is encountered or a full three-part TABLE or SEQUENCE specification is encountered.

    {TABLE | SEQUENCE} [container.]schema.object;
    

    Specifies the database object for which to capture data.

    • TABLE specifies a table or a wildcarded set of tables.

    • SEQUENCE specifies a sequence or a wildcarded set of sequences.

    • container is the name of the pluggable database (PDB) that contains the object, if this database is a multitenant container database. The container part of the name is not required if this Extract group will only process data from one PDB and the default PDB is specified with the SOURCECATALOG parameter.

    • schema is the schema name or a wildcarded set of schemas.

    • object is the table or sequence name, or a wildcarded set of those objects.

    Terminate the parameter statement with a semi-colon.

    To exclude a name from a wildcard specification, use the CATALOGEXCLUDE, SCHEMAEXCLUDE, TABLEEXCLUDE, and EXCLUDEWILDCARDOBJECTSONLY parameters as appropriate.

    MAPINVISIBLECOLUMNS

    Controls whether or not Replicat includes invisible columns in Oracle target tables for default column mapping. Configure the invisible columns in your column mapping using SQL to explicitly specify column names. For example:

    CREATE TABLE tab1 (id NUMBER, data CLOB INVISIBLE);
       INSERT INTO tab1 VALUES (1, 'a');ERROR: ORA-913
       INSERT INTO tab1 (id, data) VALUES (1, 'a'); OK
    

    You can change the column visibility using ALTER TABLE. The invisible column can be part of an index, including primary key and unique index.

  3. Enter any optional Extract parameters that are recommended for your configuration. You can edit this file at any point before starting processing by using the EDIT PARAMS command in GGSCI.
  4. Save and close the file.

6.4 Setting up the Automatic Extract Mode

The automatic Extract mode captures changes for all the tables that are enabled for logical replication.

To set up an auto Extract, you must have Oracle Database 21c and higher. A table is enabled for logical replication or auto capture when:
  • It has sufficient ID or scheduling-key supplemental log data at table or schema level.

  • It has primary key (PK), unique identifier (UI), foreign key (FK) supplemental log data, and ALLKEYS supplemental log data. ALLKEYS is required in addition to PK, UI and FK because it logs all unique keys at the schema-wide supplemental logging level in the absence of a primary key.

Benefits of Using the Auto Extract Mode

  • Easy to configure captured table set

  • When captured table set changes, you don't need to update the TABLE/TABLEEXCLUDE parameter, or stop and restart Extract.

Enabling Auto Capture

See TRANLOGOPTIONS INTEGRATEDPARAMS for syntax and usage.

Use the following DDLs to enable auto capture at the table level:

CREATE/ATLER TABLE table_name ENABLE LOGICAL REPLICATION ALLKEYS

or

CREATE/ALTER TABLE table_name ENABLE LOGICAL REPLICATION ALLOWNONVALIDATEDKEYS

6.5 Configuring the Data Pump Extract

A data pump can perform data filtering, mapping, and conversion, or it can be configured in pass-through mode, where data is passively transferred as-is, without manipulation.

These steps configure the data pump that reads the local trail and sends the data across the network to a remote trail. The data pump is optional, but recommended.

Note:

If you want to perform this task using microservices, see How to Add a Path in Using the Oracle GoldenGate Microservices Architecture.
  1. In GGSCI on the source system, create the data-pump parameter file.
    EDIT PARAMS name
    

    Where: name is the name of the data pump Extract.

  2. Enter the data pump parameters in the order shown, starting a new line for each parameter statement. Your input variables will be different.

    Basic parameters for the data pump Extract group using two-part object names from a non-CDB database:

    EXTRACT extpump
    USERIDALIAS tiger1
    RMTHOST fin1, MGRPORT 7809 ENCRYPT AES192, KEYNAME securekey2
    RMTTRAIL /ggs/dirdat/rt
    SEQUENCE hr.employees_seq;
    TABLE hr.*;
    

    Basic parameters for the data pump Extract group using three-part object names from a trail that contains multitenant database data (including a pluggable database):

    EXTRACT extpump
    USERIDALIAS tiger1
    RMTHOST fin1, MGRPORT 7809 ENCRYPT AES192, KEYNAME securekey2
    RMTTRAIL /ggs/dirdat/rt
    TABLE test.ogg.tab1;
    SOURCECATALOG pdb1
    SEQUENCE hr.employees_seq;
    TABLE hr.*;
    SOURCECATALOG pdb2
    TABLE sales.*;
    TABLE acct.*;
    
    Parameter Description
    EXTRACT group

    group is the name of the data pump Extract. For more information, see Reference for Oracle GoldenGate.

    USERIDALIAS alias

    Specifies the alias of the database login credential of the user that is assigned to Extract. This credential must exist in the Oracle GoldenGate credential store.

    RMTHOST hostname,
    MGRPORT portnumber,
    [, ENCRYPT algorithm
    KEYNAME keyname]
    • RMTHOST specifies the name or IP address of the target system.

    • MGRPORT specifies the port number where Manager is running on the target.

    • ENCRYPT specifies optional encryption of data across TCP/IP.

    RMTTRAIL pathname

    Specifies the path name of the remote trail.

    SOURCECATALOG container

    Use this parameter when the source database is a multitenant container database. Specifies the name of a pluggable database that is to be used as the default container for all subsequent TABLE and SEQUENCE parameters that contain two-part names. This parameter enables you to use two-part object names (schema.object) rather than three-part names (container.schema.object). It remains in effect until another SOURCECATALOG parameter is encountered or a full three-part TABLE or SEQUENCE specification is encountered. Use this parameter when the source database is a multitenant container database.

    {TABLE | SEQUENCE} [container.]schema.object;

    Specifies a table or sequence, or multiple objects specified with a wildcard. In most cases, this listing will be the same as that in the primary Extract parameter file.

    • TABLE specifies a table or a wildcarded set of tables.

    • SEQUENCE specifies a sequence or a wildcarded set of sequences.

    • container is the name of the root container or pluggable database that contains the table or sequence, if this source database is a multitenant container database. See the SOURCECATALOG description in this table.

    • schema is the schema name or a wildcarded set of schemas.

    • object is the name of a table or sequence, or a wildcarded set of those objects.

    Terminate this parameter statement with a semi-colon.

    To exclude tables or sequences from a wildcard specification, use the TABLEEXCLUDE or SEQUENCEEXCLUDE parameter after the TABLE statement.

  3. Enter any optional Extract parameters that are recommended for your configuration. You can edit this file at any point before starting processing by using the EDIT PARAMS command in GGSCI.
  4. Save and close the file.

6.6 Next Steps

A parameter file is a plain text file that is read by an associated Oracle GoldenGate process. Oracle GoldenGate uses two types of parameter files: a GLOBALS file and runtime parameter files.