Use Compare and Repair to Keep SharePlex Replication From the Dark Side

"There has been great disturbance in the Force".  In the SharePlex galaxy, we call that "out of sync".

In a recent blog, SharePlex Jedi Mark Kurtz introduced us to the SharePlex Compare and Repair commands.  Here are some additional best practices that that you can use to help bring balance back to the Force in your galaxy.

Make Frequent Use of Compare

While Post does a great job of detecting "out of sync" conditions on data that it processes; if something happens to your data that's not been updated or changed, a "hidden" out-of-sync condition can be created.    The SharePlex Administration Guide provides some examples of these conditions and ways to help avoid them.   Once you've reviewed the results of the compare,  you can use the logs to view detailed results, and also find the SQL that repair would run.

Automate Compare Jobs
Consider using a shell or batch script to automate periodic compare jobs on critical tables.  You can pass commands to sp_ctrl via the command line.  Run these batch jobs periodically, and keep a small uprising from becoming a full-scale rebellion.   You probably don't want to fully automate repair jobs, in case you need to correct issues before you actually make the corrections.

Compare or Repair Subsets of Tables or Rows

If you have a large number of tables under replication, or a few very large tables; you can use the "compare using command to limit the number of tables being compared at any given point in time, and spread the work over time.   You can even create additional configuration files that contain only certain tables you'd like to compare in a group.

You can also use a "where" clause to filter which rows are compared.   But, use caution here, after a partial compare, SharePlex will still report the table "Out Sync" with the "Show Sync" Command; since it can't be certain that all out of sync rows were compared or repaired

If your source and target tables contain columns with different names, there are "sourcewhere" and "targetwhere" clauses to assist you.

"Use the logs, Luke"

Whenever you run a compare or repair; the detailed results of the job(s) can be found in the <vardir>/logs directory.    These logs contain detailed information on the steps SharePlex performed, and also the SQL required to correct any out-of-sync tables.  The desvr process runs on the source, and the declt process runs on the target. The log file names are formatted as follows. 

desvr_JobID_SID_pProcessID.log, where:

  • JobID is the SharePlex-assigned job ID.
  • SID is the ORACLE_SID of the Oracle instance where the source table resides.
  • ProcessID is the process ID of the sp_desvr process

declt_JobIDTableID_SID_SourceHost_pProcessID appended with either .log or .sql, where:

  • JobID is the SharePlex-assigned job ID for the job.
  • TableID is the SharePlex-assigned table ID for the table in the job.
  • SID is the ORACLE_SID of the Oracle instance where the source table resides.
  • SourceHost is the name or IP address of the source host.
  • ProcessID is the process ID of the sp_declt process.

Here are examples of log file names:

  • desvr_606_ora112_p14610.log
  • declt_606-1_ora112_prodsys_p6528.log
  • declt_606-1_ora112_prodsys_p6528.sql

I hope you've found this blog and the links useful.   When used wisely, Compare and Repair are powerful commands that can help keep your galaxy from the "Dark Side".

About the Author
Clay.Jackson
Clay Jackson is a Database Systems Consultant for Quest, specializing in Database Performance Management and Replication Tools. Prior to joining Quest, Jackson was the DBA Manager at Darigold. He also...