oracle streams

[PaulH]

I am trying to setup oracle streams by following the instructions in the link
below:

DB1 is on a solaris 2.8 machine running oracle 10.2.0.1.0
DB2 is on an AIX 5.2 machine running oracle 10.2.0.1.0

When I get to step number 10 in the documenation below I get the following
the error:

SQL> declare
2 source_scn number;
3 begin
4 source_scn := dbms_flashback.get_system_change_number();
5 dbms_apply_adm.set_table_instantiation_scn@DB2
6 ( source_object_name => 'pbh.taj',
7 source_database_name => 'DB1',
8 instantiation_scn => source_scn);
9 end;
10 /
declare
*
ERROR at line 1:
ORA-04052: error occurred when looking up remote object
STRMADMIN.DBMS_APPLY_ADM@DB2.WORLD.COM
ORA-00604: error occurred at recursive SQL level 1
ORA-02085: database link DB2.WORLD.COM connects to AIXSNAP.WORLD.COM

Can somebody point me in the right direction and tell me what this error
means and how can I fix it. Note: where ever it says the user "scott" I
substitued the user "pbh", which is a valid user in both DB's and has
DBA privs


http://dbataj.blogspot.com/2008/01/o...tween-two.html

Set up below parameters on both databases (db1, db2)

1. Enable ARCHIVELOG MODE on both database
Reference: http://dbataj.blogspot.com/2007/09/h...elog-mode.html

2. Create Stream administrator User
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;

User created.

SQL> grant connect, resource, dba to strmadmin;

Grant succeeded.

SQL> begin dbms_streams_auth.grant_admin_privilege
2 (grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> grant select_catalog_role, select any dictionary to strmadmin;

Grant succeeded.

Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> create user strmadmin identified by strmadmin;

User created.

SQL> grant connect, resource, dba to strmadmin;

Grant succeeded.

SQL> begin dbms_streams_auth.grant_admin_privilege
2 (grantee => 'strmadmin',
3 grant_privileges => true);
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> grant select_catalog_role, select any dictionary to strmadmin;

Grant succeeded.

3. Setup INIT parameters
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> alter system set global_names=true;

System altered.

SQL> alter system set streams_pool_size = 100 m;

System altered.

Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> alter system set global_names=true;

System altered.

SQL> alter system set streams_pool_size = 100 m;

System altered.

4. Create Database Link
Target Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> create database link db2
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB2';

Database link created.

Source Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> create database link db1
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB1';

Database link created.

5. Setup Source and Destination queues
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

PL/SQL procedure successfully completed.

Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

PL/SQL procedure successfully completed.

6. Setup Schema for streams
Schema: SCOTT
Table: Taj
NOTE: Unlock scott schema because in 10g scott schema is locked by default
Source Database: DB1
SQL> conn sys@db1 as sysdba
Enter password:
Connected.
SQL> alter user scott account unlock identified by tiger;

User altered.

SQL> conn scott/tiger@db1
Connected.
SQL> create table TAJ ( no number primary key,name varchar2(20),ddate date);

Table created.

Target Database: DB2
SQL> conn sys@db2 as sysdba
Enter password:
Connected.
SQL> alter user scott account unlock identified by tiger;

User altered.

SQL> conn scott/tiger@db2
Connected.
SQL> create table TAJ ( no number primary key,name varchar2(20),ddate date);

Table created.

7. Setup Supplemental logging at the source database
Source Database: DB1
SQL> conn scott/tiger@db1
Connected.
SQL> alter table taj
2 add supplemental log data (primary key,unique) columns;

Table altered.

8. Configure capture process at the source database
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( table_name => 'scott.taj',
3 streams_type => 'capture',
4 streams_name => 'capture_stream',
5 queue_name=> 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 inclusion_rule => true);
9 end;
10 /

PL/SQL procedure successfully completed.

9. Configure the propagation process
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_streams_adm.add_table_propagation_rules
2 ( table_name => 'scott.taj',
3 streams_name => 'DB1_TO_DB2',
4 source_queue_name => 'strmadmin.streams_queue',
5 destination_queue_name => 'strmadmin.streams_queue@DB2',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /

PL/SQL procedure successfully completed.
10. Set the instantiation system change number (SCN)
Source Database: DB1
SQL> CONN STRMADMIN/STRMADMIN@DB1
Connected.
SQL> declare
2 source_scn number;
3 begin
4 source_scn := dbms_flashback.get_system_change_number();
5 dbms_apply_adm.set_table_instantiation_scn@DB2
6 ( source_object_name => 'scott.taj',
7 source_database_name => 'DB1',
8 instantiation_scn => source_scn);
9 end;
10 /

PL/SQL procedure successfully completed.

11. Configure the apply process at the destination database
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( table_name => 'scott.taj',
3 streams_type => 'apply',
4 streams_name => 'apply_stream',
5 queue_name => 'strmadmin.streams_queue',
6 include_dml => true,
7 include_ddl => true,
8 source_database => 'DB1',
9 inclusion_rule => true);
10 end;
11 /

PL/SQL procedure successfully completed.
12. Start the capture and apply processes
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_capture_adm.start_capture
2 ( capture_name => 'capture_stream');
3 end;
4 /

PL/SQL procedure successfully completed.
Target Database: DB2
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> begin dbms_apply_adm.set_parameter
2 ( apply_name => 'apply_stream',
3 parameter => 'disable_on_error',
4 value => 'n');
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_apply_adm.start_apply
3 ( apply_name => 'apply_stream');
4 end;
5 /

PL/SQL procedure successfully completed.
NOTE: Stream replication environment is ready, just needed to test it.
SQL> conn scott/tiger@db1
Connected.
SQL> --DDL operation
SQL> alter table taj add (flag char(1));

Table altered.

SQL> --DML operation
SQL> begin
2 insert into taj values (1,'first_entry',sysdate,1);
3 commit;
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> conn scott/tiger@db2
Connected.
SQL> --TEST DDL operation
SQL> desc taj
Name Null? Type
----------------------------------------- -------- ----------------------------

NO NOT NULL NUMBER
NAME VARCHAR2(20)
DDATE DATE
FLAG CHAR(1)

SQL> --TEST DML operation
SQL> select * from taj;

NO NAME DDATE F
---------- -------------------- --------- -
1 first_entry 24-JAN-08 1

[davey23uk]

did you google that error and see the action required?

ORA-02085:
database link string connects to string
Cause: a database link connected to a database with a different name. The connection is rejected.
Action: create a database link with the same name as the database it connects to, or set global_names=false.