This topic contains setup guidelines that apply to specific Oracle data types. These guidelines should be addressed before you start replication for the first time.
Manage SharePlex LOB storage
The Database 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.
Preferred action: If you enable supplemental logging for primary and unique keys, you can set the SP_OCT_ENABLE_LOBMAP parameter to 0, and nothing will be stored in the SHAREPLEX_LOBMAP table. In this case, you do not have to consider its size growth. It is recommended that you enable supplemental logging for primary and unique keys to maximize the performance of the Read process.
Alternate action: 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.
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
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.
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. For more information about encryption, see the SharePlex Administration Guide.
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.
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.
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.
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.
To configure SharePlex to support TDE-protected data, two setup tools must be run:
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 ***
./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.
Run the sp_wallet utility to provide SharePlex with the new TDE Master Encryption Key.
./sp_wallet [-r port_number]
This chapter contains instructions for configuring SharePlex to replicate from Oracle to a different type of target. This is known as heterogeneous replication.
These instructions highlight specific tasks that are pertinent to the flow of data between source and target. Refer to other topics in the SharePlex documentation as needed to complete the configuration, deploy any optional features that apply, and monitor and maintain the environment.
For additional information, see:
SharePlex can post replicated Oracle data to a MySQL or Aurora target database through an Open Database Connectivity (ODBC) interface.
These instructions contain setup instructions that are specific to this target. Install SharePlex on the source and target according to the appropriate directions in this manual before performing these setup steps.
For the versions, data types and operations that are supported when using SharePlex to replicate to this target, see the SharePlex Release Notes.
Important! If replicating to MySQL or Aurora on a PaaS cloud server (no access to the operating system), see the installation instructions in Installation and setup for cloud-hosted databases.
To support replication between a source of one database type and a target of another type, the letter case of the names of the source and target columns must be the same, for example the column names on both sides in lower case or both sides in upper case. If the case differs between the source and target column names, use the column mapping feature to map the column names in the configuration file.
See the SharePlex Administration Guide for more information about column mapping with SharePlex.
Set up SharePlex and the database on the Oracle source system as follows.
To replicate from an Oracle source to an Open Target target, you must make key information available to SharePlex.
Enable PK/UK supplemental logging in the Oracle source database. SharePlex must have the Oracle key information to build an appropriate key on the target.
Set the SP_OCT_USE_SUPP_KEYS parameter to a value of 1. This parameter directs SharePlex to use the columns set by Oracle's supplemental logging as the key columns when a row is updated or deleted. When both supplemental logging and this parameter are set, it ensures that SharePlex can always build a key and that the SharePlex key will match the Oracle key.
See the SharePlex Reference Guide for more information about this parameter.
On the source, create a SharePlex configuration file that specifies capture and routing information.
If target owner or table name is defined in the database as anything other than UPPERCASE, be certain to:
Note: This is a basic one-source, one-target configuration using no additional SharePlex configuration features. See "Configure SharePlex to replicate data" in the SharePlex Administration Guide for important information about creating a configuration file and for additional setup instructions for more complex replication scenarios.
The following configuration file replicates table HR.Emp from Oracle instance ora112 to target table region1.emp in database mydb on target system sysprod. The source table is case-sensitive.
HR."Emp" region1.emp firstname.lastname@example.org
Make certain that the database setup meets all of the requirements in Open Target checklist .