I have an Oracle logical standby database being managed via data guard. Just this morning the redo apply process began failing with an ORA-01919 error, indicating one of our application roles did not exist. However, I can see the role on both primary and standby databases. We also have a physical standby that has long since applied the redo where this is happening on the logical, without issue.
I have opened an SR with Oracle. I was wondering if anyone out there has seen this before.
I guess I should mention: Oracle 10.2.0.4, Win2003 Server SP2.
UPDATE: So far, Oracle Support has not provided an answer. I thought I'd post here what I have learned so far.
It appears that a grant of DBA on the primary host to a role works fine for users granted the role. It does not work on the logical standby. IOW:
create role TEST;
grant dba to TEST;
grant TEST to auser;
connect auser
set role TEST;
grant <existing role> to <existing user>;
This works on the primary instance but fails on the logical. A workaround appears to be to grant each role on the primary to the role TEST with admin option in the logical:
grant <existing role> to TEST with admin option; <== do this on the logical standby
Then the command works on the logical standby.
it depends. With a logical standby, any actions performed by SYS on the primary will NOT be replicated to a the logical standby. So if you created that role while logged in as SYS (in the primary), the role will not exist in the logical standby, hence subsequent commands by non-SYS users (on the primary) referencing that role will fail.