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

Set up SharePlex to support Oracle data

Prepare an Oracle environment for replication > Set up SharePlex to support Oracle data

This topic contains setup guidelines that apply to specific Oracle datatypes. These guidelines should be addressed before you start replication for the first time.


  • Tables that contain a LOB or LONG should have a primary key or unique key defined on them. If a table does not have a key, SharePlex builds its own key from all of the columns except LONGs or LOBs. If a LOB or LONG is the only difference between two rows that otherwise satisfy the Post WHERE clause, then Post may update the wrong row.
  • Dedicate one or more named export queues to tables that contain LOBs. This automatically creates separate Export processes and named post queues with their own Post processes. By separating the processing of LOB datatypes from that of other data, you can improve the overall speed of replication. For more information, see Configure named export queues.
  • To ensure that SharePlex has enough shared memory when replicating LOBs, increase the SP_QUE_POST_SHMSIZE parameter to an initial setting of 60 MB. If SharePlex generates shared memory segment errors such as "Error: sp_cop process sp_mport/sp_opst_mt killed due to SIGSEGV," increase the setting. Note: A larger shared memory segment can result in a large amount of swap space being used on the system, so make sure enough disk space is available.

Manage SharePlex LOB storage

The setup utility installs some tables into a tablespace of your choosing. All but the SHAREPLEX_LOBMAP table use the default storage settings of the tablespace.

The SHAREPLEX_LOBMAP table contains entries for LOBs stored out-of-row. It is created with a 1 MB INITIAL extent, 1 MB NEXT extent, and PCTINCREASE of 10. The MAXEXTENTS is 120, allowing the table to grow to 120 MB.

The default storage usually is sufficient for SHAREPLEX_LOBMAP, permitting more than 4 million LOB entries. If the Oracle tables to be replicated have numerous LOB columns that are inserted or updated frequently, consider increasing the size the SharePlex tablespace accordingly. Take into account that this table shares the tablespace with other SharePlex tables.

If the database uses the cost-based optimizer (CBO) and the tables that SharePlex processes include numerous LOBs, incorporate the SHAREPLEX_LOBMAP table into the analysis schedule.

Note: A new installation of SharePlex does not change storage parameters from a previous installation.

Tune SharePlex based on Oracle data volume

For best performance when transaction volume is either very high or very low, certain parameter changes are recommended before replication starts.

To determine the correct values, refer to the following table under the column that corresponds to the daily archive log volume of your database.

Note: For environments generating between 5 GB and 15 GB of archive log volume daily, the default settings for SharePlex parameters are sufficient, and no action is needed.

To change the parameters, use the set param command in sp_ctrl. Contact Quest Technical Support if you need assistance. For more information about SharePlex parameters and commands, see the SharePlex Reference Guide.

Recommended settings per archive volume
Parameter Less than 5GB/day Between 5GB and 15GB/day Greater than 15GB/day Where to set the parameter
SP_OCT_CHECKPOINT_FREQ 500 Default 5000 source system
SP_ORD_CDA_LIMIT 5 Default 20 source system
SP_ORD_DELAY_RECORDS 50 Default 500 source system
SP_ORD_LDA_ARRAY_SIZE 5 Default 10 source system
SP_ORD_RMSG_LIMIT 20 Default 2000 source system
SP_IMP_WCMT_MSGCNT 100 Default 2500 target system
SP_COP_IDLETIME 120 Default 600 source and target systems

Set system process priority

If Oracle or other processes are assigned resource priority, SharePlex can be left with a default setting and little resource allocation. Oracle increases its CPU utilization during peak processing. If SharePlex loses pace with Oracle, you can try increasing its process priority.

To set process priority on Unix

Use the nice command. Consult with the System Administrator to select an appropriate value based on the requirements of all software running on the system. A root user can modify the niceness value of any process. The SharePlex Administrator user can adjust the niceness value of SharePlex.

To set process priority on Windows

  1. Run SpUtils.
  2. Select the TaskMgr tab.
  3. Right click the SharePlex instance that you want to prioritize.
  4. Select Set Priority, then select the desired priority level.

Enable Oracle direct path loads

By default SharePlex replicates changes made to tables through a SQL*Loader direct-path load (DIRECT=TRUE keyword parameter). There can be only one load per table (PARALLEL=FALSE), although there can be simultaneous loads on different tables. The database must be in archive mode, and table logging must be enabled.

If you expect the direct-path load to be sustained for a long time on the source system, it might be more efficient to load the data to the target database directly, instead of relying on replication. A large direct-path load can cause Capture to lose pace with changes that enter the redo logs from user application activity.

After the load, you should disable check constraints. You can leave ON DELETE CASCADE constraints enabled.

The SP_OCT_REPLICATE_DLOAD parameter controls whether or not direct-path loads are replicated. To disable replication of direct-path loads, change this parameter to 0. For more information, see the SharePlex Reference Guide.

Use compression

You can enable compression to reduce the amount of data that SharePlex sends across the network. SharePlex uses LZIP lossless compression. Enabling compression on the source SharePlex instance automatically enables compression to all targets of the source SharePlex instance.

By default compression is disabled. You can enable compression by itself or in conjunction with encryption.

To enable compression

Set the SP_XPT_ENABLE_COMPRESSION parameter to 1.

sp_ctrl> set param SP_XPT_ENABLE_COMPRESSION 1

To activate the parameter after you set it, stop and start Export.

Enable capture on Exadata machines

To capture data from an Exadata system, the SP_OCT_USE_OCI parameter must be set to 1. For more information, see the parameter documentation in the SharePlex Reference Guide.

If you need to adjust capture performance after replication starts, see Tune Capture on Exadata.

Configure support of Data Pump exports

When replicating Oracle Data Pump export operations, set the SP_OCT_ALLOW_DP_DDL parameter to 1, and then restart Capture.

This parameter can be enabled if SharePlex fails to replicate DDL operations that occur when running an Oracle Data Pump export/import. Occasionally, SharePlex identifies DDL in a Data Pump load as recursive DDL that should be ignored. This parameter directs SharePlex to capture that DDL.

A setting of 1 enables this parameter. After the load is finished, set this parameter back to 0 and then restart Capture.

Set up TDE Support

SharePlex uses the TDE Master Encryption Key to decrypt TDE-protected data that must be replicated. SharePlex uses the Oracle wallet password to access the TDE Master Encryption Key.

If the wallet opens successfully, Capture connects to the decryption module and processes the data. If the wallet does not open, Capture remains in the initialization state until either the wallet is opened or the process is stopped. The initialization state that is displayed in the show capture command is "Capture state: Waiting for open wallet."

Note:  The SharePlex copy/append command does not support TDE. For full information on the Oracle features that SharePlex supports, see the SharePlex Release Notes.

Required privilege to capture TDE-protected data

To decrypt TDE-protected data from the redo log, the SharePlex Administrator must open Oracle Wallet using the wallet password. By default, only the Oracle Wallet owner-user has read and write permissions for this file. You can either start as the owner of the wallet, or you can grant read permission to the file to the dba group, because the SharePlex Administrator user is a member of that group.

Configure SharePlex to capture TDE-protected data

To configure SharePlex to support TDE-protected data, two setup tools must be run:

  • (If this was not done during installation) Run ora_setup. When prompted to enable TDE replication, type "y" and then enter the fully qualified path to the TDE wallet file, including the wallet file name, when prompted. See the SharePlex Reference Guide for more information about this utility.
  • Run the sp_wallet utility to provide the Oracle Wallet password to SharePlex. This utility can be run in manual or auto-open mode.

To run sp_wallet and manually supply the password

  1. On the source system, start SharePlex from the SharePlex product directory. You are prompted to run sp_wallet.

    *** To enable TDE replication, run sp_wallet and provide the wallet password ***

  2. Run sp_wallet.

    ./sp_wallet [-r port_number]

    Important! On Windows, if you installed SharePlex on any port other than the default of 2100, use the -r option to specify the port number. For example, in the following command the port number is 9400:

    ./sp_wallet -r 9400

    wallet password: walletpw

    Wallet loaded into SharePlex

To run sp_wallet in auto-open mode

If you are using an auto-open wallet, you can configure SharePlex to open the TDE wallet automatically. This eliminates the need to run sp_wallet manually at SharePlex startup. The syntax is:

./sp_wallet --auto-open [-r port_number]

Important! Using the auto-open wallet feature has additional security considerations. See the Oracle documentation for more information. In addition, do not back up the SharePlex variable-data directory together with the Oracle wallet and the Oracle data files.

To cancel auto-open mode

./sp_wallet --no-auto-open [-r port_number]

To change the TDE master encryption key

If you need to change the TDE Master Encryption Key while a SharePlex configuration is active, take the following steps to ensure that SharePlex continues to replicate the TDE-protected data after the changes.

  1. Quiesce the source database.
  2. Make sure that Capture finishes processing the remaining data in the redo log.
  3. Shut down SharePlex.
  4. Change the TDE Master Encryption Key.
  5. Restart SharePlex.
  6. Run the sp_wallet utility to provide SharePlex with the new TDE Master Encryption Key.

    ./sp_wallet [-r port_number]

Create a configuration file

Create configuration files

This chapter contains the information that you need to know in order to create a configuration file. SharePlex takes direction from the configuration file to determine which tables to replicate and where to send the replicated data. This file also provides any special processing instructions, such as column mapping and data filtering.


Ensure compatible source-target mapping

Create a configuration file > Ensure compatible source-target mapping

The following guidelines help you ensure that the source and target objects that you want to map in a replication configuration are compatible.

  • For most replication strategies, the name and/or owner of a source object can be different from that of its target object. SharePlex replicates to the correct object because you specify it by owner and name within the configuration file. For high-availability configurations, the owner and name of a source table should be the same as the owner and name of the target table.
  • Corresponding source and target columns must contain compatible datatypes (same type, size, precision).
  • Corresponding source and target rows must contain the same values unless transformation is being used.

  • The names of the target columns must be identical to the names of the source columns, unless you use column mapping in the configuration file. For more information, see Map source and target columns.
  • A target table can have more columns than the source table. If the source and related target column names are identical, SharePlex will ignore the extra columns in the target table. If the source and target column names are not identical, SharePlex maps a one-to-one relationship in the order that the columns are defined in each table (for example, map the first column in the source to the first column in the target, map the second column to the second column, and so forth). To avoid Oracle errors if the extra (non-mapped) columns are NOT NULL, define default values for those columns. For more information, see Map source and target columns.
  • A target table cannot have fewer columns than the number of columns in the source table, unless you use vertically partitioned replication to replicate a subset of the source columns that matches, or has fewer columns than, the target. For more information, see Configure vertically partitioned replication..
Related Documents