OraFAQ Forum: Streams & AQ » Stream is not working... Need help.

Home » Server Options » Streams & AQ » Stream is not working... Need help. (Oracle9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Stream is not working... Need help. [message #326533]

Wed, 11 June 2008 22:53

shrinika
Messages: 298
Registered: April 2008

Senior Member

I am just following the below link.

http://www.oracle-base.com/articles/9i/Streams9i.php

Here is the Oracle version

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

The data are not replicating... Here are the scripts.
I have two instance. dba1 & dba2. I am trying to replicate the data from scott.dept@dba1 to scott.dept@dba2.

Here are the relevant parameters in source and target database paramters

compatible           9.2.0.0.0
log_parallelism      1
global_names         TRUE
job_queue_processes  10
aq_tm_processes      1

Stream Administrator Setup

SQL> CONN sys/password@DBA1 AS SYSDBA
Connected.
SQL> 
SQL> CREATE USER strmadmin IDENTIFIED BY strmadminpw
  2  DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

User created.

SQL> 
SQL> GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin;

Grant succeeded.

SQL> 
SQL> GRANT EXECUTE ON DBMS_AQADM            TO strmadmin;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_CAPTURE_ADM      TO strmadmin;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_PROPAGATION_ADM  TO strmadmin;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_STREAMS_ADM      TO strmadmin;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_APPLY_ADM        TO strmadmin;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_FLASHBACK        TO strmadmin;

Grant succeeded.

SQL> 
SQL> BEGIN 
  2    DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  3      privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 
  4      grantee      => 'strmadmin', 
  5      grant_option => FALSE);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> BEGIN 
  2    DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  3      privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ, 
  4      grantee      => 'strmadmin', 
  5      grant_option => FALSE);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> CONNECT strmadmin/strmadminpw@DBA1
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

PL/SQL procedure successfully completed.

SQL> 
SQL> CREATE DATABASE LINK dba2 CONNECT TO strmadmin IDENTIFIED BY strmadminpw USING 'DBA2';

Database link created.
SQL>

SQL> connect sys/password@dba2 as sysdba
Connected.
SQL> CREATE USER strmadmin IDENTIFIED BY strmadminpw
  2  DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

User created.

SQL> 
SQL> GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO strmadmin;

Grant succeeded.

SQL> 
SQL> GRANT EXECUTE ON DBMS_AQADM            TO strmadmin;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_CAPTURE_ADM      TO strmadmin;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_PROPAGATION_ADM  TO strmadmin;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_STREAMS_ADM      TO strmadmin;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_APPLY_ADM        TO strmadmin;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_FLASHBACK        TO strmadmin;

Grant succeeded.

SQL> 
SQL> BEGIN 
  2    DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  3      privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 
  4      grantee      => 'strmadmin', 
  5      grant_option => FALSE);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> 
SQL> BEGIN 
  2    DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  3      privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ, 
  4      grantee      => 'strmadmin', 
  5      grant_option => FALSE);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> 
SQL> CONNECT strmadmin/strmadminpw@dba2
Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

PL/SQL procedure successfully completed.

SQL> connect sys/password@dba2 as sysdba
Connected.
SQL> GRANT ALL ON scott.dept TO strmadmin;

Grant succeeded.

SQL>

LogMinor Tablespace Setup

SQL> CONN sys/password@DBA1 AS SYSDBA
Connected.
SQL> 
SQL> CREATE TABLESPACE logmnr_ts DATAFILE 'h:/dba1/data/logmnr01.dbf' 
  2    SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Tablespace created.

SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnr_ts');

PL/SQL procedure successfully completed.
SQL>

Supplemental Logging

SQL> CONN sys/password@DBA1 AS SYSDBA
Connected.
SQL> 
SQL> ALTER TABLE scott.dept ADD SUPPLEMENTAL LOG GROUP log_group_dept_pk (deptno) ALWAYS;

Table altered.

SQL>

Configure Propagation Process

SQL> CONNECT strmadmin/strmadminpw@DBA1
Connected.
SQL> BEGIN
  2    DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
  3      table_name              => 'scott.dept', 
  4      streams_name            => 'dba1_to_dba2', 
  5      source_queue_name       => 'strmadmin.streams_queue',
  6      destination_queue_name  => 'strmadmin.streams_queue@dba2',
  7      include_dml             =>  true,
  8      include_ddl             =>  true,
  9      source_database         => 'dba1');
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL>

Configure Capture Process

SQL> CONNECT strmadmin/strmadminpw@DBA1
Connected.
SQL> BEGIN
  2    DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  3      table_name     => 'scott.dept',   
  4      streams_type   => 'capture',
  5      streams_name   => 'capture_simp',
  6      queue_name     => 'strmadmin.streams_queue',
  7      include_dml    =>  true,
  8      include_ddl    =>  true);
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL>

Configure Apply Process

SQL> CONNECT strmadmin/strmadminpw@DBA2
Connected.
SQL> BEGIN
  2    DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  3      table_name      => 'scott.dept',
  4      streams_type    => 'apply', 
  5      streams_name    => 'apply_simp',
  6      queue_name      => 'strmadmin.streams_queue',
  7      include_dml     =>  true,
  8      include_ddl     =>  true,
  9      source_database => 'dba1');
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL>

Start Apply Process

SQL> CONNECT strmadmin/strmadminpw@DBA2
Connected.
SQL> BEGIN
  2    DBMS_APPLY_ADM.SET_PARAMETER(
  3      apply_name  => 'apply_simp', 
  4      parameter   => 'disable_on_error', 
  5      value       => 'n');
  6  
  7    DBMS_APPLY_ADM.START_APPLY(
  8      apply_name  => 'apply_simp');
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL>

Start Capture Process

SQL> CONNECT strmadmin/strmadminpw@DBA1
Connected.
SQL> BEGIN
  2    DBMS_CAPTURE_ADM.START_CAPTURE(
  3      capture_name  => 'capture_simp');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL>

I inserted one record in scott.dept@dba1. It should propagate to scott.dept@dba2. But it is not propagating...

SQL> CONNECT scott/tiger@dba1
Connected.
SQL> INSERT INTO dept (deptno, dname, loc) VALUES (99, 'Test Dept', 'UK');

1 row created.

SQL> commit;

SQL> connect scott/tiger@dba2
Connected.
SQL> select * from dept;

no rows selected

SQL>

Here is the log in dba2 instance.

SQL> connect strmadmin/strmadminpw@DBA2
Connected.
SQL> set serveroutput on
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    v_anydata  SYS.ANYDATA;
  3    v_lcr      SYS.LCR$_ROW_RECORD;  
  4    v_row_list SYS.LCR$_ROW_LIST;
  5    v_result   PLS_INTEGER;
  6  BEGIN
  7    
  8    SELECT user_data
  9    INTO   v_anydata
 10    FROM   strmadmin.streams_queue_table
 11    WHERE  rownum < 2;
 12    
 13    v_result := ANYDATA.GetObject(
 14                  self  => v_anydata,
 15                  obj   => v_lcr);
 16                  
 17    DBMS_OUTPUT.PUT_LINE('Command Type         : ' || v_lcr.Get_Command_Type);
 18    DBMS_OUTPUT.PUT_LINE('Object Owner         : ' || v_lcr.Get_Object_Owner);
 19    DBMS_OUTPUT.PUT_LINE('Object Name          : ' || v_lcr.Get_Object_Name);
 20    DBMS_OUTPUT.PUT_LINE('Source Database Name : ' || v_lcr.Get_Source_Database_Name);
 21  END;
 22  /
Command Type         : INSERT
Object Owner         : SCOTT
Object Name          : DEPT
Source Database Name : DBA1.US.ORACLE.COM

PL/SQL procedure successfully completed.

[Updated on: Wed, 11 June 2008 23:06]

Report message to a moderator

Re: Stream is not working... Need help. [message #326720 is a reply to message #326533]

Thu, 12 June 2008 07:08

shrinika
Messages: 298
Registered: April 2008

Senior Member

Any help is greatly appreicated..

Report message to a moderator

Re: Stream is not working... Need help. [message #326798 is a reply to message #326720]

Thu, 12 June 2008 11:18

shrinika
Messages: 298
Registered: April 2008

Senior Member

Just update.. The DDL is replicating fine. Only DML is not propagating. I would appreciate, if any one could help me on this.

Thanks

Report message to a moderator

Re: Stream is not working... Need help. [message #337989 is a reply to message #326798]

Sat, 02 August 2008 02:05

Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India

Senior Member

Hi Govind,

I have tried the same example and everthing is working fine on my side..

Try giving "Commit" after your DML statements..

On my side, after committing the changes (DML), withing 20 to 30 seconds, changes are reflected to destination database (replica)..

Regards,
Dipali..

Report message to a moderator

Re: Stream is not working... Need help. [message #338246 is a reply to message #326533]

Mon, 04 August 2008 05:37

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Troubleshoot step by step and see where is the problem.

Report message to a moderator

Re: Stream is not working... Need help. [message #338370 is a reply to message #338246]

Mon, 04 August 2008 14:21

shrinika
Messages: 298
Registered: April 2008

Senior Member

Hello vithalani_dipali/Arju,

I ran the streams again and it looks like DML is also replicating. I think, i might have missed the commit statement. Thanks for your reply.

One question. I am new to streams. So in the above script, where are we specifying the replication interval. It looks like, it is replicating every 30 seconds. Thanks

Report message to a moderator

Re: Stream is not working... Need help. [message #338463 is a reply to message #338370]

Tue, 05 August 2008 03:23

Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India

Senior Member

Hi Govind,

Glad to hear that your problem is solved..

Quote:

One question. I am new to streams. So in the above script, where are we specifying the replication interval. It looks like, it is replicating every 30 seconds.

Actually, i am also doing r&d on replicaton for first time..
I was just about to ask this question in forum..

Can anyone please reply about the interval in stream replication?

Regards,
Dipali..

Report message to a moderator

Re: Stream is not working... Need help. [message #338486 is a reply to message #338463]

Tue, 05 August 2008 04:05

Michel Cadot
Messages: 67666
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

DBMS_AQADM.ALTER_PROPAGATION_SCHEDULE

Regards
Michel

Report message to a moderator

Re: Stream is not working... Need help. [message #338514 is a reply to message #338486]

Tue, 05 August 2008 05:03

Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India

Senior Member

Thanks Michel.. Smile

Regards..

Report message to a moderator

Re: Stream is not working... Need help. [message #339027 is a reply to message #338514]

Wed, 06 August 2008 10:35

shrinika
Messages: 298
Registered: April 2008

Senior Member

Thanks Michel.

Report message to a moderator

Re: Stream is not working... Need help. [message #342523 is a reply to message #338463]

Fri, 22 August 2008 11:26

shrinika
Messages: 298
Registered: April 2008

Senior Member

Hello,

I am using the below oracle version.

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

Streams are working fine when i use only one queue(streams_queue). But if i use two queues(IN_QUEUE/OUT_QUEUE), then i am getting the below errors in propagation.

ORA-02068: following severe error from DB2
ORA-03113: end-of-file on communication channel
ORA-02068: following severe error from DB2
ORA-03113: end-of-file on communication channel

ORA-02068: following severe error from DB1
ORA-03113: end-of-file on communication channel
ORA-02068: following severe error from DB1
ORA-03113: end-of-file on communication channel

Here are the scripts i am using for streams.

[code]
---------------------------------------------------
-- Schema setup for DB1
---------------------------------------------------

connect sys/password@db1 as sysdba
create user strmadmin identified by strmadmin
default tablespace users quota unlimited on users;

grant connect,resource,select_catalog_role to strmadmin;

grant execute on dbms_aqadm to strmadmin;
grant execute on dbms_capture_adm to strmadmin;
grant execute on dbms_propagation_adm to strmadmin;
grant execute on dbms_streams_adm to strmadmin;
grant execute on dbms_apply_adm to strmadmin;
grant execute on dbms_flashback to strmadmin;

BEGIN
   DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 
    grantee       => 'STRMADMIN', 
    grant_option  => FALSE);
   DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.CREATE_RULE_OBJ, 
    grantee       => 'STRMADMIN', 
    grant_option  => FALSE);
END;
/

grant dba to scott,strmadmin;

connect scott/tiger@db1
grant all on dept to strmadmin;
grant all on emp to strmadmin;



connect strmadmin/strmadmin@db1

begin
  dbms_streams_adm.set_up_queue(
     queue_table => 'GML_STREAMS_QUEUE_TABLE',
     storage_clause => 'TABLESPACE TOOLS',
     queue_name => 'GML_STREAMS_IN_QUEUE',
     queue_user => 'STRMADMIN' );
end;
/

begin
  dbms_streams_adm.set_up_queue(
     queue_table => 'GML_STREAMS_QUEUE_TABLE',
     storage_clause => 'TABLESPACE TOOLS',
     queue_name => 'GML_STREAMS_OUT_QUEUE',
     queue_user => 'STRMADMIN' );
end;
/

--------------------------------------------------
-- Schema setup for DB2
---------------------------------------------------

connect sys/password@db2 as sysdba

create user strmadmin identified by strmadmin
default tablespace users quota unlimited on users;

grant connect,resource,select_catalog_role to strmadmin;

grant dba to scott,strmadmin;

grant execute on dbms_aqadm to strmadmin;
grant execute on dbms_capture_adm to strmadmin;
grant execute on dbms_propagation_adm to strmadmin;
grant execute on dbms_streams_adm to strmadmin;
grant execute on dbms_apply_adm to strmadmin;
grant execute on dbms_flashback to strmadmin;

BEGIN
   DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 
    grantee       => 'STRMADMIN', 
    grant_option  =>  FALSE);
   DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
    privilege     => DBMS_RULE_ADM.CREATE_RULE_OBJ, 
    grantee       => 'STRMADMIN', 
    grant_option  => FALSE);
END;
/

connect scott/tiger@db2
grant all on dept to strmadmin;
grant all on emp to strmadmin;


connect strmadmin/strmadmin@db2

begin
  dbms_streams_adm.set_up_queue(
     queue_table => 'GML_STREAMS_QUEUE_TABLE',
     storage_clause => 'TABLESPACE TOOLS',
     queue_name => 'GML_STREAMS_IN_QUEUE',
     queue_user => 'STRMADMIN' );
end;
/

begin
  dbms_streams_adm.set_up_queue(
     queue_table => 'GML_STREAMS_QUEUE_TABLE',
     storage_clause => 'TABLESPACE TOOLS',
     queue_name => 'GML_STREAMS_OUT_QUEUE',
     queue_user => 'STRMADMIN' );
end;
/

-----------------------------------
-- create DB link for DB1, DB2
-----------------------------------

connect strmadmin/strmadmin@db1

CREATE DATABASE LINK DB2
CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING 
'  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = NYC-LAPTOP05)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db2)
    )
  )'
/

CONNECT strmadmin/strmadmin@DB2

CREATE DATABASE LINK DB1
CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING 
'  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = NYC-LAPTOP05)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db1)
    )
  )'
/

--------------------------------------------------
-- LogMinor Tablespace setup db1, db2
---------------------------------------------------

connect sys/password@db1 as sysdba

CREATE TABLESPACE LOGMNRTS DATAFILE 'C:/ORACLE/ORADATA/DB1/logmnrts.dbf' SIZE 25M 
AUTOEXTEND ON MAXSIZE UNLIMITED
/

execute dbms_logmnr_d.set_tablespace('LOGMNRTS');

connect sys/password@db2 as sysdba

CREATE TABLESPACE LOGMNRTS DATAFILE 'C:/ORACLE/ORADATA/DB2/logmnrts.dbf' SIZE 25M 
AUTOEXTEND ON MAXSIZE UNLIMITED
/

execute dbms_logmnr_d.set_tablespace('LOGMNRTS');

--------------------------------------------------
-- Supplemental Logging db1, db2
---------------------------------------------------

connect sys/password@db2 as sysdba

ALTER TABLE scott.EMP DROP SUPPLEMENTAL LOG GROUP EMP;
ALTER TABLE scott.DEPT DROP SUPPLEMENTAL LOG GROUP DEPT;

ALTER TABLE scott.DEPT ADD SUPPLEMENTAL LOG GROUP DEPT(DEPTNO)  ALWAYS;
ALTER TABLE scott.EMP ADD SUPPLEMENTAL LOG GROUP EMP(EMPNO)  ALWAYS;

connect sys/password@db1 as sysdba

ALTER TABLE scott.EMP DROP SUPPLEMENTAL LOG GROUP EMP;
ALTER TABLE scott.DEPT DROP SUPPLEMENTAL LOG GROUP DEPT;

ALTER TABLE scott.DEPT ADD SUPPLEMENTAL LOG GROUP DEPT(DEPTNO)  ALWAYS;
ALTER TABLE scott.EMP ADD SUPPLEMENTAL LOG GROUP emp(EMPNO)  ALWAYS;

Configure apply/propagation/capture process

--------------------------------------------------
-- Configure propagation for db1, db2
--------------------------------------------------

connect strmadmin/strmadmin@db1

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
   table_name => 'SCOTT.EMP', 
   streams_name => 'GML_PROPAGATE_GML2', 
   source_queue_name => 'STRMADMIN.GML_STREAMS_OUT_QUEUE', 
   destination_queue_name => 'STRMADMIN.GML_STREAMS_IN_QUEUE@DB2', 
   include_dml => true, 
   include_ddl => true, 
   source_database => 'DB1');
END;
/

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
   table_name => 'SCOTT.DEPT', 
   streams_name => 'GML_PROPAGATE_GML2', 
   source_queue_name => 'STRMADMIN.GML_STREAMS_OUT_QUEUE', 
   destination_queue_name => 'STRMADMIN.GML_STREAMS_IN_QUEUE@DB2', 
   include_dml => true, 
   include_ddl => true, 
   source_database => 'DB1');
END;
/

connect strmadmin/strmadmin@db2

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
   table_name => 'SCOTT.EMP', 
   streams_name => 'GML_PROPAGATE_GML1', 
   source_queue_name => 'STRMADMIN.GML_STREAMS_OUT_QUEUE', 
   destination_queue_name => 'STRMADMIN.GML_STREAMS_IN_QUEUE@DB1', 
   include_dml => true, 
   include_ddl => true, 
   source_database => 'DB2');
END;
/

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
   table_name => 'SCOTT.DEPT', 
   streams_name => 'GML_PROPAGATE_GML1', 
   source_queue_name => 'STRMADMIN.GML_STREAMS_OUT_QUEUE', 
   destination_queue_name => 'STRMADMIN.GML_STREAMS_IN_QUEUE@DB1', 
   include_dml => true, 
   include_ddl => true, 
   source_database => 'DB2');
END;
/

--------------------------------------------------
-- Configure capture for db1, db2
--------------------------------------------------

CONNECT STRMADMIN/STRMADMIN@DB1

BEGIN
 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name => 'SCOTT.EMP', 
  streams_type => 'CAPTURE', 
  streams_name => 'GML_CAPTURE', 
  queue_name => 'STRMADMIN.GML_STREAMS_OUT_QUEUE', 
  include_dml => true, 
  include_ddl => true, 
  source_database => 'DB1');
END;
/

BEGIN
 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name => 'SCOTT.DEPT', 
  streams_type => 'CAPTURE', 
  streams_name => 'GML_CAPTURE', 
  queue_name => 'STRMADMIN.GML_STREAMS_OUT_QUEUE', 
  include_dml => true, 
  include_ddl => true, 
  source_database => 'DB1');
END;
/

CONNECT STRMADMIN/STRMADMIN@DB2

BEGIN
 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name => 'SCOTT.EMP', 
  streams_type => 'CAPTURE', 
  streams_name => 'GML_CAPTURE', 
  queue_name => 'STRMADMIN.GML_STREAMS_OUT_QUEUE', 
  include_dml => true, 
  include_ddl => true, 
  source_database => 'DB2');
END;
/

BEGIN
 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name => 'SCOTT.DEPT', 
  streams_type => 'CAPTURE', 
  streams_name => 'GML_CAPTURE', 
  queue_name => 'STRMADMIN.GML_STREAMS_OUT_QUEUE', 
  include_dml => true, 
  include_ddl => true, 
  source_database => 'DB2');
END;
/

--------------------------------------------------
-- Configure SCN for db1, db2
--------------------------------------------------

connect strmadmin/strmadmin@db1

declare
v_scn number;
begin
v_scn := dbms_flashback.get_system_change_number();
dbms_apply_adm.set_table_instantiation_scn@db2(
source_object_name => 'scott.dept',
source_database_name => 'DB1',
instantiation_scn => v_scn);
dbms_apply_adm.set_table_instantiation_scn@db2(
source_object_name => 'scott.emp',
source_database_name => 'DB1',
instantiation_scn => v_scn);
end;
/

connect strmadmin/strmadmin@db2

declare
v_scn number;
begin
v_scn := dbms_flashback.get_system_change_number();
dbms_apply_adm.set_table_instantiation_scn@db1(
source_object_name => 'scott.dept',
source_database_name => 'DB2',
instantiation_scn => v_scn);
dbms_apply_adm.set_table_instantiation_scn@db1(
source_object_name => 'scott.emp',
source_database_name => 'DB2',
instantiation_scn => v_scn);

end;
/

--------------------------------------------------
-- Configure APPLY for db1, db2
--------------------------------------------------

connect strmadmin/strmadmin@db2
BEGIN
 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name => 'SCOTT.EMP', 
  streams_type => 'APPLY', 
  streams_name => 'GML_APPLY_GML2', 
  queue_name => 'STRMADMIN.GML_STREAMS_OUT_QUEUE', 
  include_dml => true, 
  include_ddl => true, 
  source_database => 'DB1');
END;
/

BEGIN
 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name => 'SCOTT.DEPT', 
  streams_type => 'APPLY', 
  streams_name => 'GML_APPLY_GML2', 
  queue_name => 'STRMADMIN.GML_STREAMS_OUT_QUEUE', 
  include_dml => true, 
  include_ddl => true, 
  source_database => 'DB1');
END;
/

connect strmadmin/strmadmin@db1
BEGIN
 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name => 'SCOTT.EMP', 
  streams_type => 'APPLY', 
  streams_name => 'GML_APPLY_GML1', 
  queue_name => 'STRMADMIN.GML_STREAMS_OUT_QUEUE', 
  include_dml => true, 
  include_ddl => true, 
  source_database => 'DB2');
END;
/


BEGIN
 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name => 'SCOTT.DEPT', 
  streams_type => 'APPLY', 
  streams_name => 'GML_APPLY_GML1', 
  queue_name => 'STRMADMIN.GML_STREAMS_OUT_QUEUE', 
  include_dml => true, 
  include_ddl => true, 
  source_database => 'DB2');
END;
/

start the apply process

--------------------------------------------
-- Start apply process for db1, db2
--------------------------------------------

CONNECT STRMADMIN/STRMADMIN@DB2

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER( 
      apply_name  => 'GML_APPLY_GML2', 
      parameter   => 'DISABLE_ON_ERROR', 
      value       => 'N' );
END;
/

declare
   v_started number;
begin
   select decode(status,'ENABLED',1,0) into v_started
     from dba_apply
    where apply_name = 'GML_APPLY_GML2';

   if ( v_started = 0 ) then
      dbms_apply_adm.start_apply( apply_name => 'GML_APPLY_GML2' );
   end if;
end;
/

CONNECT STRMADMIN/STRMADMIN@db1

BEGIN
  DBMS_APPLY_ADM.SET_PARAMETER( 
      apply_name  => 'GML_APPLY_GML1', 
      parameter   => 'DISABLE_ON_ERROR', 
      value       => 'N' );
END;
/

declare
   v_started number;
begin
   select decode(status,'ENABLED',1,0) into v_started
     from dba_apply
    where apply_name = 'GML_APPLY_GML1';

   if ( v_started = 0 ) then
      dbms_apply_adm.start_apply( apply_name => 'GML_APPLY_GML1' );
   end if;
end;
/

--------------------------------------------
-- Start capture process for db1, db2
--------------------------------------------

connect strmadmin/strmadmin@db1

begin
dbms_capture_adm.start_capture(
capture_name => 'gml_capture');
end;
/

connect strmadmin/strmadmin@db2

begin
dbms_capture_adm.start_capture(
capture_name => 'gml_capture');
end;
/

Any help appreicated..

Dipali Vithalani wrote on Tue, 05 August 2008 04:23

Hi Govind,

Glad to hear that your problem is solved..

Quote:

One question. I am new to streams. So in the above script, where are we specifying the replication interval. It looks like, it is replicating every 30 seconds.

Actually, i am also doing r&d on replicaton for first time..
I was just about to ask this question in forum..

Can anyone please reply about the interval in stream replication?

Regards,
Dipali..

Report message to a moderator