SharePlex 8.6.6 - Installation Guide

Set up an Oracle database to support SharePlex

Certain Oracle database settings have an affect on replication and must be set appropriately.

Adjust OPEN_CURSORS to support Post cursors

SharePlex requires the value of the Oracle OPEN_CURSORS parameter to be set correctly on the target database. To view the OPEN_CURSORS value, query the database using the following SQL statement:

select value from V$PARAMETER where name = 'open_cursors';

The Post process reserves 10 cursors for routine calls that are closed once they finish, plus a minimum of 50 cursors per transaction when the SQL Cache feature is enabled (this is the default). For more information, see Tune SQL Caching.

If you intend to disable SQL caching, then estimate the peak number of concurrent updating transactions (sessions) that your applications generate, and follow this formula:

10 + (peak number of concurrent transactions x 2) = minimum open cursors needed

The OPEN_CURSORS value can be modified or added if absent. Consult the Oracle documentation prior to making changes to Oracle parameters.

Adjust the PROCESSES parameter to support connections

The PROCESSES parameter in the init.ora file must be set to accommodate connections created by SharePlex and the database users. Its value depends on whether a database is a source database, a target database, or one that serves as both a source and target database.

Database is a source only

If a database serves only as a source, the following formula takes into account logins made by the Read process.

(peak number of source database sessions) + (background Oracle processes) + (value of SP_ORD_LDA_ARRAY_SIZE parameter +3) = setting for PROCESSES

Database is a target only

The Post process creates as many connections on the target system as there are sessions on the source system in order to maintain transaction consistency. The PROCESSES parameter on the target system must be set high enough to accommodate all of those connections, plus:

• The background Oracle processes those connections generate
• The peak number of users expected to access the target database for queries

Use the following formula as a guide:

(peak number of source database sessions) + (peak number of target database sessions) + (background Oracle processes) = setting for PROCESSES

Database is a source and target

If a database serves as both a source and a target, the following formula takes into account connections made by:

• The Read process
• The Post process
• Background Oracle processes
• User connections

(peak number of source database sessions) + (peak number of target database sessions) + (background Oracle processes) + (value of SP_ORD_LDA_ARRAY_SIZE parameter +3) = setting for PROCESSES

Adjust the log buffer size to improve posting

The number of database writers has an impact on replication, especially when there are numerous concurrent transactions. Whenever a transaction is committed, its buffered data is flushed to disk. If most transactions are small, but the buffer is large, this can cause slow posting. When a large transaction is committed while another, more normal-sized transaction is committed, the second COMMIT must wait while the entire buffer is flushed to disk.

Reducing the size of the buffer that is flushed to disk can speed the Post process. Try decreasing the size of the log buffer to 1024 KB, or even 512 KB if possible.

Adjust the SharePlex transaction table based on user volume

SharePlex updates the SHAREPLEX_TRANS table to maintain read consistency for the target database. You might need to adjust the initrans setting of this table to improve replication performance and reduce contention on that table:

• If the production database has between 500 and 1,000 concurrent users, rebuild the SHAREPLEX_TRANS table to have an initrans of 30.
• If the production database has more than 1,000 concurrent users, rebuild the SHAREPLEX_TRANS table to have an initrans value of 40.

Control conversion of character sets

This topic explains how SharePlex handles character set conversion between an Oracle source and target, and between an Oracle source and a non-Oracle target.

Replication between an Oracle source and Oracle targets

For SharePlex to replicate all characters within the Oracle character sets that you are using, one of the following must be true:

• The character sets are identical on the source and target
• The character set of the source database is a subset of the character set of the target database (all characters contained on the source exist in the character set of the target)

The following character sets are tested and supported for SharePlex:

US7ASCII

UTF8

WE8ISO8859P1

AL16UTF16

AL32UTF8

KO16KSC5601

By default, SharePlex allows an Oracle target database to perform character conversion. Post notifies Oracle of the character encoding of the source data and Oracle performs any required conversion.

Depending on the character sets involved, the Oracle conversion might lead to data loss. For example:

Example 1: The Japanese character for 'rice' in the JA16SJIS character set has no corresponding symbol in the US7ASCII character set. If you attempt to replicate this symbol into a US7ASCII database, Oracle converts it to a '?' character.

Example 2: According to Oracle, the WE8ISO8859P1 character set is a superset of the US7ASCII character set, so it is logical to assume that any character in US7ASCII is posted unconverted into a WE8ISO8859P1 target database. This is true for characters in the range 0x00 to 0x7F. However, Oracle strips off the top bit of characters in the range 0x80 to 0xFF. This "conversion" may result in data loss while replicating to a character set that is a superset of the source.

Note: Oracle does not convert characters if the character sets are identical. Thus, posting WE8ISO8859P1 data to a database with a character set of WE8ISO8859P1 bypasses the Oracle conversion process.

To apply data without conversion

Set the SP_OPO_NLS_CONVERSION parameter to 1 to apply the data without conversion. For more information, see the SharePlex SharePlex Reference Guide.

Note: SharePlex will always convert NVARCHAR and NCLOB data if the NLS_NCHAR_CHARACTERSET of the source database is not the same as that of the target database.

Replication between an Oracle source and non-Oracle targets

When replicating to an Open Target target (non-Oracle target), SharePlex supports replication from any Oracle Unicode character set and the US7ASCII character set. SharePlex posts data to Open Target in the Unicode character set, and therefore if the source data is Unicode or US7ASCII, no conversion on the target is required.

However, if the following are true, conversion is required on the target:

• If the character set of the source data is anything other than Oracle Unicode or US7ASCII, you must install an Oracle client on the target to perform the conversion to Unicode for posting to the target.
• If the data must be posted to the target database in any character set other than Unicode, you must install an Oracle client on the target to perform the conversion and use the target command to identify the target character set for Post to use. See the SharePlex Reference Guide for more information about this command.

To perform conversion with an Oracle client on Linux

1. Install an Oracle Administrator client on the target system. The client must be the Administrator installation type. The Instant Client and Runtime installation types are not supported.
2. Set ORACLE_HOME to the client installation. Set ORACLE_SID to an alias or a non-existing SID. SharePlex does not use them and a database does not have to be running.
3. Download the Oracle-based SharePlex installer, rather than the Open Target installer, to install SharePlex on the target system. The Oracle-based installer includes functionality that directs Post to use the conversion functions from the Oracle client library to convert the data before posting to the target database.
4. Follow the instructions for installing SharePlex for Oracle (not the ones for installing on Open Target).
5. Make certain the SP_OPX_NLS_CONVERSION parameter is set to the default of 1.

To perform conversion with an Oracle client on Windows

1. Install an Oracle Administrator client on the target system. The client must be the Administrator installation type. The Instant Client and Runtime installation types are not supported.
2. In the SharePlex Registry key \HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\QuestSoftware\SharePlex\port_number, set ORACLE_HOME to the Oracle client installation location, and set ORACLE_SID to an alias or a non-existing SID. No Oracle database is required. SharePlex only needs to use the client libraries.
3. Install SharePlex using the Windows installer.
4. Make certain the SP_OPX_NLS_CONVERSION parameter is set to the default of 1.

To apply Unicode and US7ASCII data without conversion

If the source data is Unicode or US7ASCII and you are not replicating LOB data, no conversion or Oracle client is needed. Set the SP_OPX_NLS_CONVERSION parameter to 0 to disable conversion, and then restart Post if it is running.