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

section 0Before You Begin

This document touches briefly on many important and complex concepts and does not provide a detailed explanation of any one topic since the intent is to present the material in the most expedient manner. The goal is simply to help the reader become familiar enough with the product to successfully design and implement an Oracle GoldenGate environment. To that end, it is important to note that the activities of design, unit testing and integration testing which are crucial to a successful implementation have been intentionally left out of the guide. All the sample scripts are provided as is. Oracle consulting service is highly recommended for any customized implementation.

This tutorial steps you through setting up Oracle GoldenGate Remote Capture and Delivery for SQL Server. The sample steps below guide you through the installation of Oracle GoldenGate on a mid-tier Windows or Linux application server.

This tutorial takes approximately 30 minutes to complete.

What Do You Need?

Review the following information before you start this tutorial.

Certification

Remote and local capture and apply are supported for SQL Server 2012/2014/2016/2017 Enterprise Edition on Windows (Developer Edition can also be used for testing purposes) and SQL Server 2016 SP1/2017 Standard Edition. Remote apply is supported for Azure SQL Database.

Requirements

There are Microsoft bugs that impact the ability of the CDC job to capture correctly. These known issues require that you apply Microsoft patches for your SQL Server version:

For SQL Server 2012, 2014, 2016, and 2017, Microsoft has identified and fixed several important issues that directly affect the SQL Server Change Data Capture feature. This impacts the ability for Oracle GoldenGate to correctly capture data. The current known issues that require Microsoft patches include KB3030352, KB3166120, and KB4073684. If you are using SQL Server 2012, 2014, 2016, or 2017 as a source database, Oracle highly recommends that you apply the latest Service Pack or Cumulative Update for your version of SQL Server.

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

The SQL Server Agent must be running.

Ensure that Auto Update Statistics is enabled for the database.

Ensure that the server where Oracle GoldenGate is installed has the same system time and time zone as the database server.

Restrictions
  • No system databases
  • No Contained databases
  • No databases enabled with In-Memory Optimization (2014/2016/2017 feature)
  • Database Compatibility level must be 110 or higher
  • Asynchronous Always On databases are not supported
Where to Install

You can install Oracle GoldenGate on a supported Windows or Linux server. However, only SQL Server running on Windows is supported as a source or target database.

Upgrades

If you are upgrading a database that was previously enabled with 12.x 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.

Upgrading from an OGG 12.3 CDC Extract to OGG 19.1 CDC Extract requires running ADD TRANDATA again for all tables previously enabled with TRANDATA.

Careful consideration and extra steps are required for this type of upgrade, so carefully review the instructions available in the Oracle GoldenGate documentation.

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.

https://docs.oracle.com/en/middleware/goldengate/core/19.1/index.html

Download

You download Oracle GoldenGate from OTN:

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

Create

Create a source and target database, 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.

text


section 1Configure Uni-Directional SQL Server to SQL Server Replication

Prerequisites: Create a source and target database, tables, and a SQL Server Authenticated user that has sysadmin rights.

Set up the CDC Extract

  1. Create a SQL Server login to be used for initial setup and by the Extract, and grant sysadmin rights to the login. You can alter the permissions of the login to dbowner after completing the initial setup tasks and prior to starting the Extract.

  2. Create a schema in the source database that will be used for Oracle GoldenGate objects. Oracle recommends that you create a specific schema for Oracle GoldenGate.

    CREATE SCHEMA [ogg];
  3. Optional, but recommended step. Create a new filegroup in the database that will be used to store the CDC staging tables that will be created when enabling supplemental logging in an upcoming step. For example:
    ALTER DATABASE [sourcedb] ADD FILEGROUP [cdcfg]; 
    ALTER DATABASE [sourcedb] ADD FILE ( NAME = N'cdcdata', FILENAME = 
    N'C:\cdcdata.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) 
    TO FILEGROUP [cdcfg];
    
  4. For Oracle GoldenGate installed on Windows, create a system DSN to the source database and set the change the default database to option to the source database.

  5. For Oracle GoldenGate installed on Linux, follow Microsoft’s RedHat Enterprise Server installation instructions for installing the ODBC Drivers for Linux, and create an ODBC data source afterwards as described below:

    1. Create a template file for your data source:

      $ vi odbc_template_file.ini
    2. Describe the data source in the template file. In the following example, myserver_sql2017 is used as the name with DBLOGIN and SOURCEDB to connect to the source database.

      [myserver_sql2017_source] 
      Driver = ODBC Driver 17 for SQL Server
      Server = myserver,1433
      Database = dbname
      User = username
      Password = password

      Save and close the file.

    3. Install the data source using the following command:

      $ odbcinst -i -s -f odbc_template_file.ini
  6. Unzip the ggs_Windows_x64_MSSQL_64bit_CDC.zip or ggs_Linux_x64_MSSQL_64bit_CDC.zip build to a new OGG installation directory.

  7. Create a GLOBALS file in the Oracle GoldenGate installation directory and set the GGSCHEMA parameter to that of the Oracle GoldenGate schema created for the source database. Save and close the GLOBALS file.

    Using GGSCHEMA in GLOBALS is a new requirement for OGG for SQL Server CDC Capture, and is required so that ADD TRANDATA knows which schema to create necessary objects under, and Extract knows which schema to call those objects from during runtime.

    GGSCHEMA ogg
  8. Start ggsci.exe or ggsci.sh and run the command to create the necessary sub directories.

    GGSCI> CREATE SUBDIRS
  9. Create the Manager parameter file; list an unused PORT for the Manager to use, then save the file.

    GGSCI> EDIT PARAMS MGR
    PORT 7809
  10. Connect to the source database from GGSCI and enable supplemental logging for the user tables to be captured from.

    On Windows:

    GGSCI> DBLOGIN SOURCEDB sourcedsn USERID username PASSWORD password

    On Linux:

    GGSCI> DBLOGIN SOURCEDB myserver_sql2017_source USERID username 
    PASSWORD password
    GGSCI> ADD TRANDATA dbo.table1 FILEGROUP cdcfg
    GGSCI> ADD TRANDATA dbo.table2 FILEGROUP cdcfg

    Note: Remove the FILEGROUP option and filegroup name from the ADD TRANDATA command if you did not perform step 3.

  11. Manually drop the SQL Server CDC cleanup job for the database, as this could cause data loss for the Extract. Do this task from the Management Studio, within a query window for the source database.

    EXECUTE sys.sp_cdc_drop_job N'cleanup';

  12. Use the ogg_cdc_cleanup_setup.bat or ogg_cdc_cleanup_setup.sh utility provided 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 to perform this step.

    For Windows connections, you will not use the system DSN that you created but the server name or server IP address, followed by the SQL Server instance name or its TCP/IP port number.

    For Linux connections, you will not use the ODBC data source name that you created earlier but the server name or server IP address, followed by the SQL Server instance’s TCP/IP port number.

    For Windows:

    c:\>OGG\ogg_cdc_cleanup_setup.bat createJob username 
    password databasename servername\instancename ggschema

    For Linux:

    ./ogg_cdc_cleanup_setup.sh createJob username 
    password databasename servername,port ggschema
  13. Create and save a new Extract parameter file. Below is a sample of the minimum required parameters for a uni-directional implementation.

    GGSCI> EDIT PARAMS cdcext
    EXTRACT cdcext
    SOURCEDB sourcedsn USERID username PASSWORD password
    EXTTRAIL ./dirdat/ce
    
    --Note: Optional TRANLOGOPTIONS Parameter to support data extraction 
    from a secondary replica configured for read-only routing as part of an 
    Always On Availability Group 
    
    TRANLOGOPTIONS ALWAYSONREADONLYROUTING
    TABLE dbo.table1;
    TABLE dbo.table2;
    
  14. Add the Extract to the Oracle GoldenGate installation.

    GGSCI> ADD EXTRACT cdcext, TRANLOG, BEGIN NOW
    GGSCI> ADD EXTTRAIL ./dirdat/ce, EXTRACT cdcext
  15. Start the Manager and Extract and verify that the processes are running.

    GGSCI>START MGR
    GGSCI>START EXTRACT cdcext
    GGSCI>INFO ALL

Set up the Replicat

  1. Create a Windows system DSN or Linux ODBC data source connection to the target database, as you did earlier for the source database.

  2. Create and save a new Replicat parameter file. Below is a sample of the minimum required parameters for a uni-directional implementation.

    GGSCI> EDIT PARAMS cdcrep
    REPLICAT cdcrep
    TARGETDB targetdsn USERID username PASSWORD password
    MAP dbo.*, TARGET dbo.*
  3. Create a schema in the target database that will be used for Oracle GoldenGate objects. Oracle recommends that you create a specific schema for Oracle GoldenGate.

    CREATE SCHEMA [ogg];
  4. Create a checkpoint table and add the Replicat to the Oracle GoldenGate installation.

    GGSCI> DBLOGIN SOURCEDB targetdsn USERID username PASSWORD password
    GGSCI> ADD CHECKPOINTTABLE ogg.ggcheck
    GGSCI> ADD REPLICAT cdcrep, EXTTRAIL ./dirdat/ce, CHECKPOINTTABLE 
    ogg.ggcheck
    
  5. Start the Replicat and verify that all processes are running.

    GGSCI>START REPLICAT cdcrep
    GGSCI>INFO ALL
    

section 2Configure a Bi-Directional CDC Extract

To configure a Bi-Directional CDC Extract:

  1. Follow the steps for configuring Uni-Directional replication, and perform the extra steps listed below.

  2. Add a checkpoint table to all the source databases, which will be used by the Replicats.

    GGSCI> ADD CHECKPOINTTABLE ogg.ggcheck

  3. Enable supplemental logging for the checkpoint tables.

    GGSCI> ADD TRANDATA ogg.ggcheck

  4. Edit and save the Extracts’ parameter files, to include the following new entries:

    IGNOREREPLICATES
    TRANLOGOPTIONS FILTERTABLE ogg.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 listed with the Extract’s FILTERTABLE parameter.

    GGSCI>ADD REPLICAT cdcrep, EXTTRAIL ./dirdat/ce, CHECKPOINTTABLE
    ogg.ggcheck

section 3Troubleshoot Issues

Following are some troubleshooting and correction tips:

  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 but does not have a GGSCHEMA entry supplied with a schema name.

    • 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.
    • Ensure that the schema has been created in the source Database. Example: CREATE SCHEMA ggs
  2. Extract is running, but STATS reports that no data is captured.
    • 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
    • Verify that the TABLE statement for Extract includes the tables that are enabled with TRANDATA.

      INFO TRANDATA dbo.tablename

    • 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.
  3. Extract abends with the following message:

    ERROR OGG-05329  Oracle GoldenGate Capture for SQL Server, CDCEXT.prm:  
    The SQL Server CDC Capture job for database dbname is not running. 
    Please ensure that the SQL Server CDC Capture job is running.

    Verify that the SQL Server Agent and the SQL Server CDC capture job are running.

  4. Extract abends with a message regarding object versioning:

    ERROR OGG-05338 The versions of the Oracle GoldenGate CDC objects on 
    database, dbname, are not consistent with the expected version, 2. 
    The following version(s) were found: 2,Unknown. Rerun ADD TRANDATA for all 
    tables previously enabled, including heartbeat, heartbeat seed, and 
    filter tables.
    • This message occurs if you are upgrading from an earlier version of Oracle GoldenGate than 19.1 and supplemental logging was not reissued with the 19.1 GGSCI. Ensure that you have re-run ADD TRANDATA for all tables previously enabled with it, including any filter tables and the heartbeat and heartbeat_seed tables if used.
    • If Oracle GoldenGate objects or CDC tables were deleted without first running DELETE TRANDATA, then this could also leave other Oracle GoldenGate objects in the database that the Extract will evaluate against, in which case you will need to manually remove those objects.