1. Microservices Architecture Documentation
  2. Prepare
  3. Prepare Databases
  4. SQL Server
  5. Prepare the Database for Oracle GoldenGate

Prepare the Database for Oracle GoldenGate

Learn how to enable supplemental logging in the source database tables that are to be used for capture by the Extract for SQL Server and how to purge older CDC staging data.

You can learn more about CDC Capture using the following:

Using the Oracle GoldenGate for SQL Server CDC Capture Replication http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/goldengate/12c/sql_cdcrep/sql_cdcrep.html.

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

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

    Note:

    If SQL Server Transactional Replication is also enabled for the database, the CDC Capture job will not exist and instead, only the SQL Server Log Reader Agent job will exist.

  • The CDC Capture job can be tuned for better throughput and tuning information 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 are needed to disable or delete the the CDC cleanup job immediately after TRANDATA is enabled, and to enable Oracle GoldenGate's own Purge Change Data task. See Purge CDC Staging Data for more information.

To enable supplemental logging using the command line interface, use the following high-level steps:

  1. Review the Prepare Database Users and Privileges topic in order to determine required privileges and steps to enable the database for Change Data Capture, if it is not already set. Elevated permissions may be needed for GoldenGate if the database is not enabled for CDC but can be negated by having an admin manually enable the database for Change Data Capture.

    • For Google Cloud SQL for SQL Server , the database has to manually be enabled for Change Data Capture by a service admin user and executing the following command:

      EXEC msdb.dbo.gcloudsql_cdc_enable_db 'source_database';

    • For SQL Server and Azure SQL Managed Instance, adding TRANDATA will attempt to set the database for Change Data Capture if the user has sysadmin privileges, otherwise a database administrator can manually enable the database for CDC prior to adding TRANDATA, by executing the following command against the source database:

      EXEC sys.sp_cdc_enable_db;

    • For Amazon RDS for SQL Server, adding TRANDATA will also attempt to set the database for Change Data Capture if the user has been granted the permission, otherwise a database administrator with master credentials can manually enable the database for CDC prior to adding TRANDATA, by executing the following command against the source database:

      EXEC msdb.dbo.rds_cdc_enable_db ‘source_database’

  2. In the source Oracle GoldenGate installation, ensure that the GLOBALS file has the parameter GGSCHEMA schemaname and 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 ’ggschema’ and to not use the SQL Server schemas dbo or cdc.

  3. On the source Oracle GoldenGate system, open the command line interface (Admin Client).

  4. Connect to the database with the database login credentials.

  5. Issue the following command for each table that is to be captured by an Extract. 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 cdctablesSee ADD TRANDATA

See ADD TRANDATA for more details.

Purge CDC Staging Data

When enabling supplemental logging, data that is required by Extract to reconstruct transactions are stored in a series of SQL Server CDC system tables, as well Oracle GoldenGate objects that are used to track operation ordering within a transaction. These tables require routine purging in order to reduce data storage within the database. As part of enabling supplemental logging using TRANDATA, SQL Server creates its own Change Data Capture Cleanup job, however this job is unaware that an Extract may still require data from these CDC system tables and can remove that data before the Extract has a chance to capture it.

If data that Extract needs during processing has been deleted from the CDC system tables, then one of the following corrective actions might be required:

  • Alter Extract to capture from a later point in time for which CDC data is available (and accept possible data loss on the target).

  • Resynchronize the source and target tables, and then start the Oracle GoldenGate environment over again.

To remedy the situation of CDC data being removed before an Extract can process it, Oracle GoldenGate for SQL Server requires that a Purge Change Data task be created. This task will purge CDC staging data while ensuring that no data is purged that the Extract has yet to process.

Use the following steps immediately after enabling supplemental logging (TRANDATA) and prior to starting the Extract, to create the Oracle GoldenGate Purge Change Data task. The Purge Change Data task runs within the Administration Service and will automatically delete the SQL Server CDC Cleanup job when first created. There is no SQL Server Agent job for the Purge Change Data task as it is run by the Administration Service. Therefore, the Administration Service must be running in order for the cleanup task to function correctly.

To create a Purge Change Data task:

With Oracle GoldenGate for Microservices Architecture, after adding TRANDATA to tables but prior to starting Extract, a Purge Change Data task must be created to perform CDC cleanup on the database. This can be done through either one of the following:
  • Manual REST API requests
  • Administration Server Web UI
To create a Purge Change Data task using the Administration Service Web UI:
  1. Expand the Tasks section in the left pane.
  2. Click Purge Change Data from the Tasks page.
  3. Click the plus sign to display a form, and fill out the required fields to create a new Purge Change Data task.
    1. Operation Name: Name of the purge task to be created.
    2. Enabled: Set the task to enabled, which is the default value.
    3. Credential Domain and Credential Alias: Select an existing Credential Alias for the source database.
    4. Keep Rule: This value determines in hours or days, the amount of CDC staging data to keep in the source database. Depending on the version of Oracle GoldenGate, the default values are either 3 days or 1 hour. Lower CDC data retention periods reduce the amount of CDC staging data stored in the database but limit the ability for a user to reposition the Extract back to a time older than the data that exists in the staging tables.
    5. Purge Frequency: This value represents how often the task runs, with a default value of every 10 minutes. It is recommended to keep the default value unless overhead from the purge task is impacting database performance during periods of high user activity.

Note:

Only create one Purge Change Data task per source database.

Additional information of the Oracle GoldenGate CDC Cleanup job can be found in CDC Capture Method Operational Considerations.

Enabling Bi-Directional Loop Detection

Loop detection is a requirement for bi-directional implementations of Oracle GoldenGate, so that an Extract for one source database does not recapture transactions sent by a Replicat from another source database.

With the CDC Extract capture method, by default, any transaction committed by a Replicat into a database where an Extract is configured, will recapture that transaction from the Replicat as long as supplemental logging is enabled for those tables that the Replicat is delivering to.

In order to ignore recapturing transactions that are applied by a Replicat, you must use the TRANLOGOPTIONS EXCLUDEFILTERTABLE parameter for the CDC Extract. The table used as the filtering table will be the Oracle GoldenGate checkpoint table that you must create for the Replicat.

To create a Filter Table and enable Supplemental Logging:

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

  1. On the source system, run Admin Client.

  2. Connect to the deployment from the Admin Client.

  3. Issue the following command to log into the database.

    DBLOGIN USERIDALIAS alias

  4. Create the Oracle GoldenGate checkpoint table that is used by the Replicat to deliver data to the source database.

    Example: ADD CHECKPOINTTABLE ogg.ggchkpt

    It is recommended that you use the same schema name as used in the GGSCHEMA parameter of the GLOBALS file.

  5. Enable supplemental logging for the newly created checkpoint table.

    Example: ADD TRANDATA ogg.ggchkpt

  6. Add the Replicat with the checkpoint table information.

    Example: ADD REPLICAT repe, EXTTRAIL north/ea,checkpointtable ogg.ggchkpt

  7. Configure the Extract with the EXCLUDEFILTERTABLE parameter, using the Replicat’s checkpoint table for the filtering table.

    TRANLOGOPTIONS EXCLUDEFILTERTABLE ogg.ggchkpt