DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: oracle streams

Hybrid View

  1. #1
    Join Date
    Feb 2009
    Posts
    91

    oracle streams

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width