Change Data Capture with SharePlex

                                                                                                  

SharePlex replication can conveniently be used to maintain a change history target. Another industry jargon for change history is Change Data Capture or CDC. The CDC target maintains historical data on target as opposed to a copy of the source in case of a target in a regular replication.  The CDC target is typically used for data mining.

SharePlex gathers change data from source database to populate a change history target by querying redo logs. This is made possible with the advent of supplemental logging. Supplemental logging writes extra information in redo logs which facilitates capturing the change data. At the very least, the minimal database level supplemental logging needs to be enabled at the source. Though supplemental logging can result in extra overhead by way of writing additional info in redo logs, the benefits in terms of change history target far outweigh such costs. In addition, supplemental logging greatly aids regular replication.  

Here are some of the salient features of SharePlex change history:

  • Currently, SharePlex only supports Oracle targets when configuring change history.
  • Horizontal and Vertical Partitioning are supported.
  • It is possible to replicate changes from the source table to both regular and change history targets but the same target cannot serve as a regular and a CDC target unless the source table is replicated to both a regular table and a differently named CDC table on that target. Due to limited ALTER TABLE support for CDC, it is advisable to create separate SharePlex instances (or ports) for CDC and regular replication in such configuration if full DDL support is envisaged for tables in regular replication.
  • Besides the columns of the replicated table, it is possible to include additional columns termed as metadata columns in the change history target. By default, SharePlex offers some metadata columns such as USER ID, name of source system, etc. But it is possible to include one’s own metadata columns.
  • By default, a change history target only provides the after image column values of changed columns for an UPDATE. But it is possible to change this default behavior and include both before and after image for changed columns.  In both scenarios, the key value is included. Likewise, it is also possible to include all columns and not just the updated columns but this will require enabling supplemental logging for all columns of the table and it comes with additional overheads in supplemental logging.
  • SharePlex also allows disabling a particular type(s) of DML operation for change history purpose. By default all DMLs (INSERT, UPDATE or DELETE) are replicated for change history.

Please refer to SharePlex technical documentation for more details on setting up a CDC replication.

About the Author
Shailendra.Jhalani
Shailendra Jhalani is a Senior Technical Support Engineer for SharePlex Support group at Quest Software. He joined Quest Software as Technical Support Engineer in 2001. He is an Oracle Certified DBA. ...