What is the use of the parameter SP_DEQ_PARTIAL_REPAIR_CLR_OOS (4297097)

What is the use of the parameter SP_DEQ_PARTIAL_REPAIR_CLR_OOS

Starting with SharePlex 9 and up, parameter SP_DEQ_PARTIAL_REPAIR_CLR_OOS was introduced to deal with repair of a part of the table using “where” clause. The article delves on the working of this parameter including pros and cons.

General information.

When a table is repaired using a “where” clause, SharePlex works on the premise that the table can still have out of sync in other rows that are not accessed by the repair. So repair does not remove the out of sync entry for that table in the statusdb. This needs to be done manually using the command “clear status <statusid>”.

If it is known that the out of sync was only confined to some rows and a repair issued with “where” clause to fix those subset of rows. 

In order to remove the out of sync entry in statusdb entry for such repair with where clause, SharePlex 9 and up offers the parameter SP_DEQ_PARTIAL_REPAIR_CLR_OOS.  When “show sync” is subsequently issued, the table will not show up as out of sync. 

The SP_DEQ_PARTIAL_REPAIR_CLR_OOS needs to be configured to a value of 1 on target sp_ctrl prior to running another repair with “where” clause:

sp_ctrl>set param SP_DEQ_PARTIAL_REPAIR_CLR_OOS 1

The parameter will remain in effect for all subsequent Repair till it is changed back to its default of 0.

Reset the parameter back to its default value of 0 once you have completed that repair for a subset of rows. The reason is, not every repair done with a “where” clause can be guaranteed to have synchronized the whole target table. To reset it back to its default, issue the following command on  target prior to running another repair:

sp_ctrl>reset param SP_DEQ_PARTIAL_REPAIR_CLR_OOS

The following excerpt taken from Reference Guide describes the parameter in detail: SP_DEQ_PARTIAL_REPAIR_CLR_OOS This parameter enables the repair process to clear out-of-sync messages from the statusdb for a table after a compare-repair of only a subset of rows of that table, such as: Row subsets specified with a WHERE clause or an Oracle [sub]partition name. Row subsets specified with horizontally partitioned replication. This parameter works as follows: Value of 0 (disabled, the default): The out-of-sync messages are retained in the statusdb after a subset of the rows is compared and repaired. The messages are retained so that users are aware there may be out-of-sync rows that were not qualified by the selection criteria. Value of 1 (enabled): The out-of-sync messages are cleared after a subset of the rows is compared and repaired. Enable this parameter only if you know that the rows in the subset are the only ones in the table that are out-of-sync. NOTE: To be certain all of the out-of-sync rows in a table are repaired, run the compare-repair for the entire table. Default: 0 (disabled) Range of valid values: 0 or 1 (flag) Takes effect: immediately available for the next comparison. For details on "clear status" and "show sync" commands of sp_ctrl, please refer to SharePlex Reference Guide for your version.