While replicating 9i Supported DDL, the following errors are observed on target Shareplex due to Oracle error ORA-01920:
sp_ctrl> show log reverse
03/24/06 10:10 Notice: ORA-01920: user name 'USERNAME' conflicts with another user or role name. [sp_opst(osp)/6609]
03/24/06 10:10 Internal error: Poster exit on 9i ddl error due to ORA-01920: user name 'USERNAME' conflicts with another user or role name. on CREATE USER USERNAME IDENTIFIED BY VALUES 'AAAAAAAAAAA' [sp_opst (for o.SID1-o.SID2 queue queue_name)/6609]
03/24/06 10:10 Process exited sp_opst (for o.SID1-o.SID2 queue queue_name) [pid = 6609] - exit(1)
The username or role which the Post is trying to create on the target already exists.
The error is typical when replicating 9i Supported DDL (by configuring the parameter SP_OCT_REPLICATE_ALL_DDL to 1 on source). The probable reason is that the user/role was created successfully on source but on target it fails as the username/role already exists. It may have been created directly on the target without the help of 9i Supported DDL feature of Shareplex so when the user/role with the same name is now created on source and the DDL is replicated to the target for creating the same, it fails due to the conflict. See Solution SOL52606 for details on factors that can cause the failure of DDL on target when 9i Supported DDL is enabled. The workarounds are:
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 paremeter 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
Configure the parameter SP_OPO_CONT_ON_ERR to ignore the Oracle error ORA-01920. See SOL277 for details.
Drop the existing user/role on the target and start Post so that Shareplex will create the appropriate user/role. The advantage with this approach is that it will use the appropriate definition in terms of password, default tablespaces, etc in creating the entity on the target.
If it was not an intent to replicate 9i Supported DDL (typically the DDL on non-replicating tables), then disable 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).
See SOL36703 for details on 9i Supported DDL, and SOL53602 for details on pros and cons of using this feature.
The following are details on the Oracle error encountered:
Description user name `string` conflicts with another user or role name
Cause There is already a user or role with that name.
Action Specify a different user name.