This section provides information about the SQL Server CDC Capture options, features, and recommended settings.
Parent topic: Using Oracle GoldenGate with SQL Server
The SQL Server Change Data Capture job collects data from the SQL Server transaction log and loads it into the CDC 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
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 fpr Windows and UNIX.
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. Therefore secondary truncation point management is not handled by the Extract, and for the Change Data Capture Extract, these parameters are not valid.
Parent topic: CDC Capture Method Operational Considerations
The Oracle GoldenGate CDC Cleanup job is required for a CDC Extract by default, since Extract defaults to TRANLOGOPTIONS MANAGECDCCLEANUP, and 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.
There are other options to the utility as well, and those will be discussed below.
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 binary.
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 via GGSCI: DELETE EXTRACT <extname>
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 file, providing the following variable values:
ogg_cdc_cleanup_setup.bat deleteExtCheckpoint <userid> <password> <databasename> <servername\instancename> <schema>
Example: ogg_cdc_cleanup_setup.bat deleteExtCheckoint 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
If you plan to change from using a Classic Extract from Oracle GoldenGate 12.3 or earlier, to a 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, and the information provide here does not cover all requirements and is only offered as general requirements regarding supplemental logging:
1. Ensure that the Classic Extract has processed all remaining data in the logs and can be gracefully stopped.
1. 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
2. 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.*
3. Delete any heartbeat table entries if one was installed.
DELETE HEARTBEATTABLE
4. 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