Migrate and Replicate Data with Oracle GoldenGate

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:

• Configure Oracle GoldenGate for PostgreSQL.
• Set up Extract to capture data from a source database.
• Set up Replicat to deliver data to CockroachDB.
• Perform bulk replication.

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 :
  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, CockroachDB cluster running the latest , and have created a .

​

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:
   # 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:
   # 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:
   # 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 .
   • Make sure your CockroachDB Standard cluster’s root CA certificate is in the TrustStore path.
   [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:
   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.

1. In the GGSCI terminal, create and open the epos parameter file for the Oracle source:
   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:
   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.
   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:
   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:
   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.

1. Log in to the database from GGSCI:
   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:
   info all
   
   You should see the following output:
   Program     Status      Group       Lag at Chkpt  Time Since Chkpt
   MANAGER     RUNNING
   
3. Open the parameter file:
   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):
   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:
   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:
   INSERT INTO OGGADM1.testtable (col1, col2) VALUES (12, 'Example data');
   COMMIT;
   
7. and check that the data was delivered to public.testtable:
   SELECT * FROM public.testtable;
   
     col1 |     col2
   -------+---------------
       12 | Example data
   
8. Open the GGSCI terminal for Oracle:
   cd $OGG_HOME
   ./ggsci
   
9. Check that Extract is working correctly:
   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:
    cd $OGG_PG_HOME
    ./ggsci
    
11. Check that Replicat is working correctly:
    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:
   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:
   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