Chat now with support
Chat with Support

Welcome, ApexSQL customers to Quest Support Portal click here for for frequently asked questions regarding servicing your supported assets.

SharePlex 9.0 - Administration Guide

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 SharePlex to replicate data Configure replication to and from a container database Configure named queues Configure partitioned replication Configure SharePlex to maintain a change history target Configure a replication strategy Configure SharePlex to replicate Oracle DDL Set up error handling Transform data Configure SharePlex security features Activate replication in your production environment Monitor SharePlex Prevent and solve replication problems Repair out-of-sync Data Procedures to maintain Oracle high availability Make changes to an active replication environment Apply an Oracle application patch or upgrade Back up Oracle data on the source or target Tune the Capture process Tune the Post process Appendix A: Peer-To-Peer Diagram Appendix B: SharePlex environment variables

Configure horizontally partitioned replication

Use horizontally partitioned replication to divide the rows of a table into separate processing streams. You can use horizontally partitioned replication to:

  • Replicate a subset of rows to a target, while retaining the rest of the rows in the source.
  • Replicate different subsets of rows to different targets.
  • Divide the replication of a source table into parallel Post queues for faster posting to the target table.

Supported sources

Oracle

Supported targets

All

Overview of horizontally partitioned replication

To configure horizontally partitioned replication for a table, you do the following:

  1. Define a partition scheme and one or more row partitions for the partition scheme.

    • A row partition is a defined subset of rows in a source table that you want to replicate to the target.

    • A partition scheme is a logical container for row partitions.

  2. Specify the name of the partition scheme in the SharePlex configuration file to include the partitions in replication.

A partition scheme can be one of the following, depending on how the row partitions are defined:

  • A column partition scheme contains row partitions defined by a column condition. A column condition is a WHERE clause that defines a subset of the rows in the table.
  • A hash partition scheme contains row partitions defined by a hash value that directs SharePlex to distribute rows evenly across multiple queues. This option is valid for Oracle source data only.

Column partition schemes

You can use row partitions based on column conditions for the following purposes:

  • Use one row partition to replicate only a subset of the rows of a table. For example, you can replicate only those rows where the value of the YEAR column is greater than 2014. The partition scheme in this case could be named "Since2014" or "Recent."
  • Use multiple row partitions to divide the rows of a table so that each set of rows replicates to a different target. For example, a table named CORPORATE.SALES could have two row partitions named "East" and "West." The column conditions are defined accordingly, where the rows that satisfy REGION = EAST replicate to one location and the rows that satisfy REGION = WEST replicate to a different location. The partition scheme could be named "Sales_by_region."

  • Use multiple row partitions to divide the rows of a table into parallel processing streams (parallel Export-Import-Post streams) for faster posting to a target table. For example, you can improve the flow of replication to a heavily updated target table. The use of column conditions for this purpose is appropriate only if the table contains a column that enables you to split the processing evenly among parallel Post processes.

Hash partition schemes

For Oracle source data, you can use row partitions based on a hash value to divide the rows of a table into parallel processing streams (parallel Export-Import-Post streams) for faster posting to a target table. The advantage of using a hash value over column conditions to create partitions is that the rows are divided evenly and automatically by SharePlex, without the need to reference table columns in WHERE clauses. However, unlike column partition schemes, you cannot use the SharePlex compare or repair commands for hash partition schemes.

Combine partitioned replication with full-table replication

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.

Horizontally partitioned replication can be used in conjunction with full-table replication for the same table, for example to route groups of rows to different reporting systems and all rows to a backup system.

SharePlex does not support a combination of horizontally partitioned replication and full-table replication if vertically partitioned replication is also used for the same table.

Limitations of use

When using hash partitioning, the following limitations apply:

  • The table that uses hash partitions cannot be compared or repaired with the compare and repair commands.
  • Hash partitioning cannot be used for index-organized tables (IOT) or tables that contain LOBs or LONGs.
  • Hash partitioning cannot be used for a table if there are operations that delete and reinsert the same key value, or operations that update the key value and then insert the same key value. This can causing unique constraint violations because of different rowids.
  • A table cannot have partitions based on a hash value and also have partitions based on a column condition.
  • Do not use hash partitions if any operations on a table could cause rows to migrate. Examples of operations that cause rows to migrate are:

    • Update a value so that it moves to a new row partition
    • Table reoraganization
    • Split a table partition or combine two partitions
    • Export or import of the table
    • ALTER TABLE with the MOVE option
    • ALTER TABLE with the SHRINK SPACE option
    • FLASHBACK TABLE
    • Redefine a table by using dbms_redefinition
    • DML applied to a regular, non-partitioned table that can cause row shift: UPDATE that changes row size so that the data does not fit into the current block, DELETE of a row and then re-insert.

Note: If Post returns the error message "shs_SEMERR: an error occurred with the semaphore" on a Windows system, the number of semaphores may need to be increased to accommodate the queues that you created. For more information, see Post stopped .

Define partition schemes and row partitions

These instructions help you to define your row partitions and link them to partition schemes. You define and manage row partitions by using the Partition commands in sp_ctrl.

Note: SharePlex automatically creates a partition scheme when you define the first row partition for it. If you are making heavy use of horizontal partitioning, it may help to establish some naming conventions for your partition schemes. Optionally, you can assign names to row partitions if they are defined by column conditions.

To create a partition scheme based on column conditions

Issue the following command for each row partition that you want to create for a partition scheme. When you create the first row partition, SharePlex also creates the partition scheme that is specified in the command.

sp_ctrl> add partition to scheme_name set [name = name and ] condition = column_condition and route = value

See Description of command syntax

Examples

Route different sets of rows through different post queues:

sp_ctrl> add partition to scheme1 set name = q1 and condition = "C1 > 200" and route = sysb:q1@o.orasid

sp_ctrl> add partition to scheme1 set name = q2 and condition = "C1 < 200" and route = sysb:q2@o.orasid

Route different sets of rows to different targets:

sp_ctrl> add partition to scheme1 set name = east and condition = "area = east" and route = sys1e@o.orasid

sp_ctrl> add partition to scheme1 set name = west and condition = "area = west" and route = sys2w@o.orasid

To create a partition scheme based on a hash value

Issue the following command once to specify the number of hash partitions to create.

sp_ctrl> add partition to scheme_name set hash = value and route = value

See Description of command syntax

Example

Divide rows into four partitions, each processing through a different post queue:

sp_ctrl> add partition to scheme1 set hash = 4 and route = sysb:hash|#@o.ora112

Description of command syntax

Component Description
scheme_name Name of the partition scheme. SharePlex creates a partition scheme the first time that you issue an add partition command that includes the name of that scheme. There can be only one partition scheme per table in the configuration file.
condition

Column condition that defines the set of rows to be included in this partition. Use standard WHERE conditional syntax such as ((region_id = West) and region_id is not null). See How to create a column condition for additional information.

Important! For Microsoft SQL Server tables, the column conditions must only be based on the primary key columns.

The condition and hash components are mutually exclusive.

hash (Valid for Oracle only) Hash value that specifies the number of row partitions that SharePlex creates based on the rowid. The hash and condition components are mutually exclusive.
route

Routing map for this partition.

This can be one of the following, depending on whether you are using column conditions or a hash value to create the partition scheme.

If creating a column partition scheme:

Specify a standard SharePlex routing map, for example: sysB@o.myora, or a route that includes a named export or post queue. Compound routing maps are also supported.

To route a partition to multiple target tables that have different names, do the following:

  • Issue a separate add partition command for each target table that has a different name than the other target tables. Use the tablename option to specify the target table name in each command.
  • When you create an entry for this partition scheme in the configuration file, specify any target table. SharePlex will detect the other target names when the configuration is activated.
  • Set the SP_ORD_FIRST_FIND parameter to 0 so that SharePlex checks all of the column conditions in the partition scheme. By default SharePlex assumes that any given row change will satisfy only one column condition in the partition scheme. For more information, see the SharePlex Reference Guide.

If creating a partition with a hash:

Use the following format to direct SharePlex to create the named post queues:

host:basename|#{o.SID | r.database}

where:

  • host is the name of the target system.
  • basename is the base name of the post queues that SharePlex will create. Each name is appended with an integer from 0 to the number of partitions that you specify with the hash keyword.
  • |# directs SharePlex to number the queues sequentially.
  • o.SID is required for an Oracle target or r.database is required for an Open Target target.
name (Optional) Short name of this partition. Useful only for partitions based on column conditions. The use of a short name eliminates the need to type out long column conditions in the event that you need to modify or drop the partition in the future.
tablename (Optional) Fully qualified target table name, if different from the source table name. If case-sensitive, the name must be specified as required by the database.
description (Optional) Description of this partition.

How to create a column condition

The following are guidelines for creating column conditions. These guidelines do not apply to row partitions that are created with a hash value.

Choose appropriate columns

The types of columns on which you base your column conditions vary per data source:

Base column conditions on columns whose values will not change, such as PRIMARY or UNIQUE key columns. The objective is to avoid a partition shift, where changes made to the conditional columns of a partition can cause the underlying data to satisfy the conditions of a different (or no) partition.

Partition shift case 1: The column value is updated so that the new value no longer satisfies any column condition:

  • SharePlex performs the operation, but future operations on that row are not replicated. The reason: the row no longer satisfies a column condition.
  • The source and target tables of the original partition are now out of synchronization, but Post returns no errors.

Partition shift case 2: A row that satisfies one column condition gets updated to meet a different condition:

  • Post cannot find a matching target row. The reason: the original change was not replicated because it did not meet the column condition.
  • Post returns an out-of-sync error.

You have the following options for repairing the out-of-sync rows that are caused by changes to the values of column conditions:

  • Set the SP_ORD_HP_IN_SYNC parameter to a value of 1. This setting directs SharePlex to detect and correct the out-of-sync condition. Enabling this parameter causes some performance degradation, depending on how many tables are configured for horizontally partitioned replication. For more information about this parameter, see the SharePlex Reference Guide.
  • Use the compare command to repair the out-of-sync rows. For more information about this command, see the SharePlex Reference Guide.

Note: If you are using a column other than a key to base the column condition on, and you notice reduced performance with horizontally partitioned replication enabled, add a log group for that column.

Use supported data types

SharePlex supports the following data types in column conditions:

NUMBER
NUMBER
NUMBER
NUMBER
DATE
CHAR

VARCHAR

VARCHAR2

LONG VARCHAR

Notes:

  • For the dates, SharePlex uses MMDDSYYYYHH24MISS. For example:

    hiredate<‘1111 2011000000’

  • Horizontally partitioned replication does not support the following:

    • Datatypes other than the ones listed in this section. This also excludes large types like LOBs and object types such as VARRAYs and abstract datatypes.
    • Oracle TO_DATE function
    • UPDATEs or INSERTs on LONG columns larger than 100k
    • Sequences
    • TRUNCATEs of an Oracle partition

Use standard conditional syntax

The following list shows the conditional syntax that SharePlex supports in a column condition, where:

  • value can be a string or a number. Enclose strings and dates within single quote marks (‘west’). Do not use quote marks for numbers .
  • column is the name of a column in the table that you are configuring to use horizontally partitioned replication.
column = value
not (column = value)
column > value
value > column
column < value
column <= value
column >= value
column <> value
column != value
column like value
column between value1 and value2
not (column between value1 and value2 )
column is null
column is not null

Conditions can be combined into nested expressions with parentheses and the AND, OR, and NOT logical connectives.

Example column conditions

not (col1 = 5)
(col2 = 5) and not (col3 = 6)
((col1 is not null) and (col2 = 5))

Additional guidelines

  • NULLs are replicated by SharePlex in cases such as this one: not (department_id = 90). If department_id is NULL, it is replicated. To avoid replicating records with NULLs, include the column is not null syntax as part of the condition, for example: not (department_id = 90) and department_id is not null.
  • If parentheses are not used to indicate operator precedence, SharePlex supports operator precedence in the same order that SQL does. For example, a condition not x and y behaves the same way as (not x) and y. A condition x and y or z behaves the same as (x and y) or z. When a condition includes parentheses, the explicit precedence is respected.
  • If the condition column is a VARCHAR column and the values used to define the partitions are string literals, the entire condition must be enclosed in double quotes, as in the following example: add partition to scheme set route=route and condition="C2 = 'Fred'"
  • If the column name must be enclosed in quotes, then the entire condition must be enclosed in quotes, as in the following example: add partition to scheme set route=route and condition="\"c2\" > 0"
  • Do not:

    • include references to other tables in the column condition.
    • exceed the 1024 bytes maximum defined storage.
  • During the activation of a configuration that refers to partition schemes, SharePlex verifies the syntax in the column conditions of those schemes. If any syntax is incorrect, the activation fails. SharePlex prints an error to the Event Log that indicates where the error occurred.

Specify partition schemes in the configuration file

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. For more information about how to create a configuration file, see Configure SharePlex to replicate data. To configure entries for horizontally partitioned replication, use the following syntax.

Datasource:{o. | r.}database
src_owner.table tgt_owner.table

!partition_scheme

!   routing_map

Description of configuration components

Configuration component Description

o.database

The datasource designation. Use the o. notation for an Oracle source. For database, specify the ORACLE_SID.
src_owner.table and tgt_owner.table The specifications for the source and target tables, respectively.
!partition_scheme The name of the partition scheme to use for the specified source and target tables. The ! is required. The name is case-sensitive. Compound routing of multiple partition schemes is not supported, for example !schemeA+schemeB. Create a separate entry for each partition scheme that you want to use for the same source table. See Examples.
! routing_map

A placeholder routing map. It is required only if a route that you used in a partition scheme is not listed somewhere in the configuration file. SharePlex requires every route to be in the configuration file even if it is listed in a partition scheme.

  • Notes:

    • This component applies only to partitions using a column condition.
    • This applies to different named queue routes as well as to routes to different target hosts.
    • You can use a compound routing map if the names of all target tables are identical. See Examples.

See Examples.

Examples

Configuration file entry to specify a partition scheme

Datasource: r.mydb    
scott.emp scott.emp_2 !partition_emp
scott.cust scott.cust_2 !partition_cust

Correct way to specify multiple partition schemes for the same source table

Datasource: r.mydb    
scott.emp scott.emp_2 !partition_schemeA
scott.emp scott.emp_3 !partition_schemeB

Correct way to specify placeholder routing map*

! targsys1
! targsys2@o.ora2+targsys3@o.ora3

*Required only for partitions based on column conditions.

Change the hash algorithm

When using hash-based horizontally partitioned replication, you can change the hash algorithm from the default of rowid-based to block-based. To enable the block-based hash option, set the SP_OCF_HASH_BY_BLOCK parameter to 1.

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

Oracle and SQL Server

Supported targets

All

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 datatypes (same type, size, precision).
  • ALTER TABLE to add a column to a table configured for vertically partitioned replication is not supported.

Configure vertically partitioned replication

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

  • A column partition replicates data changes that are made to the specified columns.
  • An excluded column partition replicates all data changes except those made to the specified 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. For more information about how to create a configuration file, see Configure SharePlex to replicate data.

datasource_specification    
src_owner.table (src_col,src_col,...) tgt_owner.table [(tgt_col,tgt_col,...)] routing_map
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,...)

and

!(src_col,src_col,...)

The source columns to be replicated. Can be one of the following:

  • (src_col, src_col,...) specifies a column partition that lists columns to replicate.
  • !(src_col,src_col,...) specifies an excluded column partition that lists columns not to replicate. The remaining columns comprise the actual column partition.

Note: When using an excluded 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.

Follow these rules to specify either type of column partition:

  • There can be one partition per source table.
  • 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.
(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

Configure SharePlex to maintain 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 and SQL Server

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: for ADD COLUMN, Post adds a column to the table, but does not create a change history row.)
  • ALTER TABLE to MODIFY the datatype of a column

Operations not supported

  • Changes made to UDT or VARRAY columns
  • 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.

Related Documents