Oracle 10G streams mode-level replication

Database: SC _source and SC _dest

Requirement: Copy all objects under the product user in SC _source to SC _dest.

I. preparations:

1.1 place the SC _source and SC _dest databases in the archive Mode

1.2 if some tables do not have a primary key, configure secondary logs on the source database (SC _source) to ensure the accuracy of replication.

Alter database add supplemental log data (primary key, unique) columns;

1.3 Source: some parameters are set for the target database

Job_queue_processes = 4 Aq_tm_processes = 4 Global_names = true

1.4 create a stream replication Management User source, which must be configured for the target database

Create tablespace streams_tbs datafile '/Oracle/oradata/streams_tbs01.dbf' size 1024 M; Create user strmadmin identified by strmadmin default tablespace streams_tbs quota unlimited on streams_tbs; Grant dba, select_catalog_role to strmadmin;

1.5 create a database connection

SC _source: Create database link SC _dest.net connect to strmadmin identified by strmadmin using 'SC _ dest '; SC _dest: Create database link SC _source.net connect to strmadmin identified by strmadmin using 'SC _ source ';

2. Start configuring streams

SC _source

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

Configure the capture process queue

Begin Dbms_streams_adm.set_up_queue ( Queue_table => 'capture _ scstab ', Queue_name => 'capture _ scs ', Queue_user => 'strmadmin '); End; /

SC _dest

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

Configure the application process queue

Begin Dbms_streams_adm.set_up_queue ( Queue_table => 'apply _ cdtab ', Queue_name => 'apply _ scd ', Queue_user => 'strmadmin '); End; /

SC _source

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

Configure the capture process

Begin Dbms_streams_adm.add_schema_rules ( Schema_name => 'product ', Streams_type => 'capture ', Streams_name => 'capture _ scstrm ', Queue_name => 'capture _ scs ', Include_dml => true, Include_ddl => true, Inclusion_rule => true ); End; /

Configure the propagation process

Begin Dbms_streams_adm.add_schema_propagation_rules ( Schema_name => 'product ', Streams_name => 'Pro _ source_to_dest ', Source_queue_name => 'capture _ scs ', Destination_queue_name => 'apply _ scd@ SC _dest.net ', Include_dml => true, Include_ddl => true, Source_database => 'SC _ source.net '); End; /

SC _dest

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

Configure the application process

Begin Dbms_streams_adm.add_schema_rules ( Schema_name => 'product ', Streams_type => 'application ', Streams_name => 'apply _ bqtrm ', Queue_name => 'apply _ scd ', Include_dml => true, Include_ddl => true, Source_database => 'SC _ source.net '); End; /

1 2 Next Page