Chat now with support
Chat with Support

SharePlex 11.0 - SharePlex 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 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

Filter DML operations for Oracle Database

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

  • Oracle DML type (INSERT, UPDATE, DELETE)
  • DML related to Oracle sequences and Oracle 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.

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

Filter DML related to specific Oracle objects from replication

You can prevent SharePlex from replicating sequences 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
SQL*Loader direct-path loads SP_OCT_REPLICATE_DLOAD 0

Filter DML Operations for PostgreSQL Database

You can configure SharePlex to filter out the PostgreSQL DML type (INSERT, UPDATE, DELETE) from replication when wildcarding is being used.

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.

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:r.dbname

 

 

scott.emp !dml(d)

scott.emp

prodsys@r.dbname

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:r.dbname

 

 

scott.stock !dml(d,i) !key (EMPLOYEE_ID)

scott.stock

sys2@r.dbname

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 detail

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

Filter out >>>>> DELETES INSERTS

Unique Key : (EMPLOYEE_ID)

Restriction

If there are multiple specifications of a source table in the configuration file, the DML filter specification specified in the last line for the table is considered.

Map source columns to target columns

Map Source and 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.

  • If the spelling case is different between the source and target column names, enclose them within quotes.
  • 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, see Configure Data Replication.

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. For more information, see Database Specifications in a Configuration File.

src_owner.table and tgt_owner.table The specifications for the source and target tables, respectively. For more information, see Create a Configuration File.

(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. For more information, see Routing Specifications in a Configuration File.

Configuration example

This example contains no case-sensitive columns.

Datasource o.oraA

 

 

sales.prod (ID,name,vendor)

mfg.prod (UPC,product,supplier)

sysB@o.oraB

 

This example contains case-sensitive columns.

Datasource o.oraA

 

 

sales.prod (ID,"name",vendor)

mfg.prod (UPC,"product",supplier)

sysB@o.oraB

Build a configuration file using a script

SharePlex provides the following scripts to automate the building of a configuration file to specify Oracle source objects.

  • config.sql: configure all tables and optionally all sequences in the database.
  • build_config.sql: configure multiple or all tables in a schema

Supported databases

Oracle

Use config.sql

The config.sql script enables you to build a configuration that lists all of the tables, and optionally all of the sequences, in all of the schemas of a database. This script saves time when establishing a high-availability replication strategy or other scenario where you want the entire database to be replicated to an identical secondary database.

Conditions for using config.sql
  • Source and target table names must be the same.

  • The script does not configure objects in the SYS, SYSTEM, and SharePlex schemas. These schemas cannot be replicated since they are system and/or instance-specific.
  • The script does not support partitioned replication. You can use the copy config command to copy the configuration file that the script builds, then use the edit config command to add entries for tables that use partitioned replication. Activate the new configuration file, not the original one.
  • You can use the edit config command to make any other changes as needed after the configuration is built.

To run config.sql:

  1. Change directories to the config sub-directory of the SharePlex variable-data directory. The config.sql script puts configurations in the current working directory, and SharePlex configurations must reside in the config sub-directory.

    cd /vardir/config

  2. Log onto SQL*Plus as SYSTEM.
  3. Run config.sql using the full path from the util sub-directory of the SharePlex product directory.

    @ /proddir/util/config.sql

Refer to the following table when following the prompts:

Prompt What to enter
Target machine The name of the target machine, for example SystemB.
Source database SID The ORACLE_SID of the source (primary) Oracle instance, for example oraA. Do not include the o. keyword. The ORACLE_SID is case-sensitive.
Target database SID The ORACLE_SID of the target (destination) Oracle instance, for example oraB. Do not include the o. keyword. The ORACLE_SID is case-sensitive.
Replicate sequences Enter y to replicate sequences or n not to replicate sequences.
SharePlex oracle username The name of the SharePlex user in the source database. This entry prevents the SharePlex schema from being replicated, which would cause replication problems. If a valid name is not provided, the script fails.

Note: The name assigned by SharePlex to the configuration is config.file. If you run the script again to create another configuration file, it overwrites the first file. To preserve the original file, rename it before you create the second one.

Next steps:

  • If any tables or owners are case-sensitive, open the configuration file with the edit config command in sp_ctrl, then use the text editor to enclose case-sensitive table and owner names within double-quote marks, for example “scott”.“emp”. The script does not add the quote marks required by Oracle to enforce case-sensitivity.

    sp_ctrl> edit config filename

  • To ensure that the configuration is in the correct location, issue the list config command. If the name of the configuration is not shown, it was created in the wrong directory. Find the file and move it to the config sub-directory of the variable-data directory.

    sp_ctrl> list config

Use build_config.sql

The build_config.sql script enables you to build a configuration that contains multiple (or all) tables in a schema. It is an interactive script that prompts for each component of the configuration step by step. Instead of entering the information for each object and the routing individually, you can use a wildcard to select certain tables at once, or you can select all of the tables in the schema.

Conditions for using build_config.sql
  • Source and target table names must be the same.
  • The script does not support sequences. Before you activate the configuration that the script builds, you can use the edit config command in sp_ctrl to add entries for sequences.
  • The script does not support partitioned replication. You can use the copy config command to copy the configuration that the script builds, then use the edit config command to add entries for the tables that use partitioned replication. Activate the new configuration, not the original.
  • The script does not configure objects in the SYS, SYSTEM, and SharePlex schemas. These schemas cannot be replicated since they are system and/or instance-specific.
  • You can run build_config.sql for different schemas, then combine those configurations into one configuration by using a text editor. Make certain to eliminate all but one Datasource:o.SID line, which is the first non-commented line of the file. Do not move the file out of the config sub-directory.
  • You can use the edit config command to make any other changes as needed after the configuration is built.

To run build_config.sql:

  1. Change directories to the config sub-directory of the SharePlex variable-data directory. The build_config.sql script puts configurations in the current working directory, and SharePlex configurations must reside in the config sub-directory.

    cd /vardir/config

  2. Log onto SQL*Plus as SYSTEM.
  3. Run build_config.sql using the full path from the util sub-directory of the SharePlex product directory.

    @ /proddir/util/build_config.sql

Refer to the following table when following the prompts.

Prompt What to enter
Target machine The name of the target machine, for example SystemB.
Source database SID The ORACLE_SID of the source (primary) Oracle instance, for example oraA. Do not include the o. keyword. The ORACLE_SID is case-sensitive.
Target database SID The ORACLE_SID of the target (destination) Oracle instance, for example oraB. Do not include the o. keyword. The ORACLE_SID is case-sensitive.
Owner of the source database tables The owner of the source tables.
Owner of the target database tables The owner of the target tables.
Table name to include (blank for all)

Do one of the following:

  • Press Enter to accept the default, which selects all tables that belong to the source owner.
  • Enter a wildcard (%) character and a string to select certain tables, for example %e_salary%.
  • Enter an individual table name.
Name of the output file to create A name for the configuration. The script gives the file a .lst suffix, for example Scott_config.lst.

Next steps:

  • If any tables or owners are case-sensitive, open the configuration with the edit config command in sp_ctrl, then use the text editor to enclose case-sensitive table and owner names within double-quote marks, for example “scott”.“emp”. The script does not add the quote marks required by Oracle to enforce case-sensitivity.

    sp_ctrl> edit config filename

  • To ensure that the configuration is in the correct location, issue the list config command. If the name of the configuration is not shown, it was created in the wrong directory. Find the file and move it to the config sub-directory of the variable-data directory.

    sp_ctrl> list config

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating