Chat now with support
Chat with Support

SharePlex 11.2 - 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

Configure Replication to maintain high availability

These instructions show you how to set up SharePlex for the purpose of high availability: replicating to a secondary Oracle database that is a mirror of the source database. This strategy uses bi-directional replication with two SharePlex configurations that are the reverse of each other. The configuration on the secondary (standby) machine remains in an activated state with the Export process on that system stopped in readiness for failover if the primary machine fails.

Note: For CrunchyData High Availability cluster environment setup information, see the Install SharePlex on PostgreSQL High Availability Cluster section in the SharePlex Installation and Setup Guide.

This strategy supports business requirements such as the following:

  • Disaster recovery
  • Continuous operation of business applications throughout maintenance cycles or mechanical failures

In this strategy, SharePlex operates as follows:

  • Under normal conditions, SharePlex replicates changes from the primary database to the secondary database.
  • When the primary system or database is offline and users are transferred to the secondary system, SharePlex captures their changes and queues the data on that system until the primary system is restored.
  • When the primary system is restored, SharePlex updates it with those changes and then resumes capture and replication from the primary database.

Supported sources

Oracle and PostgreSQL

Supported targets

Oracle

Capabilities

This replication strategy supports the use of named export and post queues.

Note: Column mapping and partitioned replication is not appropriate in a high availability configuration. Source and target objects can have different names but this makes the management of a high-availability structure more complicated.

Requirements

  • Prepare the system, install SharePlex, and configure database accounts according to the instructions in the SharePlex Installation Guide.
  • All objects must exist in their entirety on both systems.
  • The target objects must have the same structure and qualified names as their source objects.
  • Enable archive logging on all systems.

  • Create a script that denies INSERT, UPDATE and DELETE operations to all users except SharePlex.

For failover purposes, the following are required:

  • Make the applications used on the primary system available on the secondary system.
  • Copy non-replicated database objects and critical files outside the instance to the secondary system.
  • Create a script that grants INSERT, UPDATE and DELETE privileges to all users, which can be run during a failover procedure.
  • Create a script that enables constraints on the secondary system to be used during a failover procedure.
  • Develop a failover procedure for relocating users to the secondary system.

Note: If you use an Oracle hot backup to create the secondary instance, keep the script. It can be modified to re-create the primary instance.

Conventions used in the syntax

In the configuration syntax in this topic, the placeholders represent the following:

  • hostA is the primary system.

  • hostB is the secondary system.
  • ownerA.object is the fully qualified name of an object on hostA or a wildcarded specification.
  • ownerB.object is the fully qualified name of an object on hostB or a wildcarded specification.
  • oraA is the Oracle instance on hostA.
  • oraB is the Oracle instance on hostB.

Important! See Configure SharePlex to Replicate Data for more information about the components of a configuration file.

Configuration

A high availability configuration uses two configurations that are the reverse of each other. To replicate all objects in the database, you can use the config.sql script to simplify the configuration process. For more information, see the Configuration Scripts section in the SharePlex Reference Guide.

Configuration on the source system (primary system)

Datasource:o.oraA

   
ownerA.object ownerB.object hostB@o.oraB

Configuration on the target system (secondary system)

Datasource:o.oraB

   
ownerB.object ownerA.object hostA@o.oraA

Make the system ready for failover

  1. On the secondary system (the one that will initially be the passive system) run sp_ctrl and then issue the following command to stop the Export process on the secondary system so that nothing accidentally happening on the secondary system (such as a scheduled job changing data) gets replicated back to the primary system. This is the required state of SharePlex on that system until there is a need for a role switch between systems.
  2. Perform initial synchronization and startup. You will activate the source configuration during this procedure. For more information, see Start Replication on your Production Systems.
  3. Making sure the Export process is stopped on the secondary system, activate the configuration on that system. The configuration on the secondary machine remains in an activated state, but the stopped Export process and lack of user activity ensure that the system remains static in readiness for failover.
  4. Monitor the SharePlex instance that is linked to the secondary Oracle instance to make sure no non-SharePlex DDL or DML changes were performed. You can do this as follows: View the status of the export queue on the secondary system using the qstatus command in sp_ctrl. The queue should be empty, because the Capture process on a system ignores the Post process on that system. If there are any messages in the export queue, it means those transactions originated on the secondary system or the SP_OCT_REPLICATE_POSTER parameter was mistakenly enabled. See the SharePlex Reference Guide for more information about SharePlex commands and parameters.
  5. Maintain backups of replication files.

Perform recovery procedures

If a system fails in your high-availability environment, you can move replication to a secondary system and then move it back to the primary system when it is restored. For more information, see Recover Replication after Oracle Failover .

Configure DDL replication

This chapter contains the information that you need to know in order to replicate Oracle DDL operations that are supported by SharePlex.

Contents

DDL that SharePlex supports

DDL that SharePlex Supports

SharePlex supports DDL replication for Oracle databases only. SharePlex replicates certain Oracle DDL changes that are written to the redo logs. Changes that bypass the redo logs are not replicated.

For details on the DDL that SharePlex supports, see the SharePlex Release Notes.

In a cascading configuration, DDL replication is supported from source to target through an intermediary system. However, DDL initiated on the intermediary system could cause inconsistencies leading to Post errors and must be avoided. For more information, see Configure Replication through an Intermediary System.

DDL against objects owned by the SharePlex user or against system-owned objects is not replicated.

Note: Because the source and target databases can be of different versions in SharePlex replication, the source and target objects can be different. When DDL is applied to the target, it may fail if the operation is forbidden on the target but allowed on the source.

For a list of objects for which DDL is supported, see the SharePlex Release Notes.

Enable DDL replication

Control Oracle DDL Replication

By default, SharePlex replicates some Oracle DDL for objects that are listed in the active configuration (explicitly or by wildcard), but you can expand this support with parameter settings.

Important!
  • For the most current information about supported DDL and requirements or limitations, see the SharePlex Release Notes provided with this release. That information may supercede what is documented here.
  • DDL replication is supported only to Oracle targets, except for ALTER TABLE to ADD COLUMN or DROP COLUMN, which are supported for all SharePlex targets.

SharePlex provides default DDL support for objects in the configuration file. You can expand this support through parameter settings.

See the SharePlex Release Notes for detailed information about the DDL that is supported by SharePlex.

Default support for Oracle DDL

SharePlex provides some basic DDL support by default.

DDL for existing objects

By default SharePlex replicates:

  • TRUNCATE TABLE 
  • ALTER TABLE to:

    ADD, MODIFY, DROP, SPLIT, COALESCE, MOVE, TRUNCATE, EXCHANGE PARTITION/SUBPARTITION

    ADD, MODIFY, or DROP columns

    when:

    • the affected object exists in the source and target at the time of activation and
    • its name is listed in the configuration file (explicitly or through wildcard).
  • DROP TABLE

This functionality is controlled by the SP_OCT_REPLICATE_DDL parameter.

The valid values are as follows:

0 (disable replication of both ALTER TABLE and TRUNCATE)

1 (enable ALTER replication only)

2 (enable TRUNCATE replication only)

3 (enable replication of ALTER and TRUNCATE)

DDL for objects added after activation

By default, the SharePlex Auto-Add feature is also enabled to provide DDL support for tables and indexes that are created after activation. When SharePlex detects a CREATE statement for one of these objects and its name satisfies a wildcard in the configuration file, SharePlex does the following:

  • replicates the CREATE to add the object to the target
  • adds the object to replication
  • maintains that object through future DDL and DML changes

The Auto-Add feature is controlled by the SP_OCT_AUTOADD_ENABLE parameter, which is set to 1 (enabled) by default.

See theSharePlex Reference Guide for details about this parameter.

Optional DDL on objects in replication

You can enable the replication of the following DDL when it is issued on objects that are in the configuration file. To enable the replication of a DDL command, set the associated parameter to 1.

DDL command Set this parameter to 1
CREATE / DROP TRIGGER SP_OCT_REPLICATE_TRIGGER
CREATE / DROP SYNONYM SP_OCT_REPLICATE_SYNONYM
GRANT SP_OCT_REPLICATE_GRANT

See theSharePlex Reference Guide for details about these parameters.

Optional Auto-Add support for Oracle DDL

You can expand Auto-Add support to include any of the object types listed in the following table. The object is added to replication if its name satisfies a wildcard specification in the active configuration file.

To enable auto-add of individual object types:

  1. Make certain the SP_OCT_AUTOADD_ENABLE parameter is set to 1.
  2. Set the appropriate parameter to 1, using the following table as your guide.

    DDL to auto-add Parameter to set to 1 Additional requirements
    CREATE / DROP SEQUENCE SP_OCT_AUTOADD_SEQ

    Set the SP_SYS_TARGET_COMPATIBILITY parameter to at least 8.6.3.

    CREATE /DROP MATERIALIZED VIEW* SP_OCT_AUTOADD_MVIEW

    Set the SP_SYS_TARGET_COMPATIBILITY parameter to at least 8.6.2.

* SharePlex does not replicate materialized views to materialized views. SharePlex converts a CREATE MATERIALIZED VIEW to a CREATE TABLE, applies the CREATE TABLE to the target, and then replicates the DML that populates the view. SharePlex replicates DROP MATERIALIZED VIEW, but not ALTER MATERIALIZED VIEW. See theSharePlex Reference Guide for details about these parameters.

Expanded DDL support for objects outside replication

You can configure SharePlex to replicate DDL for certain objects that are not listed in the configuration file. SharePlex replicates the DDL statements, but does not replicate any data change operations made to the objects because they are not part of active replication. Therefore, SharePlex does not maintain synchronization of these objects on the target. The objects must exist in the source and target prior to configuration activation.

Note: Expanded DDL replication supports not only tables and sequences but also a wide range of other objects such as procedures, functions, users, and views, which are not part of replication. Some of these objects may have underlying objects that are in replication. In those cases, Expanded DDL replication applies not only to the object that is outside the replication configuration, but also to the underlying objects that are in replication.

SharePlex does not support the Oracle Flashback Table feature. If the SP_REPLICATE_ALL_DDL parameter is enabled (value of 1), SharePlex may try to replicate the flashback DDL, which will return an error. To perform Flashback Table on a table that is in replication, use the following procedures to work around this issue:

  1. Remove source objects from replication
  2. Perform the flashback
  3. Add or change objects in an active configuration

To replicate DDL for objects outside the replication configuration:

  1. Set the SP_OCT_REPLICATE_ALL_DDL parameter to 1.

  2. See theSharePlex Reference Guide for details about this parameter.

  3. See the SharePlex Release Notes for more information about supported DDL for objects that are not in the configuration file.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating