CDC Capture Method Operational Considerations

Learn about the SQL Server CDC Capture options, features, and recommended settings.

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 tables, cdc.lsn_time_mapping table, and OracleGGTranTables table are 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’,
```

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, Oracle GoldenGate creates a table called OracleGGVersion under the GGSCHEMA specified in the GLOBALS file, if it does not already exist.

Next, Oracle GoldenGate 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, 6. The following versions(s) were found: 3. Rerun ADD TRANDATA for all tables previously enabled, including heartbeat, heartbeat seed, and filter tables.

Supported and Unsupported Extract Parameters for SQL Server Change Data Capture

This section describes parameters used for the CDC Capture method.

The following table lists the supported and unsupported parameters.

Supported Parameters	Unsupported Parameters
`TRANLOGOPTIONS`	`TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT/NOMANAGESECONDARYTRUNCATIONPOINT/ACTIVESECONDARYTRUNCATIONPOINT`
`ALWAYSONREADONLYROUTING`	-
`TRANLOGOPTIONS LOB_CHUNK_SIZE`	-
`TRANLOGOPTIONS MANAGECDCCLEANUP/NOMANAGECDCCLEANUPTRANLOGOPTIONS`	-
`TRANLOGOPTIONS QUERYTIMEOUT`	-
`TRANLOGOPTIONS TRANCOUNT`	-

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.

Details of the Oracle GoldenGate CDC Cleanup Process

The Oracle GoldenGate Purge Change Data task is required for a CDC Extract by default, since Extract defaults to TRANLOGOPTIONS MANAGECDCCLEANUP.

There should only be one purge task for each database enabled for CDC Capture, and you must create the task using the steps mentioned in the Prepare the Database for Oracle GoldenGate section of this document.

Modifying the Oracle GoldenGate Purge Change Data Task

The default purge task frequency schedule, and data retention period for the Oracle GoldenGate Purge Change Data task is to run every 10 minutes, with a data retention policy of 3 days or 1 hour, depending on the version of Oracle GoldenGate installed.

For customer specific requirements, it may be necessary to adjust the retention period (Keep Rule option) and the task run-time schedule (Purge Frequency option).

The Keep Rule option determines in hours or days, the amount of CDC staging data to keep in the source database. Depending on the version of Oracle GoldenGate installed, 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. Typically, there would be no need to reposition an existing Extract back to an earlier point in time, so it is recommended to use the newer default setting of 1 hour unless there is a specific case that requires more staging data to remain in the database. Note that though if you change this value from a higher retention period to a very short retention period, the next time the task schedule runs, it could consume a lot of transaction log space and system overhead. So it is recommended to slowly decrease the Keep Rule value over time, until you reach the desired ending value.

The Purge Frequency represents how often the task runs, with a default 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.

To modify an existing Purge Change Data task, navigate to the Configuration from the menu on the left of the Administration Service, to open the Configuration page.

Click Tasks from the Configuration page to open the Tasks page.
Click Purge Change Data from the Tasks page.
Click the Alter Task icon next to an existing task.
Modify the values of Keep Rule and Purge Frequency options as required.
Click Submit to save the changes.

Deleting the Oracle GoldenGate Purge Change Data Task

Deleting a Purge Change Data task for a database should only be done if there are no Extracts configured to capture against the specific database.

To delete an existing Purge Change Data task, navigate to the Configuration option from the menu on the left of the Administration Service, to open the Configuration page.

Click Tasks from the Configuration page to open the Tasks page.
Click Purge Change Data from the Tasks page.
Click the Delete Task icon next to the task to be removed.

Updating 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 23ai 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 Prepare the Database for Oracle GoldenGate to re-enable supplemental logging and other necessary components, and re-add the heartbeat table.