> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Migrate and Replicate Data with Oracle GoldenGate

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

[Oracle GoldenGate](https://www.oracle.com/integration/goldengate) is a managed service that can collect, replicate, and manage transactional data between databases. GoldenGate can use CockroachDB as a sink by leveraging CockroachDB's PostgreSQL compatibility. This page describes how to:

* [Configure Oracle GoldenGate for PostgreSQL](#configure-oracle-goldengate-for-postgresql).
* [Set up Extract to capture data from a source database](#set-up-extract-to-capture-data-from-a-source-database).
* [Set up Replicat to deliver data to CockroachDB](#set-up-replicat-to-deliver-data-to-cockroachdb).
* [Perform bulk replication](#perform-bulk-replication).

As of this writing, GoldenGate supports the following database [sources](https://docs.oracle.com/en/middleware/goldengate/core/21.3/coredoc/configure-databases):

* MySQL
* Oracle
* PostgreSQL
* SQL Server
* Db2 z/OS

This page describes GoldenGate at a high level and assumes some familiarity with this tool. For detailed information, refer to the [Oracle GoldenGate documentation](https://docs.oracle.com/en/middleware/goldengate/core/21.3).

For limitations on what PostgreSQL features are supported, refer to Oracle's [Details of Supported PostgreSQL Data Types](https://docs.oracle.com/en/middleware/goldengate/core/19.1/gghdb/understanding-whats-supported-postgresql).

## Before you begin

* Oracle GoldenGate runs as a process separate from CockroachDB and the source database. Ensure your host meets the [minimum requirements](https://docs.oracle.com/en/middleware/goldengate/core/21.3/installing/overview).
* Ensure that your Oracle client or client libraries are compatible with the host Oracle version.
* Ensure that you have installed both required Oracle GoldenGate components:
  1. [Oracle GoldenGate for Oracle](https://www.oracle.com/middleware/technologies/goldengate-downloads) is required to pull source data and route it to proper [trail files](https://docs.oracle.com/goldengate/c1230/gg-winux/GGCON/processes-and-terminology).
  2. [Oracle GoldenGate for PostgreSQL](https://www.oracle.com/middleware/technologies/goldengate-downloads) is required to pull data from the trail files to CockroachDB.
* For CockroachDB clusters running v22.1 and earlier, enable the following <InternalLink path="cluster-settings">cluster settings</InternalLink>:

  ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  SET CLUSTER SETTING sql.defaults.datestyle.enabled = true;
  SET CLUSTER SETTING sql.defaults.intervalstyle.enabled = true;
  ```

  For CockroachDB v22.2 and later, these settings are enabled by default.
* Ensure [libpg](https://www.postgresql.org/download/linux/redhat) is available on the Oracle GoldenGate host.
* Ensure you have a secure, <InternalLink version="cockroachcloud" path="network-authorization">publicly available</InternalLink> CockroachDB cluster running the latest **v24.3** <InternalLink version="releases" path="v24.3">production release</InternalLink>, and have created a <InternalLink path="security-reference/authorization#sql-users">SQL user</InternalLink>.

## Configure Oracle GoldenGate for PostgreSQL

This section describes how to configure Oracle GoldenGate for PostgreSQL to work with CockroachDB. Oracle GoldenGate for PostgreSQL is the process that pulls data from [trail files](https://docs.oracle.com/goldengate/c1230/gg-winux/GGCON/processes-and-terminology) over to CockroachDB. This is a separate installation from Oracle GoldenGate for Oracle, which will be used to pull source data and route it to proper [trail files](https://docs.oracle.com/goldengate/c1230/gg-winux/GGCON/processes-and-terminology). For more information, refer to the [Oracle GoldenGate for PostgreSQL documentation](https://docs.oracle.com/en/middleware/goldengate/core/19.1/gghdb/preparing-database-oracle-goldengate-postgresql). The following example uses a [CockroachDB Standard cluster](https://cockroachlabs.com/docs/cockroachcloud/create-your-cluster).

1. Edit the `ODBC.ini` file to set up the ODBC data sources and configuration:

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   # No changes should be needed for CRDBLOCAL
   [ODBC Data Sources]
   PG_src=DataDirect 7.1 PostgreSQL Wire Protocol
   PG_tgt=DataDirect 7.1 PostgreSQL Wire Protocol
   CRDB=DataDirect 7.1 PostgreSQL Wire Protocol

   [ODBC]
   IANAAppCodePage=4
   # The following path is your Oracle GoldenGate for PostgreSQL installation directory
   InstallDir=/u01/ggs-pg
   ```
2. Ensure that all the PostgreSQL libraries are installed and referenced in `LD_LIBRARY_PATH`.

   The path should at least include `/usr/pgsql-13/lib` and `/u01/ggs-pg/lib`:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   # The path is a concatenation of your PostgreSQL libraries and GoldenGate installation directory
   export LD_LIBRARY_PATH=/usr/pgsql-13/lib:/u01/ggs-pg/lib
   ```
3. Set up the `ODBC.ini` file for the Oracle GoldenGate host:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   # This is needed so that OGG knows where to look for connection details for the database
   export ODBCINI=/etc/odbc.ini
   ```

   To make this change permanent, you must also add the command to your shell's configuration file, such as `~/.zshrc`.
4. In the `ODBC.ini` file, set up the CockroachDB Standard parameters:
   * Replace the login details with your own. Be sure to prefix the database name with <code>{'{hostname}'}</code>.
   * Make sure your CockroachDB Standard cluster's [root CA certificate](https://cockroachlabs.com/docs/cockroachcloud/connect-to-your-cluster#connect-to-your-cluster) is in the `TrustStore` path.

     ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     [CRDBSTANDARD]
     # The following driver will always point to your Oracle GoldenGate for PostgreSQL installation
     Driver=/u01/ggs-pg/lib/GGpsql25.so
     Description=DataDirect 7.1 PostgreSQL Wire Protocol
     Database={hostname}.{database}
     HostName={host}
     PortNumber=26257
     LogonID={your sql user}
     Password={your sql user's pass}
     EncryptionMethod=1
     ValidateServerCertificate=1
     TrustStore=/root/.postgresql/root.crt
     ```
5. Log in to the database:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   cd /u01/ggs-pg

   ./ggsci

   # To log into the local database
   DBLOGIN SOURCEDB CRDBLOCAL

   # To log into the CockroachDB Standard database and then enter your password
   DBLOGIN SOURCEDB CRDBSTANDARD
   ```

## Set up Extract to capture data from a source database

[Extract](https://docs.oracle.com/goldengate/c1230/gg-winux/GGCON/processes-and-terminology) is Oracle GoldenGate's data capture mechanism that is configured to run against the source database.

<Tip>
  Complete the steps in this section on a machine and in a directory where Oracle GoldenGate for Oracle is installed.
</Tip>

1. In the GGSCI terminal, create and open the `epos` parameter file for the Oracle source:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   cd /u01/ggs # Otherwise known as $OGG_HOME
   ./ggsci

   edit param epos
   view param epos
   ```
2. Edit the parameters that configure Extract to send data to the trail file at `./dirdat/ab` on the remote host:

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   EXTRACT epos
   USERIDALIAS gg_source
   RMTHOST {host-name}, MGRPORT {port}
   RMTTRAIL ./dirdat/ab
   TABLE OGGADM1.testtable;
   ```
3. In the GGSCI terminal, run the following and start the Extract service.

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   add extract epos, tranlog, begin now
   add rmttrail ./dirdat/ab, extract epos megabytes 10

   # This is crucial in order to address the logmining not found error
   register EXTRACT epos, DATABASE

   # Starts service
   start epos
   ```
4. Check the status of the Extract by creating a test table `OGGADM1.testtable` in Oracle and adding a row:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   CREATE TABLE OGGADM1.testtable (col1 number, col2 varchar2(20));
   ALTER TABLE OGGADM1.testtable ADD PRIMARY KEY (col1);
   INSERT INTO OGGADM1.testtable (col1, col2) VALUES (11, 'Example data');
   COMMIT;
   ```
5. In GGSCI, in another terminal, check that Extract is working correctly:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   stats EXTRACT epos
   ```

   You should see an output similar to the following:

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   Extracting from OGGADM1.TESTTABLE to OGGADM1.TESTTABLE:
   *** Total statistics since 2023-06-09 19:06:44 ***
       Total inserts                              1.00
       Total updates                              0.00
       Total deletes                              0.00
       Total upserts                              0.00
       Total discards                             0.00
       Total operations                           1.00
   *** Daily statistics since 2023-06-09 19:06:44 ***
       Total inserts                              1.00
       Total updates                              0.00
       Total deletes                              0.00
       Total upserts                              0.00
       Total discards                             0.00
       Total operations                           1.00
   *** Hourly statistics since 2023-06-09 19:06:44 ***
       Total inserts                              1.00
       Total updates                              0.00
       Total deletes                              0.00
       Total upserts                              0.00
       Total discards                             0.00
       Total operations                           1.00
   *** Latest statistics since 2023-06-09 19:06:44 ***
       Total inserts                              1.00
       Total updates                              0.00
       Total deletes                              0.00
       Total upserts                              0.00
       Total discards                             0.00
       Total operations                           1.00
   End of statistics.
   ```

## Set up Replicat to deliver data to CockroachDB

[Replicat](https://docs.oracle.com/goldengate/c1230/gg-winux/GGCON/processes-and-terminology) is an Oracle process that reads [trail files](https://docs.oracle.com/goldengate/c1230/gg-winux/GGCON/processes-and-terminology) and delivers data to a target database.

<Tip>
  Run the steps in this section on a machine and in a directory where Oracle GoldenGate for PostgreSQL is installed.
</Tip>

1. Log in to the database from GGSCI:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   export LD_LIBRARY_PATH=/usr/pgsql-13/lib:/u01/ggs-pg/lib
   export ODBCINI=/etc/odbc.ini

   cd /u01/ggs-pg
   ./ggsci

   # Log into the DB
   DBLOGIN SOURCEDB CRDBLOCAL
   ```
2. Check the status in the GGSCI terminal:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   info all
   ```

   You should see the following output:

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   Program     Status      Group       Lag at Chkpt  Time Since Chkpt
   MANAGER     RUNNING
   ```
3. Open the parameter file:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   edit param RORPSQL
   view param RORPSQL
   ```
4. Edit the parameters to configure the `MAP` statement, which maps the source (`OGGADM1.testtable`) to the target (`public.testtable`):

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   REPLICAT RORPSQL
   SETENV ( PGCLIENTENCODING = "UTF8" )
   SETENV (ODBCINI="/etc/odbc.ini" )
   SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
   TARGETDB CRDBLOCAL
   DISCARDFILE ./dirrpt/diskg.dsc, purge
   MAP OGGADM1.testtable, TARGET public.testtable, COLMAP (COL1=col1,COL2=col2);
   ```
5. In the GGSCI terminal, run the following and start the Replicat service:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   add replicat RORPSQL, NODBCHECKPOINT, exttrail ./dirdat/ab
   start RORPSQL
   ```

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   Program     Status      Group       Lag at Chkpt  Time Since Chkpt
   MANAGER     RUNNING
   REPLICAT    RUNNING     RORPSQL     00:00:00      00:00:09
   ```
6. Test that Extract and Replicat are working properly by adding values to `OGGADM1.testtable`:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   INSERT INTO OGGADM1.testtable (col1, col2) VALUES (12, 'Example data');
   COMMIT;
   ```
7. <InternalLink path="connect-to-the-database">Connect to the target CockroachDB Standard cluster</InternalLink> and check that the data was delivered to `public.testtable`:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   SELECT * FROM public.testtable;
   ```

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     col1 |     col2
   -------+---------------
       12 | Example data
   ```
8. Open the GGSCI terminal for Oracle:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   cd $OGG_HOME
   ./ggsci
   ```
9. Check that Extract is working correctly:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   stats EXTRACT EPOS
   ```

   You should see an output similar to the following:

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   Extracting from OGGADM1.TESTTABLE to OGGADM1.TESTTABLE:
   *** Total statistics since 2023-06-09 19:06:44 ***
       Total inserts                              4.00
       Total updates                              0.00
       Total deletes                              0.00
       Total upserts                              0.00
       Total discards                             0.00
       Total operations                           4.00
   *** Daily statistics since 2023-06-09 19:06:44 ***
       Total inserts                              4.00
       Total updates                              0.00
       Total deletes                              0.00
       Total upserts                              0.00
       Total discards                             0.00
       Total operations                           4.00
   *** Hourly statistics since 2023-06-09 19:06:44 ***
       Total inserts                              4.00
       Total updates                              0.00
       Total deletes                              0.00
       Total upserts                              0.00
       Total discards                             0.00
       Total operations                           4.00
   *** Latest statistics since 2023-06-09 19:06:44 ***
       Total inserts                              4.00
       Total updates                              0.00
       Total deletes                              0.00
       Total upserts                              0.00
       Total discards                             0.00
       Total operations                           4.00
   End of statistics.
   ```
10. Open the GGSCI terminal for CockroachDB:

    ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    cd $OGG_PG_HOME
    ./ggsci
    ```
11. Check that Replicat is working correctly:

    ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    stats REPLICAT RORPSQL
    ```

    You should see an output similar to the following:

    ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    Replicating from OGGADM1.TESTTABLE to public.testtable:
    *** Total statistics since 2023-06-09 19:10:20 ***
        Total inserts                              4.00
        Total updates                              0.00
        Total deletes                              0.00
        Total upserts                              0.00
        Total discards                             0.00
        Total operations                           4.00
    *** Daily statistics since 2023-06-09 19:10:20 ***
        Total inserts                              4.00
        Total updates                              0.00
        Total deletes                              0.00
        Total upserts                              0.00
        Total discards                             0.00
        Total operations                           4.00
    *** Hourly statistics since 2023-06-09 19:10:20 ***
        Total inserts                              4.00
        Total updates                              0.00
        Total deletes                              0.00
        Total upserts                              0.00
        Total discards                             0.00
        Total operations                           4.00
    *** Latest statistics since 2023-06-09 19:10:20 ***
        Total inserts                              4.00
        Total updates                              0.00
        Total deletes                              0.00
        Total upserts                              0.00
        Total discards                             0.00
        Total operations                           4.00
    End of statistics.
    ```

## Perform bulk replication

1. Keep the [Extract process](#set-up-extract-to-capture-data-from-a-source-database) running on Oracle and the [Replicat process](#set-up-replicat-to-deliver-data-to-cockroachdb) running for CockroachDB.
2. In the source database, bulk insert some data:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   INSERT INTO OGGADM1.testtable2 (col1, col2)
   SELECT level + 99, 'Example data'
   FROM dual
   CONNECT BY level <= 50000 - 99;
   ```
3. Run the status command on the Extract GGSCI terminal:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   stats EXTRACT EPOS
   ```

   You will see an output similar to the following:

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   Extracting from OGGADM1.TESTTABLE2 to OGGADM1.TESTTABLE2:
   *** Total statistics since 2023-06-09 21:02:39 ***
       Total inserts                          49903.00
       Total updates                              0.00
       Total deletes                              0.00
       Total upserts                              0.00
       Total discards                             0.00
       Total operations                       49903.00
   *** Daily statistics since 2023-06-09 21:02:39 ***
       Total inserts                          49903.00
       Total updates                              0.00
       Total deletes                              0.00
       Total upserts                              0.00
       Total discards                             0.00
       Total operations                       49903.00
   *** Hourly statistics since 2023-06-09 21:02:39 ***
       Total inserts                          49903.00
       Total updates                              0.00
       Total deletes                              0.00
       Total upserts                              0.00
       Total discards                             0.00
       Total operations                       49903.00
   *** Latest statistics since 2023-06-09 21:02:39 ***
       Total inserts                          49903.00
       Total updates                              0.00
       Total deletes                              0.00
       Total upserts                              0.00
       Total discards                             0.00
       Total operations                       49903.00
   End of statistics.
   ```
4. Run the status command on the Replicat GGSCI terminal:

   The number of inserts takes a few minutes to update. After the process finishes inserting and committing to the database, it will report back the final inserts. During the process, it will show the previous state:

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   Replicating from OGGADM1.TESTTABLE2 to public.testtable:
   *** Total statistics since 2023-06-09 21:04:05 ***
       Total inserts                          49903.00
       Total updates                              0.00
       Total deletes                              0.00
       Total upserts                              0.00
       Total discards                             0.00
       Total operations                       49903.00
   *** Daily statistics since 2023-06-09 21:04:05 ***
       Total inserts                          49903.00
       Total updates                              0.00
       Total deletes                              0.00
       Total upserts                              0.00
       Total discards                             0.00
       Total operations                       49903.00
   *** Hourly statistics since 2023-06-09 21:04:05 ***
       Total inserts                          49903.00
       Total updates                              0.00
       Total deletes                              0.00
       Total upserts                              0.00
       Total discards                             0.00
       Total operations                       49903.00
   *** Latest statistics since 2023-06-09 21:04:05 ***
       Total inserts                          49903.00
       Total updates                              0.00
       Total deletes                              0.00
       Total upserts                              0.00
       Total discards                             0.00
       Total operations                       49903.00
   End of statistics.
   ```

   To verify that the insert is happening, you can run: `SELECT COUNT(*) FROM public.testtable;`. The query will hang while it's still inserting.
5. To track the status of an ongoing replication, view the report:

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   ./ggsci

   VIEW REPORT RORPSQL
   ```

## See also

* <InternalLink version="molt" path="migration-overview">Migration Overview</InternalLink>
* <InternalLink version="cockroachcloud" path="migrations-page">Schema Conversion Tool</InternalLink>
* <InternalLink path="change-data-capture-overview">Change Data Capture Overview</InternalLink>
* <InternalLink path="third-party-database-tools">Third-Party Tools Supported by Cockroach Labs</InternalLink>