SharePlex 9.2.7 - Installation and Setup for Oracle Source

Set up an Oracle environment for replication

Contents

Overview

This chapter contains instructions for preparing an Oracle source or target database environment for replication. The tasks outlined in this chapter must be performed before you perform initial synchronization to start replication in your environment. Perform all tasks that apply to your data and to your replication objectives.

Set up Oracle logging to support SharePlex

SharePlex captures from the online and archived Oracle redo logs. SharePlex supports redo logs and datafiles that are stored on raw devices, on filesystem devices, and ASM instances.

Enable archive logging

If the Capture process stops (or is stopped by a SharePlex user) while replication is active, Capture records its location in the redo log and continues from that point when started again. However, Capture may need to read the archive logs instead of the redo logs if the following conditions occur:

• There is a long delay between when Capture stops and starts again, and the redo logs wrap during that time. When the archive logs are available, Capture reads through them to locate the records rhR it missed.
• Capture loses pace with Oracle transaction activity, and the redo logs wrap before Capture catches up to Oracle.

To support uninterrupted capture when an online log is not available, archive logging must be enabled on the source system and any other system from which SharePlex will capture data, such as an intermediate system in a cascading replication strategy. Otherwise, if the online logs wrap before Capture is finished processing them, a resynchronization of the source and target data will be necessary.

To avoid Capture issues, configure archive logging to support faster and uninterrupted replication as follows:

Configure the online logs

Ideally, the redo logs should be configured so that SharePlex can avoid reading the archive logs. In most cases, reading the online logs is faster than reading the archives. Ensure that the online redo logs are large and numerous enough to minimize processing from the archive logs. At minimum, there should be enough redo log capacity to hold several hours worth of data without wrapping.

To test an appropriate online log configuration

In pre-production testing, you can determine if Capture is reading the archive logs by doing the following:

1. Determine the log that SharePlex is processing by querying the SHAREPLEX_ACTID table.

   SQL> select seqno from splex.shareplex_actid

2. Determine the log that Oracle is writing to by querying Oracle's V$LOG table.

   SQL> select sequence# from v$log where status='CURRENT'

3. Subtract the seqno value from the sequence# value. This reveals how many logs Capture lags behind Oracle.
4. Subtract the number of online redo logs from that value. If the number is negative, SharePlex is processing archive logs. For example, if you have 10 redo logs and SharePlex is 11 logs behind, it is processing archive logs. You can then use this result to adjust the online logging configuration.

Important: If Capture falls behind the pace at which Oracle generates redo volume, the following may apply:

• It might be more practical to resynchronize the data instead of waiting for SharePlex to restore parity by capturing from the archive logs.
• It is possible to run out of disk space on the source system while Capture processes and queues the operations that it missed.
• There is the potential for SharePlex to lose information needed for Post to construct SQL statements, especially if the needed archive logs no longer are available. Always monitor disk space and latency while SharePlex is running.

Set the appropriate logging level

• Minimal supplemental logging must be set before you activate the SharePlex replication configuration.

• In addition to minimal supplemental logging, it is strongly recommended that you enable both primary key and unique key supplemental logging, or that you create a supplemental log group on unique columns for every table in replication. When the key column values for a row update are in the redo log, SharePlex does not have to fetch them from the database. On a busy system, this greatly improves the performance of the Read process. Some SharePlex features require primary key and unique key logging to be enabled.

  Note:

  ALTER TABLE DDL commands that change the rowid of a table can affect subsequent DML operations if the primary or unique keys of the tables in replication are not being logged. When the keys are not logged, SharePlex fetches their values based on the rowid. Any operation that changes the rowid, such as ALTER TABLE...MOVE, can cause the wrong key values to be used for subsequent DML operations.

• If you are using vertically partitioned replication for any table, you can use table-level logging to log only the columns that you want to replicate, plus any other columns that may be referenced by them, such as foreign keys. If you are using horizontally partitioned replication for the same table, make certain that you log the columns that you specified as the filters.

Set up Oracle database objects for replication

This topic provides information about how to configure certain characteristics of Oracle database objects that you will be replicating with SharePlex.

Ensure row uniqueness

SharePlex must have a way to ensure that the row it is changing on the target is the correct one that matches the source row. This is accomplished by ensuring a one-to-one relationship through the use of keys and indexes.

The role of keys

SharePlex works fastest when there is a primary key or a unique key on all source and target tables being replicated, especially large tables and tables containing LONG columns. When choosing a key to use, SharePlex uses the best available key column in the following order of priority.

• Primary key
• Unique key with the fewest columns, where at least one of the columns is NOT NULL

• Unique key with the fewest columns.

For best performance, it is recommended that you enable primary and unique key supplemental logging.

If a table does not have a primary or unique key, or if Oracle logs the wrong unique key for SharePlex, you can specify columns for SharePlex to use as a key when you create the configuration file. This is known as a key definition and is specified in the configuration file. For more information, see Define a unique key in the SharePlex Administration Guide.

An alternative to a key definition is to create or use a unique index based on one or more columns that establish uniqueness.

Ensure that the correct key is logged

When primary and unique key supplemental logging is enabled and a table does not have a primary key, Oracle has to decide what type of unique key to log. If the table has multiple unique keys, Oracle determines the best key to use and logs those column values for every UPDATE. If a table does not have any type of key, Oracle logs all columns that are not a LONG or a LOB.

SharePlex must also identify a key to use to replicate data. Like Oracle, SharePlex chooses a key in this order:

• primary key if there is one
• the best (or only) unique key if there is one
• all the columns

If a table being replicated by SharePlex does not have a primary key, but does have multiple unique keys, it is possible that the key columns that Oracle logs are not those that SharePlex requires.

Tables without a key or unique index

If SharePlex cannot detect a key or unique index on a table, it constructs a key using all of the columns except LONGs and LOBs. This key is maintained internally and is not created on the table itself.

This is not a desirable option, because the resultant WHERE clause causes Oracle to perform a full table scan on the target table to find the row, and that significantly slows replication. Additionally, row uniqueness cannot be enforced.

For example, if there is a possibility that non-LONG columns in different rows could contain identical values, but the LONG columns could have different values, the table can be out of synchronization without being detected by you or SharePlex. The following example illustrates the problem. The rows in the table are identical except for the LONG columns, and there is no primary or unique key.

Suppose a user on the source system changes Column A to 15 in the first row. When constructing the SQL statement to apply the change to the target table, SharePlex constructs a key using Columns A and B (UPDATE tablename SET Column A to 15 WHERE Column A = 10 and Column B = 20) to locate the row to change. There are three rows that meet this criteria, so SharePlex could post the change to the wrong row.

Keys with nulls

If a key permits NULLs, SharePlex cannot ensure the uniqueness of a row for UPDATEs and DELETES, so there is a possibility it could change the wrong row on the target system. To control how SharePlex addresses keys that permit NULLs, set the SP_SYS_IN_SYNC parameter. See the SharePlex Reference Guide for more information.

Changes to key values

SharePlex handles changes to the value of a key column without any special settings. However, if sequences are used for keys and if there is a possibility that those values will be updated, create the sequences so that the updates cannot result in a duplication of keys on the target system. Otherwise, if a new value is used to apply an operation, and that value already exists as a key in another row in the target table, SharePlex will return unique-key constraint violations and out-of-sync errors. This type of error can happen when you update values using an “x +n” formula, where n is an incremental increase. It is possible for one of the “x +n” values to equal an existing value.

The following is an example, where the values in the key column are increased by 1:

Key_Col

1

4

5

7

SQL> update table X set a=a+1; commit

The new values become the following, and they are replicated to the target system:

Key_Col

2

5

6

8

SharePlex performs the update in the order that the operations enter the redo log:

update x set a=2 where a=1; (Succeeds)

update x set a=5 where a=4; (Fails because a value of 5 already exists.)

update x set a=6 where a=5; (Succeeds)

update x set a=8 where a=7; (Succeeds)

The pre-image value that Post uses for the target sequence is the same as the increased value replicated from the source. Oracle rejects the operation as a unique constraint violation. Another example would be a transaction that updates A to B and then updates B to C.

Important! If you plan to use peer-to-peer replication, there are additional requirements for keys. For more information, see Configure peer-to-peer replication in the SharePlex Administration Guide.

Indexes

The correct use of indexes is important in a replication environment. An index maintains the integrity of the target data.

• When replicating a source table that has a unique index, the target table also should have a unique index.
• All large tables should have a unique index on the target system. If there is no unique index, Oracle scans the entire table in order to locate the rows to be changed by Post.
• Some applications do not use primary key constraints, and therefore a unique index is not created by default. Often, however, there will be an index that was created, but not named, as a unique index (not using CREATE UNIQUE INDEX command), although it was created on one or more columns populated with unique values, such as a person’s name and employee identification number. If no unique index exists for a table, it is recommended that you create one or specify a user-defined key when you create the configuration file. For more information, see Define a unique key in the SharePlex Administration Guide.
• Once you have identified or created a unique index, you can ensure that Oracle uses it by employing the hints feature of SharePlex. For more information, see Use Oracle INDEX hints in the SharePlex Administration Guide.

• If tables have foreign keys, make sure the appropriate columns are indexed so that modifications to the foreign keys do not cause full table scans.

• Keep your indexes up-to-date, or it can slow the Post process. Rebuild the ones that are fragmented.

If there are too many indexes on a target table, Oracle must update them all as rows are added and deleted. This slows the entire system, including replication. Consider limiting the number of indexes to the ones that have the most utility.

For applications that perform mostly one type of DML, consider the following:

• INSERTs: Use just a few indexes, to limit their maintenance.
• UPDATEs: Use an index on a column that does not change after an INSERT statement.
• DELETEs: Remove as many indexes as you can.

If you run large batch jobs that do millions of SQL operations, remove unnecessary indexes before the batch job, then rebuild them at the end. This makes SharePlex run faster, and you will have more organized indexes afterward.

Bitmap indexes

For performance purposes, avoid the use of bitmap indexes while the Post process is applying the data. These indexes can adversely affect the performance of the Post process.

If you must use bitmap indexes on target tables, weigh their benefit for queries against the their impact on the transactions applied by Post:

• When Oracle adds, updates or deletes a bitmap entry, it effectively locks all the rows associated with the bitmap segment.
• A bitmap segment can contain references to hundreds of rows. As a result, changes made by different Post sessions (there is a Post session for every session on the source system) can block each other if their work updates bitmap entries in the same bitmap segment.
• To proceed, Post must detect and resolve the blocking, which delays posting significantly if the number of locks is high.
• In general, frequent inserts by multiple concurrent sessions into a table with bitmap indexes will incur lock conflicts, but random update and delete activity on such a table will not. SharePlex follows the Oracle recommendation to have a bitmap index on a more static table.

Note: Replicating bitmap indexes is not recommended. Every time you change a table with a bitmap index, the index is rebuilt. This associated cost (Oracle time and resources) to rebuild is added to your SQL UPDATE statement.

Prevent triggers from firing on the target

DML changes resulting from a trigger firing on the source system enter the redo log and are replicated to the target database by SharePlex. Consequently, if the same trigger fires on the target system and initiates the same DML changes (which already were made through replication) there will be out-of-sync errors.

For example, if an INSERT to TableA on the source system triggers an INSERT into TableB, SharePlex replicates both INSERTs to the target system. The Post process applies the first INSERT to TableA on the target system, triggering an INSERT into TableB. Thus, when Post attempts to post the replicated INSERT to TableB, there will be a unique-key violation. The row already exists because the trigger fired for TableA.

Triggers can be handled as follows, depending on your replication strategy:

Triggers on objects not in the replication configuration can remain active.

For important information about how to use the trigger scripts, see the SharePlex Reference Guide.

Configure integrity constraints

Integrity constraints have a effect on replication. Follow these guidelines to ensure that they are handled.

Foreign key constraints

Foreign key constraints must be disabled on the target tables. SharePlex replicates the results of the source foreign key constraints. Tables with foreign keys to one another must all be included in the replication configuration for accurate replication of the source foreign key results. All tables with referential constraints must exist in the target database. If you leave one or more out, the referential integrity could become corrupted.

Note: If constraints are DEFERRED on the target tables, the Post transaction can fail on the constraint validation. To work around this problem, enable the SP_OPO_DISABLE_OBJNUM parameter to allow the transaction to succeed. The underlying target table still remains out-of-sync until it is resynchronized.

ON DELETE CASCADE constraints

SharePlex provides a feature that allows ON DELETE CASCADE constraints to remain enabled on the target tables, but it must be enabled explicitly through a parameter setting. Post detects the ON DELETE CASCADE dependencies and suppresses the posting of any replicated cascaded deletes to the child tables.

If you do not enable this support through SharePlex you must disable these constraints manually on the target. Otherwise SharePlex replicates both the primary delete and the cascaded deletes, which results in conflicts and errors when the deletes cascade on the target.

To enable ON DELETE CASCADE support

• Enable the logging of primary keys, unique index columns, and foreign key columns on the source.

• Set the following SharePlex parameters:

  • SP_OPO_DEPENDENCY_CHECK parameter to 2
  • SP_OCT_REDUCED_KEY parameter to 0
  • SP_OPO_REDUCED_KEY parameter to 0, 1 or 2

NOTE: In peer-to-peer replication, SP_OPO_REDUCED_KEY must be set to 0.

Check constraints

Disable check constraints on the target system. They add unnecessary overhead. These checks are redundant in a well maintained, synchronized replication environment because they are satisfied on the source system. For high-availability purposes, you can build a script that re-enables the constraints as part of the failover procedure.

Prevent access to target objects

In all scenarios except peer-to-peer replication, the SharePlex database user must be the only user allowed to perform DML or DDL on the target objects. If DML or DDL changes are made to target objects by other individuals, jobs or applications, the target data may no longer reflect the state of the data on the source system. For more information, see Understand the concept of synchronization in the SharePlex Administration Guide.

Configure sequences

SharePlex replicates changes to Oracle sequences that are made in an ALTER SEQUENCE and DROP SEQUENCE commands and during DML transactions. It may not be necessary to replicate sequences in certain replication strategies.

• High availability: Yes

  The way that SharePlex replicates sequences allows users to begin using the failover database immediately without worrying about incrementing the sequences or reusing one.

• Reporting, data sharing, other basic one-way replication: No

  If sequences are unnecessary on the target system, do not replicate them. It can slow down replication. Even if a sequence is used to generate keys in a source table, the sequence values are part of the key columns when the replicated rows are inserted on the target system. The sequence itself does not have to be replicated.

• Peer-to-peer: No

  SharePlex does not support peer-to-peer replication of identical sequences. For more information, see Configure peer-to-peer replication in the SharePlex Administration Guide.

To configure sequences for replication

• To replicate sequences, the supplemental logging of primary and unique keys must be enabled at the database level, or you must enable supplemental logging for primary keys on the sys.seq$ table.

• Use caching and set the cache to increments of at least 20. When sequences are cached, SharePlex can replicate the values as a group. When sequences are not cached, SharePlex must go to disk every time a value is obtained from the sequence, which slows replication of more critical data.

• To ensure the uniqueness of sequences on the target system, the start value of the target sequence must be larger than the start value of the source sequence. Use the following formula to determine the target START_WITH value:

  source_current_value+ (source_INCREMENT_BY_value x source_CACHE_value) =target_START_WITH_value

  Important! (source_INCREMENT_BY_value x source_CACHE_value) should not exceed 2 GB, or replication of sequences will fail.

• Specify sequences in the configuration by owner and name, as you would a table.
• Because changes to sequences are DDL commands, the Post process pauses all posting until the sequence update is finished. For that reason, it is recommended that you process sequences through a separate post queue from the tables, especially if the sequences are not cached. For more information, see Configure data replication in the .SharePlex Administration Guide.

SharePlex uses the ALTER SEQUENCE command to update sequences in a target database as follows:

• Change the increment value to:

  source_INCREMENT_BY_valuexsource_CACHE_value

• Set to NOCACHE.
• UPDATE the sequence(s).

• ALTER the sequence(s) again by setting the following values:

  Increment_value=source_INCREMENT_BY_value

  Cache_value=source_CACHE_value

SharePlex treats ALTER SEQUENCE operations like a simple SELECT (UPDATE) to a sequence because the redo log records make no distinctions between the two operations.

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

For the PROCESSES and SESSIONS parameters, 65 is the minimum value required by the SharePlex Post process so that it can open enough SQL connections to the target database to handle current transaction volume. This value is determined by the default setting of the SP_OPO_THREADS_MAX parameter, plus one for the main Post thread.

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.

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.
• If you are replicating LOB data, conversion is required regardless of what the source character set is.

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. Install SharePlex using the Linux/Unix installer for your operating system.
4. 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.