SharePlex 9.2.6 - Administration Guide

Change Partitioned Replication

You can change a horizontally partitioned or vertically partitioned replication configuration while replication is active. Both of these procedures require the reactivation of the active replication configuration, but SharePlex only locks tables that are associated with those changes.

Note: To learn more about changing a configuration file, review Change an active configuration file.

For more information about partitioned replication, see Configure partitioned replication.

Supported databases

Oracle

All targets

To change horizontally partitioned replication

1. Run sp_ctrl.

2. Issue one of the following commands to change the partition or partition scheme. For syntax and other information, see the alphabetical command listings in the SharePlex Reference Guide.

   Command	Auth. level	Description
   add partition	2	Creates partition schemes and row partitions.
   drop partition	2	Removes a row partition from a partition scheme.
   drop partition scheme	2	Removes a partition scheme.
   modify partition	2	Modifies a row partition of a partition scheme.

3. If you dropped a partition scheme:

   1. Copy (but do not deactivate) the active configuration file to a new file.

      sp_ctrl> copy config filename to newname

   2. Edit the copy to remove or change the routing map where the partition scheme was specified.

      sp_ctrl> edit config filename

4. Activate the new configuration file.

   sp_ctrl> activate config filename

To change vertically partitioned replication

1. Make a copy of (but do not deactivate) the active configuration file.

   sp_ctrl> copy config filename to newname

2. Edit the copy to change the appropriate column partition.

   sp_ctrl> edit config filename

3. Activate the new configuration file.

   sp_ctrl> activate config filename

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.

Remove objects from replication

To remove source objects from replication, the configuration must be reactivated.

Note: (Oracle only) Objects being removed are locked when the configuration is activated, but only those objects are locked, so the activation is less time-consuming that the original activation.

You can prevent posting to a table without removing it from the configuration file. You may need to do this if, for example, there is data corruption and you do not want DML or DDL operations to be applied to that table. To prevent posting to a table, use the SP_OPO_DISABLE_OBJECT_NUM parameter. For more information about this parameter, see the SharePlex Reference Guide.

Supported databases

All databases supported by SharePlex

Procedure

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. In the new configuration file, delete the entries for the objects that you want to remove from replication. If the object that you want to remove from replication satisfies a wildcard, use the not notation to exclude the object. For more information, see Use Wildcards to specify multiple objects.

4. Save and close the file.

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

   sp_ctrl> activate config newname

6. Allow users to access the removed objects.

Make DDL changes in an active replication configuration

This procedure applies to DDL changes that are not of a type that is supported by SharePlex. DDL that is supported by SharePlex can be applied to the source database without reactivating the configuration file or stopping user access to objects, assuming the applicable SharePlex parameters are set correctly. Supported DDL is replicated by SharePlex to the target, where it is applied by Post. For a list of supported DDL operations and required parameters, see the SharePlex Release Notes.

Use this procedure to apply DDL that is not of a type that is supported by SharePlex. The DDL must be applied outside SharePlex on both the source and target systems. This procedure requires stopping access to the objects in the configuration file and a reactivation of the configuration file to update the internal tables. However, only the changed objects are analyzed, so the activation time will be shorter than the time required for a full activation.

Supported databases

Oracle

Requirements

• You must know how to run SharePlex. For more information, see Run SharePlex.
• You must understand how to activate a configuration file with the activate config command.
• You must understand the SharePlex flush command. For more information, see the SharePlex Reference Guide.

Procedure

1. On the source system, stop access to the source objects (on all systems if using peer-to-peer replication).

2. On the source system (trusted source in peer-to-peer), flush the data from the source system to the target systems. This command stops the Post process and places a marker in the data stream that establishes a synchronization point between the source and target data.

   sp_ctrl> flush datasource

   where: datasource is the database specification of the source instance, for example o.oraA.

3. On the target system (all secondary systems in peer-to-peer) verify that the number of messages in the post queue is 0 on each system and that Post stopped.

   sp_ctrl> lstatus

4. On the source system, make the DDL changes.

5. On the source system, reactivate the configuration file.

   sp_ctrl> activate config filename

6. On the source system, allow user activity to resume. Their replicated changes will accumulate in the post queue.
7. On the target system, make the corresponding DDL changes.

8. [High availability and peer-to-peer replication only] On the secondary systems, reactivate the configuration file.

   sp_ctrl> activate config filename

9. On the target systems, start Post.

   sp_ctrl> start post

SharePlex resumes replication from the last stop point and the data remains synchronized.