René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Multimaster replication in Oracle

Three users (not nessesseraly distinct users. They're more of a role or a responsability). These users can be incorporated as one oracle user, if desired.

Creating the replication administrator

create user repadmin identified by repadmin 
  default tablespace ts_data 
  temporary tablespace temp;

grant create session to repadmin;

Granting nessessery privileges to the administrator

Must be executed on all master sites. 
begin
  dbms_repcat_admin.grant_admin_any_schema('REPADMIN');
end;
/

Registering propagator

In this example, the propagator is REPADMIN.
Must be executed on all master sites. 
begin
  dbms_defer_sys.register_propagator('REPADMIN'); 
end;
/

Registering receiver

In this example, the receiver is REPADMIN.
Must be executed on all master sites. 
begin
   dbms_repcat_admin.register_user_repgroup (
      username       => 'repadmin',
      privilege_type => 'receiver',
      list_of_gnames =>  null);
end;
/

Public database link

System needs to create a public database link to all other master sites: 
create public database link  
  otherdb.foo.invalid
using
  '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=foo.invalid)(Port=1530)))(CONNECT_DATA=(SID=otherdb)))';
Must be executed on all master sites.

Private database link

The replication administrators need a private database link. Execute this as REPADMIN: 
connect repadmin/repadmin

create database link otherdb.foo.invalid connect to repadmin identified by repadmin;
Must be executed on all master sites.

Scheduling purge

begin      
  dbms_defer_sys.schedule_purge(          
  next_date        =>  sysdate        ,          
  interval         => 'sysdate + 1/24',          
  delay_seconds    =>  0              ,          
  rollback_segment => '');   
end;   
/

Scheduling push

begin      
  dbms_defer_sys.schedule_push(          
    destination   => 'otherdb.foo.invalid',
    interval      => 'sysdate + 1/60/24',
    next_date     => sysdate,          
    stop_on_error => false,          
    delay_seconds => 0,          
    write_trace   => true,
    parallelism   => 1);   
end;   
/

Creating replicated objects

Now, the basic infrastructure for a replicated environment is built. We can now create the objects to be replicated:
In this example, the objects belong to the user RENE.
Create the objects on all master sites participating in the replication environment. 
connect rene/rene

create table obj_1 (
  a number          primary key,
  b varchar2(20)
);

create table obj_2 (
  c number          primary key,
  d varchar2(10)
);

Defining replication group

Only at the master definition site, to be executed by the replication administrator. 
begin
  dbms_repcat.create_master_repgroup('rep_objects');
end;
/

Adding objects to replication group

Only at the master definition site, to be executed by the replication administrator. 
begin
  dbms_repcat.create_master_repobject(
    'RENE', 'OBJ_1', 'TABLE', gname=>'rep_objects');

  dbms_repcat.create_master_repobject(
    'RENE', 'OBJ_2', 'TABLE', gname=>'rep_objects');
end;
/

Specifying master database

Only at the master definition site, to be executed by the replication administrator. 
begin 
  dbms_repcat.add_master_database (
     gname                => 'rep_objects', 
     master               => 'otherdb.foo.invalid',
     use_existing_objects => true,
     copy_rows            => false,
     propagation_mode     => 'ASYNCHRONOUS');
end;
/

Generating replication support for replicated objects

Only at the master definition site, to be executed by the replication administrator. 
begin
  dbms_repcat.generate_replication_support('RENE','OBJ_1','TABLE');
  dbms_repcat.generate_replication_support('RENE','OBJ_2','TABLE');
end;
/

Resuming replication

Only at master definition site: 
begin
  dbms_repcat.resume_master_activity('rep_objects'
  -- , override=>true
  );
end;
/