-
Title
Poster Oracle error ORA-01917 user or role does not exist -
Description
The following errors are observed in target event_log when SP_OCT_REPLICATE_GRANT is enabled and a SELECT on a table is granted to a user but the user does not exist on target. However, the Post does not stop due to this error:
sp_ctrl (alvsupl18:9111)> show log reverse
.
.
Error 2018-07-02 17:08:34.197031 11782 221996800 s:5 Poster: 15033 - Failed to execute SQL on table "SHA91"."TABLE1": ORA-01917: user or role 'SHA91A' does not exist (posting from o.ORA11GR2, queue alvsupl14, to o.ORA11GR2) [module opo]
Notice 2018-07-02 17:08:34.196972 11782 221996800 s:5 Poster: Oracle error: ORA-01917: user or role 'SHA91A' does not exist rowid:N/A (posting from o.ORA11GR2, queue alvsupl14, to o.ORA11GR2) [module opo]
Notice 2018-07-02 17:08:34.196660 11782 221996800 s:5 Poster: ORA-01917: user or role 'SHA91A' does not exist (posting from o.ORA11GR2, queue alvsupl14, to o.ORA11GR2) [module osp]
Info 2018-07-02 17:05:23.824231 11782 960821088 Poster launched, pid = 11782 (posting from o.ORA11GR2, queue alvsupl14, to o.ORA11GR2)
-
Cause
The user on which the grant is to be bestowed on target does not exist on target.
-
Resolution
When a grant on a replicating table is replicated across and if the user to which the grant was bestowed on the source do not exist on the targe, you get this error. But the error does not stop the Post process. If desired to have the grant on the target table, the user will have to be created on target and the grant executed on target table independently.
-
Additional Information
Here are the details of test that resulted in the above error, in case it explains the issue better: On source the parameter SP_OCT_REPLICATE_GRANT is set to 1 for allowing replication of GRANT: sp_ctrl (alvsupl14:9111)> set param SP_OCT_REPLICATE_GRANT 1 sp_ctrl (alvsupl14:9111)> stop capture sp_ctrl (alvsupl14:9111)> start capture I then granted the following privilege to a user on source that does not exist on target: SQL> grant select on table1 to sha91a; Grant succeeded. The grant replicated to the target but failed to apply on the target database. The Post showed the error message Poster: Oracle error: ORA-01917: user or role 'SHA91A' does not exist sp_ctrl (alvsupl18:9111)> show log reverse . . Error 2018-07-02 17:08:34.197031 11782 221996800 s:5 Poster: 15033 - Failed to execute SQL on table "SHA91"."TABLE1": ORA-01917: user or role 'SHA91A' does not exist (posting from o.ORA11GR2, queue alvsupl14, to o.ORA11GR2) [module opo] Notice 2018-07-02 17:08:34.196972 11782 221996800 s:5 Poster: Oracle error: ORA-01917: user or role 'SHA91A' does not exist rowid:N/A (posting from o.ORA11GR2, queue alvsupl14, to o.ORA11GR2) [module opo] Notice 2018-07-02 17:08:34.196660 11782 221996800 s:5 Poster: ORA-01917: user or role 'SHA91A' does not exist (posting from o.ORA11GR2, queue alvsupl14, to o.ORA11GR2) [module osp] Info 2018-07-02 17:05:23.824231 11782 960821088 Poster launched, pid = 11782 (posting from o.ORA11GR2, queue alvsupl14, to o.ORA11GR2)