-
Title
Post stopped with "Internal error: Poster exit on 9i ddl error due to ORA-01442" -
Description
While replicating 9i Supported DDL, the following errors are observed on target Shareplex due to Oracle error ORA-01442:
[5] 02/12/07 05:01 Notice: ORA-01442: column to be modified to NOT NULL is already NOT NULL. [sp_opst_mt(osp)/22744]
[5] 02/12/07 05:01 Internal error: Poster exit on 9i ddl error due to ORA-01442: column to be modified to NOT NULL is already NOT NULL. on ALTER TABLE OWNER.TABLE_NAME MODIFY(NAME... [sp_opst_mt (for o.SID1-o.SID2 queue queue_name)/22744]
02/12/07 05:01 Process exited sp_opst_mt (for o.SID1-o.SID2 queue queue_name) [pid = 22744] - exit(1) -
Cause
The column to be modified to NOT NULL is already NOT NULL so the DDL failed.
-
Resolution
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 has a column which is already defined as NOT NULL whereas on source it is not. The subsequent DDL to modify it to NOT NULL replicated to the target and failed since the target table already had this constraint defined. The problem can be resolved in one of the following ways:
A.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 0 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
To enable it back
sp_ctrl>reset param SP_OPO_STOP_ON_DDL_ERR
B.One can also configure the parameter SP_OPO_CONT_ON_ERR to ignore the Oracle error ORA-00942. See SOL277 for details.
C.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>set param SP_OCT_REPLICATE_ALL_DDL 0 (the parameter is live and will take place immediately).
D.Modify the target table so as to make the column nullable and restart Post.