Enabling Bi-Directional Loop Detection

Loop detection is a requirement for bi-directional implementations of Oracle GoldenGate, so that an Extract for one source database does not recapture transactions sent by a Replicat from another source database.

With the CDC Extract capture method, by default, any transaction committed by a Replicat into a database where an Extract is configured, will recapture that transaction from the Replicat as long as supplemental logging is enabled for those tables that the Replicat is delivering to.

In order to ignore recapturing transactions that are applied by a Replicat, you must use the TRANLOGOPTIONS FILTERTABLE parameter for the CDC Extract. The table used as the filtering table will be the Oracle GoldenGate checkpoint table that you must create for the Replicat.

To create a Filter Table and enable Supplemental Logging:

The steps below require a database user who is a member of the SQL Server System Administrators (sysadmin) role.

  1. On the source system, run GGSCI

  2. Issue the following command to log into the database.

    DBLOGIN SOURCEDB DSN [,{USERID user, PASSWORD password | USERIDALIAS alias}]

    In the preceding example, the SOURCEDB DSN is the name of the SQL Server data source. The USERID user is the database login and PASSWORD password is the password that is required if the data source connects through SQL Server authentication. Alternatively, USERIDALIAS alias is the alias for the credentials if they are stored in a credentials store. If using DBLOGIN with a DSN that is using Integrated Windows authentication, the connection to the database for the GGSCI session is that of the user running GGSCI. In order to issue ADD TRANDATA or DELETE TRANDATA, this user must be a member of the SQL Server sysadmin server role.

  3. Create the Oracle GoldenGate checkpoint table that is used by the Replicat to deliver data to the source database.

    Example: ADD CHECKPOINTTABLE ogg.ggchkpt

    It is recommended that you use the same schema name as used in the GGSCHEMA parameter of the GLOBALS file.

  4. Enable supplemental logging for the newly created checkpoint table.

    Example: ADD TRANDATA ogg.ggchkpt

  5. Add the Replicat with the checkpoint table information.

    Example: ADD REPLICAT reptgt1, EXTTRAIL ./dirdat/e2,checkpointtable ogg.ggchkpt

  6. Configure the Extract with the IGNOREREPLICATES (on by default) and FILTERTABLE parameters, using the Replicat’s checkpoint table for the filtering table.

    TRANLOGOPTIONS IGNOREREPLICATES

    TRANLOGOPTIONS FILTERTABLE ogg.ggchkpt