29 CDC Capture Method Operational Considerations
This section provides information about the SQL Server CDC Capture options, features, and recommended settings.
Topics:
- Tuning SQL Server Change Data Capture
- Oracle GoldenGate CDC Object Versioning
- Valid and Invalid Extract Parameters for SQL Server Change Data Capture
- Details of the Oracle GoldenGate CDC Cleanup Process
- Changing from Classic Extract to a CDC Extract
Parent topic: Using Oracle GoldenGate for SQL Server
Tuning SQL Server Change Data Capture
-
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
, andOracleGGTranTables
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 change tracking tables in the format
OracleGG_object id_CT.
-
Stored procedures in the format
fetch_database name_object id
-
Stored procedures
OracleCDCExtract
,OracleGGCreateProcs
, andOracleGGCreateNextBatch
. -
After successfully completing the
ADD TRANDATA
command, GGSCI creates a table calledOracleGGVersion
under theGGSCHEMA
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 theOracleGGVersion
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
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.
-
Log in to the Database with
DBLOGIN
from GGSCI:DBLOGIN SOURCEDB dsn_name USERIDALIAS alias_name
-
Stop the Extract:
STOP EXTRACT extract_name
-
Delete the Extract:
DELETE EXTRACT extract_name
By logging in to the database, the DELETE EXTRACT
command removes
the entry from the OracleGGExtractCheckpoint
table, for that
specific Extract.
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 21c 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:
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 namesDELETE 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