Home » Server Options » Streams & AQ » Error while executing Apply process in Oracle Streams
Error while executing Apply process in Oracle Streams [message #75663] Tue, 13 January 2004 03:11 Go to next message
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 Go to previous messageGo to next message
Frank Naude
Messages: 4579
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Error while executing Apply process in Oracle Streams [message #75695 is a reply to message #75694] Tue, 30 March 2004 22:07 Go to previous message
Blair
Messages: 3
Registered: December 2001
Junior Member
Sorry ...
I was not right ...
With grant create any table - it's work ...

--
wbr
Blair
Previous Topic: Streams Slow, high CPU usage
Next Topic: Heterogenous replication using Oracle 9i streams
Goto Forum:
  


Current Time: Fri Mar 29 06:22:40 CDT 2024