Add an Extract for MySQL

Extract is a process that runs agains the source data source connection and extracts, or captures, data. Learn to add an Extract for MySQL Database Server, HeatWave, HeatWave on Azure, HeatWave on AWS, Amazon Aurora MySQL, Amazon RDS for MySQL, Amazon RDS for MariaDB, Azure Database for MySQL, and Google Cloud SQL for MySQL technologies.

Related Topics

Before you begin

Before adding and running an Extract, ensure that you prepare and configure the system for Oracle GoldenGate.

Add an Extract

To add an extract for MySQL database:
  1. In the OCI GoldenGate deployment console, ensure that you're on the Administration Service Overview page, and then click Add Extract (plus icon).
  2. On the Add Extract page, for Extract Type, select one of the following, and then click Next.
    • Change Data Capture
    • Initial Load
  3. On the Extract Options page, under Basic Information, complete the fields as needed:
    1. For Process Name, enter a name for the Extract process, up to 8 characters.
    2. (Optional) For Description, enter a brief description to help you distinguish this process from others.
    3. For Intent, select the option that best describes the purpose of this Extract:
      • Unidirectional (default)
      • Disaster Recovery
      • N-Way
    4. Enable Remote only if capturing data from a MySQL database that doesn't use global transaction identifiers (GTIDs).
    5. For Credential Domain, select Oracle GoldenGate.
    6. For Credential Alias, select your source MySQL connection.
    7. For Trail Name, enter a two character name for the Trail file.
    8. (Optional) For Trail Subdirectory, set a custom location for the generated Trail file.
    9. (Optional) For Trail Size, set the max size for the generated trail file.
  4. (Optional) Under Encryption Profile, enter the encryption profile description. The Local Wallet profile is selected by default if an encryption profile wasn't created.
    1. Select the profile name from the dropdown. You can select the Local Wallet or a custom profile.
    2. Select the encryption profile from the dropdown.
    3. Specify the masterkey for the encryption profile.
  5. (Optional) Under Managed Options, you can configure the following:
    • Profile Name
    • Auto Start
    • Auto Restart

    Note:

    Adding a profile and configuring Auto Start and Auto Restart options enables your deployment to restart automatically after a network disruption. See Configure managed processes for more information.
  6. Click Next.
  7. On the Extract Parameters page, you can edit the parameter file in the text area to list the table details to capture. For example:
    table source.table1;

    Note:

    GoldenGate uses Greenwich Mean Time (GMT) by default. Use SETENV to override the default setting for the Extract. This change applies only to the Extract process and not the deployment.
    setenv (TZ="US/Eastern")
setenv (TZ="GMT+5")

    Learn more about SETENV.

  8. Select Register Extract in the background to register the Extract in the background asynchronously.
  9. Click Create and Run to create and start the Extract. If you click Create, then you can manually start Extract later from the Administration Service Overview page.
You're returned to the Administration Service Overview page, where you can view the status of the Extract process. Select Details from the Extract Action menu to view process information, checkpoint, statistics, paramters, and reports.

Add an Extract (23ai)

Before you begin
  • Oracle GoldenGate relies on the redo logs to capture the data that it needs to replicate source transactions. Enable supplemental logging on the source database for unidirectional replication, or both source and target for bidirectional replication: 
    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
  • Ensure that you enable table-level supplemental by adding TRANDATA. If schema-level supplemental logging is already enabled, you can skip these steps.
    1. In the OCI GoldenGate deployment console, expand DB Connections, and select your source database.
    2. Next to TRANDATA Information, click Add TRANDATA (plus icon).
    3. For Schema Name, enter the database schema name, and then click Submit.
  • Prepare your database for Oracle GoldenGate.
To add an Extract in Oracle GoldenGate 23ai (23.4):
  1. In the OCI GoldenGate deployment console, on the Administration Service Home page, click Add Extract.
  2. In the Add Extract panel, on the Extract Information page, complete the following fields as needed, and then click Next:
    • Select an Extract Type:
      • Integrated Extract
      • Initial Load Extract
    • Enable Downstream Capture (optional), if applicable
    • Enter a Process Name, no more than 8 characters long.
    • Enter a Description (optional) to help you distinguish this process from others.
  3. On the Extract Options page, complete the following fields as needed, and then click Next:
    • Select the Source Credentials:
      • Domain
      • Alias
    • Registration options:
      • Enter the Commit Sequence Number (CSN).
      • For Share, choose a method to share the LogMiner data directory:
        • Automatic: allows the system to choose the method for sharing.
        • None: doesn't share the dictionary.
        • Extract Name: shares the LogMiner dictionary for this Extract.
      • Enable Optimized to optimize Extract registration.
    • Extract Trail:
      • Enter a Name for the Extract process.
      • Enter a Subdirectory name to set a custom location for the generated Trail file.
      • Enter Trail Sequence to set the starting number for Trail files.
      • Enter a Trail Size to set the max size for the generated trail file.
      • Select an Encryption Profile. The Local Wallet profile is selected by default if an encryption profile wasn't created.
      • Select an Encryption Algorithm:
        • NONE
        • AES256
        • AES192
        • AES128
  4. The Downstream Capture page applies only if Downstream Capture is selected on the Extract Information page.
  5. On the Managed Options page, complete the following optional fields as needed, and then click Next:
    • Profile Name
    • Critical to deployment health
    • Auto Start
    • Auto Restart

    Note:

    Adding a profile and configuring Auto Start and Auto Restart options enables your deployment to restart automatically after a network disruption. See Configure managed processes to learn more.

  6. On the Parameter File page, you can edit the parameter file in the text area to list the table details to capture. For example:
    table source.table1;

    Note:

    GoldenGate uses Greenwich Mean Time (GMT) by default. Use SETENV to override the default setting for the Extract. This change applies only to the Extract process and not the deployment.
    setenv (TZ="US/Eastern")
setenv (TZ="GMT+5")
  7. Click Create and Run to create and start the Extract. If you click Create, then you can manually start the Extract later from the Administration Service Home page.

Learn more

Interested in learning more about the Extract process and capturing data? See:

Known Issues

Remote change data capture Extracts fail for GTID enabled databases

When you create a Change Data Capture Extract process with the Remote option enabled for a MySQL database that uses global transaction identifiers (GTIDs), the Extract process fails and the following error is reported:
ERROR   OGG-25192  Trail file '<trail name>' is remote. Only local trail allowed for this extract.

Workaround: On the Parameter file screen of the Change Data Capture Extract, remove the line, TRANLOGOPTIONS ALTLOGDEST REMOTE.

For more information, see Using Oracle GoldenGate for MySQL.