If you add or drop columns in an Oracle source table, you must create a new capture registration. PowerExchange generates an associated extraction map by default. Use the generated extraction map instead of creating a user-defined extraction map. The PWX CDC Reader on the Oracle CDC V2 Connector system will retrieve information from the new generated extraction map.
To switch to the new table definition and preserve access to previously captured data, you must perform several steps in both the PowerExchange and Oracle CDC Connector environments.
Note: You do not need to perform these steps if you selectively capture change data for a subset of columns, and the DDL changes do not affect any of these columns.
1Stop data change activity on the Oracle source table.
2Verify that any change data that was captured under the current table definition has completed Oracle CDC Connector extraction processing. Then stop all mapping tasks that extract change data for the table.
3Shut down the PowerExchange Logger for Linux, UNIX, and Windows.
4In the PowerExchange Navigator, open the original capture registration and set its status to History.
5Drop the supplemental log group for the table.
6Add or drop columns in the source table.
7In the PowerExchange Navigator, create a new capture registration for the table that reflects the column changes. Include the following settings:
- In the Condense list, select Part.
- In the Supplement Log Group Name box, enter a name for the supplemental log group that must be created for the table. The PowerExchange Navigator generates DDL for creating the supplemental log group. If you select Execute DDL now, the PowerExchange Navigator runs the DDL when you complete the registration. If you do not have the authority to run the DDL, you can ask your DBA to run it.
- In the Status list, select Active.
8Warm start the PowerExchange Logger.
9Alter the target table to reflect the source table changes, if necessary.
10In Data Integration, for each mapping that includes the source table, perform the following steps:
- Select the Source transformation. Under Source Properties, either click the Synchronize button on the Fields tab or select the table again in the Object field on the Source tab. Either action causes the PWX CDC Metadata Adapter to retrieve the updated extraction-map metadata.
- If you changed the target table, select the Target transformation. Under Target Properties, click the Synchronize button on the Target Fields tab or select the table again in the Object field on the Target tab.
- Edit the field mapping to reflect the added or removed columns.
11If necessary, rematerialize the target tables.
12In the mapping task, set a time-based restart point that coincides with the time of target rematerialization or let the mapping task restart from the last checkpoint that was recorded in the state table on the target.