Oracle Database Discussions

Hi:

I am trying to setup a bi-directional streams environment. The setup currently is working uni-directional and for the reverse stream I am hitting the db link issue. I have searched several forums and found solution only if the select or insert through link does not work. The strange thing in my environment is that I am able to select, insert update through the link, but from streams it is failing with the ORA-02085: database link error.

Here are the details: I setup a link between DB1 and DB2 by name DB2link. And from DB1 I am able to do:

select * from dual@DB2link;

D
-
X

Now when I try to make changes through DML on DB1, I get the following error:

SELECT p.DESTINATION_DBLINK,DECODE(s.SCHEDULE_DISABLED,'Y', 'Disabled','N', 'Enabled') SCHEDULE_DISABLED, s.PROCESS_NAME, s.FAILURES, s.LAST_ERROR_TIME, s.LAST_ERROR_MSG FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p WHERE p.PROPAGATION_NAME = 'DB1_TO_DB2_PROP' AND p.DESTINATION_DBLINK = s.DESTINATION AND s.SCHEMA = p.SOURCE_QUEUE_OWNER AND s.QNAME = p.SOURCE_QUEUE_NAME;

DESTINATION_DBLINK
--------------------------------------------------------------------------------

SCHEDULE PROCESS_ FAILURES LAST_ERR
-------- -------- ---------- --------
LAST_ERROR_MSG
--------------------------------------------------------------------------------

DB1
Enabled 5 11:19:40
ORA-00000: normal, successful completion
ORA-02085: database link DB2link connects to DB1

DB1

DESTINATION_DBLINK
--------------------------------------------------------------------------------

SCHEDULE PROCESS_ FAILURES LAST_ERR
-------- -------- ---------- --------
LAST_ERROR_MSG
--------------------------------------------------------------------------------

Enabled 5 11:19:40
ORA-00000: normal, successful completion
ORA-02085: database link DB2link connects to DB1

The global_name in both databases is set to true and I have already set the global_names system parameter to [DBName][DOMAINName]

I am able to stream from DB2 to DB1 successfully but the reverse is failing.

Please let me know if you know of any solutions where the link works manually from select, insert etc but fails from streams.

Thanks
Gopi