What to check when out of sync errors are being encountered in Shareplex. (4325336)

What to check when out of sync errors are being encountered in Shareplex.

Shareplex post process is logging SQL in Shareplex var dir/log/*errlog.sql and event_log shows Shareplex detecting out of sync (OOS).

This is typically due to these:

INSERT - unique constraint violation, record with the same key already exist on target
UPDATE - pre-image of the column being changed does not match whats on target, or the record does not exist on target
DELETE - the record Shareplex tries to delete is already deleted on target

Possible causes:
1. Triggers are not disabled on target
2. Delete cascade FK constraints are not disabled on target
3. Cron jobs or dbms jobs are running on target which may issue DML on replicated tables; or users are accessing target
4. Software bug

Check the following:

1. Triggers - disable all triggers on the target system:

If you can not disable triggers on target, then you can run $SP_SYS_PRODDIR/util/sp_add_trigger.sql on target. This script will add a WHEN CLAUSE to existing triggers except those owned by SYS/SYSTEM/SPLEX. This will allow triggers not to fire when DML is issued by SPLEX user. This script works on row-level triggers only.

If you have statement level trigger, then you need to wrap around the trigger body with the following:

If user !=SPLEX then
trigger body....
end if;

2. Delete Cascade --- disable on target:

If you can not disable due to peer to peer, then you need to create a conflict resolution procedure to ignore delete conflict. Refer to Shareplex Admin Guide for more information on setting up conflict resolution.

3. Check cron tab and dba_jobs to be sure there are no batch jobs running on the target system:

select job, what, broken from dba_jobs;
select * from dba_jobs_running;

4. Be sure there are no users accessing the tables on the target. You can query v$sqlarea and v$session and check to see if there are users other than SPLEX doing DML on target.

For example:

selelct sql_text, parsing_user_id from v$sqlarea where sql_text like %Update OWNER.table_name%;
select user#, username from v$session where user#=xxxx (xxxx is the parsing_user_id from previous query);

5. Tables related are not grouped in the same post queue or only one of them is in replication.

6. Table without PK/UK, but has a unique index which allow null value on the indexed column.

7. Capture encountered corruption and skipped bad block, this may lead to OOS too. Send capture log to Tech Support for review.

8. Capture encountered null H_record on source, row chaining information is missing and thus causing OOS. Send capture log to Tech Support for review.

9. Application is updating key columns in batches like these:

update table set keycol=2 where keycol=1;
update table set keycol=3 where keycol=2;
update table set keycol=4 where keycol=3;

10. Check $SP_SYS_VARDIR/log/*errlog.sql, it logs failed SQL which include the original rowid from source and the SQL statement post is trying to issue and failed. You can find out the key value and run SQL statement on both source and target to see which column it is not matching and this may help troubleshoot the cause.

NOTE: If youve checked all of the above and can not find cause for OOS, then please contact Quest Shareplex Support to investigate the issue.