This chapter contains instructions for how to configure SharePlex to maintain a change-history target. SharePlex enables you to maintain this history, while also replicating the same data set to maintain up-to-date targets.
This chapter contains instructions for how to configure SharePlex to maintain a change-history target. SharePlex enables you to maintain this history, while also replicating the same data set to maintain up-to-date targets.
A change history target differs from a replication target in that a change history target maintains a record of every change that occurs to a source object or objects, rather than simply maintaining a mirror of the current state of the source data. While regular replication overlays current target data with change data, change history inserts the change data to the target as a new record. The old data is preserved as a step-by-step record of change. The historical data can be queried and analyzed for such purposes as data mining or resolving customer disputes.
By using SharePlex to maintain change history on a secondary server, you can offload the overhead from the production database. Such overhead includes the SQL work of adding the history rows, the extra storage of those rows, and the query activity against the historical data.
Note: File, JMS, and Kafka targets support change history by default, because every source change is written as a separate XML record. There is no overlaying of old data with new. Metadata that is supported for these targets is included automatically when Post writes the XML. For a list of supported metadata, see the target command in the SharePlex Reference Guide.
This replication strategy supports the following:
Oracle
Oracle target
SharePlex supports adding a change history row for these operations:
Note:Post does not drop the column from the table, but does create a change history row.
NOTE: Post adds a column to the table, but does not create a change history row.
Note: SharePlex replicates tables with the UDT fields in the base type ONLY. In case of columns containing multiple subtypes, replication is applicable only for base type fields.
In a change history configuration, each target table serve as a history table that records every change made to the source data as a continuous series of rows.
Each new change row that SharePlex inserts includes the following:
SharePlex can be configured to include the before image of update operations in the history or to control which operation types are included in the history. For example, you could include only updates and deletes.
To configure change history, you use special syntax in the SharePlex configuration file and, optionally, configure filter rules and other attributes to customize the history to your needs.
Perform the following steps to create a change-history configuration file:
Create the Oracle target history tables with the same name and structure as the source tables whose history they will track, but omit all constraints on all columns.
Important: The Oracle 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 target.
On the source system, create a configuration file using the following syntax. For more information about how to create a configuration file, see Configure Data Replication.
datasource_specification | ||
src_owner.table | !cdc:tgt_owner.table | host@c.SID |
where:
(Optional) Run the following script on the target tables to add default metadata columns with their default names. Post automatically populates the default metadata columns without any additional configuration. You can customize the script to meet your requirements.
product_dir/util/add_change_tracking_columns.sql
Notes:
|
This section describes how you can customize the SharePlex change history configuration.
You can use the target command with the colname option to customize the name of any target metadata column. For instructions, see the SharePlex Reference Guide.
You can include the before image of updates in the target table by setting the SP_OPO_TRACK_PREIMAGE parameter to U. This parameter causes Post to insert two rows to the target table for every change made to the tracked source table: one for the after image and one for the before image. The before image is composed of the key values plus the before values of the columns that were changed, unless the SP_OCT_USE_SUPP_KEYS parameter is used.
When before images are enabled, the SHAREPLEX_SOURCE_OPERATION column values for the two records will be:
UPDATE BEFORE
UPDATE AFTER
Note: The before row will not include the before image of any LOB columns, because the redo log does not contain the before image of LOBs.
You can override the global setting of SP_OPO_TRACK_PREIMAGE at the table level by using the set cdc preimage option of the target command.
For more information about SP_OPO_TRACK_PREIMAGE and the target command, see the SharePlex Reference Guide.
This option is valid for Oracle data only. To include the values of all table columns in each target history record, rather than only the changed columns, configure the following:
Turn on supplemental logging for all columns of the source tables that are being tracked. For example:
Alter table emp ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Note: When both SP_OCT_USE_SUPP_KEYS and SP_OPO_TRACK_PREIMAGE are enabled, the before image includes all column values as they were before the change.
To disable the change history of a DML operation type, set the SP_OPO_TRACK_OPERATIONS parameter to the appropriate value or values. Separate values with a slash (/). For example, to maintain change history only for inserts and updates, change the parameter to I/U.The default is I/U/D which sends all DML operation types to the history records.
You can use the set rule option of the target command to apply conditions on columns to control whether a change is applied to the target history table. For example, you can specify a rule that if column 1 and column 3 are changed, then apply the operation and discard any changes that apply to other columns. For instructions, see the SharePlex Reference Guide.
By default, the COMMIT record is not included in the history tables. To configure Post to insert a row for every COMMIT, set the SP_OPO_TRACK_COMMITS parameter to 1.
This chapter contains instructions for configuring SharePlex to support different replication objectives. Production implementations can vary widely from basic configurations with one source and target, to multiple instances of SharePlex with named queues,multiple targets, partitioned data, and more.
It is difficult to foresee and document every possible way that an organization may want to deploy SharePlex. The goal of this documentation is to present instructions for setting up the basic deployment types in a way that is clear enough for you to be able to combine them and expand upon them to suit your needs. Additional deployment assistance is available through our Professional Services organization.
Configure Replication to share or distribute data
Configure Replication to maintain a central data store
Configure peer-to-peer replication
Configure PostgreSQL to PostgreSQL Replication
Configure PostgreSQL to Oracle replication
Develop conflict resolution routines
User defined conflict resolution routines for Oracle to Oracle
User defined conflict resolution routines for PostgreSQL to PostgreSQL
User defined conflict resolution routines for PostgreSQL to Oracle
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Términos de uso Privacidad Cookie Preference Center