Home » Server Options » Streams & AQ » Stream is not working... Need help. (Oracle9i)
Stream is not working... Need help. [message #326533] |
Wed, 11 June 2008 22:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shrinika
Messages: 306 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 #337989 is a reply to message #326798] |
Sat, 02 August 2008 02:05 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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..
|
|
|
|
Re: Stream is not working... Need help. [message #338370 is a reply to message #338246] |
Mon, 04 August 2008 14:21 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shrinika
Messages: 306 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
|
|
|
Re: Stream is not working... Need help. [message #338463 is a reply to message #338370] |
Tue, 05 August 2008 03:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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..
|
|
|
|
|
|
Re: Stream is not working... Need help. [message #342523 is a reply to message #338463] |
Fri, 22 August 2008 11:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shrinika
Messages: 306 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..
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 20:59:16 CDT 2024
|