Oracle Multi-Directional Data Replication

In part one of this series, we illustrated the procedures for setting up bi-directional data replication and conflict resolution between two databases using Oracle 10g Streams. In the second half of this series, we will provide the steps to add a new source site into our existing bi-directional replicated environment. At the end of the set up, we will have a three-way directional replicated environment. Changes originate from one database, are propagated to the rest of the databases, thus keeping all sites synchronized.

Adding a New Source Site

An overview of the current Oracle bi-directional Streams environment:

Host Name	Instance Name	Global Database Name	Database Role
trout3	ladb	ladb.world	Source / Destination database
trout4	sfdb	sfdb.world	Source / Destination database

An overview of the Oracle multi-directional Streams environment:

Host Name	Instance Name	Global Database Name	Database Role
trout1	dcdb	dcdb.world	Source / Destination database
trout3	ladb	ladb.world	Source / Destination database
trout4	sfdb	sfdb.world	Source / Destination database

Step 1: Configure archive log mode

Archived redo logs are required by the capture process to extract changes. Verify dcdb is in archive log mode and configure your log archive destination or flash recovery area.

Step 2: Modify initialization parameters

Parameter	dcdb	Description
compatible	10.2.0	Both databases are running Oracle 10gR2.
global_names	true	Database link name must match global name.
job_queue_processes	2	The maximum number of job queue processes to propagate messages. Set this parameter to at least 2.
db_recovery_file_dest_size	10G	Specifies the storage size of database recovery files such as archive logs.
db_recovery_file_dest	/u01/app/oracle/flashdest	Specifies the location of database recovery files such as archive logs.
parallel_max_servers	6	The maximum number of parallel execution processes that can be used by the capture and apply processes. You may encounter ora-1372 or ora-16081 during Streams capture and apply if the value is set too low. Set this parameter to at least 6.
sga_target	500M	Enables Automatic Shared Memory Management (ASMM). In Oracle 10gR2, ASMM automatically manages the streams_pool_size. streams_pool_size provides buffer areas for streams processing. If this parameter is not set, you should manually set streams_pool_area to at least 200M. Otherwise, memory will be allocated for Streams from the shared pool area.

Step 3: Set up tnsnames.ora

Add the TNS entries on trout1, trout3 and trout4.

DCDB.WORLD =
   (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = trout1)(PORT = 1521))
       (CONNECT_DATA =
       (SERVER = DEDICATED)
            (SERVICE_NAME = dcdb)
        )
     )   
LADB.WORLD =
   (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = trout3)(PORT = 1521))
       (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = ladb)
        )
    )
SFDB.WORLD =
   (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = trout4)(PORT = 1521))
       (CONNECT_DATA =
       (SERVER = DEDICATED)
            (SERVICE_NAME = sfdb)
        )
     )   

Step 4: Create tablespaces

Create the tablespaces for the application schema and Streams administrator in dcdb.

create tablespace appsts datafile '/u02/oradata/dcdb/appsts01.dbf' size 100M;
 
create tablespace streamsts datafile '/u02/oradata/dcdb/streamsts01.dbf' size 100M;

Step 5: Create Streams administrator

Create the Streams administrator in dcdb.

create user strmadmin identified by strmadmin default tablespace streamsts temporary tablespace temp;
grant dba,select_catalog_role to strmadmin;
exec dbms_streams_auth.grant_admin_privilege('strmadmin',true);

Step 6: Create application schema

Create the application schema in dcdb.

create user apps identified by apps default tablespace appsts temporary tablespace temp;
grant connect,resource to apps;
grant select on v_$database to apps;

Step 7: Create database link

As the Streams administrator, create the following private database links in each database.

On dcdb

create database link ladb.world connect to strmadmin identified by strmadmin using 'ladb.world';
create database link sfdb.world connect to strmadmin identified by strmadmin using 'sfdb.world';

On ladb

create database link dcdb.world connect to strmadmin identified by strmadmin using 'dcdb.world';

On sfdb

create database link dcdb.world connect to strmadmin identified by strmadmin using 'dcdb.world';

Step 8: Create Streams queues

As the Streams administrator, create the capture and apply queues in dcdb.

begin
dbms_streams_adm.set_up_queue(
  queue_table   => 'apply_dcqtab',
  queue_name    => 'apply_dcq',
  queue_user    => 'strmadmin');
end;
/
begin
dbms_streams_adm.set_up_queue(
  queue_table   => 'capture_dcqtab',
  queue_name    => 'capture_dcq',
  queue_user    => 'strmadmin');
end;
/

Step 9: Configure capture, apply and propagation processes

As the Streams administrator on each source database, execute:

On ladb

begin
 dbms_streams_adm.add_schema_propagation_rules (
  schema_name  => 'apps',
  streams_name  => 'prop_ladb_to_dcdb',
  source_queue_name => 'capture_laq',
  destination_queue_name => 'apply_dcq@dcdb.world',
  include_dml  => true,
  include_ddl  => true,
  source_database => 'ladb.world');
end;
/
begin
 dbms_streams_adm.add_schema_rules (
  schema_name  => 'apps',
  streams_type  => 'apply',
  streams_name  => 'apply_src_dcdb',
  queue_name  => 'apply_laq',
  include_dml  => true,
  include_ddl  => true,
  source_database => 'dcdb.world');
end;
/

On sfdb

begin
 dbms_streams_adm.add_schema_propagation_rules (
  schema_name  => 'apps',
  streams_name  => 'prop_sfdb_to_dcdb',
  source_queue_name => 'capture_sfq',
  destination_queue_name => 'apply_dcq@dcdb.world',
  include_dml  => true,
  include_ddl  => true,
  source_database => 'sfdb.world');
end;
/
begin
 dbms_streams_adm.add_schema_rules (
  schema_name  => 'apps',
  streams_type  => 'apply',
  streams_name  => 'apply_src_dcdb',
  queue_name  => 'apply_sfq',
  include_dml  => true,
  include_ddl  => true,
  source_database => 'dcdb.world');
end;
/

On dcdb

begin
 dbms_streams_adm.add_schema_rules (
  schema_name  => 'apps',
  streams_type  => 'capture',
  streams_name  => 'capture_dcstrm',
  queue_name  => 'capture_dcq',
  include_dml  => true,
  include_ddl  => true,
  inclusion_rule => true);
end;
/
begin
 dbms_streams_adm.add_schema_propagation_rules (
  schema_name  => 'apps',
  streams_name  => 'prop_dcdb_to_ladb',
  source_queue_name => 'capture_dcq',
  destination_queue_name => 'apply_laq@ladb.world',
  include_dml  => true,
  include_ddl  => true,
  source_database => 'dcdb.world');
end;
/
begin
 dbms_streams_adm.add_schema_propagation_rules (
  schema_name  => 'apps',
  streams_name  => 'prop_dcdb_to_sfdb',
  source_queue_name => 'capture_dcq',
  destination_queue_name => 'apply_sfq@sfdb.world',
  include_dml  => true,
  include_ddl  => true,
  source_database => 'dcdb.world');
end;
/
begin
 dbms_streams_adm.add_schema_rules (
  schema_name  => 'apps',
  streams_type  => 'apply',
  streams_name  => 'apply_src_ladb',
  queue_name  => 'apply_dcq',
  include_dml  => true,
  include_ddl  => true,
  source_database => 'ladb.world');
end;
/
begin
 dbms_streams_adm.add_schema_rules (
  schema_name  => 'apps',
  streams_type  => 'apply',
  streams_name  => 'apply_src_sfdb',
  queue_name  => 'apply_dcq',
  include_dml  => true,
  include_ddl  => true,
  source_database => 'sfdb.world');
end;
/

Step 10: Prepare schema instantiation

As the Streams administrator, prepare for instantiation all current and future schema objects in ladb and sfdb. The lowest SCN of each object in the apps schema is recorded for instantiation.

On ladb

exec dbms_capture_adm.prepare_schema_instantiation(schema_name =>'apps');

On sfdb

exec dbms_capture_adm.prepare_schema_instantiation(schema_name =>'apps');