Chat now with support
Chat with Support

SharePlex 8.6.6 - Administration Guide

About this Guide Conventions used in this guide Overview of SharePlex Run SharePlex Run multiple instances of SharePlex Execute commands in sp_ctrl SharePlex parameters Prepare an Oracle environment for replication Create a configuration file Configure replication to Open Target targets Configure a replication strategy Configure partitioned replication Configure named queues Configure SharePlex to maintain a change history target Replicate Oracle DDL Set up error handling Transform data Configure SharePlex security features Activate replication in your production environment Monitor SharePlex Prevent and solve replication problems Repair out-of-sync Data Procedures to maintain Oracle high availability Make changes to an active replication environment Apply an Oracle application patch or upgrade Back up Oracle data on the source or target Tune the Capture process Tune the Post process Appendix A: Peer-To-Peer Diagram Appendix B: SharePlex environment variables

Before you patch or upgrade an application

Apply an Oracle application patch or upgrade > Before you patch or upgrade an application

Review the following topics before you patch or upgrade an application on a system where SharePlex replication is active.

Which procedure to use?

There are different procedures for applying an application patch or upgrade to an Oracle database while replication is in process. Which one to choose depends on what changes the patch or upgrade makes.

Changes made by the patch/upgrade Steps to take

If the patch/upgrade does any of these:

  • Applies DDL that is not supported by SharePlex
  • Adds new tables (including temporary tables renamed to a permanent table)
  • Creates or modifies triggers or constraints

Manually apply the patch/upgrade to the source and target by following either of these procedures:

Apply patch/upgrade to source then copy it to target

Apply patch/upgrade to source and target

Does any of the following:

  • Adds indexes, stored procedures and/or packages to source system
  • Changes users and security on source system (other than SharePlex)
  • Performs DML changes.
  • Performs DDL changes that SharePlex replicates.

And:

Does not make any of the changes listed in the previous rows of this table.

Manually apply the patch/upgrade to the source, then allow SharePlex to replicate the changes to the target. Follow this procedure:

Apply patch to source and replicate it to the target

Note: Because this procedure assumes that SharePlex can replicate all of the changes that the patch or upgrade applies, the patch/upgrade is not applied to the target.

The effect of patches and upgrades on partitioned replication

A patch or upgrade can make changes that affect the column partitions or column conditions in your replication configuration. Take the following into account when you perform this procedure.

How a patch or upgrade can affect vertically partitioned replication

See Configure vertically partitioned replication for more information.

If the patch or upgrade does this to a table: Do this:
Adds columns that do not satisfy the column partition of the table (Optional) Drop the columns from the target table after the patch or upgrade is applied.
Adds columns that need to be in the column partition of the table Add those columns to the source and target column partition lists in the configuration file.
Drops columns that are part of the column partition of the table

Remove those columns from the source and target column partition lists in the configuration file.

Changes the name of a column that is in the column partition of a table Change the column name in the source and target column partition lists in the configuration file.
How a patch or upgrade can affect horizontally partitioned replication

See Configure horizontally partitioned replication for more information.

If the patch or upgrade does this to a table: Do this:
Adds rows that need to be in a partition scheme Add a column condition for the rows in the SHAREPLEX_PARTITION table.
Deletes rows that are part of a partition scheme Remove the column condition from the partition scheme in the SHAREPLEX_PARTITION table.

Naming conventions used

In these procedures, the "source" system is one of the following:

  • The source system of a single-direction replication configuration, including cascading replication.
  • All source systems of a consolidated replication configuration.
  • The trusted source system in a peer-to-peer replication configuration.

these procedures, the "target" system is one of the following:

  • The target system of a single-direction replication configuration, including cascading and consolidated replication.
  • The secondary systems in a peer-to-peer replication configuration.

In this procedure, the SharePlex commands in the procedure apply to all sp_cop instances that apply to the replication strategy you are using (for example, all sp_cop processes on a target in consolidated replication).

Apply patch/upgrade to source then copy it to target

Apply an Oracle application patch or upgrade > Apply patch/upgrade to source then copy it to target

Supported databases

Oracle on all supported platforms

When to use this procedure

Use this procedure if the patch or upgrade does any of the following:

  • Makes DDL changes of a type not replicated by SharePlex.
  • Used a temporary table that was renamed to a permanent table that must be included in replication (for example, a reorganization.
  • Added objects that must be replicated.

Overview of the procedure

Use this procedure to run an Oracle hot backup to copy patches or upgrades from the source system to the target system, instead of applying the patch or upgrade directly on the target system. This is useful if the patch or upgrade makes extensive changes that are of the type(s) not supported by SharePlex replication, or if you are unsure of what it does.

With this procedure, you can keep the configuration file active on the source system. You use the reconcile command to identify and eliminate the following:

  • Duplicate DML and DDL from the patch or upgrade operations that were replicated but also applied by the backup.
  • Production transactions that were replicated but also applied by the backup.

Note: It could be less time-consuming to apply a patch or upgrade manually to the target system, instead of using a hot backup. Examples of such situations are when the operation only adds an index or stored procedures, or when it only changes security. See Apply patch/upgrade to source and target.

Apply the patch/upgrade

  1. Stop user access to the instances involved in replication on the source and target systems, but do not shut down SharePlex.
  2. On the source system, run sp_ctrl.
  3. On the source system, flush the data 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 the datasource of the source instance, for example o.oraA.

  4. On the source system, apply the patch or upgrade.
  5. On the source system, restore user access to the source instance.
  6. [If the patch/upgrade adds objects that must be replicated] Edit the configuration file as follows (do not deactivate it). The patch or upgrade may have affected column partitions or column conditions in partitioned replication. See Change an active configuration file.

    • Copy the configuration file.

      sp_ctrl> copy config filename to newname

    • Edit the copy.

      sp_ctrl> edit config newname

      Save the file.

  7. Do one of the following:

    • If you added objects in the previous step, activate the new configuration file.

      sp_ctrl> activate config newname

    • If you did not make any changes to the original configuration file, activate that one.

      sp_ctrl> activate config filename

  8. On the source, run the Oracle hot backup.
  9. On the source, switch log files and make a note of the highest archive-log sequence number.

    svrmgr1> alter system switch logfile;

  10. On the target, recover the target database from the hot backup using the UNTIL CANCEL option in the RECOVER clause, and cancel the recovery after Oracle fully applies the log that you recorded in the previous step.
  11. On the target system, open the database with the RESETLOGS option.
  12. Run ora_setup on the target instance, but do not create a new user. Choose the existing SharePlex user and password (copied in the backup). See the SharePlex Reference Guide for ora_setup instructions.
  13. On the target system, issue the reconcile command using the sequence number of the log that you noted previously. If you are using named post queues, issue the command for each one. If you do not know the queue names, issue the qstatus command first.

    sp_ctrl> qstatus

    sp_ctrl> reconcile queue queuename for datasource-datadest seq sequence_number

    Example: reconcile queue SysA for o.oraA-o.oraA seq 1234

    Note: The reconcile process retains control of sp_ctrl until it is finished.

  14. On the target system, if the patch or upgrade installed triggers on the tables in replication, disable them or run the sp_add_trigger.sql utility script so that the triggers ignore the SharePlex user.
  15. On the target system, if the patch or upgrade added check constraints or scheduled jobs that perform DML, disable them.
  16. On the target system, perform any cleanup required by partitioned replication.

  17. On the target system, start Post.

    sp_ctrl> start post

    The two instances are now in synchronization, and SharePlex resumes replication.

Apply patch/upgrade to source and target

Apply an Oracle application patch or upgrade > Apply patch/upgrade to source and target

Supported databases

Oracle on all supported platforms

When to use this procedure

Use this procedure if the patch or upgrade does any of the following:

  • Makes DDL changes of a type not replicated by SharePlex.
  • Used a temporary table that was renamed to a permanent table (for example, a reorganization, which changes the object ID)
  • Added objects that must be replicated.

Overview of the procedure

Use this procedure to apply an application patch or upgrade if it includes changes to the database that are not replicated by SharePlex and you can stop user access to the source database to deactivate and reactivate the configuration file. It requires deactivation of the configuration file so that SharePlex can rebuild its object information to incorporate the changes that the patch or upgrade applied. When you reactivate the configuration, SharePlex will re-analyze all of the objects again. You can allow users to access the source data while the patch or upgrade is applied to the target system.

Apply the patch/upgrade

  1. Stop user access to the instances involved in replication on the source and target systems, but do not shut down SharePlex.
  2. On the source system, flush the data 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 the datasource of the source instance, for example o.oraA.

  3. On the source system, deactivate the configuration.

    sp_ctrl> deactivate config filename

  4. On the source system, apply the patch or upgrade.
  5. [If the patch/upgrade adds objects that must be replicated] On the source system, edit the configuration file, including making any changes to column partitions or column conditions if using partitioned replication. See Change an active configuration file.

    sp_ctrl> edit config filename

  6. On the source system, activate the configuration file.

    sp_ctrl> activate config filename

  7. On the source system, restore user access to the source instance.
  8. On the target system, apply the patch or upgrade.
  9. On the target system, if the patch or upgrade installed triggers on the tables in replication, disable them or run the sp_add_trigger.sql utility script so that the triggers ignore the SharePlex user.
  10. On the target system, if the patch or upgrade added check constraints or scheduled jobs that perform DML, disable them.
  11. On the target system, perform any cleanup required by partitioned replication.
  12. On the target system, start Post.

    sp_ctrl> start post

    The two instances are now in synchronization, and SharePlex resumes replication.

Apply patch to source and replicate it to the target

Apply an Oracle application patch or upgrade > Apply patch to source and replicate it to the target

Supported databases

Oracle on all supported platforms

When to use this procedure

Use this procedure if all of the operations applied by a patch or upgrade are supported by SharePlex and can be replicated to the target. This includes DML changes and DDL that is supported by SharePlex. If you are not sure whether the patch or upgrade performs operations that are not supported by SharePlex, use Apply patch/upgrade to source then copy it to target.

Note: For a list of operations that SharePlex supports, see the SharePlex Release Notes.

Apply the patch/upgrade

  1. Stop user access to the Oracle instances on the source and target systems.
  2. On the source system, flush the data 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 the datasource of the source instance, for example o.oraA.

  3. On the source system, apply the patch or upgrade.
  4. On the source system, restore user access to the source instance.
  5. On the target system, if the patch or upgrade created or modified triggers, disable them or run the sp_add_trigger.sql utility script so that the triggers ignore the SharePlex user.
  6. On the target system, restore user access to the target instance.
Related Documents