Hello

I have set up Streams between two databases on the same box. The data is being streamed to different schemas and I have used a transformation. The sysntax is below:

--DESTINATION (MINEDEV)

create tablespace tbs_streams
datafile '/orasis/MINEDEV/streams01.dbf'
size 1000m
logging
extent management local
segment space management auto;

-----------------------------------------------------------------------------------------------------------------------------
--create user

create user STRADMIN identified by sys
default tablespace tbs_streams
temporary tablespace temp;


-----------------------------------------------------------------------------------------------------------------------------

--grant privileges

grant DBA, IMP_FULL_DATABASE, EXP_FULL_DATABASE to STRADMIN;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'stradmin',
grant_privileges => true);
END;
/
commit;

-----------------------------------------------------------------------------------------------------------------------------
--create database link (run as stradmin)

create database link MCSDEV01 connect to stradmin identified by sys using 'MCSDEV01';


--------------------------------------------------------------------------------------------------------------------

--create apply queue

exec dbms_streams_adm.set_up_queue(queue_name =>'APPLY_CENREPLIRPT');


-----------------------------------------------------------------------------------------------------------------
++++++++++++++

---SOURCE (MCSDEV01)

--create tablespace

create tablespace tbs_streams
datafile '/oradata/MCSDEV01/streams01.dbf'
size 1000m
logging
extent management local
segment space management auto;


----------------------------------------------------------------------------------------------------------------------------

--create user

create user STRADMIN identified by sys
default tablespace tbs_streams
temporary tablespace temp;


-----------------------------------------------------------------------------------------------------------------------------

--grant privileges

grant DBA, IMP_FULL_DATABASE, EXP_FULL_DATABASE to STRADMIN;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'stradmin',
grant_privileges => true);
END;
/
commit;

-----------------------------------------------------------------------------------------------------------------------------
--create database link (run as stradmin)


create database link MINEDEV connect to stradmin identified by sys using 'MINEDEV';

-----------------------------------------------------------------------------------------------------------------------------

--create capture queue


exec dbms_streams_adm.set_up_queue(queue_name => 'CAPTURE_CENREPLIRPT');

-----------------------------------------------------------------------------------------------------------------------------

--add supplemental logging on destination

alter table dev_p2.MCSRPTORDNOTSENT
add supplemental log data (all) columns;


alter table dev_p2.MCSRPTSTOCKVAL
add supplemental log data (all) columns;

-----------------------------------------------------------------------------------------------------------------------------

----

--add table rules for capture (MCSRPTORDNOTSENT TABLE)

begin
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'DEV_P2.MCSRPTORDNOTSENT',
streams_type => 'capture',
streams_name => 'CAPTURE_CENREPLIRPT',
queue_name => 'stradmin.CAPTURE_CENREPLIRPT',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
inclusion_rule => true);
END;
/


---------------------

--add table rules for capture (MCSRPTSTOCKVAL TABLE)

--get insert LCR's only

begin
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'DEV_P2.MCSRPTSTOCKVAL',
streams_type => 'capture',
streams_name => 'CAPTURE_CENREPLIRPT',
queue_name => 'stradmin.CAPTURE_CENREPLIRPT',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
and_condition =>':lcr.get_command_type() = ''INSERT''',
inclusion_rule => true);
END;
/

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

--do this at source



--add table rules for propagation (MCSRPTORDNOTSENT TABLE)

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'DEV_P2.MCSRPTORDNOTSENT',
streams_name => 'PROPAGATION_CENREPLIRPT',
source_queue_name => 'stradmin.CAPTURE_CENREPLIRPT',
destination_queue_name => 'stradmin.APPLY_CENREPLIRPT@MINEDEV',
include_dml => true,
include_ddl => true,
source_database => 'MCSDEV01');
END;
/

---------------------------------------

--add table rules for propagation (MCSRPTSTOCKVAL TABLE)

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'DEV_P2.MCSRPTSTOCKVAL',
streams_name => 'PROPAGATION_CENREPLIRPT',
source_queue_name => 'stradmin.CAPTURE_CENREPLIRPT',
destination_queue_name => 'stradmin.APPLY_CENREPLIRPT@MINEDEV',
include_dml => true,
include_ddl => true,
source_database => 'MCSDEV01');
END;
/


--enable propagation to change table owner from dev_p2 to cen_repli (dml only)

begin
dbms_streams_adm.rename_schema (
rule_name=>'MCSRPTSTOCKVAL89',
from_schema_name=>'DEV_P2',
to_schema_name=>'CEN_REPLI');
end;
/



begin
dbms_streams_adm.rename_schema (
rule_name=>'MCSRPTORDNOTSENT86',
from_schema_name=>'DEV_P2',
to_schema_name=>'CEN_REPLI');
end;
/


-----------------------------------------------------------------------------------------------------------------------------

--enable grants to the tables to the stradmin user (run on destination)

connect cen_repli/cen_repli

grant all on MCSRPTSTOCKVAL to stradmin;

grant all on MCSRPTORDNOTSENT to stradmin;

-----------------------------------------------------------------------------------------------------------------------------

-----

--add table rules for apply (MCSRPTORDNOTSENT TABLE)


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'CEN_REPLI.MCSRPTORDNOTSENT',
streams_type => 'apply',
streams_name => 'APPLY_CENREPLIRPT',
queue_name => 'stradmin.APPLY_CENREPLIRPT',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'MCSDEV01');
END;
/

-------


--add table rules for apply (MCSRPTSTOCKVAL TABLE)


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'CEN_REPLI.MCSRPTSTOCKVAL',
streams_type => 'apply',
streams_name => 'APPLY_CENREPLIRPT',
queue_name => 'stradmin.APPLY_CENREPLIRPT',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'MCSDEV01',
and_condition =>':lcr.get_command_type() = ''INSERT''');
END;
/


-------------------------------------------------------------------------------------------------------------------

--prepare the tables for instantiation (AT SOURCE)

exec dbms_capture_adm.prepare_table_instantiation('DEV_P2.MCSRPTSTOCKVAL');

exec dbms_capture_adm.prepare_table_instantiation('DEV_P2.MCSRPTORDNOTSENT');


--get scn (AT SOURCE)

select dbms_flashback.get_system_change_number from dual;


-----------------------------------------------------------------------------------------------------------------------------

--apply the scn at destination

begin
DBMS_APPLY_ADM.SET_table_INSTANTIATION_SCN(
source_object_name => 'DEV_P2.MCSRPTSTOCKVAL',
source_database_name => 'MCSDEV01',
instantiation_scn => 6433730);
END;
/


begin
DBMS_APPLY_ADM.SET_table_INSTANTIATION_SCN(
source_object_name => 'DEV_P2.MCSRPTORDNOTSENT',
source_database_name => 'MCSDEV01',
instantiation_scn => 6433730);
END;
/

------------------------------------------------------------------------------------------------------------------------------------------

I have started apply, capture and propagation through OEM and have inserted some data into one of the destination tables but the data is not streaming across. No error messages are listed either!!! Could someone please help?

Thanks

"The sysntax is below:"


Gee, any chance of "sysntax" errors?


"Could someone please help?"

Have you checked your steps by verifying with dictionary queries?

It's impossible to review all of your code, you need to do troubleshooting.

It's not trivial, about 40 pages of checklists.

There is a chapter on Streams debugging in the "Oracle Streams" book, try that first:

http://www.rampant-books.com/book_2004_2_streams.htm

I was not looking for someone to check syntax errors as I have done this myself. The link you have given me is for a book as well, which is not much help to me. I am quite new to streams and thought that maybe something could stand out. Thanks for your help

Hi,

>> I am quite new to streams and thought that maybe something could stand out.

Nobody is going to hand-review all of this code!

I would troubleshoot it, as per the Oracle University Streams class. Have you been trained in Streams?

Here are the main Streams views that you can use to verify that your steps work:

http://www.dba-oracle.com/t_streams_monito...reams_views.htm

To see if your rules took you can query dba_streams_rules, dba_streams_schema_rules and dba_streams_table_rules. These views displaysinformation about the global rules.

This view only contains information about rules created using the add_global_propagation or add_global_rules procedures in the dbms_streams_adm package. It does not contain information about rules created using the dbms_rule_adm package.

dba_streams_message_consumer - This view displays information about the Streams messaging clients in the database.

dba_streams_message_rules - This view displays information about the Streams messaging rules in the database.

dba_streams_rules - This view displays information about the rules used by all the Streams processes in the database. This has more columns and comprehensive details than the dba_rules view.