Chat now with support
Chat with Support

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

Frequently Asked Questions about activation

What happens during activation?

During activation, SharePlex gets the information it needs to identify and understand the objects that are configured for replication and build routing maps. For more information, see What is activation?.

How long does activation take?

The length of time that activation takes varies, depending on the size, number and structure of the configured objects.

Do users have to stop access to the data?

An Oracle database can remain available for transactions. Each source table is briefly locked so SharePlex can build its internal object information. Then the lock is released. This happens very quickly and may not even be noticeable by users. However, if a business application has NOWAIT locking, the SharePlex lock may cause the application to fail if it attempts to obtain a lock on an object that is being activated.

Can DDL be performed during activation?

No. The definition of objects cannot be changed during activation.

Can I activate multiple configurations for the same datasource?

Yes. You can create different configurations for the same datasource. You must use multiple instances of SharePlex (different sp_cop processes and variable-data directories, running on different ports). See Run multiple instances of SharePlex

Can I activate configurations for different datasources at the same time?

Yes. For more information, see How to activate multiple configuration files.

Can I test a configuration before I activate it?

Yes. This is highly recommended. Use the verify config command. For more information, see Requirements for activating a configuration.

Can I interrupt an activation to make changes?

No. Activation is meant to be an uninterrupted procedure that initiates replication while maintaining the same series of transactional events as those appearing in the redo log. You can use the abort config command to terminate an activation, but you may then need to clean out the queues and resynchronize the source and target data again. Ideally, you should have a tested configuration file ready to be activated and be prepared to issue commands to the database and to SharePlex.

Can I activate against a quiet database?

Yes. A quiet database is optional to activate Oracle capture. The Oracle database must be mounted and open, however.

Can I increase the number of activation threads?

Yes, but only for an Oracle source. The number of threads is controlled globally by the SP_OCF_THREAD_COUNT parameter, which must be set before you issue the activate config command. You can override this value for a particular activation by using the threads option when you issue the activate config command.

How to activate multiple configuration files

You can activate one configuration per datasource on a system. For example, if there are ConfigA, ConfigB and ConfigC for the same datasource, you can activate only one of them at a time. Activating another configuration for the same datasource automatically deactivates the first one.

However, if ConfigA replicates data from one datasource, while ConfigB replicates data from a different datasource, you can activate both of those configurations at the same time. Replication for those configurations can operate concurrently.

To activate multiple configuration files

The activation process retains control of the sp_ctrl interface until the activation is finished. Because an activation can take a significant amount of time, you can activate different configurations in different sessions of sp_ctrl. Activate the first configuration, then open another session of sp_ctrl to activate the second one. Open as many sessions of sp_ctrl as you have configurations to activate.

The number of activation threads can be controlled for an Oracle source. To set the number of threads, use the SP_OCF_THREAD_COUNT parameter, which is a global setting for the instance of SharePlex. When activating more than one configuration concurrently, you can override this value for a particular activation by using the threads option when you issue the activate config command.

Activate replication with an Oracle hot backup on an active database

Use this procedure to use an Oracle hot backup to establish a target Oracle instance and activate replication without quieting the source database. This procedure involves using the reconcile command to ensure that transactions which occurred after the point of backup are applied to the target, while eliminating redundant replicated transactions that were already captured by the backup.

Preliminary considerations

Read these points before you proceed.

Supported databases

Oracle source and Oracle target

Supported replication strategies

All replication strategies are supported with the following limitations:

Limitation applies to: Description
Consolidated replication (many sources to one target)

To establish consolidated replication, the use of a hot backup from all source systems is not possible. A backup from one source will override the data that was applied by a backup from a different source. You can use a hot backup of one of the source instances to establish a target instance, and then use another copy method to apply the objects from the other source instances. Possible methods include:

Peer-to-peer

To establish peer-to-peer replication, you must:

  1. Quiet all of the systems except the trusted source system for the duration of this procedure.
  2. Move all users to the trusted source system, and then follow this procedure.

Only after this procedure has been performed on all of the secondary systems may users may resume activity on them.

Windows systems To use a hot backup between Windows systems, the target system must have an instance already created containing an identical ORACLE_SID and directory structure created with the Oracle creation tools. Oracle runs as a service on Windows, and the Registry entries must exist before starting the database recovery process. The database can start empty, because the hot backup will populate it.

Requirements

  • [Unix and Linux systems] Verify that the ORACLE_SID and ORACLE_HOME in the oratab file are correct for the instance you will be establishing with the hot backup. The SID must be the SID used in the routing map in the configuration file that you will be activating.
  • Read the requirements before you start this procedure. For more information, see Requirements for activating a configuration.
  • Make certain a SharePlex database account exists in the source database (only). This account usually is created when SharePlex first is installed. See the SharePlex Installation and Setup Guide for more information.
  • Before you start, review this procedure and see the SharePlex Reference Guide for more information about the commands that are used.

Troubleshooting

If the configuration fails to activate, you can find information about the failure in these places:

  • Use the show log command to view the event_log.
  • View the activation process log, which is a file named SID_oconf##.log in the log sub-directory of the SharePlex variable data directory.

See also Solve Database Setup problems.

Procedures

There are two procedures for activation with a hot backup, depending on your replication strategy.

Activation with hot backup: all strategies except cascading

Activation with hot backup: cascading replication

Activation with hot backup: all strategies except cascading

Use this procedure for all replication strategies except cascading replication where SharePlex will be posting to a database on the intermediary system.

  1. On the source and target systems, go to the bin sub-directory of the SharePlex product directory, and start sp_cop and sp_ctrl. In a cluster, the source is the primary node where the cluster VIP is running.

  2. On both systems, verify that the SharePlex processes are running.

    sp_ctrl> status

  3. On the target system (primary node of a target cluster), stop the Post process. This allows replicated data to accumulate in the post queue until the database has been recovered and reconciled.

    sp_ctrl> stop post

  4. On the source system, run the Oracle hot backup.

  5. When the backup is finished, activate the configuration on the source system.

    sp_ctrl> activate config filename

  6. On the source system, monitor activation status.

    Note: The command retains control of sp_ctrl until activation is finished.

  7. When activation is complete, switch log files on the source system.

    On-premises database:

    svrmgr1> alter system switch logfile;

    Amazon RDS database:

    Run the Amazon RDS procedure rdsadmin.rdsadmin_util.switch_logfile.

  8. Do one of the following:

    • To recover the database to a sequence number, make a note of the highest archive-log sequence number.

    • To recover the database to a Oracle System Change Number (SCN), pick an SCN to recover to on the target database.

  9. On the target system, do one of the following:

    • If recovering to a sequence number, recover the database from the hot backup using the UNTIL CANCEL option in the RECOVER clause, and cancel the recovery after Oracle has fully applied the log from the previous step.
    • If recovering to a SCN, recover the database from the hot backup using the UNTIL CHANGE scn option in the RECOVER clause, and cancel the recovery after Oracle has applied the logs matching the SCN from the previous step.
  10. On the target system, open the database with the RESETLOGS option.

  11. On the target system, run Database Setup for Oracle on the database. When prompted for the SharePlex database user, enter n to choose the existing user and password (these were copied in the backup).

    Would you like to create a new SharePlex user [y]. n

    NoteS:

  12. [Optional] If you are using named post queues and are unsure of the queue names, issue the qstatus command.

    sp_ctrl> qstatus

  13. On the target system, issue the reconcile command as follows, depending on the recovery option you chose. If you are using named post queues, issue the command for each one.

    • If recovering to a sequence number, substitute the sequence number of the log that you noted previously.

      sp_ctrl> reconcile queue queuename for datasource-datadest seq sequence_number

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

    • If recovering to a SCN, substitute the SCN that you noted previously.

      sp_ctrl> reconcile queue queuename for datasource-datadest scn scn_number

      Example: reconcile queue SysA for o.oraA-o.oraA scn 0123456789

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

  14. On the target system, run the cleanup.sql script to truncate the SharePlex internal tables. Instructions for running this script are in the SharePlex Reference Guide.
  15. On the target system, disable triggers on the tables, or run the sp_add_trigger.sql utility script so that the triggers ignore the SharePlex user.
  16. On the target system, disable check constraints and scheduled jobs that perform DML.
  17. [Partitioned replication only] If you are using vertically partitioned or horizontally partitioned replication for any tables, delete the unneeded columns and rows from those tables.

  18. [High availability only] On the target (secondary) system, stop Export.

    sp_ctrl> stop export

  19. [High availability and peer-to-peer only] On the target (secondary) system, activate the configuration so that SharePlex is ready in the event of failover.

    sp_ctrl> activate config filename

  20. On the target system, start the Post process. The two instances are now in synchronization, and SharePlex will continue replicating to maintain synchronization.

    sp_ctrl> start post

  21. [Optional] If this was only a partial backup, drop the tablespaces that were not copied over during the hot backup.

Activation with hot backup: cascading replication

Use this procedure for cascading replication where SharePlex will be posting to a database on the intermediary system.

You will apply the backup to the intermediary system first (represented as “sysB”), and then to the target system (represented as “sysC”).

  1. On all systems, go to the bin sub-directory of the SharePlex product directory, and start sp_cop and sp_ctrl
  2. On all systems, verify that the SharePlex processes are running.

    sp_ctrl> status

  3. On the intermediary and target systems, stop the Post process. This allows replicated data to accumulate in the post queue until the databases are recovered.

  4. sp_ctrl> stop post

  5. On the source system, run the Oracle hot backup to the intermediary and target systems.

  6. When the backup is finished, activate the configuration on the source system.

    sp_ctrl> activate config filename

  7. On the source system, view activation status.

    Note: The command retains control of sp_ctrl until activation is finished.

  8. When activation is complete, switch log files on the source system.

    On-premises database:

    svrmgr1> alter system switch logfile;

    Amazon RDS database:

    Run the Amazon RDS procedure rdsadmin.rdsadmin_util.switch_logfile.

  9. Make a note of the highest archive-log sequence number.
  10. On the intermediary system, recover the database from the hot backup using the UNTIL CANCEL option in the RECOVER clause, and cancel the recovery after Oracle has fully applied the log from the previous step.
  11. On the intermediary system, open the database with the RESETLOGS option.

  12. On the intermediary system, run Database Setup on the database. When prompted for the SharePlex database user, enter n to choose the existing user and password (these were copied in the backup).

    Would you like to create a new SharePlex user [y]. n

    Notes:

  13. [Optional] If you are using named post queues and are unsure of the queue names, issue the qstatus command.

    sp_ctrl> qstatus

  14. On the intermediary system, issue the reconcile command for each post queue. For seq sequence_number, substitute the sequence number of the log that you noted previously.

    sp_ctrl> reconcile queue queuename for datasource-datadest seq sequence_number

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

  15. On the intermediary system, run the cleanup.sql script to truncate all of the SharePlex internal tables. Instructions for running this script are in the SharePlex Reference Guide.

  16. On the intermediary system, disable triggers on the tables, or run the sp_add_trigger.sql utility script so that the triggers ignore the SharePlex user.
  17. On the intermediary system, disable check constraints and scheduled jobs that perform DML.

  18. On the intermediary system, set the SP_OCT_REPLICATE_POSTER parameter to 1. This directs SharePlex to capture posted changes on that system and replicate them to the target system.

    sp_ctrl> set param SP_OCT_REPLICATE_POSTER 1

  19. [Partitioned replication only] If you are using vertically partitioned or horizontally partitioned replication for any tables, delete the unneeded columns and rows from those tables.

    Important! Do not start any Post processes yet.

  20. On the target system, recover the database from the hot backup using the UNTIL CANCEL option in the RECOVER clause, and cancel the recovery after Oracle has fully applied the log that you reconciled to in the previous steps taken on the intermediary system.

  21. On the target system, open the database with the RESETLOGS option.

  22. On the target system, run Database Setup on the database. When prompted for the SharePlex database user, enter n to choose the existing user and password (these were copied in the backup).

    Would you like to create a new SharePlex user [y]. n

    Note: SharePlex can remain running during the setup process. For more information about Database Setup, see Database Setup Utilities in the SharePlex Reference Guide.

  23. On the target system, run the cleanup.sql script to truncate the SharePlex internal tables. Instructions for running this script are in the SharePlex Reference Guide.

  24. On the target system, disable triggers on the tables, or run the sp_add_trigger.sql utility script so that the triggers ignore the SharePlex user.
  25. On the target system, disable check constraints and scheduled jobs that perform DML.

  26. [Partitioned replication only] If you are using vertically partitioned or horizontally partitioned replication for any tables, delete the unneeded columns and rows from those tables.

  27. On the intermediary system, activate the configuration file.

    sp_ctrl> activate config filename

  28. On the intermediary system, monitor activation status.

    Note: The command retains control of sp_ctrl until activation is finished.

  29. On the intermediary and target systems, start the Post process. All instances are now in synchronization, and SharePlex will continue replicating to maintain synchronization.

    sp_ctrl> start post

  30. [Optional] If this was only a partial backup, drop the tablespaces that were not copied over during the hot backup.

Activate replication with an Oracle hot backup on a quiet database

Use this procedure to use an Oracle hot backup to establish a target Oracle instance and activate replication if user activity can be stopped while the procedure is performed. This procedure can be used for all replication configurations.

Preliminary considerations

Read these points before you proceed.

Supported databases

Oracle source and Oracle target

Supported replication strategies

All but high-availability. This procedure is not appropriate for a high-availability strategy because it requires the source database to be quieted while the backup is taken and the configuration file is being activated.

Certain limitations apply:

Limitation applies to: Description
Consolidated replication (many sources to one target)

To establish consolidated replication, the use of a hot backup from all source systems is not possible. A backup from one source will override the data that was applied by a backup from a different source. You can use a hot backup of one of the source instances to establish a target instance, and then use another copy method to apply the objects from the other source instances. Possible methods include:

Windows systems To use a hot backup between Windows systems, the target system must have an instance already created containing an identical ORACLE_SID and directory structure created with the Oracle creation tools. Oracle runs as a service on Windows, and the Registry entries must exist before starting the database recovery process. The database can start empty, because the hot backup will populate it.

Requirements

  • [Unix and Linux systems] Verify that the ORACLE_SID and ORACLE_HOME in the oratab file are correct for the instance you will be establishing with the hot backup. The SID must be the SID used in the routing map in the configuration file that you will be activating.
  • Read the requirements before you start this procedure. For more information, see Requirements for activating a configuration.
  • Users must stop accessing the production database while the hot backup and configuration activation take place.
  • Make certain a SharePlex database account exists in the source database (only). This account usually is created during SharePlex installation. See the SharePlex Installation and Setup Guide for more information.
  • Before you start, review this procedure and see the SharePlex Reference Guide for more information about the commands that are used.

Procedure

Note: If you are not using cascading replication, ignore all references to an intermediary system. For more information, see Configure replication through an intermediary system.

  1. On the source system, complete the Oracle hot backup.
  2. On the source system, stop user access to the source database by shutting it down and opening it in restricted mode.

  3. On the source system, switch the redo logs.

    On-premises database:

    svrmgr1> alter system switch logfile;

    Amazon RDS database:

    Run the Amazon RDS procedure rdsadmin.rdsadmin_util.switch_logfile.

  4. Keep a record of the sequence number of the current log.
  5. On all systems, start sp_cop and sp_ctrl from the bin sub-directory of the SharePlex product directory.

  6. On all systems, verify that sp_cop and sp_ctrl are running.

    sp_ctrl> status

  7. On the intermediary and target systems, stop Post. Stopping Post allows replicated data to accumulate in the post queue until the databases have been recovered.

    sp_ctrl> stop post

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

    sp_ctrl> activate config filename

  9. On the source system, view activation status.

    Note: The command retains control of sp_ctrl until activation is finished.

  10. When the activation is finished, allow users to resume access to the source database.
  11. List the archive logs on the intermediary and target systems. Delete any logs made after the one for which you made a record.
  12. On the intermediary and target systems, recover the database to the log number that you recorded. Make sure a full recovery is performed.
  13. On the intermediary and target systems, open the database.

  14. On the intermediary and target systems, run the Database Setup utility for the target instance. When prompted for the SharePlex database user, enter n to choose the existing user and password (these were copied in the backup).

    Would you like to create a new SharePlex user [y].n

    Notes:

    • SharePlex can remain running during the setup process.
    • For more information about Database Setup, see Database Setup Utilities in the SharePlex Reference Guide.
  15. On the intermediary and target systems, run the cleanup.sql script to truncate the SharePlex internal tables. Instructions for running this script are in the SharePlex Reference Guide.
  16. On the intermediary and target systems, disable triggers on the tables, or run the sp_add_trigger.sql utility script so that the triggers ignore the SharePlex user.
  17. On the intermediary and target systems, disable check constraints and scheduled jobs that perform DML.
  18. [Partitioned replication only] If you are using vertically partitioned or horizontally partitioned replication for any tables, delete the unneeded columns and rows from those tables on the intermediary and target systems.

  19. [Intermediary system only] On the intermediary system, set the SP_OCT_REPLICATE_POSTER parameter to 1. This directs SharePlex to capture posted changes on that system and replicate them to the target system.

    sp_ctrl> set param SP_OCT_REPLICATE_POSTER 1

  20. On the intermediary system, activate the configuration file.

    sp_ctrl> activate config filename

  21. On the intermediary system, monitor activation status.

    Note: The command retains control of sp_ctrl until activation is finished.

  22. When activation is finished, start the Post process on the intermediary and target systems. All instances are now in synchronization, and SharePlex will continue replicating to maintain synchronization.

    sp_ctrl> start post

  23. [Optional] If this was only a partial backup, drop the tablespaces that were not copied over during the hot backup.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating