Step‑by‑Step Oracle Multi‑Master Replication Setup with Full SQL Scripts

Overview

The article provides a practical, detailed tutorial for implementing Oracle multi‑master replication, using two master sites (cl.world and js.world) and two shared tables (survey and line). It explains each configuration step and supplies the exact SQL commands needed.

Step 1 – Create Replication Sites

Log in to the primary database as SYSTEM and create a dedicated replication administrator user.

CREATE USER repadmin IDENTIFIED BY repadmin;
BEGIN
  DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(username => 'repadmin');
END;

Step 2 – Assign Propagator and Receiver

Register the replication administrator as the propagator (to push changes) and as the receiver (to accept changes) for the site.

BEGIN
  DBMS_DEFER_SYS.REGISTER_PROPAGATOR(username => 'repadmin');
END;

BEGIN
  DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP(
    username => 'repadmin',
    privilege_type => 'receiver',
    list_of_gnames => NULL);
END;

Step 3 – Set Purge Schedule

Configure a hourly purge to clean processed transactions from the queue.

CONNECT repadmin/[email protected]
BEGIN
  DBMS_DEFER_SYS.SCHEDULE_PURGE(
    next_date => SYSDATE,
    interval => SYSDATE + 1/24,
    delay_seconds => 0);
END;

Step 4 – Create Database Links Between Sites

Establish public database links so each master can reach the other.

CONNECT SYSTEM/[email protected]
CREATE PUBLIC DATABASE LINK js.world USING 'js.world';
CONNECT repadmin/[email protected]
CREATE DATABASE LINK js.world CONNECT TO repadmin IDENTIFIED BY repadmin;

CONNECT SYSTEM/[email protected]
CREATE PUBLIC DATABASE LINK cl.world USING 'cl.world';
CONNECT repadmin/[email protected]
CREATE DATABASE LINK cl.world CONNECT TO repadmin IDENTIFIED BY repadmin;

Step 5 – Schedule Push Operations

Set a 10‑minute interval for pushing changes from cl.world to js.world.

CONNECT repadmin/[email protected]
BEGIN
  DBMS_DEFER_SYS.SCHEDULE_PUSH(
    destination => 'js.world',
    interval => 'SYSDATE + (1/144)',
    next_date => SYSDATE,
    parallelism => 1,
    execution_seconds => 1500,
    delay_seconds => 1200);
END;

Step 6 – Create Master Replication Group

Define a master replication group to manage replicated objects.

CONNECT repadmin/[email protected]
BEGIN
  DBMS_REPCAT.CREATE_MASTER_REPGROUP(gname => 'inte_repg');
END;

Step 7 – Register Tables in the Group

Add the survey and line tables to the group, linking them to the integration schema.

BEGIN
  DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
    gname => 'inte_repg',
    type => 'TABLE',
    oname => 'survey',
    sname => 'integration',
    use_existing_object => TRUE,
    copy_rows => FALSE);
END;

BEGIN
  DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
    gname => 'inte_repg',
    type => 'TABLE',
    oname => 'line',
    sname => 'integration',
    use_existing_object => TRUE,
    copy_rows => FALSE);
END;

Step 8 – Add Peer Master Databases

Specify the other master site (js.world) as a participant in the replication group.

BEGIN
  DBMS_REPCAT.ADD_MASTER_DATABASE(
    gname => 'inte_repg',
    master => 'js.world',
    use_existing_objects => TRUE,
    copy_rows => FALSE,
    propagation_mode => 'ASYNCHRONOUS');
END;

Step 9 – Generate Replication Support

Create the internal replication support structures for each table.

BEGIN
  DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
    sname => 'integration',
    oname => 'survey',
    type => 'TABLE',
    min_communication => TRUE);
END;

Repeat the same call for the line table.

Step 10 – Resume Replication

After all objects are configured, start the replication process.

BEGIN
  DBMS_REPCAT.RESUME_MASTER_ACTIVITY(gname => 'inte_repg');
END;

Perform the same configuration on the js.world site to complete the bi‑directional replication setup.