Chat now with support
Chat with Support

SharePlex 10.2 - Administration Guide

About this Guide Conventions used in this guide Revision History Overview of SharePlex Run SharePlex Run multiple instances of SharePlex Execute commands in sp_ctrl Set SharePlex parameters Configure data replication Configure replication to and from a container database Configure named queues Configure partitioned replication Configure replication to a change history target Configure a replication strategy Configure DDL replication Configure error handling Configure data transformation Configure security features Start replication on your production systems Monitor SharePlex Prevent and solve replication problems Repair out-of-sync data Tune the Capture process Tune the Post process Recover replication after Oracle failover Make changes to an active replication environment Apply an Oracle application patch or upgrade Back up Oracle data on the source or target Troubleshooting Tips Appendix A: Peer-To-Peer Diagram Appendix B: SharePlex environment variables

Overview of the change-history target

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.

Capabilities

This replication strategy supports the following:

  • Identical or different source and target names
  • Use of vertically partitioned replication
  • Use of horizontally partitioned replication
  • Use of named export and post queues
  • Combination of regular replication and change-history replication of the same source object(s)

Supported sources

Oracle

Supported targets

Oracle target

Operations supported

SharePlex supports adding a change history row for these operations:

  • INSERT
  • UPDATE
  • DELETE
  • TRUNCATE
  • ALTER TABLE to DROP COLUMN (Note:Post does not drop the column from the table, but does create a change history row.)
  • ALTER TABLE to ADD COLUMN (NOTE: Post adds a column to the table, but does not create a change history row.)
  • ALTER TABLE to MODIFY the data type of a column

Operations not supported

  • Changes made to UDT or VARRAY columns
    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.
  • DBMS_LOB operation that is used to change a part of a LOB column (The value stored for that column on the target will not be the complete LOB column.)

How SharePlex maintains change history

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:

  • the values of the key columns
  • the after image of the changed columns. For inserts and updates, the after image consists of the new values of the columns that were changed (or added in the case of an insert). For deletes, the after image consists of the key values plus the other columns set to null.
  • (optionally) a set of metadata values that provide context for the change. For example, there is metadata that captures the userid of the user who made the change and the source system where the change was made (useful when change data is tracked from multiple databases).

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.

Configure change history

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.

Create a change-history configuration file

  1. Make certain that SharePlex is installed and the system is prepared according to the instructions in the SharePlex Installation Guide.

  2. 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.

  3. Disable triggers on the target tables.
  4. Allow no DML or DDL to be performed on the target tables except by SharePlex.

  5. 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:

    • Datasource:o.SID is the ORACLE_SID of the source Oracle instance.
    • src_owner.table is the fully qualified name of a source object (owner.object) or a wildcarded specification.
    • !cdc: identifies the target as a change-history table.
    • tgt_owner.table is the fully qualified name of the target history table or a wildcarded specification.
    • host is the target system.
    • c.SID specifies the target Oracle instance.

  6. (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:

    • The script only adds the default columns. To add optional columns, or to change a column name, use the target command to add them to the Post configuration. For a list of default and optional metadata columns, see the target command in the SharePlex Reference Guide.
    • The default columns are automatically added to new tables that are added to the SharePlex change history configuration.

Additional change history configuration options

This section describes how you can customize the SharePlex change history configuration.

Customize column names

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.

Add the before image to each change row

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.

Include all columns of an operation in the history

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:

  1. 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;

  2. Set the SP_OCT_USE_SUPP_KEYS parameter to 1.
  3. Set the SP_OCT_INCLUDE_UNCHANGED_COL to 1.

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.

Disable change history of an operation type

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.

Set rules and filters

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.

Include COMMITs

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.

Configure a replication strategy

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.

Contents

Configure Replication to share or distribute data

These instructions show you how to set up SharePlex for the purpose of sharing or distributing data from one source system to one or more target systems. This strategy supports business requirements such as the following:

  • reporting to support real-time decision making
  • data sharing to support research and transparency requirements
  • data integration throughout an enterprise
  • customer service inquiries and other query-intensive applications
  • data auditing and archiving

Supported sources

Oracle

Supported targets

All

Capabilities

This replication strategy supports the following:

  • Replication to one or more target systems
  • Replication between databases on the same system
  • Replication between schemas in the same database (Oracle) 
  • Identical or different source and target names
  • Use of vertically partitioned replication
  • Use of horizontally partitioned replication
  • Use of named export and post queues
  • Use of transformation

Requirements

  • Prepare the system, install SharePlex, and configure database accounts according to the instructions in the SharePlex Installation Guide.
  • No DML or DDL should be performed on the target tables except by SharePlex. Tables on the target system that are outside the replication configuration can have DML and DDL operations without affecting replication.

  • If sequences are unnecessary on the target system, do not replicate them. It can slow down replication. Even if a sequence is used to generate keys in a source table, the sequence values are part of the key columns when the replicated rows are inserted on the target system. The sequence itself does not have to be replicated.

Important! These instructions assume you have a full understanding of SharePlex configuration files. They use abbreviated representations of important syntax elements.

For more information, see Configure data replication.

Conventions used in the syntax

In the configuration syntax in this topic, the placeholders represent the following:

  • source_specification[n] is the fully qualified name of a source object (owner.object) or a wildcarded specification.
  • target_specification[n] is the fully qualified name of a target object or a wildcarded specification.
  • host is the name of a system where SharePlex runs. Different systems are identified by appending a letter to the names, like hostB.
  • db is a database specification. The database specification consists of either o. or r. prepended to the Oracle SID, TNS alias, or database name, as appropriate for the connection type. A database identifier is not required if the target is JMS, Kafka, or a file.

Important!

Replicate within the local system

Replication on the same system supports the following configurations:

  • Within one Oracle instance, replicate to different tables within the same schema or to the same table in different schemas.
  • Replicate to from an Oracle instance to any SharePlex-supported target on the same system.

On the Windows platform, SharePlex does not support replication between Oracle databases that reside on the same system, but you can replicate to Open Target targets on the same system.

Configuration options

datasource_specification

    
source_specification1 target_specification1 hostA[@db]
source_specification2 target_specification2 hostA[@db]
Example

This example shows how you can replicate data to the same Oracle instance, to a different Oracle instance (Unix and Linux only), and to different target types, all on the same local system.

Datasource:o.oraA    
hr.emp hr.emp2 hostA@o.oraA
hr.sal hr.sal2 hostA@o.oraB
fin.* fin.* hostA@r.mss
act.* !file hostA

Configuration when using SharePlex Manager

Replication from and to the same machine omits an Export process. However, SharePlex Manager expects an export queue to exist. If using this configuration with SharePlex Manager, you must explicitly configure an export queue as follows. The hostA* component in the routing map creates the export queue and an Export process, which sends the data to an Import process, then the post queue.

datasource_specification

    
source_specification1 target_specification1 hostA*hostA[@db]
source_specification2 target_specification2 hostA*hostA[@db]

Replicate to a remote target system

Configuration options

datasource_specification

    
source_specification1 target_specification1 hostB[@db]
source_specification2 target_specification2 hostB[@db]
Example

The last line in this example shows how you can replicate data to different target types on the same remote target system.

Datasource:o.oraA    
hr.emp hr.emp2 hostB@o.oraB
hr.sal hr.sal2 hostB@o.oraB
fin.* !file hostB

Replicate to multiple target systems

This topology is known as broadcast replication. It provides the flexibility to distribute different data to different target systems, or all of the data to all of the target systems, or any combination as needed. It assumes the source system can make a direct connection to all of the target systems. All routing is handled through one configuration file.

For more information, see Configure replication through an intermediary system.

Configuration options

If the target specification is identical on all targets

If the target specification of a source object is identical on all target systems, you can use a compound routing map, rather than type a separate entry for each route. For more information, see Configure data replication.

datasource_specification

    
source_specification1 target_specification1 hostB[@db]+hostC[@db][+...]
source_specification2 target_specification2 hostC[@db]+hostD[@db][+...]
If the target specification is not identical on all targets
  • When the target specification of a source object is different on some or all target systems, you must use a separate configuration entry to specify each one that is different.
  • You can use a compound routing map for routes where the target specifications are identical.

datasource_specification

    
source_specification1 target_specification1 hostB[@db]
source_specification1 target_specification2 hostC[@db]
Example

Note: This example does not cover all possible source-target combinations. The last entry in this example shows the use of horizontally partitioned replication to distribute different data from the sales.accounts table to different regional databases.

Datasource:o.oraA    
hr.emp hr.emp2 hostB@o.oraB
hr.emp hr."Emp_3" hostC@r.mssB
hr.emp !jms hostX
cust.% cust.% hostD@o.oraD+hostE@o.oraE
sales.accounts sales.accounts !regions
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating