SharePlex 10.2.1 - Installation and Setup Guide

Add Oracle sequences to an active replication configuration

The procedure to use to add a sequence to an active configuration file depends on whether or not you can stop user access to the objects that use the sequence. If the sequences are used to populate a column in a table, you may not be able to stop user access.

Review the following procedures to determine which one will work best in your environment:

Enable auto-add of sequences

Add sequences if auto-add is not enabled

Supported databases

Oracle source and target

Enable auto-add of sequences

You can configure SharePlex to add sequences to replication automatically if their names satisfy a wildcard in the configuration file. For more information, see Control Oracle DDL replication.

Add sequences if auto-add is not enabled

The following procedures apply if the auto-add feature for sequences is not enabled.

Add a sequence if the sequence does not populate a column

1. Stop user activity to the objects on the source system.

2. In sp_ctrl, issue the copy config command to make a copy of the active configuration file.

   sp_ctrl> copy config filename to newname

   Where: filename is the name of the active file and newname is the name of the new one.

3. Issue the edit config command to open the new configuration file in the default text editor.

   sp_ctrl> edit config newname

4. Add the new sequences to the configuration file.
5. Save and close the file.

6. Create the target sequence on the target system. To ensure uniqueness 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_INCREMENT_BY_value = START_WITH_value

7. Activate the new configuration. This deactivates the original configuration.

   sp_ctrl> activate config newname

8. Allow users to access the objects.

Add a sequence if the sequence populates a column

1. In sp_ctrl, issue the copy config command to make a copy of the active configuration file.

   sp_ctrl> copy config filename to newname

   Where: filename is the name of the active file and newname is the name of the new one.

2. Issue the edit config command to open the new configuration file in the default text editor.

   sp_ctrl> edit config newname

3. Add the new sequences to the configuration file.
4. Save and close the file.

5. Activate the new configuration. This deactivates the original configuration.

   sp_ctrl> activate config newname

6. On the source system, flush the data from source system to the target system. This command stops Post and places a marker in the data stream that establishes a synchronization point between source and target data.

   sp_ctrl>  flush datasource

   Where: datasource is o.ORACLE_SID of the source instance — for example o.oraA.

7. After Post stops, issue the following Oracle command on the target system to find the last known value of the sequence. Make a record of this value.

   select max(column_name) = last known value

8. Determine the value of the following equation.

   source_INCREMENT_BY_value x source_CACHE_value

   For example, if the source sequence is incremented by 2 and the cache size is 10, the value would be 20.

9. Starting with the value that you recorded for the select max (column_name) command, determine the next highest multiple of (source_INCREMENT_BY_value x source_CACHE_value).

   Example:

   INCREMENT_BY = 2

   CACHE = 10

   select max(column_name) = 24

   Next highest multiple of (2 x 10) after 24 = 40.

10. To the value obtained in the previous step, add another multiple of (source_INCREMENT_BY_value x source_CACHE_value). The result determines the START WITH value of the target sequence. For example, in the previous equation the START WITH value would be: 40 + (2 x 10) = 60.
11. Create the target sequence with the START WITH value that you calculated.

12. On the target, start Post.

    sp_ctrl> start post

    SharePlex will continue replicating the data, while keeping the target sequence at least one multiple of (source_INCREMENT_BY_value x source_CACHE_value) ahead of the source sequence.

IMPORTANT!

Sequences continue to be incremented even when a transaction is rolled back. If numerous rollbacks are issued for a source table that uses a replicated sequence, it causes the sequence values to increase without actually being used in columns in the table. As a result, when Post applies the next valid operation, the sequence value on the target system could be less than the value in the replicated row.

When there are numerous rollbacks, view the target table regularly to ensure that the current value of the target sequence remains greater than the maximum value in the table. If the current value of the target sequence is less than the maximum value in the table, repeat the preceding procedure to re-establish the sequence relationships.