Chat now with support
Chat with Support

SharePlex 11.0 - SharePlex Administration Guide

About this Guide Conventions used in this guide Revision History Overview of SharePlex Run SharePlex Run multiple instances of SharePlex Execute commands in sp_ctrl Set SharePlex parameters Configure data replication Configure replication to and from a container database Configure named queues Configure partitioned replication Configure replication to a change history target Configure a replication strategy Configure DDL replication Configure error handling Configure data transformation Configure security features Assign SharePlex users to security groups Start replication on your production systems Monitor SharePlex Prevent and solve replication problems Repair out-of-sync data Tune the Capture process Tune the Post process Recover replication after Oracle failover Make changes to an active replication environment Apply an Oracle application patch or upgrade Back up Oracle data on the source or target Troubleshooting Tips Appendix A: Peer-To-Peer Diagram Appendix B: SharePlex environment variables

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

Remove Source 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 theSharePlex Reference Guide.

Supported databases

All databases supported by SharePlex

Procedure

To remove the source objects from replication:

  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

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.

Make Oracle changes that affect replication

This topic helps you make common changes to the Oracle environment while replication is active.

Supported databases

Oracle on Linux and UNIX

Move the location of ORACLE_HOME

If you change the ORACLE_HOME, you need to relink SharePlex to the Oracle libraries.

Perform the following steps to relink SharePlex to the Oracle libraries:

  1. Shut down SharePlex.

    sp_ctrl> shutdown

  2. Move the ORACLE_HOME.
  3. Edit the oratab file to point to the new ORACLE_HOME.
  4. Edit the connections.yaml file to point to the new ORACLE_HOME. This file is in the data subdirectory of the SharePlex var directory.
  5. Start SharePlex.

Change the target ORACLE_SID

  1. On the source system, run sp_ctrl.

  2. On the source system, copy the active configuration file to a new name, but do not deactivate it.

    sp_ctrl> copy config filename to newname

  3. On the source system, open the new configuration file.

    sp_ctrl> edit config filename

  4. Change the ORACLE_SID to the new one in all of the routing maps that include this target database and target system.
  5. Save and close the configuration file, but do not activate it.
  6. On the source system, stop user access to the objects involved in replication.

  7. On the source system, flush the data in the queues to the target. This stops the Post process and establishes a synchronization point between the source and target databases.

    sp_ctrl> flush datasource

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

  8. On the source system, activate the new configuration file. This will deactivate the original configuration file.

    sp_ctrl> activate config filename

    Note: The activation will be brief because SharePlex does not need to analyze the tables.

  9. On the source system, allow users to access the objects involved in replication.

  10. On the target system, verify that Post stopped. If Post is not stopped, continue to issue the command until it shows that Post stopped.

    sp_ctrl> status

  11. On the target system, shut down the database and then rename the ORACLE_SID.

  12. On the target system, start Post.

    sp_ctrl> start post

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating