Oracle Multi-Master Replication: An Example

Posted at Friday, March 07, 2008
Oracle Replication can be quite powerful; and at the same time, difficult to set-up and maintain. It is the precursor to Oracle Streams and shares many of its characteristics.

In this example, I show how to configure Multi-master replication: the ability to update the same objects at n-peers, simultaneously. Basically, Oracle replicates the objects to each 'master' site. Users can then update any site (i.e. Database), after which Oracle will propagate changes to each member site. Oracle merges the changes and resolves any conflicts - update, delete, unique - per the conflict resolution methods you set-up.

My example uses 2 - 11g (11.1.0.6) databases: a 2-node RAC database called JLC and a standalone database called NF.

My example create a sample schema, REPL_EXAMPLE and a sample object to replicate: a table called SAMPLE.

I also demonstrate how to specify rules for conflict resolution.

I have automated the example via a series of scripts. You can modify the scripts for your environment and run as appropriate.

run_repl_example.bsh (The master script)

#!/bin/bash

sqlplus -S /nolog @run_repl_example.sql
echo "Sleeping for 60 secs...";
sleep 60;
sqlplus -S /nolog @dml_repl.sql
sqlplus -S /nolog @check_repl.sql
echo "Sleeping for 60 secs...";
sleep 60;
sqlplus -S /nolog @check_sample.sql
exit;

The master script first calls run_repl_example.sql

run_repl_example.sql

/* Run all the scripts for 
the Multi-master
Replication Example */

SET VERIFY OFF;

/* If they exist drop the replication groups */

@@drop_repl_mstr_groups.sql

/* Set-up each site for Replication */
@@setup_repl_sites.sql

/* Create the example schemas */

@@create_repl_example.sql

/* Create the objects to be propagated to
the example schemas */

@@create_repl_example_objs.sql

/* Create the Replication Group
and start propagation */

@@create_repl_mstr_groups.sql

exit;

As you can see, this script, in turn, calls the following scripts:

drop_repl_mstr_groups.sql (Drops the configuration at the Master Definition Site)

SPOOL drop_repl_mstr_groups.log

CONNECT REPADMIN/&&pwd_repadmin@JLC;

EXEC DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY('EXAMPLE_GROUP');
EXEC DBMS_REPCAT.REMOVE_MASTER_DATABASES('EXAMPLE_GROUP','NF.COLESTOCK.TEST,JLC.COLESTOCK.TEST');
EXEC DBMS_REPCAT.DROP_MASTER_REPGROUP('EXAMPLE_GROUP', TRUE,TRUE);
COMMIT;

SPOOL OFF;

setup_repl_sites.sql (Satifies all pre-requisites for Oracle Replication at each master site)

SPOOL setup_repl_sites.log;

CONNECT SYSTEM/&&pwd_system_jlc@JLC;

DROP USER REPADMIN CASCADE;

CREATE USER "REPADMIN" IDENTIFIED BY "&pwd_repadmin";

BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(
username => '"REPADMIN"');
END;
/
grant comment any table to "REPADMIN";
grant lock any table to "REPADMIN";
grant select any dictionary to "REPADMIN";
grant analyze any to "REPADMIN";

BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR(username => '"REPADMIN"');
END;
/

grant execute any procedure to "REPADMIN";

CONNECT SYSTEM/&&pwd_system_jlc@JLC;

BEGIN
SYSMAN.MGMT_USER.DROP_USER('REPADMIN');
SYSMAN.MGMT_USER.MAKE_EM_USER('REPADMIN');
END;
/

CONNECT SYSTEM/&&pwd_system_nf@NF;

DROP USER REPADMIN CASCADE;

CREATE USER "REPADMIN" IDENTIFIED BY "&&pwd_repadmin";

BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(
username => '"REPADMIN"');
END;
/
grant comment any table to "REPADMIN";
grant lock any table to "REPADMIN";
grant select any dictionary to "REPADMIN";
grant analyze any to "REPADMIN";

BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR(username => '"REPADMIN"');
END;
/

grant execute any procedure to "REPADMIN";

CONNECT SYSTEM/&&pwd_system_nf@NF;

BEGIN
SYSMAN.MGMT_USER.DROP_USER('REPADMIN');
SYSMAN.MGMT_USER.MAKE_EM_USER('REPADMIN');
END;
/

CONNECT SYSTEM/&&pwd_system_jlc@JLC

DROP PUBLIC DATABASE LINK "NF.COLESTOCK.TEST";

CREATE PUBLIC DATABASE LINK "NF.COLESTOCK.TEST" USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.colestock.test)(PORT=1523)))(CONNECT_DATA=(SID=nf)(server=DEDICATED)))';

CONNECT REPADMIN/&&pwd_repadmin@JLC;

DROP DATABASE LINK NF.COLESTOCK.TEST;

CREATE DATABASE LINK NF.COLESTOCK.TEST CONNECT TO "REPADMIN" IDENTIFIED BY "&&pwd_repadmin";

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH(
destination => 'NF.COLESTOCK.TEST',
interval => '/*1:Mins*/ sysdate + 1/(60*24)',
next_date => to_date('Mar 5, 2008 6:37:00 PM','MON DD, YYYY HH:MI:SS PM'),
stop_on_error => false,
delay_seconds => 5,
parallelism => 1);
END;
/

CONNECT SYSTEM/&&pwd_system_nf@NF

DROP PUBLIC DATABASE LINK "JLC.COLESTOCK.TEST";

CREATE PUBLIC DATABASE LINK "JLC.COLESTOCK.TEST" USING '(DESCRIPTION = (ADDRESS_LIST = (address = (protocol = tcp)(host = rac1-vip)(port = 1525)) (address = (protocol = tcp)(host = rac2-vip)(port = 1525)) (LOAD_BALANCE = yes)) (CONNECT_DATA = (SERVER = dedicated) (SERVICE_NAME = jlc.colestock.test) (failover_mode= (type = select) (method = basic) (retries = 180) (delay = 5))))';

CONNECT REPADMIN/&&pwd_repadmin@NF

DROP DATABASE LINK JLC.COLESTOCK.TEST;

CREATE DATABASE LINK JLC.COLESTOCK.TEST CONNECT TO "REPADMIN" IDENTIFIED BY "&&pwd_repadmin";

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH(
destination => 'JLC.COLESTOCK.TEST',
interval => '/*1:Mins*/ sysdate + 1/(60*24)',
next_date => to_date('Mar 5, 2008 6:37:00 PM','MON DD, YYYY HH:MI:SS PM'),
stop_on_error => false,
delay_seconds => 5,
parallelism => 1);
END;
/

CONNECT REPADMIN/&&pwd_repadmin@JLC

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE(
next_date => to_date('Mar 5, 2008 6:38:40 PM','MON DD, YYYY HH:MI:SS PM'),
interval => '/*15:Mins*/ sysdate + 15/(60*24)',
delay_seconds =>0,
rollback_segment => '');
END;
/

CONNECT REPADMIN/&&pwd_repadmin@NF

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE(
next_date => to_date('Mar 5, 2008 6:38:00 PM','MON DD, YYYY HH:MI:SS PM'),
interval => '/*15:Mins*/ sysdate + 15/(60*24)',
delay_seconds =>0,
rollback_segment => '');
END;
/

SPOOL OFF;

create_repl_example.sql (Creates the example schemas)

SPOOL create_repl_example.log;

CONNECT SYSTEM/&&pwd_system_jlc@JLC;

DROP USER REPL_EXAMPLE CASCADE;

CREATE USER REPL_EXAMPLE IDENTIFIED BY "&&pwd_repl";

grant alter session to REPL_EXAMPLE;
grant create cluster to REPL_EXAMPLE;
grant create database link to REPL_EXAMPLE;
grant create sequence to REPL_EXAMPLE;
grant create session to REPL_EXAMPLE;
grant create synonym to REPL_EXAMPLE;
grant create table to REPL_EXAMPLE;
grant create view to REPL_EXAMPLE;
grant create procedure to REPL_EXAMPLE;
grant create trigger to REPL_EXAMPLE;
grant unlimited tablespace to REPL_EXAMPLE;
grant create type to REPL_EXAMPLE;
grant create any snapshot to REPL_EXAMPLE;
grant alter any snapshot to REPL_EXAMPLE;


CONNECT SYSTEM/&&pwd_system_nf@NF;

DROP USER REPL_EXAMPLE CASCADE;

CREATE USER REPL_EXAMPLE IDENTIFIED BY "&&pwd_repl";

grant alter session to REPL_EXAMPLE;
grant create cluster to REPL_EXAMPLE;
grant create database link to REPL_EXAMPLE;
grant create sequence to REPL_EXAMPLE;
grant create session to REPL_EXAMPLE;
grant create synonym to REPL_EXAMPLE;
grant create table to REPL_EXAMPLE;
grant create view to REPL_EXAMPLE;
grant create procedure to REPL_EXAMPLE;
grant create trigger to REPL_EXAMPLE;
grant unlimited tablespace to REPL_EXAMPLE;
grant create type to REPL_EXAMPLE;
grant create any snapshot to REPL_EXAMPLE;
grant alter any snapshot to REPL_EXAMPLE;

SPOOL OFF;

create_repl_example_objs.sql (Creates the example object to be replicated)

SPOOL create_repl_example_objs.log;

CONNECT SYSTEM/&&pwd_system_jlc@JLC;

ALTER SESSION SET CURRENT_SCHEMA="REPL_EXAMPLE";

DROP TABLE SAMPLE;

CREATE TABLE SAMPLE (SAMPLE_ID NUMBER(10) NOT NULL,
SAMPLE_SITE VARCHAR2(30) NOT NULL,
SAMPLE_MSG VARCHAR2(200) NOT NULL,
SAMPLE_TSTAMP TIMESTAMP WITH TIME ZONE NOT NULL);

ALTER TABLE SAMPLE ADD CONSTRAINT SAMPLE_PK PRIMARY KEY (SAMPLE_ID);

INSERT INTO SAMPLE VALUES (1,'JLC','Pre-replication row',current_timestamp);
COMMIT;

SPOOL OFF;

create_repl_mstr_groups.sql (Creates the master sites and registers objects to be propagated, including conflict resolution rules)

SPOOL create_repl_mstr_groups.log;

CONNECT REPADMIN/&&pwd_repadmin@JLC;

BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY(gname=>'EXAMPLE_GROUP');
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP
(gname => 'EXAMPLE_GROUP',
qualifier => '',
group_comment => 'Example Multi-master Replication Group');
END;
/
COMMIT;

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(gname => 'EXAMPLE_GROUP',
type => 'TABLE',
oname => 'SAMPLE',
sname => 'REPL_EXAMPLE',
copy_rows => TRUE,
use_existing_object => FALSE);
END;
/

COMMIT;

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(gname => 'EXAMPLE_GROUP',
type => 'INDEX',
oname => 'SAMPLE_PK',
sname => 'REPL_EXAMPLE',
copy_rows => FALSE,
use_existing_object => FALSE) ;
END;
/

COMMIT;

BEGIN
DBMS_REPCAT.MAKE_COLUMN_GROUP
(sname => 'REPL_EXAMPLE',
oname => 'SAMPLE',
column_group => 'sample_cg',
list_of_column_names => 'sample_id, sample_site, sample_msg, sample_tstamp');
END;
/

COMMIT;

BEGIN
DBMS_REPCAT.ADD_UPDATE_RESOLUTION
(sname => 'REPL_EXAMPLE',
oname => 'SAMPLE',
column_group => 'sample_cg',
sequence_no => 1,
method => 'earliest timestamp' ,
parameter_column_name => 'sample_tstamp' );
END;
/

COMMIT;

BEGIN
DBMS_REPCAT.ADD_UNIQUE_RESOLUTION
(sname => 'REPL_EXAMPLE',
oname => 'SAMPLE',
constraint_name => 'SAMPLE_PK',
sequence_no => 1,
method => 'DISCARD',
parameter_column_name => 'SAMPLE_ID');
END;
/

COMMIT;

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(sname => 'REPL_EXAMPLE',
oname => 'SAMPLE',
type => 'TABLE',
generate_80_compatible => FALSE);
END;
/

COMMIT;

BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE(gname => 'EXAMPLE_GROUP',
master => 'NF.COLESTOCK.TEST',
use_existing_objects => FALSE,
copy_rows => TRUE,
propagation_mode => 'ASYNCHRONOUS');
END;
/

COMMIT;

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY(gname => 'EXAMPLE_GROUP',override=>TRUE);
END;
/

COMMIT;
SPOOL OFF;

At this point, the example replication is set-up. The sample table, SAMPLE can be updated by either site. An update confict resolution handler is in place; it specifies that if 2 updates should conflict, that the update with the earlier timestamp will prevail. A unique conflict resolution handler is also in place; it specifies that if a transaction violates the SAMPLE_PK primary key constraint that it is to be discarded when Oracle tries to propagate it to other master sites.

At this point, the master script sleeps for 60 seconds. This is necessary to allow the push job time to copy and instantiate the SAMPLE table to the other master sites: in my case, this table is copied to NF.COLESTOCK.TEST, which is a master site but not the master definition site.

Next sample values are inserted into the replicated table; I do this to test the configuration.

dml_repl.sql (Inserts sample values to test propagation and conflict resolution methods)

SPOOL dml_repl.log;
SET VERIFY OFF;

CONNECT REPL_EXAMPLE/&&pwd_repl_example@NF;

/* Valid Insert from master site */
INSERT INTO SAMPLE VALUES (2,'NF','ID 2 FIRST INSERT - MAY BE DISCARDED ON JLC',CURRENT_TIMESTAMP);
COMMIT;

/* Test Update Conflict Resolution */
UPDATE SAMPLE SET SAMPLE_MSG='Minimum Timestamp Update of ID 1',SAMPLE_TSTAMP=CURRENT_TIMESTAMP WHERE SAMPLE_ID=1;
COMMIT;

CONNECT REPL_EXAMPLE/&&pwd_repl_example@JLC;

/* Test Conflict resolution */
INSERT INTO SAMPLE VALUES (2,'JLC','ID 2 SECOND INSERT - MAY BE DISCARDED ON NF',CURRENT_TIMESTAMP);
COMMIT;

/* Valid Insert from master site */
INSERT INTO SAMPLE VALUES (3,'JLC','Normal Insert from JLC',CURRENT_TIMESTAMP);
COMMIT;

/* Test Update Conflict Resolution */
UPDATE SAMPLE SET SAMPLE_MSG='Maximum Timestamp Update of ID 1',SAMPLE_TSTAMP=CURRENT_TIMESTAMP WHERE SAMPLE_ID=1;
COMMIT;

SPOOL OFF;
EXIT;

Next the master script preforms diagnostic queries to validate that the configuration is working.

check_repl.sql (Runs diagnostic scripts)

SPOOL check_repl.log;

PROMPT "Checking Scheduled Jobs on NF...";

CONNECT REPADMIN/&&pwd_repadmin@NF;

COLUMN JOB FORMAT A70;
COLUMN DBLINK FORMAT A20;
COLUMN NAME FORMAT A20;
COLUMN SNAME FORMAT A20;

SET LINES 220;

select substr(what,1,70) as "JOB",
broken,
failures,
total_time,
next_sec
from user_jobs;

PROMPT "Querying Replication Info on NF...";

SELECT GNAME,
DBLINK,
MASTERDEF,
MASTER,
PROP_UPDATES,
MY_DBLINK
FROM DBA_REPSITES WHERE GNAME='EXAMPLE_GROUP';

SELECT SNAME,
ONAME,
TYPE,
DBLINK,
HOW
FROM DBA_REPPROP;

SELECT * FROM GV$REPLQUEUE;

SELECT * FROM GV$REPLPROP;

COLUMN GLOBAL_NAME HEADING 'Database' FORMAT A25
COLUMN ADMIN_REQUESTS HEADING 'Admin|Reqests' FORMAT 9999
COLUMN STATUS HEADING 'Admin|Errors' FORMAT 9999
COLUMN TRAN HEADING 'Def|Trans|Pairs' FORMAT 9999
COLUMN ERRORS HEADING 'Def|Trans|Errors' FORMAT 9999
COLUMN COMPLETE HEADING 'Propagated|Trans' FORMAT 9999

SELECT G.GLOBAL_NAME, D.ADMIN_REQUESTS, E.STATUS, DT.TRAN, DE.ERRORS, C.COMPLETE
FROM (SELECT GLOBAL_NAME FROM GLOBAL_NAME) G,
(SELECT COUNT(ID) ADMIN_REQUESTS FROM DBA_REPCATLOG) D,
(SELECT COUNT(STATUS) STATUS FROM DBA_REPCATLOG WHERE STATUS = 'ERROR') E,
(SELECT COUNT(*) TRAN FROM DEFTRANDEST) DT,
(SELECT COUNT(*) ERRORS FROM DEFERROR) DE,
(SELECT COUNT(A.DEFERRED_TRAN_ID) COMPLETE FROM DEFTRAN A
WHERE A.DEFERRED_TRAN_ID NOT IN (
SELECT B.DEFERRED_TRAN_ID FROM DEFTRANDEST B)) C;

PROMPT "Checking Scheduled Jobs on JLC...";

CONNECT REPADMIN/&&pwd_repadmin@JLC;

COLUMN JOB FORMAT A70;
COLUMN DBLINK FORMAT A20;
COLUMN NAME FORMAT A20;
COLUMN SNAME FORMAT A20;

SET LINES 220;

select substr(what,1,70) as "JOB",
broken,
failures,
total_time,
next_sec
from user_jobs;

PROMPT "Querying Replication Info on JLC...";

SELECT GNAME,
DBLINK,
MASTERDEF,
MASTER,
PROP_UPDATES,
MY_DBLINK
FROM DBA_REPSITES WHERE GNAME='EXAMPLE_GROUP';

SELECT SNAME,
ONAME,
TYPE,
DBLINK,
HOW
FROM DBA_REPPROP;

SELECT * FROM GV$REPLQUEUE;

SELECT * FROM GV$REPLPROP;

COLUMN GLOBAL_NAME HEADING 'Database' FORMAT A25
COLUMN ADMIN_REQUESTS HEADING 'Admin|Reqests' FORMAT 9999
COLUMN STATUS HEADING 'Admin|Errors' FORMAT 9999
COLUMN TRAN HEADING 'Def|Trans|Pairs' FORMAT 9999
COLUMN ERRORS HEADING 'Def|Trans|Errors' FORMAT 9999
COLUMN COMPLETE HEADING 'Propagated|Trans' FORMAT 9999

SELECT G.GLOBAL_NAME, D.ADMIN_REQUESTS, E.STATUS, DT.TRAN, DE.ERRORS, C.COMPLETE
FROM (SELECT GLOBAL_NAME FROM GLOBAL_NAME) G,
(SELECT COUNT(ID) ADMIN_REQUESTS FROM DBA_REPCATLOG) D,
(SELECT COUNT(STATUS) STATUS FROM DBA_REPCATLOG WHERE STATUS = 'ERROR') E,
(SELECT COUNT(*) TRAN FROM DEFTRANDEST) DT,
(SELECT COUNT(*) ERRORS FROM DEFERROR) DE,
(SELECT COUNT(A.DEFERRED_TRAN_ID) COMPLETE FROM DEFTRAN A
WHERE A.DEFERRED_TRAN_ID NOT IN (
SELECT B.DEFERRED_TRAN_ID FROM DEFTRANDEST B)) C;

SPOOL OFF;
EXIT;

The aforementioned diagnostic script - as with all the scripts in this example - generates its output to a corresponding log file.

Of course, as an alternative to this script, you could use Grid Control to obtain vital statistics:



The master script then sleeps for another 60 seconds. This is necessary to allow for the previoulsy inserted data to be synchronized between the master sites.

Next the master script queries each site to evaluate the contents of the replicated table.

check_sample.sql (Queries the replicated object on each site):

SPOOL check_sample.log;
SET VERIFY OFF;
PROMPT "Querying SAMPLE table contents from NF...";

CONNECT REPL_EXAMPLE/&&pwd_repl_example@NF;
COLUMN SAMPLE_ID FORMAT 999
COLUMN SAMPLE_SITE FORMAT A3
COLUMN SAMPLE_MSG FORMAT A50
COLUMN SAMPLE_TSTAMP FORMAT A35
SET LINES 220;

SELECT * FROM SAMPLE;

PROMPT "Querying SAMPLE table contents from JLC...";

CONNECT REPL_EXAMPLE/&&pwd_repl_example@JLC;
COLUMN SAMPLE_ID FORMAT 999
COLUMN SAMPLE_SITE FORMAT A3
COLUMN SAMPLE_MSG FORMAT A50
COLUMN SAMPLE_TSTAMP FORMAT A35
SET LINES 220;

SELECT * FROM SAMPLE;

SPOOL OFF;
EXIT;


The aforementioned should yield these results:

"Querying SAMPLE table contents from NF..."
Enter value for pwd_repl_example: ********

SAMPLE_ID SAM SAMPLE_MSG SAMPLE_TSTAMP
--------- --- -------------------------------------------------- -----------------------------------
1 JLC Minimum Timestamp Update of ID 1 06-MAR-08 08.41.10.729664 PM -07:00
2 NF ID 2 FIRST INSERT - MAY BE DISCARDED ON JLC 06-MAR-08 08.41.10.708899 PM -07:00
3 JLC Normal Insert from JLC 06-MAR-08 08.41.11.146178 PM -07:00


"Querying SAMPLE table contents from JLC...

"SAMPLE_ID SAM SAMPLE_MSG SAMPLE_TSTAMP
--------- --- -------------------------------------------------- -----------------------------------
1 JLC Minimum Timestamp Update of ID 1 06-MAR-08 08.41.10.729664 PM -07:00
2 JLC ID 2 SECOND INSERT - MAY BE DISCARDED ON NF 06-MAR-08 08.41.11.078133 PM -07:00
3 JLC Normal Insert from JLC 06-MAR-08 08.41.11.146178 PM -07:00

The complete scripts - including log files - can be found here: multi-master-example.tar


Labels: