What is the use of the parameter SP_OPO_SKIP_FAIL_DDL_ON_RESTART? (4300282)

The parameter SP_OPO_SKIP_FAIL_DDL_ON_RESTART is used to define the behavior of Post when it encounters a failed DDL. By default (with the parameter set to 1), if you restart Post upon a DDL failure, the Post will skip that DDL and would be able to run normally so long as the underlying conditions responsible for Post error are manually resolved on target database.

If wishing to have Post not run even after manual restart and despite the underlying conditions corrected (that resulted in DDL error in the first place), then the parameter needs to be set to 0. The utility of this parameter is to avoid restarting of Post without first understanding what caused the error. For example, if you add a column manually on the replicating table on target and also add it on that table on source, and if your DDL replication is on, then Post will stop due to the DDL error ORA-01430 “column being added already exists in table”. This is because that DDL  to add column is a supported DDL and SharePlex simply sends it from source to target to update the target table.  Now, with the default setting of 1 for this parameter on target, the Post will simply discard that offending DDL which causes ORA-01430 when the Post is restarted after it errors out. The net effect will be, even though the DDL failed to apply, there are no issues with replication as the column already exists on target and the structure of the target table matches with that of the source table. The object cache which is SharePlex representation of the structure of replicating table is also updated on target even though there was a DDL error. When a DDL is executed on source, the object cache is first updated on source and subsequently it is also updated on target irrespective of whether that DDL succeeds on target. The only problem is, such user errors (of manually creating a supported DDL on target database) caused the DDL error in the first place and if the user simply restarts Post as a matter of habit, the Post discards the offending DDL and starts running normally. However, this masks the user error of running the DDL manually on target even though SharePlex also replicates the same DDL as the DDL is a supported one. If the parameter SP_OPO_SKIP_FAIL_DDL_ON_RESTART were set to 0, then the offending DDL will not be discarded upon restart of Post and the user will be alerted about the underlying problem which is causing the DDL to fail even after restart of Post. Once the underlying problem has been understood and/or addressed, it is ok to reset the parameter back to its default and restart Post.

To set the parameter to 0 so that the offending DDL is not discarded upon restart of Post:

sp_ctrl>set param SP_OPO_SKIP_FAIL_DDL_ON_RESTART 0

sp_ctrl>start post

To reset the parameter back to its default:

sp_ctrl>reset param SP_OPO_SKIP_FAIL_DDL_ON_RESTART

sp_ctrl>stop post

sp_ctrl>start post