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.

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