Oracle Bi-Directional Data Replication

April 13, 2006

Data availability is an important factor to the success of any businesses. In a distributed environment, Oracle Streams improves data availability and accessibility by sharing and replicating information to multiple sites quickly and reliably. Oracle Streams was introduced in Oracle 9i and has since matured into robust and powerful software is used to perform data replication, data warehouse transformation and messaging.

This is part one of a two-part series of illustrating the set up of bi-directional replication using Oracle 10g Streams. Part one provides the procedures for setting up bi-directional schema-level replication and conflict resolution between two databases. Part two will demonstrate how a new source database can be easily added into the replicated environment.

Streams Overview

There are three basic processes of Oracle Streams: Capture, Staging and Consumption (Apply).

The capture process at the source site captures events such as DML and DDL from either the online redo logs or archived log files, formats the changes into Logical Change Records (LCRs) and queues them into a staging area (queue). The LCRs are then propagated to an apply queue at the destination site where the changes are eventually dequeued and applied by the apply process.

In a bi-directional replication set up, each site captures, propagates and applies changes to the other site, keeping the databases at both sites current. Each site acts as a source database and as a destination database. Because simultaneous updates can be performed on the same records by both sites, conflicts can arise. In the following sections, we will discuss Streams pre-built update conflict resolution handler and how conflicts can be avoided or minimized.

Conflict Detection

When an apply process applies row LCR at the destination database, conflicts are automatically detected when

Ideally, you should design your system to avoid or minimize conflicts. There are various ways of achieving that such as using unique sequence keys at each replicated site, horizontal subsetting of data to ensure each site can only make changes to its own data and avoiding physical deletion of records by logically marking the records deleted and purging them at a later time.

Conflict Resolution

Oracle only provides the update conflict handler. If the pre-built conflict handler is insufficient, you can also create a custom conflict handler to resolve possible conflicts.

The four types of pre-built update conflict handlers are

The type of update conflict handler to select from is driven by your business rules and requirements. For example, if you designate one site as the authoritative site, you may want to discard changes propagated from other sites when a conflict occurs. In this article, we will use the discard and overwrite methods to resolve conflicts.

Designing your system to avoid and resolving possible conflicts is one of the most critical aspects for a successful implementation of bi-directional replication. Detailed information on Conflict Resolution can be obtained from http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14228/conflict.htm#i1006084 .

Setting Up Bi-Directional Replication

An overview of the Oracle Streams environment:

Step 1: Configure archive log mode

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

Step 2: Modify initialization parameters

Step 3: Set up tnsnames.ora

Add the TNS entries on trout3 and trout4.

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.

On ladb

create tablespace appsts datafile '/u02/oradata/ladb/appsts01.dbf' size 100M; 

create tablespace streamsts datafile '/u02/oradata/ladb/streamsts01.dbf' size 100M;

On sfdb

create tablespace appsts datafile '/u02/oradata/sfdb/appsts01.dbf' size 100M; 

create tablespace streamsts datafile '/u02/oradata/sfdb/streamsts01.dbf' size 100M;

Step 5: Create Streams administrator

Create the Streams administrator in both ladb and sfdb.

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 both ladb and sfdb.

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 application schema’s objects

Create the application schema’s objects in ladb. The objects in sfdb are created later using Oracle’s Data Pump utility.

connect apps/apps@ladb.world
create table dept (
 deptno   number(10) not null, 
 dname   varchar2(20)  not null,
 site  varchar2(10)  not null
);
create or replace trigger dept_site_trg 
before insert on dept for each row
begin
 if :new.site is null then
  select name into :new.site from v$database; 
 end if;
end;
/
alter table dept add constraint dept_pk primary key (deptno);
create sequence deptno_laseq start with 1 increment by 5; 
create sequence deptno_sfseq start with 2 increment by 5;
insert into dept values (deptno_laseq.nextval,'FINANCE','LADB');
insert into dept values (deptno_laseq.nextval,'HR','LADB');
insert into dept values (deptno_laseq.nextval,'RESEARCH','LADB');
commit;
create table emp (
 empno  number(10)  not null,
 ename  varchar2(20) not null,
 site  varchar2(10)    not null,
 job  varchar2(10),
 deptno  number(10)
);
create or replace trigger emp_site_trg 
before insert on emp for each row
begin
 if :new.site is null then
  select name into :new.site from v$database; 
 end if;
end;
/
alter table emp add constraint emp_pk primary key (empno);
alter table emp add constraint emp_fk foreign key (deptno) references dept (deptno);
create sequence empno_laseq start with 1 increment by 5;  
create sequence empno_sfseq start with 2 increment by 5;  
insert into emp values (empno_laseq.nextval,'MIKE','LADB','CLERK',1);
insert into emp values (empno_laseq.nextval,'JANE','LADB','ANALYST',1);
insert into emp values (empno_laseq.nextval,'ZACH','LADB','ENGINEER',11);
commit; 

Step 8: Enable table level supplemental logging

Set up supplemental logging as the apps user on ladb. Supplemental logging logs additional columns information into the redo logs for row identification.

There are two types of supplemental log groups:

The following adds columns to the conditional supplemental log group for the emp and dept tables:

alter table dept add supplemental log group loggrp_dept (deptno,dname,site);
alter table emp add supplemental log group loggrp_emp (empno,ename,site,job,deptno);

SQL> select log_group_name, table_name
  2  from dba_log_groups
  3  where owner='APPS';
LOG_GROUP_NAME                 TABLE_NAME
------------------------------ ------------------------------
LOGGRP_EMP                     EMP
LOGGRP_DEPT                    DEPT

Step 9: Create database link

As the Streams administrator on ladb, create a private database link to sfdb and on sfdb, create a private database link to ladb.

On ladb

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

On sfdb

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