You set the parameter SP_OPO_DISABLE_OBJECT_NUM with queue option to disable posting on a problem table (which replicates thru the named Post queue specified) with the intent of better control but find that the posting still continues on the table. The following option was used:
sp_ctrl>set param SP_OPO_DISABLE_OBJECT_NUM queue <queuename> <object id from source database>
Post is not ignoring transactions for a specific table replicating thru a named Post queue despite configuring the parameter sp_opo_disable_object_num with queue option due to incorrect value specified for the queue name.
The parameter SP_OPO_DISABLE_OBJECT_NUM is provided so that Post can be instructed to conveniently ignore posting on a specific object which would otherwise cause issues on target Shareplex. Such issues can range from Post stuck with some error condition, slowness of Post, etc. At times one may find that the setting of the parameter is not doing its task.
In our case, the following option was used:
sp_ctrl>set param SP_OPO_DISABLE_OBJECT_NUM queue ORDQUEUEB 294300
Despite Shareplex having accepted the above command, the posting on the table was not ignored as evident from the following event log entries:
07/02/08 14:35 Warning: 15022 - Error posting ODR_INSERT1 to table "SSP_ORD"."ORD_VIDEO_END_USER". [sp_opst (for o.ssordprd-
o.ssorddr queue OrdDRQueueB)/27780]
07/02/08 14:35 Notice: Oracle error: ORA-02291: integrity constraint (SSP_ORD.ORD_VIDEO_END_USER_FK1) violated - parent key
not found. rowid:AABH2cADcAAFm8KAAB [sp_opst (for o.ssordprd-o.ssorddr queue OrdDRQueueB)/27780]
07/02/08 14:35 Error: 15033 - Failed to execute SQL on table "SSP_ORD"."ORD_VIDEO_END_USER": ORA-02291: integrity constraint
(SSP_ORD.ORD_VIDEO_END_USER_FK1) violated - parent key not found. [sp_opst (for o.ssordprd-o.ssorddr queue OrdDRQueueB)/277
80]
On further probing the setting of all Post parameters by issuing the command "list param all post", it was observed that the command did take effect within Shareplex:
.
.
SP_OPO_DISABLE_OBJECT_NUM 294300 Restart Process
Queue ORDQUEUEB
Default Value:
.
.
However, on closer examination it was observed that the queuename specified was ORDQUEUEB which was a non-existent queue whereas the table was replicating thru a queue named ORDRQUEUEB (note that the two names are different). The corrective action was to reset the old parameter and activate a new parameter as follows:
sp_ctrl>reset param SP_OPO_DISABLE_OBJECT_NUM queue ORDQUEUEB
sp_ctrl>set param SP_OPO_DISABLE_OBJECT_NUM queue ORDRQUEUEB 294300
sp_ctrl>stop post
sp_ctrl>start post
The point made here is that Shareplex will not parse the queue name specified in the above command to check if the queue name does exist and whether the table actually replicates thru that queue (both of which were found to be false in our scenario). So it is the responsibility of the user to provide the correct values for the queue name when using the queue option for SP_OPO_DISABLE_OBJECT_NUM.
