SharePlex 9.0 - Administration Guide

Change a configuration file

Many procedures that change an element of the replication environment will also involve changing the SharePlex configuration file. SharePlex does not permit anyone to edit an active configuration file. You must make a copy first. By copying the file first, you preserve the original file in case it is needed again or you encounter a problem with the new file. By copying the file first, you also can control when to activate the new configuration file.

In most cases, activation of an edited configuration is less time-consuming than the original activation if you do not deactivate the original configuration. The activation of the new configuration automatically deactivates the original configuration, and SharePlex only needs to analyze the new, changed, and removed objects. If you deactivate the original configuration before you activate a new one, SharePlex re-analyzes all of the objects.

To change an configuration file, see Add or change table specifications in an active configuration.

Add or change table specifications in an active replication configuration

This section provides instructions for adding or changing a table specification while replication is active.

Supported databases

Oracle or SQL Server source

All targets

Oracle procedure

If you are using wildcards and an object that you are adding satisfies the wildcard specification, it is not necessary to add the object to the configuration file if the source is Oracle. Any new objects that match the wildcard criteria are automatically added into replication. Only add objects that must be explicitly stated by name.

Important! Do not deactivate the original configuration.

1. If adding new tables, add them to the source and target (populated in both places, if applicable) to establish a synchronized initial state. Do not allow transactional access to the source table yet.

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 entries for the new tables or change existing entries.

   Note: To change partitioned replication, see Change Partitioned Replication.

5. Save the configuration file.

6. Activate the new configuration. This deactivates the original configuration. Only the new or changed tables are activated, so the activation should not be as long as the initial activation.

   sp_ctrl> activate config newname

7. Allow access to the newly added tables.

SQL Server procedure

The SharePlex auto-add feature is not supported for SQL Server source tables. Therefore, the configuration file must be reactivated if you need to add a table.

Important! Do not deactivate the original configuration.

1. If adding new tables, add them to the source and target (populated in both places, if applicable) to establish a synchronized initial state. Do not allow transactional access to the source table yet.

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 entries for the new table(s) or change existing entries.

   Note: To change partitioned replication, see Change Partitioned Replication.

5. Save the configuration file.
6. Stop transactional access to the new and changed tables.

7. Activate the new configuration. This deactivates the original configuration. Only the new or changed tables are activated, so the activation should not be as long as the initial activation.

   sp_ctrl> activate config newname

8. Allow transactional access the tables.

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 or SQL Server source

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.