【OGG】Using the Oracle GoldenGate for SQL Server CDC Capture Replication

0    419    1


👉 本文共约2422个字,系统预计阅读时间或需10分钟。


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

【OGG】Using the Oracle GoldenGate for SQL Server CDC Capture Replication

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

一、Setting 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 ggs_Windows_x64_MSSQL_64bit_CDC.zip 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 -

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

    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.

  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.

  9. Create and save a new Extract parameter file using this sample of the minimum required parameters for a uni-directional implementation.

    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.

二、Setting 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.

  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.

  4. Add the pump to the Oracle GoldenGate installation.

  5. Add the Replicat to the Oracle GoldenGate installation.

  6. Start and verify that the processes are running.

三、Setting Up the Bi-Directional CDC Extract

  1. Follow all of the steps for configuring Uni-Directional replication in Step 1 and Step 1.

  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.

  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.

四、Troubleshooting Issues

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

  1. TRANDATA commands are failing with the following message:

    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

    a. 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.
    b. 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.

    a. 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:

    Extract has checkpointed:


    b. Verify that the TABLE statement for Extract includes tables that are actually enabled with TRANDATA .

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







  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部