Using the Oracle GoldenGate for SQL Server CDC Capture Replication

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.

Background

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

What Do You Need?

Before starting this tutorial:

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 - CREATE SCHEMA ggs. Do not to use the dbo schema.

   GGSCI> EDIT PARAMS ./GLOBALS

   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.

   GGSCI> CREATE SUBDIRS

5. Create the Manager parameter file; list a valid PORT for the Manager to use, then save the file. For example, PORT 7809.

   GGSCI> EDIT PARAMS MGR

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]
   GGSCI> ADD TRANDATA dbo.* 

   Or for a Windows Authenticated DSN:

   GGSCI> DBLOGIN SOURCEDB sourcedsn
   GGSCI> ADD TRANDATA dbo.* 

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 EXTRACT cdcext, TRANLOG, BEGIN NOW 
    GGSCI> ADD EXTTRAIL ./dirdat/ce, EXTRACT cdcext

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.

   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.

   GGSCI> EDIT PARAMS cdcrep

   REPLICAT cdcrep
   TARGETDB targetdsn [USERID user PASSWORD password]
   MAP dbo.*, TARGET dbo.*;
   

4. Add the pump to the Oracle GoldenGate installation.

   GGSCI> ADD EXTRACT cdcpmp, EXTTRAILSOURCE ./dirat/ce
   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 CHECKPOINTTABLE ggs.ggcheck
   GGSCI> ADD REPLICAT cdcrep, EXTTRAIL ./dirdat/cp,CHECKPOINTTABLE ggs.ggcheck
   

6. Start and verify that the processes are running.

   GGSCI>START MGR
   GGSCI>START *
   GGSCI>INFO ALL
   

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.

   GGSCI> ADD CHECKPOINTTABLE ggs.ggcheck

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.

   IGNOREREPLICATES 
   TRANLOGOPTIONS FILTERTABLE ggs.ggcheck 

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

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

      INFO EXTRACT cdcext SHOWCH

      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.

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