Add or change table specifications in an active replication configuration
This section provides instructions for adding a supported object to replication, or changing the specifications of an object, while replication is active.
Supported databases
Oracle source
All targets
Oracle procedure
Note: To add sequences to replication, see Add Oracle sequences to an active replication configuration.
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.
- 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.
-
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.
-
Issue the edit config command to open the new configuration file in the default text editor.
sp_ctrl> edit config newname
-
Add the entries for the new tables or change existing entries.
Note: To change partitioned replication, see Change Partitioned Replication.
- Save the configuration file.
-
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
- Allow access to the newly added 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
All targets
To change horizontally partitioned replication
- Run sp_ctrl.
-
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.
|
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. |
-
If you dropped a partition scheme:
-
Copy (but do not deactivate) the active configuration file to a new file.
sp_ctrl> copy config filename to newname
-
Edit the copy to remove or change the routing map where the partition scheme was specified.
sp_ctrl> edit config filename
-
Activate the new configuration file.
sp_ctrl> activate config filename
To change vertically partitioned replication
-
Make a copy of (but do not deactivate) the active configuration file.
sp_ctrl> copy config filename to newname
-
Edit the copy to change the appropriate column partition.
sp_ctrl> edit config filename
-
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
- Stop user activity to the objects on the source system.
-
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.
-
Issue the edit config command to open the new configuration file in the default text editor.
sp_ctrl> edit config newname
- Add the new sequences to the configuration file.
- Save and close the file.
-
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
-
Activate the new configuration. This deactivates the original configuration.
sp_ctrl> activate config newname
- Allow users to access the objects.
Add a sequence if the sequence populates a column
-
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.
-
Issue the edit config command to open the new configuration file in the default text editor.
sp_ctrl> edit config newname
- Add the new sequences to the configuration file.
- Save and close the file.
-
Activate the new configuration. This deactivates the original configuration.
sp_ctrl> activate config newname
-
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.
-
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
-
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.
-
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.
- 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.
- Create the target sequence with the START WITH value that you calculated.
-
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
-
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.
-
Issue the edit config command to open the new configuration file in the default text editor.
sp_ctrl> edit config newname
-
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.
- Save and close the file.
-
Activate the new configuration. This deactivates the original configuration.
sp_ctrl> activate config newname
- Allow users to access the removed objects.
