1. Oracle GoldenGate Classic Architecture Documentation
  2. Prepare
  3. Prepare Your Database for Oracle GoldenGate Classic Architecture
  4. SQL Server
  5. Preparing the Database for Oracle GoldenGate — CDC Capture
  6. Enabling CDC Supplemental Logging

Enabling CDC Supplemental Logging

With the CDC Extract, the method of capturing change data is via SQL Server Change Data Capture tables, so it is imperative that you follow the procedures and requirements below, so that change data is correctly logged, maintained, and captured by Extract.

You will enable supplemental logging with the ADD TRANDATA command so that Extract can capture the information that is required to reconstruct transactions.

ADD TRANDATA must be issued for all tables that are to be captured by Oracle GoldenGate, and to do so requires that a valid schema be used in order to create the necessary Oracle GoldenGate tables and stored procedures.

Enabling supplemental logging for a CDC Extract does the following:

  • Enables SQL Server Change Data Capture at the database level, if it’s not already enabled.

    • EXECUTE sys.sp_cdc_enable_db

  • Creates a Change Data Capture staging table for each base table enabled with supplemental logging by running EXECUTE sys.sp_cdc_enable_table, and creates a trigger for each CDC table. The CDC table exists as part of the system tables within the database and has a naming convention like, cdc.OracleGG_basetableobjectid_CT.

  • Creates a tracking table of naming convention schema.OracleGGTranTables. This table is used to store transaction indicators for the CDC tables, and is populated when the trigger for a CDC table is fired. The table will be owned by the schema listed in the GLOBALS file’s, GGSCHEMA parameter.

  • Creates a unique fetch stored procedure for each CDC table, as well as several other stored procedures that are required for Extract to function. These stored procedures will be owned by the schema listed in the GLOBALS file’s, GGSCHEMA parameter.

  • Also, as part of enabling CDC for tables, SQL Server creates two jobs per database:

    cdc.dbname_capture

    cdc.dbname_cleanup

  • The CDC Capture job is the job that reads the SQL Server transaction log and populates the data into the CDC tables, and it is from those CDC tables that the Extract will capture the transactions. So it is of extreme importance that the CDC capture job be running at all times. This too requires that SQL Server Agent be set to run at all times and enabled to run automatically when SQL Server starts.

  • Important tuning information of the CDC Capture job can be found in CDC Capture Method Operational Considerations.

  • The CDC Cleanup job that is created by Microsoft does not have any dependencies on whether the Oracle GoldenGate Extract has captured data in the CDC tables or not. Therefore, extra steps need to be followed in order to disable or delete the CDC cleanup job immediately after TRANDATA is enabled, and to enable Oracle GoldenGate's own CDC cleanup job. See Retaining the CDC Table History Data for more information.

The following steps require a database user who is a member of the SQL Server System Administrators (sysadmin) role.

  1. In the source Oracle GoldenGate installation, ensure that a GLOBALS (all CAPS and no extension) file has been created with the parameter GGSCHEMA schemaname. Ensure that the schema name used has been created (CREATE SCHEMA schemaname) in the source database. This schema will be used by all subsequent Oracle GoldenGate components created in the database, therefore it is recommended to create a unique schema that is solely used by Oracle GoldenGate, such as ‘ogg’. It is recommended not to use the SQL Server schema cdc and to create a new schema specific to Oracle GoldenGate.
  2. On the source system, run GGSCI
  3. Issue the following command to log into the database:
    DBLOGIN SOURCEDB DSN [,{USERID user, PASSWORD password | USERIDALIAS alias}]

    Where:

    • SOURCEDB DSN is the name of the SQL Server data source.

    • USERID user is the database login and PASSWORD password is the password that is required if the data source connects via SQL Server authentication. Alternatively, USERIDALIAS alias is the alias for the credentials if they are stored in a credentials store. If using DBLOGIN with a DSN that is using Integrated Windows authentication, the connection to the database for the GGSCI session will be that of the user running GGSCI. In order to issue ADD TRANDATA or DELETE TRANDATA, this user must be a member of the SQL Server sysadmin server role.

  4. In GGSCI, issue the following command for each table that is, or will be, in the Extract configuration. You can use a wildcard to specify multiple table names.

    ADD TRANDATA owner.table

    ADD TRANDATA owner.*

    Optionally, you can designate the filegroup in which the SQL Server Change Data Capture staging tables will be placed, by using the FILEGROUP option with an existing filegroup name.

    ADD TRANDATA owner.table FILEGROUP cdctables

    See ADD TRANDATA