1. Oracle GoldenGate Microservices Documentation
  2. Extract
  3. Add Extracts

Add Extracts

Learn about adding different type of Extract(s), depending on the specific requirement, and database used with Oracle GoldenGate.

Topics:

Parent topic: Extract

Add a Primary Extract

Set up database credentials to create and run Extract using the steps in Add Database Credentials.

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

  1. From the Overview page of the Administration Service, click the + sign next to Extracts.

  2. Choose the type of Extract to create and click Next.

    Note:

    To learn about creating initial load Extract, see About Instantiating with Initial Load Extract. You can also create a Change Data Capture (CDC) Extract for MySQL and SQL Server databases. See Add a Change Data Capture (CDC) Extract.

  3. Provide the required information designated with an asterisk (*). Here's a description of the options in the different sections for the Add Extract screen:

    Option Description Database
    Basic Information Section
    Process Name Name of the Extract process. The name of the Extract process can be up to 8 characters. All databases
    Description Description of the Extract process being created. All databases
    Intent Describes the purpose of creating the Extract. The default option is Unidirectional. Other options are High Availability, Disaster Recovery, N-Way, which are informational only. All databases
    Begin Used to set the beginning location in the redo or transaction log from which the Extract will start to capture data. Available options are Now, Custom Time, CSN or Position in Log, and EOF depending on the supported database. All databases
    Trail Name A two character trail name. All databases
    Trail Subdirectory, Size, Sequence, and Offset You can further configure the trail details. All databases
    Remote

    Enable this option if the Extract trail is remote.

    For Oracle databases, enable this option if the Extract trail is to be written directly to a remote Oracle GoldenGate Classic installation.

    For MySQL, setting this option enables the TRANLOGOPTIONS ALTLOGDEST REMOTE parameter to support a remote Extract, and is not related to trails.

    		Oracle, MySQL
    Registration Information Section
    CSN Commit Sequence Number (CSN) value Oracle
    Share Choose the method to share the LogMiner data dictionary. Options are:
    • Automatic: This option allows the system to choose the method for sharing the dictionary .
    • None: Choosing this option, will not allow the dictionary to be shared.
    • Extract: Choose this option to allow sharing the LogMiner dictionary for specific Extract.
    		 Oracle
    Optimized Enable this option to optimize the Extract registration. Oracle
    Downstream Capture Enable this option to set up a downstream Extract for log mining. Oracle
    Register Only Use this option to just register the Extract and not add the Extract. The registration creates the replication slot when you register the Extract or use the Register Only option. PostgreSQL
    Source Database Credential
    Create new credential If you haven't set up your database login credentials, you can create and save the database login credentials from here. All
    Credential Domain Create a domain for the database. All
    Credential Alias Specify a credential for the database login. All
    User ID Specify a user name for logging into the database. All
    Password, Verify Password Enter the password used to login to the database and reenter the password to verify. All
    Credential Domain Saves the credential user under the specified domain name. Enables the same alias to be used by multiple Oracle GoldenGate installations that use the same credential store. The default domain is Oracle GoldenGate. All databases
    Credential Alias Specifies an alias for the user name. Use this option if you do not want the user name to be in a parameter file or command. If 
    ALIAS
    is not used, the alias defaults to the user name, which then must be used in parameter files and commands where a login is required. You can create multiple entries for a user, each with a different alias, by using the 
    ADD USER
    option with 
    ALIAS
    .    		 All databases
    Downstream Mining
    Mining Credential Domain Domain name of the downstream mining database. Oracle
    Mining Credential Alias Alias for the mining downstream database. Oracle
    No UserID Enable this option if there is no source database connection. Selecting this option enables the ADG fetch options. Oracle
    ADG Fetch Credential Domain Domain name for the ADG fetch database. Oracle
    ADG Fetch Credential Alias Domain alias for the ADG fetch database. Oracle

  4. (Optional) Enter the encryption profile description. If you have not created an encryption profile, then the Local Wallet profile would be selected, by default.

    1. Select the profile name from the list box. You can select the Local Wallet or a custom profile.

    2. Select the encryption profile type from the list box.

    3. Specify the masterkey for the encryption profile. This option doesn't exist with SQL Server.

  5. This is an optional step. Enter the Managed Options while creating all types of Extract processes. See Configure Managed Processes.

    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.

  6. Click Next.

  7. You can edit the parameter file in the text area to list the table details that you are interested in capturing. For example, table source.table1;.

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

  9. 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 drop-down on the Overview page.

    You are returned to the Overview page of the Administration Service. Select the Action list if you want to look at the Extract details such as process information, checkpoint, statistics, parameters, and report.

Topics:

Parent topic: Add Extracts

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 Downstream Database Mining.

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

Parent topic: Add a Primary Extract

Add a Change Data Capture (CDC) Extract

These steps configure a CDC Extract to capture transactional data from a source database.

CDC Extract is available with SQL Server and PostgreSQL databases.

Note:

One Extract per database is generally sufficient, but multiple Extracts are allowed if the replication slots are available.
  1. Using the Admin Client, or REST API client on the source system, create the Extract parameter file. EDIT PARAMS extname

    In this sample, extname is the name of the primary Extract and matches the name of the Extract that was registered with the database in the previous steps.

    To learn about using Oracle GoldenGate Microservices to perform this task, see Add a Primary Extract.

  2. Enter the Extract parameters in the order shown, starting a new line for each parameter statement. Sample basic parameters for Extract for Microservices installations:
    EXTRACT extname
SOURCEDB dsn_name 
USERIDALIAS alias
EXTTRAIL ep
GETTRUNCATES
TABLE schema.*;
    Parameter Description
    EXTRACT extname extname is the name of the Extract and cannot be more than 8 alpha-numeric characters in length. For more information, see extract in Reference for Oracle GoldenGate.
    SOURCEDB dsn_name Specifies the name of the database connection DSN.
    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.
    EXTTRAIL trailname Specifies a two character, local trail to which the primary Extract writes captured data.
    GETTRUNCATES Optional parameter but needed in order to capture truncation operations.

    TABLE schema.object;

    or

    TABLE schema.*;     		Specifies the database object for which to capture data.
    • TABLE specifies a table or a wildcarded set of tables.
    • 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.
    • * is a wildcard for all tables in the schema.

    Terminate the parameter statement with a semi-colon.

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

    Note:

    If the schema of tables to be captured from is the same as the schema in GGSCHEMA of the GLOBALS file, which is not recommended, then you cannot use schema.* in 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.
  4. Save and close the file.
  5. Add the Extract and its associated trail file.

Topics:

Parent topic: Add Extracts

PostgreSQL: Change Data Capture (CDC) Extract

The Oracle GoldenGate Extract process for PostgreSQL receives logical records from the PostgreSQL test_decoding database plugin and writes them in commit order into trail files for downstream consumption by a Replicat.

Parent topic: Add a Change Data Capture (CDC) Extract

SQL Server: Change Data Capture (CDC) Extract

See CDC Capture Method Operational Considerations for operational considerations when adding a CDC Extract for SQL Server.

Parent topic: Add a Change Data Capture (CDC) Extract

Add Online Extract Groups

You can use the MA web interface or the Admin Client command line interface to set up Extract groups in these forms. This section describes the options and parameters used with the ADD EXTRACT command.

Topics:

Parent topic: Add Extracts

Add an Extract Group

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

Where:

  • group 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.

      YYYY-MM-DD HH:MM[:SS[.CCCCCC ]] as 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.

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

Parent topic: Add Online Extract Groups

Create a Parameter File for Online Extraction

Follow these instructions to create a parameter file for an online Extract group. A parameter file is not required for an alias Extract group.

  1. On the source system, issue the following command:

    EDIT PARAMS name

    Where:

    name is either the name of the Extract group 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.

  2. 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.
    [SOURCEDB dsn | container | catalog] [, USERIDALIAS alias options | , USERID user, options] Specifies database connection information. SOURCEDB specifies the source data source name (DSN). See for more information.

    USERID and USERIDALIAS specify database credentials if required.

    RMTHOSTOPTIONS host, MGRPORT port, [, ENCRYPT algorithm KEYNAME key_name] Specifies the target system, the port where Manager is running, and optional encryption of data across TCP/IP. Only required when sending data over IP to a remote system (if ADD RMTTRAIL was used to create the trail). Not required if the trail is on the local system (if ADD EXTTRAIL was used).

    Not valid for a passive Extract group.
    ENCRYPTTRAIL algorithm Encrypts all trails that are specified after this entry.
    LOGALLSUPCOLS Use when using integrated Replicat for an Oracle target, or when using Conflict Detection and Resolution (CDR) support. Writes the before images of scheduling columns to the trail. (Scheduling columns are primary key, unique index, and foreign key columns.) See LOGALLSUPCOLS in Reference for Oracle GoldenGate.
    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.
    SEQUENCE [container.]owner.sequence; Specifies the fully qualified name of an Oracle sequence to capture. Include the container name if the database is a multitenant container database (CDB).
    TABLE [container. | catalog.]owner.object; 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. See Specifying Object Names in Oracle GoldenGate Input for guidelines for specifying object names in parameter files.
    CATALOGEXCLUDE

    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.

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

  4. Save and close the parameter file.

Parent topic: Add Online Extract Groups

Extract Actions

Extract actions include tasks like monitoring details for the Extract, checkpoint details, DDL/DML statistics, cache manager statistics, and other details.

Use the Action button to start or stop the Extract or view and manage its details. When you select the Action, Details option for an Extract, you can perform the following tasks for it.

When you change the status, the list options change accordingly. As status changes, the icons change to indicate the current and final status. The events are added to the Critical Events table. Additionally, progress pop-up notifications appear at the bottom of the page.

Topics:

Parent topic: Add Extracts

Access Extract Details

From the Extract section of the Administration Service Overview page, click Action, Details for the specific Extract to view its details. The following tabs are displayed:

  • Process Information:

    The status of the selected Extract process including the type, credentials, and trail details including trail name, trail subdirectory, trail sequence, and trail size.

  • Checkpoint:

    The checkpoint log name, path, timestamp, sequence, and offset value. You can monitor the input details, such as when starting, at recovery, and the current state. The checkpoint output values display the current checkpoint details.

  • Statistics:

    The active replication maps along with replication statistics based on the process type. You sort the lost to view the entire statistical data, daily, or hourly basis.

  • Cache Manager Statistics:

    Access the global statistics and object pool statistics information for the Extract process from this page.

  • Parameters:

    The parameters configured when the process was added. You can edit the parameters by clicking the pencil icon. Make sure that you apply your changes.

  • Report:

    A detailed report of the process including parameter settings and a log of the transactions. You could copy the report text and save it to a file so that you can share or archive it.

Parent topic: Extract Actions

Start or Stop Extract

From the Administration Service Overview page, click Action, Start/Stop option. If the Extract is in abended state, it displays with a yellow icon. A green icon indicates that the Extract is running and a red icon indicates Extract is in stopped state.

Parent topic: Extract Actions

Delete Extract

To delete an Extract:

  1. Stop the Extract using the Actions, Stop option from the Extract section of the Administration Service Overview page.

  2. Click Delete to remove the Extract.

Note:

The Delete option appears only when the Extract is in Stopped state.

Parent topic: Extract Actions