Chat now with support
Chat mit Support

SharePlex 12.1 - Administration Guide

Revision History About this Guide Conventions used in this guide 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 Assign SharePlex users to security groups 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

Configure vertically partitioned replication

Use vertically partitioned replication to replicate a subset of the columns of a table. For example, you can replicate data changes made to C1, C2, C3, and C4, but not changes made to C5 and C6, as shown in the diagram.

Supported sources and targets

  • PostgreSQL to PostgreSQL, Oracle, SQL Server, and Kafka

  • Oracle to all targets

  • PGDB as a Service to PGDB as a Service

  • PGDB as a Service to Oracle

  • PGDB as a Service to PostgreSQL

Guidelines for using vertically partitioned replication

Follow these guidelines when creating a configuration file that includes vertically partitioned replication:

  • Vertically partitioned replication is appropriate for reporting and other data sharing strategies, but it is not appropriate for high availability environments. Once you configure a table for vertically partitioned replication, SharePlex does not recognize the other columns, so data in those columns is not replicated.

  • You can combine horizontally partitioned and vertically partitioned replication for maximum control over which information is distributed, and to where.

    For example: A company has a headquarters and regional divisions. The headquarters maintains the corporate database, and each region maintains a regional database. The headquarters uses vertically partitioned replication to share some of the column data of a table to those locations, while retaining other sensitive data at headquarters. Row changes made to the shared columns are further partitioned horizontally, for replication to the appropriate regional database.

  • A table cannot be configured to replicate some columns to one target system and all columns to another (combination of vertically partitioned replication and full-table replication). You can, however, configure full-table replication to an identical table on one target, and then configure vertically partitioned replication from that target to a second target that contains the table that requires only the partition columns.
  • A target table can, but does not have to, contain all of the same columns as its source table. The target can contain just the columns being replicated from the source table. The names of corresponding source and target columns do not need to be the same. Corresponding columns must contain the same data types (same type, size, precision).

Overview of vertically partitioned replication: Oracle to all targets

To configure vertically partitioned replication, you specify either a column partition or an exclusion column partition in the configuration file:

  • A column partition specifies the columns that you want to include in replication. Only data changes that are made to the specified columns get sent to the target.
  • An exclusion column partition specifies columns to be excluded from replication. No data from those columns is replicated to the target.

Follow these rules to specify either type of column partition:

  • There can be one partition per source table. A column partition and an exclusion partition are mutually exclusive.
  • A column list must be enclosed within parentheses.
  • Separate each column name with a comma. A space after the comma is optional.
  • The maximum length of a partition is 174820 bytes (the maximum line length allowed in a configuration file). Therefore, the actual number of columns that you can list depends on the length of each name.
  • The columns can be contiguous or non-contiguous in the source table. For example, you can replicate the first, third and seventh columns of a table.
  • Key columns are not required to be included in the partition.
  • If using horizontally partitioned and vertically partitioned replication together for this table, all of the columns in the partition scheme must be part of the column condition.
  • Use one configuration file for all of the data that you want to replicate from a given datasource, including tables that will have full-table replication and those that will use partitioned replication.

To configure entries for vertically partitioned replication, use the following syntax. For more information about how to create a configuration file, see Configure SharePlex to replicate data.

datasource_specification    

# table specification with column partition

src_owner.table (src_col,src_col,...)

tgt_owner.table [(tgt_col,tgt_col,...)] routing_map

# table specification with exclusion column partition

src_owner.table !(src_col,src_col,...) tgt_owner.table routing_map
Configuration component Description
src_owner.table and tgt_owner.table The specifications for the source and target tables, respectively.

(src_col, src_col,...)

Specifies a column partition that lists the columns to include in replication. No other column data is replicated, including data in columns that are added after the start of replication (assuming DDL replication is enabled).

!(src_col,src_col,...)

Specifies an exclusion column partition that lists the columns to exclude from replication. All other column data is replicated, including data in columns that are added after the start of replication (assuming DDL replication is enabled).

Note: When using an exclusion column partition, the corresponding source and target column names must be identical, and the excluded columns cannot be used in a key definition. For more information, see Define a Unique Key .

(tgt_col,tgt_col,...)

The target columns. Use this option to map source columns to target columns that have different owners or names. If the source and target columns have identical owners or names, the target columns can be omitted.

To map source columns to target columns, follow these rules:

  • The syntax rules for the source column partition also apply to the target column list.

  • The target columns must have identical definitions as their source columns, except for their names.

  • List the target columns in the same logical order as their corresponding source columns. This is required regardless of the actual order of the target columns in the table, so that SharePlex builds the correct correlations in the object cache. For example, a change to the second column in the source list is replicated to the second column in the target list.
routing_map

The routing map for the column partition. The routing map can be one of the following:

  • If using horizontally partitioned replication for the source table, specify a partition scheme, as in: !partition_scheme.

  • If not using horizontally partitioned replication for the source table, specify a routing map as follows:

    • Use a simple routing map like sysB@o.myora if replicating the column partition to one target. A route with a named export or post queue is supported. For more information, see:

      Configure Named Export Queues

      Configure Named Post Queues

    • Use a compound routing map like sysB@o.myora+sysC@o.myora2 if replicating the column partition to multiple target systems.

Important! A compound routing map must be used, rather than listing multiple targets in separate entries, because only one column condition per source table can be listed in the configuration file. To use a compound routing map, the owners and names of all of the target tables must be identical. For more information, see Routing Specifications in a Configuration File.

Configuration examples

The following is a vertically partitioned replication configuration replicating to multiple targets by using a compound routing map. To use a compound routing map for this source table, all targets must be named scott.sal.

Datasourceo.oraA    
scott.emp (c1,c2) scott.sal

sysB@o.oraB+sysC@o.oraC

The following is a vertically partitioned replication configuration replicating to a single target where the target columns have different names from those of the source.

Datasourceo.oraA    
scott.emp (c1,c2) scott.sal (c5,c6)

sysB@o.oraB

The following configuration file is not valid because it repeats the same column partition of scott.emp (c1, c2) twice in the configuration file.

Datasourceo.oraA    
scott.emp (c1,c2) scott.cust (c1,c2)

sysB@o.oraB

scott.emp (c1,c2) scott.sales (c1,c2) sysC@o.oraC

Overview of vertically partitioned replication for PostgreSQL and PostgreSQL Database as a Service

Supported targets

PostgreSQL, Oracle, SQL Server, and Kafka

NOTE: PostgreSQL to SQL server replication does not support the BOOLEAN, TIME, TIME WITH TIME ZONE, and BYTEA data types for vertically partitioned data.

To configure vertically partitioned replication:

To configure vertically partitioned replication, you specify either a column partition or an exclusion column partition in the configuration file:

  • A column partition specifies the columns that you want to include in replication. Only data changes that are made to the specified columns get sent to the target.
  • An exclusion column partition specifies columns to be excluded from replication. No data from those columns is replicated to the target.

Follow these rules to specify either type of column partition:

  • There can be one partition per source table. A column partition and an exclusion partition are mutually exclusive.
  • A column list must be enclosed within parentheses.
  • Separate each column name with a comma. A space after the comma is optional.
  • The maximum length of a partition is 174820 bytes (the maximum line length allowed in a configuration file). Therefore, the actual number of columns that you can list depends on the length of each name.
  • The columns can be contiguous or non-contiguous in the source table. For example, you can replicate the first, third and seventh columns of a table.
  • Key columns are not required to be included in the partition. There can be a performance impact if you are not partitioning key columns.
  • Use one configuration file for all of the data that you want to replicate from a given datasource, including tables that will have full-table replication and those that will use partitioned replication.

To configure entries for vertically partitioned replication, use the following syntax:

datasource_specification    

# table specification with column partition

src_schema.table (src_col,src_col,...)

tgt_schema.table [(tgt_col,tgt_col,...)] routing_map

# table specification with exclusion column partition

src_schema.table !(src_col,src_col,...) tgt_schema.table routing_map
Configuration component Description
src_schema.table and tgt_schema.table The specifications for the source and target tables, respectively.
(tgt_col,tgt_col,...)

The target columns. Use this option to map source columns to target columns that have different schemas or names. If the source and target columns have identical schemas or names, the target columns can be omitted.

To map source columns to target columns, follow these rules:

  • The syntax rules for the source column partition also apply to the target column list.

  • The target columns must have identical definitions as their source columns, except for their names.

  • List the target columns in the same logical order as their corresponding source columns. This is required regardless of the actual order of the target columns in the table, so that SharePlex builds the correct correlations in the object cache. For example, a change to the second column in the source list is replicated to the second column in the target list.
routing_map

The routing map for the column partition. The routing map can be one of the following:

  • If using horizontally partitioned replication for the source table, specify a partition scheme, as in: !partition_scheme.

  • If not using horizontally partitioned replication for the source table, specify a routing map as follows:

    • Use a simple routing map like sysB@r.dbname if replicating the column partition to one target. A route with a named export or post queue is supported. For more information, see:

      Configure Named Export Queues

      Configure Named Post Queues

    • Use a compound routing map like sysB@r.dbname+sysC@r.dbname2 if replicating the column partition to multiple target systems.

Important! A compound routing map must be used, rather than listing multiple targets in separate entries, because only one column condition per source table can be listed in the configuration file. To use a compound routing map, the schemas and names of all of the target tables must be identical.

Configuration examples

The following is a vertically partitioned replication configuration replicating to multiple targets by using a compound routing map. To use a compound routing map for this source table, all targets must be named scott.sal.

Datasource: r.dbname    
scott.emp (c1,c2) scott.sal

sysB@r.dbname1 + sysC@r.dbname2

The following is a vertically partitioned replication configuration replicating to a single target where the target columns have different names from those of the source.

Datasource: r.dbname    
scott.emp (c1,c2) scott.sal (c5,c6)

sysB@r.dbname1

The following configuration file is not valid because it repeats the same column partition of scott.emp (c1, c2) twice in the configuration file.

Datasource: r.dbname    
scott.emp (c1,c2) scott.cust (c1,c2)

sysB@r.dbname1

scott.emp (c1,c2) scott.cust (c1,c2) sysC@r.dbname2

Configure replication to a change history target

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.

Contents

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

Perform the following steps to 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 SharePlex to Replicate Data.

    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.

Verwandte Dokumente

The document was helpful.

Bewertung auswählen

I easily found the information I needed.

Bewertung auswählen