Migrate and Replicate Data with Oracle GoldenGate

On this page Carat arrow pointing down

Oracle 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:

As of this writing, GoldenGate supports the following database sources:

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

For limitations on what PostgreSQL features are supported, refer to Oracle's Details of Supported PostgreSQL Data Types.

Before you begin

  • Oracle GoldenGate runs as a process separate from CockroachDB and the source database. Ensure your host meets the minimum requirements.

  • 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 is required to pull source data and route it to proper trail files.
    2. Oracle GoldenGate for PostgreSQL is required to pull data from the trail files to CockroachDB.
  • For CockroachDB clusters running v22.1 and earlier, enable the following cluster settings:

    icon/buttons/copy
    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 is available on the Oracle GoldenGate host.

  • Ensure you have a secure, publicly available CockroachDB cluster running the latest v23.2 production release, and have created a SQL user.

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 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. For more information, refer to the Oracle GoldenGate for PostgreSQL documentation. The following example uses a CockroachDB Standard cluster.

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

    icon/buttons/copy
    # 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:

    icon/buttons/copy
    # 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:

    icon/buttons/copy
    # 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 {hostname}.
    • Make sure your CockroachDB Standard cluster's root CA certificate is in the TrustStore path.
    icon/buttons/copy
    [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:

    icon/buttons/copy
    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 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.

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

    icon/buttons/copy
    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:

    icon/buttons/copy
    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.

    icon/buttons/copy
    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:

    icon/buttons/copy
    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:

    icon/buttons/copy
    stats EXTRACT epos
    

    You should see an output similar to the following:

    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 is an Oracle process that reads trail files 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.

  1. Log in to the database from GGSCI:

    icon/buttons/copy
    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:

    icon/buttons/copy
    info all
    

    You should see the following output:

    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    MANAGER     RUNNING                                           
    
  3. Open the parameter file:

    icon/buttons/copy
    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):

    icon/buttons/copy
    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:

    icon/buttons/copy
    add replicat RORPSQL, NODBCHECKPOINT, exttrail ./dirdat/ab
    start RORPSQL
    
    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:

    icon/buttons/copy
    INSERT INTO OGGADM1.testtable (col1, col2) VALUES (12, 'Example data');
    COMMIT;
    
  7. Connect to the target CockroachDB Standard cluster and check that the data was delivered to public.testtable:

    icon/buttons/copy
    SELECT * FROM public.testtable;
    
      col1 |     col2
    -------+---------------
        12 | Example data
    
  8. Open the GGSCI terminal for Oracle:

    icon/buttons/copy
    cd $OGG_HOME
    ./ggsci
    
  9. Check that Extract is working correctly:

    icon/buttons/copy
    stats EXTRACT EPOS
    

    You should see an output similar to the following:

    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:

    icon/buttons/copy
    cd $OGG_PG_HOME
    ./ggsci
    
  11. Check that Replicat is working correctly:

    icon/buttons/copy
    stats REPLICAT RORPSQL
    

    You should see an output similar to the following:

    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 running on Oracle and the Replicat process running for CockroachDB.
  2. In the source database, bulk insert some data:

    icon/buttons/copy
    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:

    icon/buttons/copy
    stats EXTRACT EPOS
    

    You will see an output similar to the following:

    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:

    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:

    ./ggsci
    
    VIEW REPORT RORPSQL
    

See also


Yes No
On this page

Yes No