The conventional methodology followed by SharePlex and most other replication solutions is to ignore out of sync DML within a transaction while applying rest of the successful DML to the target database.
A transaction may span one or more objects, each having one or more rows, or DML. When posting that transaction, if any row runs into out of sync conditions, the default behavior is to simply discard that row, record the failed DML in a file and move on to the next row and so on till the complete transaction is processed. In the process the failed DML are logged in a file named SID_errlog.sql (where SID denotes the Oracle system identifier of the source database), whereas the successful DML make it to the target table(s). Though rarely used, it is possible to configure SharePlex to stop posting altogether when running into out of sync and manually start post again after correcting that out of condition. There is a Post parameter named SP_OPO_OUT_OF_SYNC_SUSPEND which can be configured to 1 to disallow posting at the first encounter of out of sync DML.
The reasons behind allowing successful DML to post while discarding the rest are, targets are usually meant for reporting so speed is of essence and a near perfect accuracy is acceptable though it never means that the integrity of the target data should be compromised. It is just that reporting involves aggregates and some missing rows will not alter result in a significant way. Moreover, such out of sync are not a regular phenomenon and if they were, they need to be urgently investigated and corrected. Since the failed DML are logged separately, it is possible to correct the target data for the tables impacted at a convenient time. If this was a configuration for High Availability or Peer to Peer replication, then the out of sync should be investigated and fixed sooner as the nodes of SharePlex may undergo role reversal at a short notice and it is not a good idea to make the erstwhile secondary as primary when the out of sync conditions lurk in that secondary.
With the advent of version 8.6.6, it is possible to take a different approach when running into out of sync situations while posting to the target. Call it an exception handling methodology. The 8.6.6 has a Post parameter SP_OPO_SAVE_OOS_TRANSACTION, which, if enabled on target, logs the out of sync transaction in a separate sql file (other than the *errlog.sql file) along with the “alter session” entries so that the failed transaction can be applied after taking corrective action. The complete transaction is discarded instead of only the failed DML within the transaction. The latter is the default behavior where only failed DMLs within a transaction are discarded.
This feature is also referred in SharePlex jargon as logical transaction rollback. Here is the way to enable it:
On target sp_ctrl set the parameter to 1 and bounce Post:
sp_ctrl>set param SP_OPO_SAVE_OOS_TRANSACTION 1
sp_ctrl>stop post (make sure it is “stopped by user” and not “stopping” and wait till it is stopped by user)
From this point on, all the transactions that can result in out of sync will not be applied but would be written to a separate .sql file located in /vardir/log/oos subdirectory. The following are some of the points to be noted.
SCN is the commit System Change Number (SCN) of the transaction and queue is the name of the Post queue that contains the transaction.
Example file name:
Here is a sample file generated when the parameter is enabled:
alter session set nls_date_format = 'MMDDSYYYYHH24MISS';
alter session set nls_timestamp_format = 'MMDDSYYYYHH24MISS.FF';
alter session set nls_timestamp_tz_format = 'MMDDSYYYYHH24MISS.FF TZR';
alter session set time_zone = '+00:00';
UPDATE "SHA86"."TABLE1" SET "NUM_COL" = '5' WHERE "NUM_COL" = '4'
AND ROWNUM = 1;
-- Prior line has out of sync
-- redolog seq#/offset 14340/686608
-- redolog timestamp 933528685 (01/17/17 17:31:25)
-- original rowid AAA1qHAAEAAAXaEAAD
-- Row Not Found
DELETE FROM "SHA86"."TABLE1" WHERE "NUM_COL" = '5' AND ROWNUM = 1;
-- redolog seq#/offset 14340/744464
-- redolog timestamp 933528711 (01/17/17 17:31:51)
Please refer to SharePlex Reference Guide for details.