IBM Support

How do I start replication after Oracle DDL changes?

How To


Summary

This document describes the steps to follow to resume replication following a DDL change that results in a table structure change.

CDC maintains and depends on metadata describing source and target tables and columns being replicated. When DDL changes occur which results in the table representation in the log to differ versus the image currently in the CDC metadata (usually due to a change to the source table structures), the metadata no longer contains the correct information with which to read source database log records to capture change data during mirroring.

For that reason, following a DDL change that results in table structural changes, some action is required to update the metadata before replication can be performed or resumed. This document describes how to update table definitions to accommodate a DDL change so that replication can proceed.

Objective

The purpose is to enable CDC subscriptions to start mirroring again following a planned or unplanned DDL change on a source Oracle Database.

Steps

The full steps are described in 

Planned DDL Steps

For a planned DDL change, first perform the following three steps (A,B,C), and then perform the Unplanned DDL steps.
See the full steps in the attached document.
  • Step A: For a planned DDL execution, end all DML activity on all of the tables being altered.
  • Step B: Ensure there are no open transactions involving the table being altered.
  • Step C: End replication for all running subscriptions in the instance. Stop all subscriptions at head of log using the scheduled end-controlled shutdown.
  • Step D: Follow the ‘Unplanned DDL Steps’ to complete the procedure

Unplanned DDL Steps

If the subscription stops because CDC detected a DDL change that altered the structure of the table, it will start a normal (controlled) shutdown. The shutdown must be allowed to complete or a refresh might be required.
Note: If a table is shared in multiple subscriptions and at least one of those subscriptions is using a private scraper, then wait for the private scraper to hit the same DDL detected error before following the next steps (step 2 onwards). If you do not, you must refresh the table for those subscriptions that used a private scraper.
  • Step 1. For all the target tables affected, apply DDL changes before the next step
  • Step 2. On Source, update all table definitions by using dmreaddtable
  • Step 3. On Source, describe the tables by using dmdescribe
  • Step 4. On Target, update table definitions for target (only if DDL changes occur on the target side)
  • Step 5. End replication for all subscriptions within the instance, and clear the staging store by using dmclearstagingstore
  • Step 6. Start mirroring for all subscriptions

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSTRGZ","label":"InfoSphere Data Replication"},"ARM Category":[{"code":"a8m50000000L3GOAA0","label":"CDC->Engine Issues->Source Engine Issues->DDL Related"}],"ARM Case Number":"TS003405122","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Product Synonym

CDC;CDD;IDR;IIDR

Document Information

Modified date:
14 May 2020

UID

ibm16209511

Page Feedback