Starting with SharePlex 8.6.6, there is a parameter SP_OPO_SAVE_OOS_TRANSACTION, which, if enabled 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 DML 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:
Target:
sp_ctrl>set param SP_OPO_SAVE_OOS_TRANSACTION 1
sp_ctrl>stop post (make sure it is “stopped by user” and not “stopping”)
sp_ctrl>start post
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.
Each rolled back transaction has its own SQL file. The naming convention for the file is SCN_queue.sql, where:
SCN is the commit System Change Number (SCN) of the transaction.
queue is the name of the Post queue that contains the transaction.
Example file name:
4346118046_postq1.sql
Here is a sample file generated when the parameter is enabled:
alvsupu07 # cd $SP_SYS_VARDIR/oos
alvsupu07 # ls -ltr
.
.
-rw-rw-r-- 1 oracle spadmin 720 Jan 17 13:26 002404049121_alvsupu15.sql
view 002404049121_alvsupu15.sql
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;
-- Prior line has out of sync
-- redolog seq#/offset 14340/744464
-- redolog timestamp 933528711 (01/17/17 17:31:51)
-- original rowid AAA1qHAAEAAAXaEAAD
COMMIT;
The logging in the SCN_queue.sql and SID_errlog.sql is mutually exclusive, meaning, in case of out of sync transaction there is no logging in the errlog.sql file so long as the parameter SP_OPO_SAVE_OOS_TRANSACTION is enabled.
The SCN_queue.sql is ready to be applied to the target database to sync it, so long as the underlying out of condition are corrected. Otherwise the sql listed in it will fail to apply. It has all the “alter session…” entries in it that are needed to apply the sql listed in it.
There is no way to undo the skipping of the out of sync transaction once it is skipped.
The enabling of the parameter will only make logical sense if the application apply all the interdependent changes in one transaction only.
One major difference between how SharePlex handles out of sync with or without this parameter is, in case of latter the successful DMLs within an out of sync transaction will still apply and only those DML that resulted in the out of sync are discarded into errlog.sql file. For example, if my transaction has one INSERT which will not result in any error and has one UPDATE that can result in “row not found”, then my INSERT will be successfully applied to the target and the UDPATE will be discarded and will be logged in *errlog.sql file.
The parameter will operate on tables having most of the common data type but may exclude some data type. For the exhaustive list of supported data type, please refer to SharePlex Reference Guide.
When the parameter is enabled, the statusdb file is still updated for out of sync. The only difference is, all tables forming part of the discarded transaction go out of sync and the statusdb is updated for all of these tables. Without the parameter, the statusdb is only updated for table(s) in the specific DML(s) which fail to apply in a given transaction.
Due to the fact that each failed transaction generates its own .sql file, it is imperative that periodic housekeeping be done for the files generated. When correcting the out of sync using repair or other means, the .sql file should be removed from the SCN_queue.sql file.
Currently the feature is only supported for Oracle targets.
