Oracle by Example brandingUsing the Oracle GoldenGate for SQL Server CDC Capture Replication

section 0Before You Begin

This tutorial shows you how to use the CDC Capture replication, which utilizes the SQL Server Change Data Capture feature to read DML from the transaction log and load it into individual staging tables for each user table enabled with supplemental logging.  The CDC Extract then reads the DML from the staging tables and reconstructs transactions then writes the data into trail files.

This tutorial takes approximately 15 minutes to complete.


Previously, only Classic Capture was possible with SQL Server databases. With the Oracle GoldenGate 12c ( release, the CDC Capture replication is introduced.

What Do You Need?

Before starting this tutorial:


The SQL Server 2008/2008R2/2012/2014/2016 Enterprise Edition on Windows (Developer Edition can also be used for testing purposes), or SQL Server 2016 SP1 Standard Edition. 

The Standard Edition of SQL Server 2008-2014 does not contain the CDC features, so TRANDATA cannot be enabled. 


There are Microsoft bugs that impact the ability of the CDC job to capture correctly, and have been identified with the following two bugs that you need to apply based on your SQL Server version:

1. For SQL Server 2012, 2014, and 2016, Microsoft has identified and fixed an issue where some UPDATE operations may be written incorrectly to a CDC staging table as an INSERT followed by a DELETE, rather than a DELETE and INSERT pair. This may cause downstream replication issues (such as a primary key violation) so Oracle recommends that you apply this Microsoft fix:

2. For SQL Server 2016, prior to enabling supplemental logging, you must ensure that you have patched the SQL Server instance based on the following Microsoft bug fix:

If the instance is not correctly patched with this Microsoft fix, issuing ADD TRANDATA against a table for the database may incorrectly report that supplemental logging succeeded, when in fact it may not have. This results in no records being captured for that table. You can verify that the TRANDATA command was successful by issuing the INFO TRANDATA command.

Only user databases are supported (can be AlwaysOn Primary or readable Synchronous Secondary).

The SQL Server Agent must be running. The SQL Server Native Client Drivers are supported; the Microsoft ODBC Driver 11 and 13 are not supported.

  • No system database
  • No Contained databases
  • No databases enabled with In-Memory Optimization (2014/2016 feature)
  • Database Compatibility level must be 100 or higher
  • Asynchronous AlwaysOn databases are not supported
Where to Install

You can install Oracle GoldenGate on any local Windows database server or a remote Windows server.


If you are upgrading a database that was previously enabled with Classic Extract supplemental logging, you must DELETE TRANDATA, and then re-add it with the CDC Capture build. You can use the CDC Capture build to DELETE TRANDATA previously enabled from a Classic Capture build.


You should review Release Notes and the Oracle GoldenGate for SQL Server documents for complete information regarding SQL Server instance and database requirements including supported data types.


You download the product from OTN:


Create a source and target database and tables and a SQL Server Authenticated user that has sysadmin rights. Unlike Classic Extract, there is no need for a full backup of the source database and the database can be in simple recovery model.

Using the following tasks, you create both a Uni-Directional and a Bi-Directional SQL Server to SQL Server Replication.

section 1Setting Up the Uni-Directional CDC Extract

  1. Create a system DSN to the source database and set the change the default database to option to the source database. Use a Windows or SQL Server login that has sysadmin rights for this connection. You can alter the permissions to dbowner at a later time, if you want to use the same account for the Extract and are not allowed to have Extract running with sysadmin.
  2. Unzip the file to a new Oracle GoldenGate installation directory.
  3. Create a GLOBALS file in the base Oracle GoldenGate installation directory, and set the GGSCHEMA parameter to that of an existing or new schema in the source database. Oracle recommends that you create a specific schema for Oracle GoldenGate objects. For example - CREATE SCHEMA ggs. Do not to use the dbo schema.

    Save the GLOBALS file.

    Using GGSCHEMA in the GLOBALS file is a new requirement for Oracle GoldenGate for SQL Server CDC Capture. It is required so that ADD TRANDATA can identify which schema to create necessary objects under then Extract knows which schema to call those objects from during runtime. Classic Extract does not have this requirement.

  4. Start ggsci.exe and create the necessary sub directories.
  5. Create the Manager parameter file; list a valid PORT for the Manager to use, then save the file. For example, PORT 7809.
  6. Connect to the source database from GGSCI and enable supplemental logging for the user tables to be captured from using one of the following:

    For a SQL Server Authenticated DSN, the USERID and PASSWORD are optional:

    GGSCI> DBLOGIN SOURCEDB sourcedsn [USERID user PASSWORD password]

    Or for a Windows Authenticated DSN:

  7. In the Management Studio, within a query window for the source database. You must manually drop the SQL Server CDC cleanup job for the database because it may cause data loss for the Extract.
    EXECUTE sys.sp_cdc_drop_job 'cleanup';
  8. Use the ogg_cdc_cleanup_setup.bat utility (in the Oracle GoldenGate installation directory) to create the Oracle GoldenGate CDC cleanup job and associated objects. The ggschema name used must be the same that you used with the GGSCHEMA parameter of the GLOBALS file. You must use a SQL Server authenticated user that has sysadmin rights.
    d:\>OGG\ogg_cdc_cleanup_setup.bat createJob username password databasename
        servername\instancename ggschema
  9. Create and save a new Extract parameter file using this sample of the minimum required parameters for a uni-directional implementation.
    GGSCI> EDIT PARAMS cdcext                            
    EXTRACT cdcext 
    SOURCEDB sourcedsn [USERID user PASSWORD password]
    EXTTRAIL ./dirdat/ce
    TABLE dbo.*;
    Do Not wildcard the TABLE statement if you are using the same schema with GGSCHEMA TABLE dbo.*;.
  10. Add the Extract to the Oracle GoldenGate installation.
    GGSCI> ADD EXTTRAIL ./dirdat/ce, EXTRACT cdcext

section 2Setting Up the Uni-Directional Pump and Replicat

  1. Create and save a new pump parameter file using the following sample of the minimum required parameters for a uni-directional implementation.
    EXTRACT cdcpmp
    RMTHOST servername MGRPORT 7809
    RMTTRAIL ./dirdat/cp
    TABLE dbo.*;
  2. Create a system DSN to the target database, as you did for the source database in Step 1.
  3. Create and save a new Replicat parameter file using the following command and sample of the minimum required parameters for a uni-directional implementation. The USERID and PASSWORD are optional.
    REPLICAT cdcrep
    TARGETDB targetdsn [USERID user PASSWORD password]
    MAP dbo.*, TARGET dbo.*;
  4. Add the pump to the Oracle GoldenGate installation.
    GGSCI> ADD RMTTRAIL ./dirdat/cp, EXTRACT cdcpmp
  5. Add the Replicat to the Oracle GoldenGate installation.
    GGSCI> DBLOGIN SOURCEDB targetdsn [USERID user PASSWORD password]
    GGSCI> ADD REPLICAT cdcrep, EXTTRAIL ./dirdat/cp,CHECKPOINTTABLE ggs.ggcheck
  6. Start and verify that the processes are running.

section 3Setting Up the Bi-Directional CDC Extract

  1. Follow all of the steps for configuring Uni-Directional replication in Step 1 and Step 2.
  2. Add a checkpoint table to all of your source databases that the Replicat will use when adding the Replicat to deliver to the source database.
  3. Enable supplemental logging for the checkpoint table.
    GGSCI> ADD TRANDATA ggs.ggcheck
  4. Edit your Extract parameter files to add the following entries, and then save the file.
  5. When you create the Replicat that delivers to this source database, you must use the same checkpoint table for the Replicat that is specified by the Extract's FILTERTABLE parameter.
    GGSCI> ADD REPLICAT cdcrep, EXTTRAIL ./dirdat/cp, CHECKPOINTTABLE ggs.ggcheck

section 4Troubleshooting Issues

You may encounter these issues and can use this information to correct them.

  1. TRANDATA commands are failing with the following message:
    ERROR OGG-05263 No GGSCHEMA clause was specified in the GLOBALS file...

    This message appears when either there is no GLOBALS file, or there is a GLOBALS file that does not have a GGSCHEMA entry supplied with a schema name

    1. Ensure that the GLOBALS file exists, that there is a valid GGSCHEMA entry, and that the schema listed is a valid schema in the database, restart GGSCI and re-issue the TRANDATA commands.
    2. Ensure that the schema has been created in the source Database. For example, CREATE SCHEMA ggs.
  2. Extract is running, but STATS reports that no data is captured.
    1. Verify that the Extract has checkpointed with a valid LSN. When Extract first starts, if there has not been any DML for tables enabled with TRANDATA since Extract was created, it will not checkpoint until new DML operations are picked up by the CDC Capture job and loaded into the staging tables.

      Extract has not checkpointed:

      Current Checkpoint (position of last record read in the data source): Timestamp: 2017-08-15 14:12:26.454000

      Extract has checkpointed:

      Current Checkpoint (position of last record read in the data source):
        Timestamp: 2017-09-27 09:17:37.640000
        LSN: 0000f525:00000930:0007-0000f525:00000930:0007, Tran: 0000:0b475e72
    2. Verify that the TABLE statement for Extract includes tables that are actually enabled with TRANDATA.
    3. Ensure that if the TABLE statement includes wildcards, that it is not for the schema that is used with the GGSCHEMA parameter of the GLOBALS file.