Chat now with support
Chat with Support

SharePlex 8.6.6 - 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 SharePlex parameters Prepare an Oracle environment for replication Create a configuration file Configure replication to Open Target targets Configure a replication strategy Configure partitioned replication Configure named queues Configure SharePlex to maintain a change history target 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

Use Wildcards to specify multiple objects

Create a configuration file > Use Wildcards to specify multiple objects

You can use the percent (%) and underscore (_) wildcard symbols to specify multiple objects of a schema in one entry of the configuration file. SharePlex replicates any objects that satisfy the wildcard, except those that you explicitly exclude.

Requirements and limitations of wildcard support

Supported wildcard syntax

SharePlex supports the following SQL wildcards

  • Percent (%) wildcard to specify a string. (See the Examples.)
  • Underscore (_) wildcard to specify a single-character.
  • For table names that contain a percent sign or an underscore character (for example emp_salary), SharePlex recognizes the backslash (\) as an escape character to denote the character as a literal, not a wildcard.

Specify wildcarded names in the configuration file

Use this template for help when specifying a wildcarded name in the configuration file.

Configuration with wildcarded object names
datasource_specification
expand src_owner.wildcard_name [not (list)]

tgt_owner.wildcard_name

routing_map
Description of syntax elements
Component Description
expand

Indicates that the specification contains wildcard characters that must be expanded. When SharePlex detects the expand keyword, it queries the database for all objects that match the criteria in the wildcard specification. Without this required keyword, the wildcard characters are assumed to be part of an explicit object name, and no wildcard expansion is performed.

Note: Leave a space between expand and the start of the source object specification.

src_owner.wildcard_name
  • src_owner is the owner of the source objects. Owner names cannot be wildcarded. If wildcards are used in the owner name, SharePlex assumes that they are part of the owner (schema) name.
  • wildcard_name is the wildcarded name of the source objects.

The names of the target objects must be identical to those of the source objects, but the objects may belong to different owners.

not (list)

An exclusion list that defines objects to omit from the wildcard expansion. Use this option to exclude objects that you do not want to be replicated. Note: This not keyword does not have the same meaning as the SQL wildcard NOT operator.

  • The not keyword and parentheses are required elements.
  • list is a comma-separated list of tables owned by the same owner, either wildcarded or explicit. Example: not (spo%, gen%, product).

Leave a space before and after the not keyword. A space is allowed after each comma in the list.

Note: If an object that satisfies a wildcard is listed elsewhere in the configuration file, that entry overrides the processing or routing specified in the wildcarded entry. In this case, a not clause is not needed. See the Examples.

tgt_owner.wildcard_name
  • tgt_owner is the owner of the target objects.
  • wildcard_name is the wildcarded name of the target objects.

The target specification must be in the form of owner.%. Partially expanded target wildcarded names are not supported, such as owner.tab%.

routing_map Any valid routing map. For more information, see Routing specifications in a configuration file.

Validate a wildcard specification

To confirm that a wildcard specification will produce the specific list of tables that you want to replicate, issue the verify config command in sp_ctrl before you activate the configuration. This command produces a list of the objects that SharePlex will capture and replicate, as well as any problems that occurred. For more information about this command, see the SharePlex Reference Guide.

Examples

Examples of valid wildcard specifications

Example 1: The following wildcard specification directs SharePlex to activate all tables owned by scott, where the table name is like prod% except if the table name is like %temp%. All tables that match this description are replicated to tables of the same names on the target in the hal schema. Note that SharePlex automatically upshifts the names, so that it actually activates all tables where the table name is like 'PROD%' but not like '%TEMP%'.

Datasource:o.sidA	 	 
expand scott.prod% not (%temp%)    hal.%    sysa@o.sidB

Example 2: The following example shows how you can specify special handling for one of the tables in a wildcarded specification, in this case the photo table. All tables but photo are routed through the default post queue. The separate entry for the photo table overrides the wildcarded entry and processes the photo table through a named post queue. For more information, see Configure named post queues.

Datasource:o.sidA	 	 
cust.%        cust.%        hostB@o.oraB
cust.photo    cust.photo    hostB:lobQ@o.oraB

The following are additional examples of valid wildcard specifications

Datasource:o.sidA	 	 
expand scott.%test%    scott.%    sysa@o.sidB
Datasource:o.sidA	 	 
expand scott.%t__t%    fred.%    sysa@o.sidB
Datasource:o.sidA	 	 
expand scott.% not (spo%, gen%, prodct)    scott.%     sysa@o.sidB
Datasource:o.sidA	 	 
expand scott.prod% not (%temp%)    hal.%    sysa@o.sidB

Examples of invalid wildcard specifications

The following example contains a wildcarded schema, which is not permitted.

Datasource:o.sidA	 	 
expand rob%.%test%    scott.%    sysa@o.sidB

The following example contains a partially wildcarded target object name, which is not permitted.

Datasource:o.sidA	 	 
expand scott.%test%    scott.%obj%    sysa@o.sidB

Define a unique key

Create a configuration file > Define a unique key

If a table was not created with a primary or unique key, you can specify columns to use as a key when you specify the object in the configuration file. SharePlex uses the specified columns as a unique key in its WHERE clause to locate target rows for posting.

NoteS:

  • Without a primary or unique key, SharePlex uses all of the columns of a table (or all of the columns in a column partition) as a key, which slows replication performance.
  • When a key definition is specified for a table that has a PRIMARY or UNIQUE key, the key definition overrides the defined key. This can be useful if you do not want any of the existing keys to be used by SharePlex.

The columns that you specify as a key must meet the following criteria:

  • They cannot be LONG or LOB columns.
  • They must be able to uniquely identify a row. Otherwise, replication could return out-of-sync errors or post to incorrect target rows.
  • They must be part of the column partition if the table is configured for vertically partitioned replication. When using the exclude column notation in vertical partitioning, the excluded columns cannot be used in the key definition. For more information about partitioned replication, see Configure partitioned replication.
  • Include the columns in a supplemental log group. Otherwise, SharePlex must query the database for their values.
  • Create an index on the target table and add the index to the SharePlex hints file, located in the variable-data directory, which directs the Post process to use the index.

Syntax for key definition

To create a key definition, type a space after the source object and use the following syntax, including the parentheses.

src_owner.table !key (column_list)

where:

  • !key is a required keyword.
  • column_list is a list of columns to include in the key. Separate column names with commas. A space after the comma is optional.

datasource_specification

   
src_owner.table !key (col_name, col2_name, ...)

tgt_owner.table

host@o.SID

Example

Datasource:o.ora1
scott.tab !key(name,ID)    scott.tab2    sysB@oraB

Filter DML operations

Create a configuration file > Filter DML operations

You can configure SharePlex to filter out the following DML from replication when wildcarding is being used.

  • DML type (INSERT, UPDATE, DELETE)
  • DML related to sequences, materialized views, and SQL*Loader direct-path loads.

Filter out a DML type

You can configure SharePlex to filter any type of DML operation so that the operation is not replicated to the target table. DML filtering is compatible with most other SharePlex configuration syntax. See the Restrictions.

Configure a DML filter

To configure a DML filter, add the following syntax to the source table specification. Leave a space between the table specification and the filter specification. You can specify multiple operation types to filter. Any additional syntax for other features, such as a column list or key definition, must follow the DML filter specification.

!dml(DML_type[,DML_type][,...])

Where DML_type is one of the following:

DML_type input Operation type
i INSERT
d DELETE
u UPDATE

Examples

Example 1

The following example filters DELETE operations from being replicated to the target table.

Datasource:o.ora

 

 

scott.emp !dml(d)

scott.emp

prodsys@o.sysdb

Example 2

The following example filters DELETEs and INSERTs so that only UPDATEs are replicated to the target table. This example also shows how a DML filter is compatible with a column mapping specification.

Datasource:o.ora

 

 

scott.stock !dml(d,i) (ID, name)

scott.stock (SKU, prod)

sys2@o.sysdb

View current DML filters

Use the verify config command to view the DML that is being filtered for each table in the configuration file. This command can be used for an active or inactive configuration file.

sp_ctrl> verify config myconfig

7: "SCOTT"."EMP" "SCOTT"."EMP" prodsys@o.proddb

Filter out >>>>> DELETES

Unique Key : (EMPLOYEE_ID)

Restrictions

  • The copy and compare commands do not support tables that include DML filtering in their specifications.
  • If there are multiple specifications of a source table in the configuration file, the DML filter specification must be identical for all of them. Multiple specifications of the same source table occur if you are routing to multiple targets without using a compound routing map (see Routing specifications in a configuration file), or if you are combining full-table replication with horizontally partitioned replication (see Configure horizontally partitioned replication).

Filter DML related to specific Oracle objects from replication

You can prevent SharePlex from replicating sequences, materialized views, and SQL*Loader direct-path loads. By default the replication of these objects is enabled.

Filter out this object Set this parameter Value
Sequences SP_OCT_REPLICATE_SEQUENCES 0
Materialized Views SP_OCT_REPLICATE_MVIEW 0
SQL*Loader direct-path loads SP_OCT_REPLICATE_DLOAD 0

Map source columns to target columns

Create a configuration file > Map source columns to target columns

When source and target column names are different, you can specify an explicit column mapping in the configuration file, to ensure that Post applies row data to the correct target columns.

Guidelines for using column mapping

  • To use column mapping, you must map every column in a source table to a column in the target table, even if only some source and target names are different. When some columns are mapped but not others, the entry is considered to be a column partition for vertically partitioned replication, and data changes for non-listed columns are not replicated.

  • You can use horizontally partitioned replication and column mapping for the same source table, but you cannot combine column mapping with vertically partitioned replication.
  • A target table can have more columns than the source table, but there must at least be a target column for every source column.
  • ALTER TABLE to add a column to a table that is configured with column mapping is not supported.

Configure column mapping

The following syntax creates a column map. For more information about the configuration file, see Create configuration files.

datasource_specification    
src_owner.table (src_col,src_col,...) tgt_owner.table (tgt_col,tgt_col,...) routing_map
Configuration component Description
datasource_specification

The datasource specification. See Database specifications in a configuration file for more information.

src_owner.table and tgt_owner.table The specifications for the source and target tables, respectively. See How to create a configuration file for more information.

(src_col,src_col,...)

A list of the source columns.

Follow these rules to specify a column list:

  • 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 column list is 174820 bytes (the maximum line length allowed in a configuration file).
(tgt_col,tgt_col,...)

A list of the target columns.

  • 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 map 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.
  • The syntax rules for the source list also apply to the target list.
routing_map

The routing map. See Routing specifications in a configuration file for more information.

Configuration example

Datasourceo.oraA

 

 

sales.prod (ID,name,vendor)

mfg.prod (UPC,product,supplier)

sysB@o.oraB

Related Documents