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:
The Knowledge Base provides filtering options and links to other resources that can help you use and troubleshoot SharePlex.
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:
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:
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.
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
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:
|
For a list of supported DDL, refer to the SharePlex Release Notes. To undo duplicate DDL changes made manually and also by SharePlex:
|
| 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:
|
| 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:
|
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:
|
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. |
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:
|
For a list of supported DDL, refer to the SharePlex Release Notes. To undo duplicate DDL changes made manually and also by SharePlex:
|
| 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.) |
|
| 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. |
If data is out-of-synchronization, do the following before you resynchronize the data:
See How to resynchronize source and target tables to resynchronize data.
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:
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 . |
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 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.
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:
The Knowledge Base provides filtering options and links to other resources that can help you use and troubleshoot SharePlex.
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:
If the process is not running, start the NuTCRACKER service by using the Services panel of the Administrative Tools Control Panel.
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
If you cannot locate the files or cannot restore them to the correct location, do the following:
Stop the SharePlex and NuTCRACKER services, if running.
Run regedit to open the Registry Editor.
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
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.
| 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. |
| 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). |
The following instructions help you decide how to resynchronize out-of-sync tables.
You can resynchronize the data in a number of ways. See the following topics:
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
Reactivate the configuration if you had to make any changes to it.
sp_ctrl> activate config filename
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.
[If necessary] On the target system, issue the show sync command to identify the tables that are out of synchronization.
sp_ctrl> show sync
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
On the source system, reactivate the configuration file if you had to make any changes.
sp_ctrl> activate config filename
On the target system, issue the status command until it shows that Post stopped.
sp_ctrl> status
On the target system, restore the tables.
On the target system, determine the status ID of each message by viewing the Status Database.
sp_ctrl> show statusdb detail
On the target system, clear each message with the following command.
sp_ctrl> clear status statusID
On the target system, start the Post process.
sp_ctrl> start post
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.
On the source system, set the source tablespace to READ ONLY.
SQL> ALTER TABLESPACE name READ ONLY;
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
On the source system, set the source tablespace(s) to READ WRITE mode.
SQL> ALTER TABLESPACE name READ WRITE;
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.
On the source system, reactivate the configuration file if you had to make any changes to it.
sp_ctrl> activate config filename
On the target system, start the Post process.
sp_ctrl> start post
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.
To resynchronize with a hot backup
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
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
Recover the target database from the hot backup:
Open the database with the RESETLOGS option.
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.
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
On the target system, disable or modify the following according to your replication strategy:
On the source system, reactivate the configuration file if you had to make any changes to it.
sp_ctrl> activate config filename
On the target system, start the Post process. The two instances are now in synchronization, and SharePlex will continue replicating.
sp_ctrl> start post
© 2022 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy
