Hi Ron,
I'm not a streams expert, but I once tried the setup as described in the
article you mention. It was 9ir2 db, not 10g as in the article.
In your scenario, I'm missing the part where you set the instantiation
number for the table, as described in step 11 of the article.
Other suggestions:
- DBA_APPLY_ERROR lists valuable information
- since you set the disable_on_error parameter for the apply process, it
should stop and leave a tracefile in bdump when it encounters an error,
the tracefile might be interesting
- I solved a permission problem in my configuration by enabling
sql_trace for the strmadmin sessions through a logon-trigger
HTH, Koen Ron Rogers wrote:
List, After printing and getting confused with the "Oracle Streams Replication Admin Guide", I have been following the article "Making Data Flow" by Sanjay Mishra published in the Oracle Magazine Nov/Dec 2004.
SOURCE database REDHATDB.GLC tnsnames REDHATDB-TCP DESTINATION database LINUXDB.GLC tnsnames TESTBOX-TCP Step I have taken>
SOURCE and DESTINATION databases FLASHBACK ON GLOBAL_NAMES = TRUE STREAMS_POOL_SIZE = 209715200
Created a tablespace streams_tbs size 200 M
Created a user strmadmin identified by strmadmin default tablespace streams_tbs temporary tablespace temp
quota unlimited.
granted connect, resource, dba to strmadmin.
As SYS on SOURCE and DESTINATION database.
begin dbms_streams_auth.grant_admin_privilege( grantee => 'strmadmin', grant_privileges => true); end; /
grant select_catalog_role to strmadmin; grant select any dictionary to strmadmin;
As STRMADMIN on SOURCE database created the database link.
connect strmadmin/strmadmin@redhatdb-tcp
create database link linuxbox.glc connect to strmadmin idenfified by strmadmin using 'testbox-tcp';
As STRMADMIN on DESTINATION database created the database link.
connect strmadmin/strmadmin@testbox-tcp
create database link redhatdb.glc connect to strmadmin idenfified by strmadmin using 'redhatdb-tcp';
as STRMADMIN on the SOURCE and DESTINATION database. exec dbms_streams_adm.set_up_queue();
on the DESTINATION database created the table ARCHTEST that I want to replicate using streams.
grante all on ARCHTEST to strmadmin;
On the SOURCE database as the table owner.
alter table ARCHTEST add supplemental lof data (all) columns;
At the SOURCE database export the table ARCHTEST and import into the DESTINATION database.
As STRMADMIN on SOURCE database.
begin dbms_streams_adm.add_table_rules ( table_name =>'ARCHTEST', streams_type => 'capture', streams_name => 'capture_stream', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, inclusion_rule => true); end;
begin dbms_streams_adm.add_table_propagation_rules ( table_name =>'ARCHTEST', streams_name => 'redhatdb_to_testbox', source_queue_name => 'strmadmin.streams_queue', destination_queue_name => 'strmadmin.streams_queue@xxxxxxxxxxxx', include_dml => true, include_ddl => true, inclusion_rule => true); end;
as STRMADMIN on the DESTINATION database.
begin dbms_streams_adm.add_table_rules ( table_name =>'ARCHTEST', streams_type => 'apply', streams_name => 'apply_stream', queue_name => 'strmadmin.streams_queue', include_dml => true, include_ddl => true, source_database => 'REDHATDB.GLC', inclusion_rule => true); end;
As STRMADMIN on SOURCE database.
begin dbms_capture_adm.start_capture( capture_name => 'capture_stream'); end;
as STRMADMIN on the DESTINATION database.
begin dbms_apply_adm.set_parameter( apply_name => 'apply_stream', parameter => 'disable_on_error', value => 'n'); end;
begin dbms_apply_adm.start_apply( apply_name => 'apply_stream'); end;
The article indicates that I should be complete in my setup on the source and destination databases but I do not see any changes in the ARCHTEST table on the destination database when I make changes to the data in the source database. Is there a step that I missed or set up incorrectly? I would like to get this working so I can replicate changes to 100 plus tables from production to my test database.
Thanks for any direction that is offered. Ron
-- //www.freelists.org/webpage/oracle-l
-- Regards, Koen
Siemens COM COM D MN A
Phone : +32 14 25 3000 Email : Koen.Van_Langenhove@xxxxxxxxxxx - - - - - - - Unix IS user-friendly .., it's just quite picky about its friends.
-- //www.freelists.org/webpage/oracle-l