Post error "Poster: Poster exit on 9i ddl error due to ORA-02443: Cannot drop constraint - nonexistent constraint. on ALTER TABLE"
While replicating 9i Supported DDL, the following errors are observed on target Shareplex due to Oracle error ORA-02443:
:
Info 2010-04-19 21:56:09.393323 33567202 1 Poster exited with code=1, pid = 8855672 (posting from NAME1, queue QUEUE_NAME, to NAME2)
Error 2010-04-19 21:56:09.122195 8855672 5912 s:2 Poster: Poster exit on 9i ddl error due to ORA-02443: Cannot drop constraint - nonexistent constraint. on ALTER TABLE "OWNER"."TABLE_NAME" . DROP CONSTRAINT constraint_name... (posting from NAME1, queue QUEUE_NAME, to NAME2) [module opo]
Notice 2010-04-19 21:56:09.121919 8855672 5912 s:2 Poster: ORA-02443: Cannot drop constraint - nonexistent constraint. (posting from NAME1, queue QUEUE_NAME, to NAME2) [module osp]
Ã
The constraint to be dropped does not exist in target database
Resolution :
Resolution 1 : On target ignore the DDL by configuring the parameter SP_OPO_STOP_ON_DDL_ERR to 0 for the time being and restart Post. From this point on all DDL errors will be ignored by Post. Once the Post is past the problematic DDL, the parameter can be reset to the default of 1 by the sp_ctrl command ââ‚reset param <parameter name>;;ââ‚. Keeping it enabled indefinitely is not a good idea. See SOL21396 for details. In brief:
sp_ctrl>set param SP_OPO_STOP_ON_DDL_ERR 0
Once the problem message has been ignored, it is worth resetting the parameter back to its default. To reset it back:
sp_ctrl>reset param SP_OPO_STOP_ON_DDL_ERR
Resolution 2: Modify the target table so as to recreate the constraint and restart Post.
The error is typical when replicating 9i Supported DDL (by configuring the parameter SP_OCT_REPLICATE_ALL_DDL to 1 on source). The error can happen if the tables are independently created on source and target and the target table does not have the said constraint whereas the source table does. The subsequent DDL issued on source to drop the constraint replicated to the target and failed since the target table did not had this constraint defined. The error can also occur if the constraint originally existed on target table but has since been dropped and now the Post is not able to find it.
To avoid the problem in future, the following should be explored. If it was not an intent to replicate 9i Supported DDL (typically the DDL on non-replicating tables), then it may be worth disabling the feature by setting the parameter SP_OCT_REPLICATE_ALL_DDL to 0 on source. To do this:
sp_ctrl>reset param SP_OCT_REPLICATE_ALL_DDL (the parameter is live and will take place immediately).
Ã
