Advanced Replication

Oracle Advanced Replication was a Oracle database replication technology that supported bidirectional replication, multiple masters, conflict resolution, etc. Unfortunately, Advanced Replication was deprecated in Oracle 12.1 and removed in subsequent versions. Oracle advised customers to migrate to Golden Gate, which is a cost option, leaving many customers high and dry, looking for different solutions.

Prerequisites

• The Oracle Enterprise Edition software is installed on all participating servers.

• Advanced Replication is installed on all participating databases. If not, connect as SYS and run ORACLE_HOME/rdbms/admin/catrep.sql (catrep.sql is executed when you run catproc.sql as well).

• The following initialization parameters must be set:
  • JOB_QUEUE_PROCESSES - set to at least 1
  • JOB_QUEUE_INTERVAL - only required for Oracle 8i and below
  • GLOBAL_NAMES set to TRUE

Supported objects

Advanced replication can not only be used to replicate tables, but also indexes, views, packages, procedures, functions, triggers, synonyms, etc. However, it cannot replicate all object types. Most notably, it cannot replicate sequences.

Replication Manager GUI

One can use the Enterprise Manager Replication Manager to configure replication or by issuing PL/SQL API calls.

The Replication Manager GUI can be installed from the "client CD". Select an "Administrator" type install.

The rest of this article will focus on the PL/SQL API method.

Setup master sites

The following setup is required for each Master Site (this includes the master definition site and all other master destination sites):

CONNECT / AS SYSDBA

-- Ensure the database global name is correctly set
ALTER DATABASE RENAME global_name TO site1.world;

-- Create public DB links to all replication sites
CREATE PUBLIC DATABASE LINK site2.world USING 'site2.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;

-- Authorize 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');

-- Authorize 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@site1

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

-- Schedule job to push transactions to all master sites with appropriate intervals
EXECUTE Dbms_Defer_Sys.Schedule_Push(        -
        destination   => 'site2.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:

NOTE: From now on we only work on one site - the one you want to be the master definition site.

CONNECT repadmin/repadmin@site1

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

-- Add master destination sites
EXECUTE Dbms_Repcat.Add_Master_Database('MYGRP', 'site2.world');

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

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

-- OPTIONAL: Register columns for conflict resolution - only required if the table doesn't have a primary key!
EXECUTE Dbms_Repcat.Make_Column_Group(  -
        sname => 'SCOTT',               -
        oname => 'EMP',                 -
        column_group => 'EMP_COLGRP',   -
        list_of_column_names => 'EMPNO');

-- OPTIONAL: 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 = 'MYGRP';

Add objects to an existing group

Here are some examples of how to add objects to an existing group (say, MYGRP, created in the above section). Note that these commands are executed from the master definition site.

• Add a table to a group:

exec dbms_repcat.suspend_master_activity(gname=>'MYGRP');
exec dbms_repcat.create_master_repobject('SCOTT', 'EMP', 'TABLE', gname=>'MYGRP');
exec dbms_repcat.generate_replication_support('SCOTT', 'EMP', 'TABLE');
exec dbms_repcat.resume_master_activity(gname=>'MYGRP');

• Add a function to a group:

exec dbms_repcat.suspend_master_activity(gname=>'MYGRP');
exec dbms_repcat.create_master_repobject('SCOTT', 'USERS_UPDATE', 'FUNCTION', gname=>'MYGRP');
exec dbms_repcat.resume_master_activity(gname=>'MYGRP');

Remove replication support

To remove replication, perform the following on the Master Definition Site:

CONNECT repadmin/repadmin@site1

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

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

Next do the following on all Master Sites:

CONNECT repadmin/repadmin@site1

-- Remove private database links to other master databases
EXECUTE Dbms_Repcat.Drop_Master_Repgroup('MYGRP');
DROP DATABASE LINK site2.world;

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

CONNECT sys@site1

-- 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 site2.world;

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 => 'MYGRP');

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

Monitoring and clearing errors

Check for configuration errors and outstanding admin requests:

SELECT to_char(timestamp, 'DD Mon HH24:MI') timestamp, request, status, message
  FROM sys.dba_repcatlog ORDER BY timestamp;

Remove all admin errors:

exec dbms_repcat.purge_master_log(null, null, null);

Check for replication errors:

SELECT destination "Destination", errcount FROM sys.deferrcount;
SELECT * FROM sys.deferror;

Remove all DEFTRAN errors - only do this as last resort!

exec dbms_defer_sys.delete_error(NULL, NULL);

Replication Views

Some of the common views used to monitor replication:

• DBA_REPSITES - Sites that participates in the replication
• DBA_REPGROUP - Replication groups
• DBA_REPOBJECT - Objects that are replicated (for each group)
• DBA_REPCONFLICT - Registered conflict handlers

• DBA_REPCATLOG - Outstanding Admin Requests and configuration errors

• DEFTRAN - Deferred transactions
• DEFERROR - Deferred transaction errors
• DEFERRCOUNT - Deferred transaction error counts

Also see

• Advanced Replication Conflict Resolution
• Advanced Replication FAQ - Frequently asked questions