Basic Replication made simple

Basic replication is the only type of replication supported in Standard Edition One and Standard Edition. In basic replication only data is replicated not other data structures (eg indexes) or SP (procedures,functions etc. Replication is one-way from master site to replication site unlike advance replication which is capable of handling multi-master replication. The replication site uses materialized view to refresh the data and these are read-only.

Following is a test implementation to try out

create a oracle db user on both DBs. Call this replication user

On Master Site create the table and materialized view logs on that table and insert some data

create table res_bkg (booking_id number primary key, name varchar2(100), city varchar2(100), cost number);

create materialized view log on res_bkg with primary key;

insert into res_bkg values (1,'asanga','cmb',101);
insert into res_bkg values (2,'pradeep','lhr',201);

On Replication Site:
1. Edit the tnsnames.ora file and add an entry to the master site DB (assume it's std11g1)
2. As System user

create public database link std11g1.domain.ext using 'std11g1';

3. As replication user

create database link std11g1.domain.ext connect to asanga identified by asa;

create materialized view res_bkg refresh fast with primary key start with sysdate next sysdate + 1/(24*60) as (select * from res_bkg@std11g1.domain.ext);

select * from res_bkg;

This will show all the data currently in the master site table.

Insert another row to the master site

insert into res_bkg values (3,'alvin','alx',1001);

and do a select * from res_bkg on the replication site. After the refresh interval has passed the row will appear on the replication site. Can try a delete as well

delete from res_bkg where cost=1001;

Eventually the row will be deleted from the replication site.

The replication works between standard (primary) and enterprise (replicate) editions as well.