Multi-Master Replication

Multi-Master(Advance Replication) Step by Step Configuration


Advanced Replication is the process of copying and maintaining database objects, such as tables, in multiple databases that make up a distributed database system. However, Streams is the preferred way to set up Replication in Oracle databases. Following are the replication types


Multi-Master Replication

Multi-Master replication comprises of multiple master sites equally participating in an update-anywhere model. Updates made to an individual master site are propagated (sent) to all other participating master sites using database links.

Error Transactions
These are the deferred transactions from another master site or materialized view site that could not be applied successfully at the current master site. User can remove, retry or see the details of the error in the error transaction page.

Deferred TransactionsThese are the data manipulation language (DML) changes on a table which are stored in a queue in an asynchronous replication environment. The deferred transactions are propagated to the target destinations at scheduled intervals or on-demand. User can remove, push the transaction manually, or see the details of the transaction in Deferred transaction page

Scheduled Links
A scheduled link is a database link with a user-defined schedule to push deferred transactions. It determines how a master site propagates its deferred transactions to another master site, or how a materialized view site propagates its deferred transactions to its master site. User can schedule each link and also set the additional options like propagation, stop on error etc. on the Scheduled link page. Following are main steps required for multi-mater replication.

Deploying Multi-Master ( Advance Replication)

Databases: ORCL, HR (ORCL is master site and HR is master definition site)
Database Release: Oracle 10g, Oracle 11g

Step 1 Configure Master Sites
Step 2 Create replication administrator and grant privileges
Step 3 Register the propagator
Step 4 Register the receiver
Step 5 Schedule purge at master site
Step 6 Create Database Links between the Master Sites
Step 7 Define a schedule for each database link to create scheduled links.
Step 8 Create Master Replication Group
Step 9 Add objects to master group.
Step 10 Add Additional Master Sites
Step 11 Start Replication
Step 12 Test Replication
Step 13 Altering a Replicated Object

(1) Connect as SYSTEM at master site and set following parameters

ALTER SYSTEM SET GLOBAL_NAMES=TRUE SCOPE=SPFILE;
ALTER DATABASE RENAME GLOBAL_NAME TO HR.COMPANY.COM;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10 SCOPE=SPFILE;
ALTER USER HR ACCOUNT UNLOCK;
ALTER USER HR IDENTIFIED BY HR
Note: Repeat for each master site

(2) Create replication administrator and grant necessery privileges

SQL>CREATE USER "REPADMIN" IDENTIFIED BY REPADMIN;
Note: if using new tablespace for replication, the create user as:

SQL> CREATE USER REPADMIN IDENTIFIED BY REPLICATION
DEFAULT TABLESPACE REPLICATION
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON REPLICATION
ACCOUNT UNLOCK;
GRANT CONNECT TO REPADMIN;/
User created.

BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA(username =>'REPADMIN');
END;/
PL/SQL procedure successfully completed.

Note: This privilege for creating and managing replicatetion enviourments

GRANT COMMENT ANY TABLE,LOCK ANY TABLE,SELECT ANY DICTIONARY TO REPADMIN;/

(3) Register the propagator

BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR ( username => 'REPADMIN');
END;
/
PL/SQL procedure successfully completed.

Note: The propagator is responsible for propagating the deferred transaction queue to other master sites.

(4) Register the receiver

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

Note: The receiver receives the propagated deferred transactions sent by the propagator from other master sites

(5) Schedule purge at master site

CONNECT repadmin/repadmin@ORCL
/

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE(
next_date => SYSDATE,
interval => '/*1:Mins*/ sysdate + 1/(60*24)',
delay_seconds => 0,
rollback_segment => '');
END;/
PL/SQL procedure successfully completed.

Note: In order to keep the size of the deferred transaction queue in check, you should purge successfully completed deferred transactions. The SCHEDULE_PURGE procedure automates the purge process for you. You must execute this procedure as the replication administrator.

Note: Repeat above 6 steps in database HR sites.

(6) Create Database Links between the Master Sites

CONNECT system/dba@ORCL

CREATE PUBLIC DATABASE LINK "HR" USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.150)(PORT=1521))(CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)))'

CONNECT repadmin/repadmin@ORCL;

CREATE DATABASE LINK HR CONNECT TO REPADMIN IDENTIFIED BY REPLICATION
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.150)(PORT=1521)) (CONNECT_DATA=(SID=ORCL)(SERVER=DEDICATED)))'

CREATE DATABASE LINK "HR.COMPANY.COM" CONNECT TO "REPADMIN" IDENTIFIED BY "REPADMIN";
Note: Repeat this step for others sites (create database links for all participating sites)

(7) Define a schedule for each database link to create scheduled links.

CONNECT repadmin/repadmin@HR;
Connected.
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH(
destination => 'ORCL.COMPANY.COM',
interval => '/*1:Mins*/ sysdate + 1/(60*24)',
next_date => SYSDATE,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 0);
END;
/
PL/SQL procedure successfully completed.

CONNECT repadmin/repadmin@HO;
/
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH(
destination => 'HR.COMPANY.COM',
interval => '/*1:Mins*/ sysdate + 1/(60*24)',
next_date => SYSDATE,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 0);
END;
/
PL/SQL procedure successfully completed.

(8) Create Master Replication Group

CONNECT repadmin/repadmin@HR;Conncted.

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP(gname=>'HRGROUP');
END;
PL/SQL procedure successfully completed.

Note: This step must be completed by the replication administrator

(9) Add objects to master group.

CONNECT repadmin/repadmin@HR;/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => 'HRGROUP',
type => 'TABLE',
oname => 'EMPLOYEE',
sname => 'HR',
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => 'HRGROUP',
type => 'TABLE',
oname => 'DEPARTMENT',
sname => 'HR',
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/
PL/SQL procedure successfully completed.

Note: Use the CREATE_MASTER_REPOBJECT procedure to add an object to your master group. In most cases, you probably will be adding tables and indexes to your master group, but you can also add procedures, views, synonyms, and so on.

(10) Add Additional Master Sites

BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE(
gname => '"HRGROUP"',
master => 'HR.COMPANY.COM',
use_existing_objects => TRUE,
copy_rows => TRUE,
propagation_mode => 'ASYNCHRONOUS');
END;/
PL/SQL procedure successfully completed.

Note: You should wait until HR appears in the DBA_REPSITES view before continuing. Execute the following SELECT statement in another SQL*Plus session to make sure that HR has appeared.

CONNECT repadmin/repadmin@HR;
Connected.

SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = 'HRGROUP';

SELECT SUBSTR(GNAME,1,20)GNAME,SUBSTR(DBLINK,1,20) DBLINK,MASTERDEF, SNAPMASTER,MASTER,MY_DBLINK,SUBSTR(GROUP_OWNER,1,15)OWNER
FROM DBA_REPSITES

(11) Generate Replication Support

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'HR',
oname => 'EMPLOYEE',
type => 'TABLE',
min_communication => TRUE);
END;/
PL/SQL procedure successfully completed.

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'HR',
oname => 'DEPARTMENTS',
type => 'TABLE',
min_communication => TRUE);
END;/
PL/SQL procedure successfully completed.

Note: You should wait until the DBA_REPCATLOG view is empty before resuming master activity. Execute the following SELECT statement to monitor your DBA_REPCATLOG view:

SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = 'HRGROUP';
(12) Start replication

After creating your master group, adding replication objects, generating replication support, and adding additional master databases, you need to start replication activity.

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (gname => 'HRGROUP');
END;/
PL/SQL procedure successfully completed.

(12) Test Replication

SQL> CONNECT hr/hr@HRConnected.

SQL> SELECT * FROM departments;SQL> INSERT INTO departments values (270, 'DATA WHEARHOUSE',NULL,1700);1 row created.

SQL> commit;
Commit complete.

SQL> select * from departments;

Wait some time and connect to ORCL database and check new row replicate in departments table.

SQL> CONNECT hr/hr@ORCLConnected.

SQL> select * from departments;
Note: you can perform any DML operation on Master Definition site “HR” and commit. Changes will update in "'ORCL" site and if you perform any changes in "ORCL" db then update will changes in HR vice versa.

SQL> INSERT INTO departments values (280, 'DATA BANK',NULL,1700);SQL> SELECT COUNT(*) FROM departments;
SQL> Disconnect
SQL> CONNECT hr/hr@HR
Connected.

SQL> select * from departments;
(13) Altering a Replicated Object

Step 1 - Connect to the master definition site as the replication administrator.

SQL> CONNECT repadmin/repadmin@HRConnected.

Step 2 - If necessary, then quiesce the master group.

BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (gname => 'HRGROUP');
END;
/
PL/SQL procedure successfully completed.

Step 3 - In a separate SQL*Plus session, check the status of the master group you are quiescing.

Do not proceed until the group's status is QUIESCED.

SQL> SELECT GNAME, STATUS FROM DBA_REPGROUP;
GNAME STATUS
--------------- ---------------
HRGROUP QUIESCING


Step 4 - Alter the replicated object.

BEGIN
DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
sname => 'HR',
oname => 'DEPARTMENT',
type => 'TABLE',
ddl_text => 'ALTER TABLE HR.DEPARTMENTS ADD (timestamp DATE)');
END;
/
PL/SQL procedure successfully completed.

Note: Add Timestamp column in HR.department@HR table

Step 5 - Regenerate replication support for the altered object.

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'HR',
oname => 'DEPARTMENT',
type => 'TABLE',
min_communication => TRUE);
END;
/
PL/SQL procedure successfully completed.

Step 6 - In a separate SQL*Plus session, check if DBA_REPCATLOG is empty.

SQL> SELECT COUNT(*) FROM dba_repcatlog;
Note: Do not proceed until this view is empty.

Step 7 - Resume replication activity.

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (gname => 'scott_repg');
END;
/
PL/SQL procedure successfully completed.

Now check departments table in both database at “HR” or “ORCL”.

SQL> connect hr/hr@HRConnected.

SQL> Describe Department
SQL> CONNECT hr/hr@ORCLConnected.

SQL> Describe Department
Step 11 Listing the Master Sites Participating in a Master Group

SQL> connect repadmin/repadmin@HRConnected.

COLUMN GNAME HEADING 'Master Group' FORMAT A20
COLUMN DBLINK HEADING 'Sites' FORMAT A25
COLUMN MASTERDEF HEADING 'MasterDefinitionSite?' FORMAT A10
SELECT GNAME, DBLINK, MASTERDEF
FROM DBA_REPSITES WHERE MASTER = 'Y'
AND GNAME NOT IN (SELECT GNAME FROM DBA_REPSITES WHERE SNAPMASTER = 'Y')
ORDER BY GNAME;

 Master
Definition
Master Group   Sites Site?
---------------  --------------------------- -----------
HRGROUP      ORCL.COMPANY.COM N
HRGROUP      HR.COMPANY.COM Y