The feature will only work in a reporting replication environment, that is, one way replication.
Here is some explination on this.
The feature change data capture or Change Tracking was introduced in Shareplex 7.6.2 to facilitate the auditing of changes to the source tables.
As of SharePlex 8.6 Change Tracking has been renamed to Change History and is no longer referred to as change data capture (CDC) since this is an industry standard term meaning capturing data as it is changed. CDC is accomplished by SharePlex replication, Oracle Streams and Golden Gate as well as many other replication solutions. The Change History feature will capture the after image of the data change and optionally the before image. The auditing information now called metadata is also an option. To enable capturing of the metadata, run the script proddir/util/add_change_tracking_columns.sql to add the default metadata columns to the target history table and the target command to add any optional metadata columns. This target command capability and the optional metadata columns are new with SharePlex 8.6. This has been made possible because the redologs contain most of the info needed for such auditing, with the exception of source’s user_id which is the only additional information Shareplex collects in its queues. For the feature to work, the structure of the target table needs to be modified to accommodate the additional columns to hold the changes. These columns are SHAREPLEX_SOURCE_TIME, SHAREPLEX_SOURCE_USERID, SHAREPLEX_SOURCE_OPERATION, etc. The columns are not present in the source table. They need to be created on the target table using the script add_change_tracking_columns.sql located in /proddir/util directory. This renders the feature infeasible in replication topologies other than one way replication, as the structure of the target table will now be different than that of the source table.
Additional Information:
For details on the feature, please refer to the following documentation and Shareplex Administrator Guide:
Change capture - Change History since SharePlex 8.6
As we have seen the need increase for a trail of table changes, SharePlex created an option to store the change data on a secondary server using log mining technology. The cost of extracting changes from the source database using log mining is negligible, making use of this functionality feasible. Since the change data is stored on a different database, all change data inquires can be offloaded from the production database.
This SharePlex feature allows the post process to record all changes made to a table along with other information about the change such as the time the change occurred, the SCN of the change and the user id of the user that made the change.
The original feature captured just the changed data value (after image). As of 8.6 the before image of an update can also be captured by setting the parameter SP_OPO_TRACK_PREIMAGE to U.
Post will then insert 2 rows in the history table for every change made to the tracked source table: one for the after image and one for the before image. Note that the before image of any LOB columns will not be captured since the redo log does not contain the before image of LOBs. It is also possible to capture the after image of deletes and before images of inserts which will include the key values with all other columns set to null. This parameter can be set to I, U, D or any combination of the se values.
As of 8.6 it is also possible to capture all the column data values when both the SP_OCT_USE_SUPP_KEYS and SP_OPO_TRACK_PREIMAGE parameters are enabled, and supplemental logging is enabled for all columns of the source tables that are to be tracked.
As of 8.6 it is possible to set rules and/or filters that will apply conditions on the columns to control whether a change is applied to the target history table. For example, to maintain change history only for inserts and updates, change the SP_OPO_TRACK_OPERATIONS parameter to U/I. The default is I/U/D which sends all DML operation types to the history records.
To set up change capture functionality:
• Set the parameter SP_OPO_TRACK_CHANGES on the target side. If necessary, this parameter can be set for a specific post process. This parameter has been deprecated, replaced by SP_OPO_TRACK OPERATIONS. The default setting for this parameter is I/U/D which will track insert, update and delete transactions.
• Use this syntax in the config file –
datasource:o.<sourceSID>
source owner.table !cdc:target_owner.table host@c.<targetSID>
• Create the target table with the same name and the same structure as the source table, except for constraints (Post will fail if there is a NOT NULL constraint on any of the columns). Omit all constraints on all columns. Target tables must not have PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, or CHECK constraints, nor can columns be defined with a DEFAULT value. Because this is a history of changes, a row may have the same image as another row that has the same key. Post does not perform integrity checks on a change-history table.
• Run the following script to add the default columns to the target table for the userid, time, etc:
optdir/util/add_change_tracking_columns.sql
The add_change_tracking_columns script will add the following default columns to the target table:
SHAREPLEX_SOURCE_TIME VARCHAR2(20)
SHAREPLEX_SOURCE_USERID NUMBER
SHAREPLEX_SOURCE_OPERATION VARCHAR2(20)
SHAREPLEX_SOURCE_SCN NUMBER
SHAREPLEX_SOURCE_ROWID ROWID
SHAREPLEX_SOURCE_TRANS VARCHAR2(60)
SHAREPLEX_OPERATION_SEQ NUMBER
The SHAREPLEX_SOURCE_TIME column will have the time that the operation was applied by Oracle on the source. The SHAREPLEX_SOURCE_USERID column will have the user id from the source of the user that applied the change. The SHAREPLEX_SOURCE_OPERATION column will be updated with one of five possible values:
INSERT
UPDATE
DELETE
COMMIT
TRUNCATE
ALTER TABLE to DROP COLUMN
Note that the COMMIT record will only be written to the target table if the parameter SP_OPO_TRACK_COMMITS parameter is set to 1. The parameter is set to 0 by default.
The SHAREPLEX_SOURCE_SCN column will have the source SCN at the time that the operation occurred. The SHAREPLEX_SOURCE_ROWID column will have the rowid of the row changed. The SHAREPLEX_SOURCE_TRANS column will have the transaction id with which the change is associated. The SHAREPLEX_OPERATION_SEQ column will have an incrementing value for every operation with the same transaction id.
As of 8.6, new metadata columns have been added –
• host – name or IP address of the source host (VARCHAR2)
• queue – name of the Shareplex queue (see example below) (VARCHAR2)
• source – user-defined source identifier (see example below) (VARCHAR2)
• changeid – unique sequential id of the operation (NUMBER)
Example of using the source and queue metadata columns – define specific SharePlex Post queues to line up with data from specific regions using the target command –
sp_ctrl> target target1 queue source718 set source east
sp_ctrl> target target1 queue source92 set source south
sp_ctrl> target target1 queue source101 set source west
sp_ctrl> target target1 queue source75 set source north
which will have any data coming through Post queue named source718 be related to the east region, etc.
Add the optional columns to the target history table, then enable capture of that metadata using the target command. For detailed use of the target command, consult the SharePlex Reference Guide.
sp_ctrl> target c.targSID set metadata host, queue, source, changeid
The actual table columns are divided logically into the key columns, and the non-key columns. For insert and update operations, SharePlex replicates the key columns and the non-key columns that were changed by the original DML. When post inserts a row into the target table it may insert a value into one or more of the table columns as follows:
Operation Key columns Non-key columns - updated
Operation | Key columns | Non-key columns - updated |
INSERT | X | X |
UPDATE | X | X |
DELETE | X | |
Note the following when using the change tracking feature:
• The SP_OPO_TRACK_CHANGES parameter takes effect when the post process starts (deprecated, replaced by SP_OPO_TRACK_OPERATIONS)
• The target table must not have PK, UK, not null, check, or default value constraints
• Only ALTER TABLE ADD and ALTER TABLE MODIFY DDL will be applied to the target table
• COMMIT operations will only be applied to the target table if the SP_OPO_TRACK_COMMITS parameter is set
• Changes made to UDT or VARRAY columns will not be recorded on the target
• If a DBMS_LOB operation is used to change a part of the LOB column, the value stored for that column on the target will not be the complete LOB column
• Compare will fail with an unsupported message
• Do NOT issue the copy command as it does not detect that the target table is a change tracking table and will copy the source to the target
