CDC Capture Method Operational Considerations

24 CDC Capture Method Operational Considerations

This section provides information about the SQL Server CDC Capture options, features, and recommended settings.

Topics:

Parent topic: Using Oracle GoldenGate for SQL Server

Tuning SQL Server Change Data Capture

The following information is useful in improving the capture performance of the Extract.

Ensure that Auto Create Statistics and Auto Update Statistics are enabled for the database. Maintaining statistics on the cdc.OracleGG_#####_CT , cdc.lsn_time_mapping, and OracleGGTranTables table is crucial to the performance and latency of the Extract.
The SQL Server Change Data Capture job collects data from the SQL Server transaction log and loads it into the Change Data Capture staging tables within the database.

As part of the job that is created, there are several available tuning parameters that can be used, and information on how to best tune the job can be found in the following article: https://technet.microsoft.com/en-us/library/dd266396(v=sql.100).aspx

As a general recommendation, you should change the SQL Server Change Data Capture Job polling interval from the default of 5 seconds to 1 second.

To change the default polling interval of the CDC Capture job, execute the following queries against the database:
```
EXEC [sys].[sp_cdc_change_job]
@job_type = N'capture’,
@pollinginterval = 1,
GO,
--stops cdc job
EXEC [sys].[sp_cdc_stop_job],
@job_type = N'capture’,
GO,
--restarts cdc job for new polling interval to take affect
EXEC [sys].[sp_cdc_start_job],
@job_type = N'capture’,
```

Parent topic: CDC Capture Method Operational Considerations

Oracle GoldenGate CDC Object Versioning

Oracle GoldenGate provides a version tracking subsystem to track the CDC objects that are created by Oracle GoldenGate when enabling supplemental logging. These objects are:

Oracle GoldenGate change tracking tables in the format OracleGG_object id_CT.
Stored procedures in the format fetch_database name_object id
Stored procedures OracleCDCExtract, OracleGGCreateProcs, and OracleGGCreateNextBatch.
After successfully completing the ADD TRANDATA command, GGSCI creates a table called OracleGGVersion under the GGSCHEMA specified in the GLOBALS file, if it does not already exist.

Next, GGSCI inserts a record into the table that tracks the start and end time of the TRANDATA session. When Extract starts up, it checks for consistency between itself and the Oracle GoldenGate CDC objects by comparing its internal version number with the version numbers found in the OracleGGVersion table. If it finds that the version numbers do not match, it abends with a message similar to the following:
ERROR OGG-05337 The Oracle GoldenGate CDC object versions on database, source, are not consistent with the expected version, 2. The following versions(s) were found: 1. Rerun ADD TRANDATA for all tables previously enabled, including heartbeat, heartbeat seed, and filter tables.

Parent topic: CDC Capture Method Operational Considerations

Valid and Invalid Extract Parameters for SQL Server Change Data Capture

This section describes parameters used for the CDC Capture method. For more information about supported and unsupported parameters for the CDC Capture method, review Reference for Oracle GoldenGate.

TRANLOGOPTIONS LOB_CHUNK_SIZE

The Extract parameter LOB_CHUNK_SIZE is added for the CDC Capture method to support large objects. If you have huge LOB data sizes, then you can adjust the LOB_CHUNK_SIZE from the default of 4000 bytes, to a higher value up to 65535 bytes, so that the fetch size is increased, reducing the trips needed to fetch the entire LOB.

Example: TRANLOGOPTIONS LOB_CHUNK_SIZE 8000

TRANLOGOPTIONS MANAGECDCCLEANUP/NOMANAGECDCCLEANUP

The Extract parameter MANAGECDCCLEANUP/NOMANAGECDCCLEANUP is used by the CDC Capture method to instruct the Extract on whether or not to maintain recovery checkpoint data in the Oracle GoldenGate CDC Cleanup job. The default value is MANAGECDCCLEANUP and it doesn’t have to be explicitly listed in the Extract. However, it does require creating the Oracle GoldenGate CDC Cleanup job prior to starting the Extract. MANAGECDCCLEANUP should be used for all production environments, where NOMANAGECDCCLEANUP may be used for temporary and testing implementations as needed.

Example: TRANLOGOPTIONS MANAGECDCCLEANUP

TRANLOGOPTIONS EXCLUDEUSER/EXCLUDETRANS

The SQL Server CDC Capture job does not capture user information or transaction names associated with a transaction, and as this information is not logged in the CDC staging tables, Extract has no method of excluding DML from a specific user or DML of a specific transaction name. The EXCLUDEUSER and EXCLUDETRANS parameters are therefore not valid for the CDC Capture process.

TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT/NOMANAGESECONDARYTRUNCATIONPOINT/ACTIVESECONDARYTRUNCATIONPOINT

The SQL Server Change Data Capture job is the only process that captures data from the transaction log when using the Oracle GoldenGate CDC Capture method. The secondary truncation point management is not handled by the Extract, and for the Change Data Capture Extract, these parameters are not valid.

TRANLOGOPTIONS ALWAYSONREADONLYROUTING

The ALWAYSONREADONLYROUTING parameter allows Extract for SQL Server to route its read-only processing to an available read-intent Secondary when connected to an Always On availability group listener.

TRANLOGOPTIONS QUERYTIMEOUT

Specifies how long queries to SQL Server will wait for results before reporting a timeout error message. This option takes an integer value to represent the number of seconds. The default query timeout value is 300 seconds (5 minutes). The minimum value is 0 seconds (infinite timeout). The maximum is 2147483645 seconds.

TRANLOGOPTIONS TRANCOUNT

Allows adjustment of the number of transactions processed per each call by Extract to pull data from the SQL Server change data capture staging tables. Based on your transaction workload, adjusting this value may improve capture rate throughput, although not all workloads will be positively impacted. The minimum value is 1, maximum is 100, and the default is 10.

Parent topic: CDC Capture Method Operational Considerations

Details of the Oracle GoldenGate CDC Cleanup Process

The Oracle GoldenGate CDC Cleanup job is required for a CDC Extract by default, since Extract defaults to TRANLOGOPTIONS MANAGECDCCLEANUP. It is installed from a Windows batch file (ogg_cdc_cleanup_setup.bat), which uses sqlcmd to connect to the source SQL Server database and create the necessary objects and job.

There should be one job for each database enabled for CDC Capture, and you must create the job and objects following the steps mentioned in the Preparing the Database for Oracle GoldenGate — CDC Capture section of this document.

Additional options for the utility are discussed in the following sections.

The steps below require a SQL Server authenticated database user who is a member of the SQL Server System Administrators (sysadmin ) role. Windows authentication is not supported for the .bat batch file.

Removing an Extract from the Database

When the Oracle GoldenGate CDC Cleanup object tables exist, each CDC Extract that is started against that database will create an entry in the OracleGGExtractCheckpoint table. This entry tracks a particular Extract’s point in time recovery checkpoint, which is used as the cutoff LSN for the Oracle GoldenGate CDC cleanup tasks. If there are multiple Extracts running, each logging more recent recovery checkpoints in the table, but one Extract has been removed from the system without removing its entry into the OracleGGExtractCheckpoint table, then no data will be purged newer than that deleted Extract’s old recovery checkpoint for all of the CDC staging tables. So when deleting an Extract from the database, follow the steps below to remove the Extract from the OracleGGExtractCheckpoint table if more than one Extract is running against the database.

1. Ensure that the Extract has been deleted using DELETE EXTRACT extract_name

2. On the source system, open a command prompt and change to the Oracle GoldenGate installation folder.

3. Run the ogg_cdc_cleanup_setup.bat/.sh file, providing the following variable values:

ogg_cdc_cleanup_setup.bat deleteExtCheckpoint userid password databasename servername\instancename schema

Example: ogg_cdc_cleanup_setup.bat deleteExtCheckpoint ggsuser ggspword db1 server1\inst1 ogg

4. You will be prompted to enter the name of the Extract that is to be removed, and once entered, press the Enter/Return key to continue.

Modifying the Oracle GoldenGate CDC Cleanup Job

The default schedule, retention period and operation batch size for the Oracle GoldenGate CDC Cleanup job of a database is to run every 10 minutes, with a data retention policy of 72 hours (listed as 4320 minutes), purging in batches of 500 records per transaction until the retention policy is meet, not to exceed the recovery checkpoint data of the Extract.

For variations in customer environments, or change data table data retention requirements, it may be necessary to adjust these properties to increase the purge batch size or to adjust retention policies and the job run-time schedule.

To adjust the job execution frequency, manually modify the schedule for the OracleGGCleanup_dbname_Job job within SQL Server Agent. If you need to adjust the retention period or purge batch size, you must manually edit the job step for the OracleGGCleanup_dbname_Job job within SQL Server Agent. The job step passes two parameters to the cleanup stored procedure, and you can modify the value for @retention_minutes to adjust the data retention policy as needed, or modify the @threshold value to increase or decrease the purge batch size. In high transactional environments, it may be necessary to increase the @threshold value to a number such as 10000. Monitoring the amount of time that it takes for the job to run within each cycle can be used to determine effective @threshold values.

Deleting the Oracle GoldenGate CDC Cleanup Job

If you no longer require the Oracle GoldenGate CDC Cleanup job and associated objects and need to remove them, perform the following steps:

1. Open a command prompt and change to the Oracle GoldenGate installation folder.

2. Run the ogg_cdc_cleanup_setup.bat file, providing the following variable values:

ogg_cdc_cleanup_setup.bat dropJob userid password databasename servername\instancename schema

Example: ogg_cdc_cleanup_setup.bat dropJob ggsuser ggspword db1 server1\inst1 ogg

Parent topic: CDC Capture Method Operational Considerations

Changing from Classic Extract to a CDC Extract

If you plan to change from using a Classic Extract from Oracle GoldenGate 12c (12.3.0.1) or earlier, to an Oracle GoldenGate 19c CDC Extract, then you must remove the supplemental logging that was implemented using the Classic Extract installation method, and re-enable supplemental logging using the CDC Extract installation binaries, as the calls to enable TRANDATA are different between the two versions, and the implementation of TRANDATA for Classic Extract is not supported by the CDC Extract.

Follow these general guidelines to remove and re-enable supplemental logging. Special consideration and planning should be involved if migrating from Classic to CDC Extract in a production system. The information provided here does not cover all requirements and is only offered as general requirements regarding supplemental logging:

Ensure that the Classic Extract has processed all remaining data in the logs and can be gracefully stopped.
Do one of the following, depending on how Extract was running in relation to other replication or CDC components:
- If Extract was not running concurrently with SQL Server transactional replication or a non-Oracle CDC configuration on the same database, open a query session in Management Studio and issue the following statement against the source database to disable and delete any CDC or replication components, and to clear the secondary truncation point.
  
  EXEC sys.sp_cdc_disable_db
- If Extract was running concurrently with SQL Server transactional replication or a non-Oracle CDC configuration on the same database, run GGSCI from the Classic Extract’s installation folder, login to the source database with the DBLOGIN, and then issue the following command for each table that is in the Extract configuration. You can use a wildcard to specify multiple table names
  
  DELETE TRANDATA owner.table
  
  DELETE TRANDATA owner.*
Delete any heartbeat table entries if one was installed.

DELETE HEARTBEATTABLE
Using the Oracle GoldenGate CDC Extract installation binaries, follow the steps listed in Preparing the Database for Oracle GoldenGate — CDC Capture to re-enable supplemental logging and other necessary components, and re-add the heartbeat table.

Parent topic: CDC Capture Method Operational Considerations

Restoring a Source Database Keeping CDC Data

When restoring a SQL Server database that has been enabled with CDC and you want to keep the existing CDC staging data that has already accumulated in the database, as well as the CDC settings, you must specify the KEEP_CDC option with the RESTORE statement.

This requirement is only if restoring the database to a new instance, or to the same instance but with a different database name. If you are restoring the original database on the instance, then the option is not required.

For further requirements and understanding, review the following document link from Microsoft:

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-arguments-transact-sql?view=sql-server-ver15

Additionally, you must manually recreate the CDC Capture job and the Oracle GoldenGate CDC Cleanup job.

Parent topic: CDC Capture Method Operational Considerations