The following errors are observed when replicating DDLs on non-replicating objects by setting the parameter SP_OCT_REPLICATE_ALL_DDL to 1 on source:
Error 2016-09-08 23:46:25.763932 20200 3044599552 s:1 Poster: que/seq = 1/ 848 seqno/offset = 6228/2998972384 Poster exit on ddl error due to ORA-01918: user 'USER_NAME' does not exist. on alter user USER_NAME identified by VALUES ‘nnnnnnnn’ ... (posting from name1, queue queue_name, to name2) [module opo]
Notice 2016-09-08 23:46:25.763436 20200 3044599552 s:1 Poster: ORA-01918: user
'USER_NAME' does not exist. (posting from name1, queue queue_name, to name2) [module osp]
The error is due to SP_OCT_REPLICATE_ALL_DDL set to 1 on source. This results in replication of "ALTER USER..." and if the target does not have that user or if the target user has different characteristics than that of the source, then the ALTER USER results in Post to error out. One way to deal with this is to ignore the DDL error or remove the DDL from the queue using qview utility. Another way is to disable the SP_OCT_REPLICATE_ALL_DDL if you do not feel the need to replicate DDL on non-replicating objects. Yet another way is to selectively disable the DDL replication for non-replicating objects.
1. To ignore the DDL error, on target set the following parameter:
sp_ctrl>set param SP_OPO_STOP_ON_DDL_ERR 0
But keep in mind that all subsequent DDL errors including ORA-01918 will be ignored from this point on. If you only wish to get around this specific occurrence and would like to have Post stop due to error when encountering a DDL error so that you can analyze the error first before trying the workaround, you can unset the above parameter by:
sp_ctrl>reset param SP_OPO_STOP_ON_DDL_ERR
2. If you do not wish to replicate DDL on non-replicating objects for future database activity, then set the following parameter on source:
sp_ctrl>set param SP_OCT_REPLICATE_ALL_DDL 0
sp_ctrl>stop capture (make sure Capture is “stopped by user” and not “stopping” when issuing “status” on source sp_ctrl and keep issuing “status” till it is “stopped by user”)
3. If you do not wish to disable DDL replication for all non-replicating objects but only want to selectively disable such DDL replication for some, then the DDL filtering feature can be used.
Starting with Shareplex 7.6, there is a new feature named DDL filtering that allows you to selectively disable the DDL which are replicated when SP_OCT_REPLICATE_ALL_DDL is set to 1.
The Admin Guide section titled “Filter DDL Replication” delves on this and provides an excellent reference.
You can filter the objects for which DDL is replicated when the SP_OCT_REPLICATE_ALL_DDL parameter is enabled. This is the DDL that is not in the replication configuration.
NOTE: DDL filtering is only allowed for objects outside the replication configuration. All DDL performed on objects inside the replication configuration must be replicated to keep the source and target metadata consistent so that DML succeeds.
DDL filtering is controlled in the SHAREPLEX_DDL_CONTROL table that is installed in the SharePlex schema.