| Error while executing Apply process in Oracle Streams [message #75663] |
Tue, 13 January 2004 03:11  |
Vidyanand More
Messages: 35
Registered: January 2003
|
Member |
|
|
Hi All,
Environment Details:
Oracle Database:9.2.0.4 on windows NT.
I managed to install & configure Oracle Streams thru OEM.
All looks to fine Capture,Propagation, Topology & Database Link.
However when i try to Apply job in apply process following error message is displayed.
Begin
dbms_apply_adm.execute_error
( local_transaction_id => '7.45.57',
execute_as_user => false);
end;
stack trace:
Ora: -01031 Insufficient Privileges
Ora: -06512 At "sys.dbms_apply_error", line 185
Ora: -06512 At "sys.dbms_apply_error", line 252
Ora: -06512 At "sys.dbms_apply_error", line 281
Ora: -06512 At "sys.dbms_apply_error", line 414
ora: -06512 at line 2
Source Database Sid : acc
Source Stream Administrator : stradmin (DBA Role)
Source schema : stream
Source table : dept
Target Database Sid: fin
Target Stream Administrator : stradmin (DBA Role)
Target schema : stream
Target table : dept
I am not sure why this error is displayed?
Can someone please help me?
Thanks in Advance.
Regards,
Vidyanand
|
|
|
|
| Re: Error while executing Apply process in Oracle Streams [message #75666 is a reply to message #75663] |
Tue, 13 January 2004 04:59  |
Frank Naude
Messages: 4581
Registered: April 1998
|
Senior Member |
|
|
Hi,
You need to grant your Stream Administrator user privileges to INSERT, UPDATE and DELETE data on your DEPT table. Here is a quick and (very) dirty method:
connect sys as sysdba
-- Nasty privileges, but required for Streams DML replication...
GRANT INSERT ANY TABLE to strmadmin;
GRANT UPDATE ANY TABLE to strmadmin;
GRANT DELETE ANY TABLE to strmadmin;
-- Nasty privileges, but required for Streams DDL replication...
GRANT CREATE ANY TABLE to strmadmin;
GRANT DROP ANY TABLE to strmadmin;
GRANT CREATE ANY INDEX to strmadmin;
GRANT DROP ANY INDEX to strmadmin;
GRANT CREATE ANY VIEW to strmadmin;
GRANT DROP ANY VIEW to strmadmin;
GRANT CREATE ANY PROCEDURE to strmadmin;
GRANT DROP ANY PROCEDURE to strmadmin;
GRANT ALTER ANY TABLE to strmadmin;
GRANT ALTER ANY PROCEDURE to strmadmin;
Best regards.
Frank
|
|
|
|
| Re: Error while executing Apply process in Oracle Streams [message #75669 is a reply to message #75666] |
Wed, 14 January 2004 01:27 |
Vidyanand More
Messages: 35
Registered: January 2003
|
Member |
|
|
Hi Frank,
Yes, Oracle Stream worked after executing statement to grant privileges to the stream administrator.
Thanks a lot for your help.
However I need to find out or mark rows for Insert / Update / Delete from source table e.g. dept. Does this can be acheived by defining DML Type Rules for Insert / Update & Delete.
Right now i am planing a work around which is as follows.
Alter target table dept to add new column as tran_date by default sysdate. When a new row is inserted into target table it will get populated as sysdate.
For update & delete write a row level trigger to insert rows into another table lets us say dept_change
structure as follows
Deptno Primary Key
Upd_Flag (U - Update & D - Delete).
It will be really nice if i can mange the above from Oracle Stream Rule.
Thanks in Advance.
Regards,
Vidyanand
|
|
|
|
| Re: Error while executing Apply process in Oracle Streams [message #75694 is a reply to message #75666] |
Tue, 30 March 2004 01:25 |
Blair
Messages: 3
Registered: December 2001
|
Junior Member |
|
|
[| Quote: |
]
-- Nasty privileges, but required for Streams DML replication...GRANT CREATE ANY TABLE to strmadmin;GRANT DROP ANY TABLE to strmadmin;GRANT CREATE ANY INDEX to strmadmin;GRANT DROP ANY INDEX to strmadmin;GRANT CREATE ANY VIEW to strmadmin;GRANT DROP ANY VIEW to strmadmin;GRANT CREATE ANY PROCEDURE to strmadmin;GRANT DROP ANY PROCEDURE to strmadmin;GRANT ALTER ANY TABLE to strmadmin;GRANT ALTER ANY PROCEDURE to strmadmin;
[
|
]
is not DML ... is DDL ...
I have the same problem ...
I make DDL (create table) changes on source instance, but on side destination instance have "ORA-01031: insufficient privileges". If run execute_error as sys - all ok ... if run execute_error as strmadmin - have "ORA-01031: insufficient privileges".
GRANT CREATE ANY TABLE to strmadmin - not help in this situation.
All DML (update, insert, delete) - execute normally...
Problem only "create table".
Apply user - 'cat':
BEGIN
DBMS_APPLY_ADM.alter_apply(
apply_name => 'STRMADMIN_APPLY',
apply_user => 'cat');
END;
/
What rules are needed to user 'strmadmin' or 'cat' for don't get "ORA-01031: insufficient privileges"?
Sorry my english)
--
wbr
Blair
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
