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

Solve synchronization problems

Prevent and solve replication problems > Solve synchronization problems

This section reviews the causes and solutions for common synchronization problems. If you try these solutions and are still having problems, contact Quest Support.

For more information, see Understand the concept of synchronization.

If the issue you are experiencing is not listed in this documentation, search the SharePlex Knowledge Base at:

https://support.quest.com

The Knowledge Base provides filtering options and links to other resources that can help you use and troubleshoot SharePlex.

How SharePlex reports out-of-sync conditions

For all objects except those involved in transformation, SharePlex verifies that the source and target data in a given operation are synchronized before posting the replicated data to the target. SharePlex does not verify synchronization if transformation is being used because:

  • Transformation changes the target data, so before and after images cannot be compared.
  • The transformation routine posts the data, not SharePlex.

When SharePlex determines that source and target data are different, it generates error conditions but continues to post other data from the post queue. To direct Post to stop processing altogether when it detects an out-of-sync condition, change the SP_OPO_OUT_OF_SYNC_SUSPEND (Oracle) or SP_OPX_OUT_OF_SYNC_SUSPEND (Open Target) parameter. See the Post parameter documentation in the SharePlex Reference Guide.

When an out-of-sync condition occurs, the Post process logs a message in the Status Database and also to the Event Log. To view these files in sp_ctrl:

  • Status Database: Use the show statusdb or show sync command.
  • Event Log: Use the show log command.

Use these commands frequently to monitor for out-of-sync errors.

The following is an example of how SharePlex reports an out-of-sync condition.

sp_ctrl (irvspxu14:8567)> show sync

Out Of Sync Status Database irvspxu14

Count Details

----- --------------------

3 Table "SCOTT"."TG_TEST1" out of sync for queue irvspxu14 since 16-Jun-

08 17:06:33

3 Table "SCOTT"."TG_TEST2" out of sync for queue irvspxu14 since 17-Jun-

08 15:47:58

1 Table "SCOTT"."TG_TEST3" out of sync for queue irvspxu14 since 17-Jun-

08 15:52:03

When data goes out of synchronization, SharePlex logs the failed SQL statements to the database_ID_errlog.sql file in the data sub-directory of the SharePlex variable-data directory.

Important: If you see an out-of-sync message in the Status Database and in the Event Log, but there is no record in the database_ID_errlog.sql file for the transaction, do not ignore those messages. They could be associated with a ROLLBACK. Regardless of whether or not a transaction is rolled back, SharePlex still compares the pre-images of the source and target rows. If they are different, that indicates that the data is out of synchronization. Only when a transaction is committed on the source but fails on the target does SharePlex log it to the database_ID_errlog.sql file, to give you a record of the statement that should have been applied as a tool for problem solving and for manually applying the statement if appropriate. Rolled back statements are canceled operations, and therefore not logged on the target.

Detect false out-of-sync conditions

Sometimes an out-of-sync message can be false, and the data is not out-of-sync. You can compare the source and target data by using the compare command, or you can perform the following comparison manually.

To compare with the compare command

See the compare commands in the SharePlex Reference Guide.

To verify that data is out-of-sync

  1. Get the rowid of the affected row from the database_ID_errlog.sql file in the variable-data directory on the target system.
  2. Using the rowid in the WHERE clause, run a SELECT query on the source table to get the row data for this rowid.
  3. Run a SELECT query on the target table, using the row data that you got from the source query in the WHERE clause.
  4. Compare the query results. If they are different, the rows are out of synchronization. If they are the same, the rows are synchronized.

Common out-of-sync conditions and solutions

The following are common ways in which data goes out of synchronization. In most cases, SharePlex detects out-of-sync conditions and returns an error message, but there are some situations where an out-of-sync condition is hidden, and SharePlex will not return an error.

Out of sync condition Description Solution

Incorrect cleanup procedure

If one of the database_cleansp cleanup utilities was run on some, but not all, systems associated with an active configuration, SharePlex perceives an out-of-sync condition.

Determine whether or not the cleanup utility was run on all systems by viewing the Event Log. The log tells you if and when it was run on each system. It also tells you if and when the configuration was activated. You can compare the times for those events to determine what happened.

If the cleanup was not completed on all replication systems for this configuration, run the cleanup utility on all systems. Because the cleanup removes replication queues and processes and deactivates the configuration, you must perform initial synchronization again.

DDL changes

Some DDL-related causes of out-of-sync conditions are:

  • Non-replicated DDL changes are made to source objects, but the configuration was not reactivated so that the objects can be re-analyzed.
  • DDL that SharePlex replicates also gets performed manually on the target.

For a list of supported DDL, refer to the SharePlex Release Notes.

To undo duplicate DDL changes made manually and also by SharePlex:

  1. Stop the Post process (it might already be stopped).

    sp_ctrl(sysB)> stop post

  2. Alter the target table to undo the DDL changes.
  3. Start Post and let SharePlex post the replicated DDL (which is still in the post queue).

    sp_ctrl(sysB)> start post

 

DML changes made directly to the target If applications or users make DML changes to the tables on the target, the results of those changes will cause hidden out-of-sync conditions until Post attempts to apply a replicated change to the affected rows. When the change is applied, SharePlex returns an out-of-sync error.

Prevent all DML access to the target tables that are in replication.

You can use the compare and repair commands to compare tables for out-of-sync rows and then repair those rows. For more information, see the command documentation in the SharePlex Reference Guide.

Triggers on target objects

Triggers must be disabled on target objects. The triggers fire on the source system and SharePlex replicates their effects to the target.

If triggers have fired on the target system, the objects changed by the triggers are out of synchronization and must be resynchronized. See How to resynchronize source and target tables.

To disable the effects of triggers on an Oracle target

To disable the effects of triggers on target Oracle objects after the data is resynchronized, you can either of the following:

  • Run the sp_add_trigger.sql script, which directs triggers to ignore the SharePlex Oracle user. See the utilities documentation in the SharePlex Reference Guide for more information about the trigger scripts.

  • Disable the triggers if they are not needed.
Unnecessary constraints

The only constraints that are necessary on target tables in a one-way replication configuration are primary and unique key constraints. CHECK constraints are not necessary on the target because they are satisfied on the source. FOREIGN KEY and ON DELETE CASCADE constraints are also satisfied on the source, and SharePlex replicates the child operations to the correct tables on the target.

For an Oracle target, you can leave ON DELETE CASCADE constraints enabled if you configure SharePlex to ignore them.

See Set up Oracle database objects for replicationSet up Oracle database objects for replication

Duplicate entries in the configuration file. Duplicate entries, where the source, target, and routing map are identical, cause double posting on the target.

Copy the configuration file to a new file.

Find and remove any duplicates in the new file.

Perform a resynchronization and reactivation by following the directions in How to resynchronize source and target tables

Lack of disk space

Data goes out of synchronization if user transactions continue when SharePlex does not have enough room to accommodate them in the queues. This can happen if:

  • The network or target system was unavailable and too much data accumulated in the export queue.
  • The target was unavailable and too much data accumulated in the Post queue.
  • Capture lost pace with the logging of source transactions. In this case, data accumulates in the capture queue.
  • A SharePlex process was stopped, but not restarted.
  • The flush command was issued, but Post was not started again.
See How to resolve disk space shortage.

Changes to column conditions in horizontally partitioned replication

Out-of-sync conditions can result from the use of horizontally partitioned replication in the following cases:

  • A column condition value is updated and the new value no longer satisfies the row selection criteria.
  • A row that does not satisfy the column condition is updated to satisfy the condition.

See for more information about how to create column conditions so that partition shift does not occur.

Not reactivating after a configuration change.

If a table was added to the configuration, but the configuration was not reactivated, operations on that table are not being replicated.

Note: For Oracle source tables, the auto-add feature is enabled by default, and any new table whose name satisfies a wildcard in the configuration file gets automatically added to replication. See For more information, see Control Oracle DDL replication..

Resynchronize the affected tables, then reactivate the configuration so that SharePlex can update its object cache. To resynchronize the tables, see How to resynchronize source and target tables.

Queue corruption

If the SharePlex queues are corrupted, such as if there is a system failure, the data in them can be lost. This requires a resynchronization.

To resynchronize the tables, see How to resynchronize source and target tables.

(Oracle) To avoid queue corruption during system failure, you can use the parameter SP_QUE_SYNC. See the Queue parameters documentation in the SharePlex Reference Guide.

Oracle-related out-of-sync conditions and solutions

The following are common synchronization issues that relate specifically to replication between Oracle databases. In most cases, SharePlex detects out-of-sync conditions and returns an error message, but there are some situations where an out-of-sync condition is hidden, and SharePlex will not return an error.

Out of sync condition Description Solution
DDL changes

Some DDL-related causes of out-of-sync conditions are:

  • Non-replicated DDL changes are made to source objects, but the configuration was not reactivated so that the objects can be re-analyzed.
  • DDL that SharePlex replicates also gets performed manually on the target.

For a list of supported DDL, refer to the SharePlex Release Notes.

To undo duplicate DDL changes made manually and also by SharePlex:

  1. Stop the Post process (it might already be stopped).

    sp_ctrl(sysB)> stop post

  2. Alter the target table to undo the DDL changes.
  3. Start Post and let SharePlex post the replicated DDL (which is still in the post queue).

    sp_ctrl(sysB)> start post

 

Inadequate or non-existing conflict resolution routines

Conflict resolution procedures are required in a peer to peer (active-active) configuration. SharePlex uses the conflict resolution procedures to determine which operation to post when the same data change is received from different systems.

Revise and test the conflict resolution procedures, then see How to resynchronize source and target tables.
Log wrap

If the redo logs wrap before Capture can process the data it needs, the data can go out of synchronization if archived logging is not enabled or if the archive logs needed by Capture were removed. (Normally, Capture would access the archive logs and continue replicating.)

See Correct the problem first

  • If archiving is not enabled, there are no archive logs for SharePlex to read. Data lost after the log wrap cannot be recovered. Enable archived logging, and resynchronize the data. See How to resynchronize source and target tables.
  • If SharePlex is many logs behind Oracle, consider resynchronizing the data instead of restoring the logs. It might take less time than Capture would take to process the missing records from the archive logs. In addition, it eliminates the possibility that the capture queue could exceed free disk space while processing the archive logs. You can base your decision on the size of the redo logs and the number of tables being replicated, both of which determine how much information Capture must process. Also take into consideration how much latency the users of the target data can tolerate.
  • If archive logs are available, copy the appropriate logs back to the archived-log directory on the source system, or use the SP_OCT_ARCH_LOC parameter to point SharePlex to their location.
LONG columns

If a table has no primary or unique key, SharePlex builds a simulated key based on all of the columns except the LONG and LOB columns. If the LONG columns in the target rows are the only columns that contain unique values, multiple rows could meet the criteria for the simulated key. SharePlex could apply the UPDATE or DELETE to the wrong row without the error being detected, causing the table to go out of synchronization without an error message.

If you can create a key from columns that ensure uniqueness on the target table(s), you can avoid this kind of out-of-sync condition. After you create the key, resynchronize and re-activate those objects so that SharePlex can update its object cache.

If adding a primary or unique key is not possible (such as when packaged applications are in use), uniqueness of rows on the target system cannot be ensured.

Changes to keys

If tables use automatically generated numerical sequences as keys, and a key value changes, this may cause duplicity on the target system. If the new value already exists as a key in another row on the target system, SharePlex returns a unique-key constraint violation and out-of-sync error. This can happen when you update values using an x +n formula, where n is an incremental increase. It is possible that one of the x +n values will equal an existing value.

Create the sequences so that the updates cannot result in a duplication of keys on the target system. See Set up Oracle database objects for replication for additional information.
DBMS_SCHEDULER procedures running on source and/or target system The effects of these procedures, such as objects being created, manipulated and dropped outside replication, may not be visible to replication or may not be supported, resulting in data changes that cause out-of-sync conditions. Exclude source and target objects from the jobs.
Virtual private database If replicating data that is configured as a virtual private database, the SharePlex database user may not have the access rights to capture the data. Any changes to that data will not be reflected on the target.

If you do not need that data replicated to the target, you can filter it out of the SharePlex configuration by means of partitioned replication.

If you want the data to be replicated, assign the SharePlex user the correct access rights.

PK/UK logging not enabled Certain replication problems can be prevented by logging key values. SharePlex fetches key values based on the rowid. Any operation that changes the rowid, such as ALTER TABLE...MOVE, can cause the wrong key values to be used for subsequent DML operations. SharePlex recommends that both primary key and unique key supplemental logging be set, or that a supplemental log group on unique columns be defined for every table in replication.

Correct the problem first

If data is out-of-synchronization, do the following before you resynchronize the data:

  1. Determine why it happened before you resynchronize the data. Otherwise, the problem can repeat itself and result in more data going out of synchronization.
  2. Stop the Post process to prevent further errors. If the accumulation of messages in the Post queue is threatening to cause disk space issues, and if there is enough disk space available on the source system, you can stop Import until Post can clear out some of the operations from other transactions. See How to resolve disk space shortage for steps.
  3. View the Status Database and the Event Log to determine the cause of the problem.
  4. Resolve the problem.

Resynchronize data

See How to resynchronize source and target tables to resynchronize data.

Solve compare command errors

Prevent and solve replication problems > Solve compare command errors

If you are having trouble with any of the compare or repair commands, refer to the following for assistance.

If the issue you are experiencing is not listed in this documentation, search the SharePlex Knowledge Base at:

https://support.quest.com

The Knowledge Base provides filtering options and links to other resources that can help you use and troubleshoot SharePlex.

Problem Description Solution
Oracle error 904

Error 904 calling oexec in de_select_prepare_to_fetch.

A comparison failed because the source and target tables being compared are structurally different. The compare and repair commands do not detect and repair out-of-sync conditions caused by unsynchronized DDL operations or tables that are not structurally identical.

Do a DESCRIBE on both tables. It probably will show that the tables do not have an equal number of columns or the datatypes are different. After you correct the DDL problem, you can run a repair to resynchronize the values in the rows.

"Too many users” error

Can not add DataEquator queue reader que_TOOMANYUSERS: User table is full.

The maximum number of processes reading from and writing to the SharePlex queues was exceeded. No more than 20 processes can read from and write to the post queue at the same time, including the replication processes and the compare and repair processes. An error is most likely to occur when a repair option is used, because a repair accesses the queue much longer than a comparison without a repair.

There is no workaround or way to adjust the limit, nor is there a way to determine how many compare processes can run concurrently without exceeding the limit.

TIP: To compare multiple tables at the same time, without being restricted by process limitations, use the compare using command. To limit the tables being compared, create a new configuration containing only the ones that you want to compare, and use it for the comparison. (Do not activate that configuration.) All of the tables are compared with one compare using process.

A client process fails to die

When a sp_desvr server process dies, the associated sp_declt client processes usually die. If a process does not die, you can kill it.

See Kill compare processes .
A server process fails to die When you kill a sp_declt client process or it dies on its own — or if the sp_desvr server process has not communicated with the client — the sp_desvr server process usually exits after a certain amount of time, which is controlled by the SP_DEQ_TIMEOUT parameter. See Kill compare processes .
     

Kill compare processes

To kill a client process

If you need to kill a sp_declt client process, and there are multiple compares running, you can determine which one to kill in one of the following ways:

  • By viewing the sp_declt log file — In the file, look at the Session IDs of the sp_declt processes and find the one that matches the PID of the sp_desvr process that died. That is the sp_declt process to kill. The sp_declt Session ID is the same as the PID of the associated sp_desvr process.
  • By viewing the Event Log — The Event Log records the startup of each sp_declt client process and its PID. A subsequent entry in the log records the compare log file to which the process is writing. Within the compare log file’s name in that entry is the PID of the server process. For example, in the following sample entry, the sp_declt process PID is 2450. The process writes to compare log ../o734v32a_declt- 1228-01.log. The 1228 is the PID of the server process.

    05/04/01 17:01 Process launched: sp_declt (for o.o734v32a-o.o734v32a- 87056 queue all) [pid = 2450]

    05/04/01 17:01 Notice: sp_declt(deq) (for o.o734v32a-o.o734v32a-87056 queue all) Opened DataEquator session log file /u10/julia30014/var7/ log/o734v32a_declt-1228-01.log

    You can search the log file names for the server process that died, and look for the client process associated with that log file to determine the correct PID to kill.

On Windows systems, the logs also record the startup of the associated sp_cop process.

To kill a server process

If you need to kill a sp_desvr server process when a sp_declt client process dies, look in the Event Log to find out which log the sp_declt client process was writing to. The Event Log records the startup of each client process and its PID. A subsequent entry in the log records the compare log file to which the process is writing. Within the compare log file’s name in that entry is the PID of the server process. For example, in the following sample entry, the sp_declt process PID is 2450. The process writes to log ../o734v32a_declt-1228-01.log. The 1228 is the PID of the server process, and that is the process to kill.

05/04/01 17:01 Process launched: sp_declt (for o.o734v32a-o.o734v32a- 87056 queue all) [pid = 2450]

05/04/01 17:01 Notice: sp_declt(deq) (for o.o734v32a-o.o734v32a-87056 queue all) Opened DataEquator session log file /u10/julia30014/var7/ log/o734v32a_declt-1228-01.log

On Windows systems, the logs also record the startup of the associated sp_cop process.

Solve other problems

This section reviews solutions to other replication problems.

If the issue you are experiencing is not listed in this documentation, search the SharePlex Knowledge Base at:

https://support.quest.com

The Knowledge Base provides filtering options and links to other resources that can help you use and troubleshoot SharePlex.

SharePlex does not run on a Windows system

SharePlex uses the MKS Toolkit® (also known as NuTCRACKER) operating environment from Parametric Technology Corporation (PTC) to run on Windows systems. If the NuTCRACKER service is stopped or disabled, or if the NuTCRACKER files have been removed or relocated, there will be errors when you try to run SharePlex.

Solution:

  1. Run the Windows Task Manager, then click the Processes tab.
  2. Check to see if the NuTCRACKER service is running. The process name is nutsrvx, where x is the version of the NuTCRACKER software.
  3. If the process is not running, start the NuTCRACKER service by using the Services panel of the Administrative Tools Control Panel.

    • If you do not see the NuTCRACKER service, re-install SharePlex.
    • If the NuTCRACKER service started, but SharePlex still returns errors, check to see if the NuTCRACKER files were relocated. To determine the correct installed location, look at the following in the Windows Registry:

      HKEY_LOCAL_MACHINE\SOFTWARE\Data Focus\Runtime

    • The InstallDir string in the right pane of the Runtime node shows the correct location for the files. Search for the MKS Toolkit folder and restore the files to the location specified in the InstallDir entry.

If you cannot locate the files or cannot restore them to the correct location, do the following:

  1. Stop the SharePlex and NuTCRACKER services, if running.

  2. Run regedit to open the Registry Editor.

  3. Delete the Data Focus and Mortice Kern Systems registry folders under HKEY_LOCAL_MACHINE\SOFTWARE\.
  4. Close the Registry Editor.
  5. Restart the system.
  6. Reinstall SharePlex. Make certain to reinstall SharePlex in the same location, and make certain to install the NuTCRACKER component when prompted.
  7. Restart the system to make the new NuTCRACKER environment effective.

A “Can’t unlink file” error occurs on Windows systems

A nuisance error similar to the ones below sometimes occur on Windows systems. The files eventually unlink.

Text file busy Unlinking file: 'r:\splex2102/rim/o.SERV+C+0.0000000

Or...

System call error: sp_ordr.exe(osp) (for o.SERV queue o.SERV) Text file busy 17003 - Can't unlink file R:\Splex2100/state/o.SERVlog_ sp_ordr.30

Common connection errors

The following are solutions to common errors when starting sp_ctrl, or with forming a connection with the host, port or [on host] commands in sp_ctrl.

Explanation of connection error messages
Error Cause Solution
Host unknown: cannot form connection Appears when either the host command or [on host] option is issued. Verify that the system to which you want to connect is running and that you are using the correct system name.
Network unreachable The network is down. Find out how long the network administrator expects it to last. If the downtime could cause the SharePlex queues to exceed their disk space, take measures to avoid having to resynchronize the data. See How to resolve disk space shortage.
User is not authorized as a SharePlex user -- check /etc/group You do not have user permissions to execute the operation. SharePlex users must be listed in the /etc/group file (Unix and Linux) or in the Users list (Windows) under one of the SharePlex user groups: SharePlex Admin, spoper, spview.
unauthorized connection attempt from host hostname. net A connection from a remote machine was denied because its name is not listed in the auth_hosts file. See the error message for the name of the system. To allow that system to connect to the sp_cop on the local system, add its name to the auth_hosts file.

Common command errors

Error Cause Solution
Deactivate/flush a nonactive datasource You are attempting to flush a configuration that is not active. None required.
Bad routing specification The syntax in the routing map is incorrect. See Routing specifications in a configuration file.
Status db file is corrupt. The Status Database has been damaged. Shut down SharePlex and remove the statusdb file, which resides in the data subdirectory of the SharePlex variable-data directory. SharePlex will create another one when you start sp_cop again.
Parameter does not exist in database.

You tried to set a parameter, and you entered the wrong name or the parameter is deprecated for your SharePlex version.

Use the list param command to view the SharePlex parameters for your version and to verify the spelling.

Parameter type checking failed - look in param - defaults file. You might have entered the wrong data type for the parameter. Use the list param command to determine the valid data type.

Unknown service specified.

or...

No such module.

or...

Service may be only one of: post, read, import, export, capture, all.

Valid service (process) names are capture, read, export, import, post. Issue the command again with the correct name.

Command was called with an invalid argument.

or…

Unknown keyword used in command.

The command contains invalid input. Issue the help command to view valid input for the command.
Permission denied for command - check your authorization level. You are not a member of the user group that can issue this command. Issue the authlevel command to view your authorization level.
Default host is not defined: use the ‘host’ command or [on host] option. SharePlex cannot to determine which system you want the command to affect. Either establish a default host with the host command or use the [on host] option with the command that you want to issue (if available).

How to resynchronize source and target objects

Prevent and solve replication problems > How to resynchronize source and target objects

The following instructions help you decide how to resynchronize out-of-sync tables.

Manually patch out-of-sync tables

Valid for: All database types

If the number of synchronization errors is small, you can try to repair out-of-sync tables manually. When the Post process detects an out-of-sync condition, it ignores the error and continues to apply the next operations in the post queue. However, Post logs source SQL statements that cause out-of-sync errors to an error file calleID_errlog.sql. (ID is the identifier that SharePlex uses for the target instance, such as the ORACLE_SID or the database name.) You can apply those SQL statements to a target table through the native SQL interface of the database. Because this procedure bypasses the comparison made by Post, the operations should succeed assuming the structure of the target table did not change.

SharePlex stores ID_errlog.sql in the data sub-directory of the variable-data directory on the target system. The entries in the file are similar to the following example:

-- Host (irvlabua) Sid (al920u64)

-- session 2, 1 error --

--

-- [1] Tue Dec 11 13:31:32 2007

-- redolog seq#/offset 26622/26980368

-- redolog timestamp 641050290 (12/11/15 13:31:30)

-- original rowid AAE0m8AAWAAAAFEAAA

-- -- NOT FOUND

delete from “SP_5”.”QA_LOB_DISABLE_INROW” t where rownum = 1 and “KEY”='01';

To apply the SQL manually

  1. Stop user access to the affected source table.
  2. On the target system, open the ID_errlog.sql file.
  3. Apply the SQL statement(s) to the target table.
  4. Reactivate the configuration if you had to make any changes to it.

    sp_ctrl> activate config filename

  5. Allow user access to the source table.

Resynchronize by copying the source tables

Valid for: All database types

This procedure restores synchronization to out-of-sync target tables by applying a copy of the source tables. You only need to resynchronize the tables that are out of synchronization, so users can continue accessing all other tables.

Important! Before you start, review this procedure and see the SharePlex Reference Guide for more information about the commands that are used.

  1. On the source and target systems, make sure sp_cop is running.
  2. On the target system, run sp_ctrl.
  3. [If necessary] On the target system, issue the show sync command to identify the tables that are out of synchronization.

    sp_ctrl> show sync

  4. On the source system, stop activity for the out-of-sync tables.
  5. On the source system, run sp_ctrl.
  6. On the source system, issue the flush command. Note: This command has additional options for use with named queues or multiple targets. See the SharePlex Reference Guide for more information about this command.

    sp_ctrl> flush datasource

  7. On the source system, copy the tables.
  8. On the source system, reactivate the configuration file if you had to make any changes.

    sp_ctrl> activate config filename

  9. On the source system, allow users back onto the source tables.
  10. On the target system, issue the status command until it shows that Post stopped.

    sp_ctrl> status

  11. On the target system, restore the tables.

  12. On the target system, disable or modify triggers, referential integrity constraints and check constraints according to the requirements of your replication strategy.
  13. On the target system, determine the status ID of each message by viewing the Status Database.

    sp_ctrl> show statusdb detail

  14. On the target system, clear each message with the following command.

    sp_ctrl> clear status statusID

  15. On the target system, start the Post process.

    sp_ctrl> start post

Resynchronize with Oracle transportable tablespace

Valid for: Oracle database

The transportable tablespace feature enables you to resynchronize numerous out-of-sync tables quickly and with minimal downtime. To use the transportable tablespace feature, follow the instructions in the Oracle documentation for generating a tablespace set, moving the tablespace set to the target database, and plugging the set into the database. The following instructions contain steps only for using this feature to resynchronize data. It assumes familiarity with using the transportable tablespace feature.

Important! Before you start, review this procedure and see the SharePlex Reference Guide for more information about the commands that are used.

  1. On the source system, set the source tablespace to READ ONLY.

    SQL> ALTER TABLESPACE name READ ONLY;

  2. On the source system, run sp_ctrl.
  3. On the source system, issue the flush command in sp_ctrl. Note: This command has additional options for use with named queues or multiple targets. See the SharePlex Reference Guide for more information.

    sp_ctrl> flush datasource

  4. Export the metadata to an export file according to the Oracle documentation.
  5. When the export is finished, copy the datafiles to a secondary location on the source system. This minimizes the impact on the source database of copying the files to the target system.
  6. On the source system, set the source tablespace(s) to READ WRITE mode.

    SQL> ALTER TABLESPACE name READ WRITE;

  7. On the target system, drop the existing datafiles and tablespaces from the target database so that the copied files can be applied.
  8. Copy the files from the secondary location on the source system to the target system.
  9. On the target system, use the Oracle import utility to import the metadata and the tablespace definitions.
  10. On the target system, set the tablespace(s) to READ WRITE mode.

    SQL> ALTER TABLESPACE name READ WRITE;

    Note: SharePlex must be the only user permitted to have write access to the target tables, unless you are using peer-to-peer replication.

  11. On the source system, reactivate the configuration file if you had to make any changes to it.

    sp_ctrl> activate config filename

  12. On the target system, run sp_ctrl.
  13. On the target system, start the Post process.

    sp_ctrl> start post

Resynchronize with an Oracle hot backup on an active database

Valid for: Oracle database

When you use an Oracle hot backup and the reconcile command to resynchronize a target instance, users can continue to access the production data while the backup is made and applied.

Important:
  • To resynchronize centralized reporting, such as a data warehouse, you cannot use a hot backup from all source systems. One backup would override the data from the previous one. You can use a hot backup of one of the source instances to establish the target instance, and then use another method such as export/import or transportable tablespaces to copy the tables from the other instances.
  • To resynchronize peer-to-peer replication, you must quiet all of the secondary source systems for the duration of this procedure. Move all users to the primary system, and then follow the procedure. After the procedure has been performed on all of the secondary systems, users can resume activity on them.
  • Before you start, review this procedure and see the SharePlex Reference Guide for more information about the commands that are used.

To resynchronize with a hot backup

  1. On the source and target systems, run sp_ctrl.
  2. On the target system, stop the Post process. This allows the replicated data to accumulate in the post queue until the target instance has been recovered and reconciled.

    sp_ctrl> stop post

  3. On the source system, run the Oracle hot backup.
  4. On the source and target systems, verify that sp_cop, sp_ctrl and all SharePlex processes (Capture, Read, Export, Import, Post) are running.

    sp_ctrl> status

  5. Switch log files on the source system.

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

    • To recover the database to an Oracle System Change Number (SCN), pick an SCN to recover to on the target database.
  6. Recover the target database from the hot backup:

    • 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.
  7. Open the database with the RESETLOGS option.

  8. On the target system, issue the reconcile command. If you are using named post queues, issue the command for each one. Issue the qstatus command if you are unsure of the queue name.

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

      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 in step 5.

      sp_ctrl> reconcile queue queuename for datasource-datadest scn scn_number

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

    The reconcile process retains control of sp_ctrl until it is finished, and then the sp_ctrl prompt returns.

  9. On the target system, log onto SQL*Plus as the Oracle user for SharePlex, and run the cleanup.sql script located in the bin sub-directory of the SharePlex product directory. This script truncates and updates the SharePlex tables, which are owned by the SharePlex user. If you are running multiple instances of sp_cop with multiple variable-data directories, there is a SharePlex Oracle user for each one. Make sure you run this script as the SharePlex user that owns the tables you want to restore. The script prompts you for the SharePlex user name and password.

    SQL> @/productdir/bin/cleanup.sql

  10. On the target system, disable or modify the following according to your replication strategy:

    • triggers
    • foreign key constraints
    • cascading delete constraints (or configure SharePlex to ignore them)
    • check constraints
    • scheduled jobs that perform DML
  11. On the source system, reactivate the configuration file if you had to make any changes to it.

    sp_ctrl> activate config filename

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

    sp_ctrl> start post

Related Documents