Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Advanced Replication

Advanced replication allows the simultaneous transfer of data between two or more Master Sites. Each Master Site is fully functional and doesn't depend on pulling updates from a central site. In this example replication is setup between two Master Sites, TSH1 and TSH2, with the TSH1 instance as the Master Definition site, making it responsible for the coordination of the replication process.

Assumptions

Setup Master Sites

The following setup is required for each replication Master Site.

CONNECT sys@tsh1

-- Create public DB links to all replication sites
CREATE PUBLIC DATABASE LINK TSH2.WORLD USING 'TSH2.WORLD';

-- Create replication administrator / propagator / receiver
CREATE USER repadmin IDENTIFIED BY repadmin
  DEFAULT   TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

-- Grant privs to the propagator, to propagate changes to remote sites
EXECUTE Dbms_Defer_Sys.Register_Propagator(username=>'REPADMIN');

-- Grant privs to the receiver to apply deferred transactions
GRANT EXECUTE ANY PROCEDURE TO repadmin;

-- Authorise the administrator to administer replication groups and schemas
EXECUTE Dbms_Repcat_Admin.Grant_Admin_Any_Repgroup('REPADMIN');
EXECUTE Dbms_Repcat_Admin.Grant_Admin_Any_Schema (username => 'REPADMIN');

-- Authorise the administrator to lock and comment tables
GRANT LOCK ANY TABLE TO repadmin;
GRANT COMMENT ANY TABLE TO repadmin;

-- Connect to the replication administrator
CONNECT repadmin/repadmin@tsh1

-- Create private db links for all repadmin users
CREATE DATABASE LINK TSH2.WORLD CONNECT TO repadmin IDENTIFIED BY repadmin USING 'TSH2.WORLD';

-- Schedule job to push transactions to all master sites with appropriate intervals
EXECUTE Dbms_Defer_Sys.Schedule_Push(        -
        destination   => 'TSH2.WORLD',       -
        interval      => 'sysdate+1/24/60',  -
        next_date     => sysdate+1/24/60,    -
        stop_on_error => FALSE,              -
        delay_seconds => 0,                  -
        parallelism   => 1);

-- Schedule job to delete successfully replicated transactions
EXECUTE Dbms_Defer_Sys.Schedule_Purge(       -
        next_date     => sysdate+1/24,       -
        interval      => 'sysdate+1/24');

Setup Master Definition Site

On the Master Definition Site define replication groups and assign destination sites to them. Each object to be replicated is defined and replication support is generated.

CONNECT repadmin/repadmin@tsh1

-- Create replication group for MASTERDEF site
EXECUTE Dbms_Repcat.Create_Master_Repgroup('MYREPGRP');

-- Add master desination sites
EXECUTE Dbms_Repcat.Add_Master_Database('MYREPGRP', 'TSH2.WORLD');

-- Wait until TSH2.WORLD appears in the DBA_REPSITES view
SELECT * FROM dba_repsites WHERE gname = 'MYREPGRP';

-- Register objects within the group
EXECUTE Dbms_Repcat.Create_Master_Repobject('SCOTT', -
        'EMP', 'TABLE', gname=>'MYREPGRP');

-- Register columns for confilct resolution
EXECUTE Dbms_Repcat.Make_Column_Group(  -
        sname => 'SCOTT',               -
        oname => 'EMP',                 -
        column_group => 'EMP_COLGRP',   -
        list_of_column_names => 'EMPNO');

-- Define conflict resolution for the registered columns
EXECUTE Dbms_Repcat.Add_Update_Resolution( -
        sname => 'SCOTT',               -
        oname => 'EMP',                 -
        column_group => 'EMP_COLGRP',   -
        sequence_no => 1,               -
        method => 'OVERWRITE',   -
        parameter_column_name => 'EMPNO');

-- Generate replication support for objects within the group
EXECUTE Dbms_Repcat.Generate_Replication_Support('SCOTT', 'EMP', 'table');

-- Wait until generation is complete (DBA_REPCATALOG is empty)
SELECT * FROM dba_repcatlog WHERE gname = 'MYREPGRP';

Start/Stop Replication

Once replication support has been generated for all objects relevant objects replication can be started or stopped as follows.

-- Start Replication
EXECUTE Dbms_Repcat.Resume_Master_Activity(gname => 'MYREPGRP');

-- Stop Replication
EXECUTE Dbms_Repcat.Suspend_Master_Activity(gname => 'MYREPGRP');

Remove Replication Support

To remove replication perform the following on the Master Definition Site.

CONNECT repadmin/repadmin@tsh1

-- Stop replication
EXECUTE Dbms_Repcat.Suspend_Master_Activity(gname=>'MYREPGRP');

-- Delete replication groups
EXECUTE Dbms_Repcat.Drop_Master_Repobject('SCOTT', 'EMP', 'TABLE');
EXECUTE Dbms_Repcat.Remove_Master_Databases('MYREPGRP', 'TSH2.WORLD');

Next do the following on all Master Sites.

CONNECT repadmin/repadmin@tsh1

-- Remove private databse links to other master databases
EXECUTE Dbms_Repcat.Drop_Master_Repgroup('MYREPGRP');
DROP DATABASE LINK TSH2.WORLD;

-- Remove any leftover jobs (see DBA_JOBS for job numbers)
EXECUTE Dbms_Job.Remove(62);
EXECUTE Dbms_Job.Remove(63);

CONNECT sys@tsh1

-- Remove the REPADMIN user
EXECUTE Dbms_Defer_Sys.Unregister_Propagator(username=>'REPADMIN');
EXECUTE Dbms_Repcat_Admin.Revoke_Admin_Any_Schema(username=>'REPADMIN');
DROP USER repadmin CASCADE;

-- Drop public database links to other master databases
DROP PUBLIC DATABASE LINK TSH2.WORLD;

Issues

Conflicts can occur when sequences are used to generate primary keys on each site. These can be prevented in several ways:

Hope this helps. Regards Tim...

Back to the Top.