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.

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:

 
Certification

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. 

Requirements

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:

https://support.microsoft.com/en-us/help/3030352

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:

https://support.microsoft.com/en-us/help/3166120/fix-could-notfind-stored-procedure-sys.sp-cdc-parse-captured-column-list-error-in-sqlserver-2016

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.

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

Upgrades

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.

Review

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.

http://docs.oracle.com/goldengate/c1230/gg-winux/index.html

Download

You download the product from OTN:

http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

Create

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

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

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

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