How to setup 2 Way MultiMaster Replication?

Posted By Sagar Patil

What objects can we Replicate?

A replication object is a database object existing on multiple servers in a distributed database system.

In a replication environment, any updates made to a replication object at one site are applied to the copies at all other sites. Advanced Replication enables you to replicate the following types of objects:

• Tables
• Indexes
• Views and Object Views
• Packages and Package Bodies
• Procedures and Functions
• User-Defined Types and Type Bodies
• Triggers
• Synonyms
• Index types
• User-Defined Operators

Let’s go ahead and create 2 multi master sites “repA” and “repB” replicating EMP/DEPT objects in SCOTT schema.

Log files repA, repB

Step 1> Setup Replication Environment at 2 master sites
Start 2 telnet windows and set prompts

$export PS1=”repA>”
SQLPLUS>set sqlprompt “repA>”
$export PS1=”repB>”
SQLPLUS>set sqlprompt “repB>”

Setup at site repA
[oracle@localhost ~]$ sqlplus system/repa@repa
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Sep 12 08:39:55 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter global_names
NAME TYPE VALUE
———————————— ———– ——————————
global_names boolean FALSE
SQL> alter system set global_names=TRUE;
System altered.
SQL> show parameter job_queue_processes
NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 1000

SQL> alter system set job_queue_processes = 1;
System altered.

SQL> CREATE USER repadmin IDENTIFIED BY repadmin;
User created.

SQL> BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (username => ‘repadmin’);
END;
PL/SQL procedure successfully completed.

SQL> GRANT SELECT ANY DICTIONARY TO repadmin;
Grant succeeded.

SQL> BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => ‘repadmin’);
END;
/
PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

[oracle@localhost ~]$ sqlplus repadmin/repadmin@repa
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Sep 12 09:26:00 2008

SQL> BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => ‘SYSDATE + 1/24′,
delay_seconds => 0);
END;
/
PL/SQL procedure successfully completed.

Setup at site repB

[oracle@localhost ~]$ sqlplus system/repb@repb
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Sep 12 09:24:00 2008
SQL> CREATE USER repadmin IDENTIFIED BY repadmin;
User created.
SQL> show parameter global_names
NAME TYPE VALUE
———————————— ———– ——————————
global_names boolean FALSE
SQL> alter system set global_names=TRUE;
System altered.
SQL> show parameter job_queue_processes
NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 1000

SQL> alter system set job_queue_processes = 1;
System altered.

SQL> BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (username => ‘repadmin’);
END;
/
PL/SQL procedure successfully completed.

SQL> GRANT SELECT ANY DICTIONARY TO repadmin;
Grant succeeded.

SQL> BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => ‘repadmin’);
END;
/
PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => ‘repadmin’,
privilege_type => ‘receiver’,
list_of_gnames => NULL);
END;
/
PL/SQL procedure successfully completed.

[oracle@localhost ~]$ sqlplus repadmin/repadmin@repb
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Sep 12 09:26:00 2008
SQL> BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => ‘SYSDATE + 1/24′,
delay_seconds => 0);
END;
/< br >
PL/SQL procedure successfully completed.
I had problems with above settings mainly delay_second. Please read more at www.dbspecialists.com/files/presentations/mm_replication.html

PL/SQL procedure successfully completed.
Create Databse links needed between RepA and repB sitesREPB
[oracle@localhost ~]$ sqlplus system/repa@repa
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Sep 12 09:30:52 2008
SQL> create public database link repb using ‘repb’;
Database link created.

[oracle@localhost ~]$ sqlplus repadmin/repadmin@repa
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Sep 12 09:31:18 2008

SQL> create database link repb connect to repadmin identified by repadmin;
Database link created.
REPB [oracle@localhost ~]$ sqlplus system/repb@repb
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Sep 12 09:30:52 2008

SQL> create public database link repa using ‘repa’;
Database link created.
[oracle@localhost ~]$ sqlplus repadmin/repadmin@repb
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Sep 12 09:31:18 2008

SQL> create database link repa connect to repadmin identified by repadmin;
Database link created.

Step 2> Define a schedule for each database link to create scheduled links.
Create a scheduled link by defining a database link when you execute the SCHEDULE_PUSH procedure. The scheduled link determines how often your deferred transaction queue is propagated to each of the other master sites. You need to execute the SCHEDULE_PUSH procedure for each database link that you created in Step 1. The database link is specified in the destination parameter of the SCHEDULE_PUSH procedure.

[oracle@localhost ~]$ sqlplus repadmin/repadmin@repa

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘repb’,
interval => ‘SYSDATE + (1/144)’,
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500);
END;
/

[oracle@localhost ~]$ sqlplus repadmin/repadmin@repb

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘repa’,
interval => ‘SYSDATE + (1/144)’,
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500);
END;
/

Use “Create Master Group” to hold replication objects. I am going to use the default SCOTT schema with following 2 tables DEPT & EMP

For release 10g and up, unlock scott account using
repA> alter user scott account unlock;
User altered.
$sqlplus repadmin/repadmin@repa
repA> begin
dbms_repcat.create_master_repgroup (
gname => ‘scott_repg’);
end;
/
Follow following steps at RepA Master Definition Site
Add objects to master group. (only at RepA)
repA> BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ‘scott_repg’,
type => ‘TABLE’,
oname => ‘EMP’,
sname => ‘scott’,
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/
repA> BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ‘scott_repg’,
type => ‘TABLE’,
oname => ‘DEPT’,
sname => ‘scott’,
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

Add additional master site (only at RepA)
repA>BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => ‘scott_repg’,
master => ‘repb’,
use_existing_objects => TRUE,
copy_rows => FALSE,
propagation_mode => ‘ASYNCHRONOUS’);
END;
/
PL/SQL procedure successfully completed.
You should wait until repB appears in the DBA_REPSITES view before continuing.
SQL> conn repadmin/repadmin
Connected.
SQL> SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = ‘SCOTT_REPG’;
DBLINK
———————————————————————-

REPA
REPB

In this example, the use_existing_objects parameter in the ADD_MASTER_DATABASE procedure is set to TRUE because it is assumed that the scott schema already exists at all master sites.

Step 3> Generate replication support – Site A.
repA>BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘scott’,
oname => ‘emp’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/
PL/SQL procedure successfully completed.

repB>BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'scott',
oname => 'dept',
type => 'TABLE',
min_communication => TRUE);
END;
/

Step 4> Start Replication & monitor the changes
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => 'scott_repg');
END;
/

Check Replication Status
select sname, master, status from sys.dba_repcat;

SNAME MASTER STATUS
SCOTT_REPG Y NORMAL

Make sure STATUS is not returned as QUISCED

If Status <> ‘NORMAL’ then Check for Replication Errors

SELECT ONAME, REQUEST, MESSAGE FROM dba_repcatlog WHERE status= ‘ERROR’

Leave a Reply

You must be logged in to post a comment.

Top of Page

Top menu