3
Create a Master Group

This chapter illustrates how to create a master group at a master replication site. The following topics will be discussed:

Creating a Master Group Overview

After you have setup your master site (see "Create Replication Site" for details), you are ready to begin building a master group. As illustrated in Figure 3-2, there is a distinct sequence that you need to follow to successfully build a replicated environment.

This chapter will create the SCOTT_MG master group and will replicate the objects illustrated in Figure 3-1:

Figure 3-1 Replicate EMP, DEPT, BONUS, and SALGRADE between all sites.

Before You Start

In order for the script in this chapter to work as designed, it is assumed that the schema SCOTT exists at ORC1.WORLD (and optionally ORC2.WORLD and ORC3.WORLD) and contains the following objects:

EMP
DEPT
BONUS
SALGRADE

If you don't have the SCOTT schema at ORC1.WORLD or the SCOTT objects do not exist, you can run a script that comes with your Oracle database to create the sample schema SCOTT and the corresponding objects.

Complete the following:

Connect to ORC1.WORLD as user SYSTEM.

CONNECT system/manager@orc1.world
If the SCOTT schema does exist, skip to step 3. Otherwise, create the user SCOTT as illustrated below:

CREATE USER scott IDENTIFIED BY tiger;

Run the SCOTT.SQL script that is contained in your <ORACLE_HOME>\RDBMS\ADMIN directory.

The schema SCOTT must exist (and be IDENTIFIED BY tiger) in order for this script to run properly. If it does not exist, be sure that you complete step 2.

Note:
If you are running multiple database instances on the same computer, you may need to alter the CONNECT string contained within the SCOTT.SQL script to contain the target database. For example, you would replace
CONNECT scott/tiger
with
CONNECT scott/tiger@orc1.world

After you have completed the three steps above, you will have a "fresh" copy of the EMP, DEPT, BONUS, and SALGRADE tables. The tables EMP and DEPT will have a primary key created during this script, though BONUS and SALGRADE will not have a primary key.

Figure 3-2 Create Master Group

Create Master Group

/*************************************************************************
STEP 1:
CREATE SCHEMA AT MASTER SITES
*************************************************************************/

CONNECT system/manager@orc2.world;
CREATE USER scott IDENTIFIED BY tiger;
GRANT CONNECT, RESOURCE TO scott;

CONNECT system/manager@orc3.world;
CREATE USER scott IDENTIFIED BY tiger;
GRANT CONNECT, RESOURCE TO scott;

/*************************************************************************
STEP 2:
CREATE MASTER GROUP
*************************************************************************/

--Use the CREATE_MASTER_REPGROUP API to define a new master group.
--When you add an object to your master group or perform other replication
--administrative tasks, you will reference the master group name defined
--during this step. The following must be executed by the replication
--administrator.

CONNECT repadmin/repadmin@orc1.world

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPGROUP (
      gname => 'SCOTT_MG');
END;
/

/*************************************************************************
STEP 3:
ADD OBJECTS TO MASTER GROUP
*************************************************************************/

--Use the CREATE_MASTER_REPOBJECT API to an object to your master group.
--In most cases, you will probably be adding tables to your master group,
--but you can also add indexes, procedures, views, synonyms, etc.  See
--CREATE_MASTER_REPOBJECT procedure for additional
--information. 

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'SCOTT_MG',
      type => 'TABLE',
      oname => 'EMP',
      sname => 'SCOTT',
      use_existing_object => TRUE,
      copy_rows => TRUE);
END;
/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'SCOTT_MG',
      type => 'TABLE',
      oname => 'dept',
      sname => 'SCOTT',
      use_existing_object => TRUE,
      copy_rows => TRUE);
END;
/

--If you will recall from the "Before You Start" section, there
--is no primary key for the BONUS or SALGRADE tables. In order for 
--replication to work properly, the replicated table either needs a 
--primary key or to have a "set column." The DBMS_REPCAT.SET_COLUMNS procedure 
is
--sufficient for multimaster replication only, but if you will also 
--support fast refreshable snapshots, you will need a primary key.
--It is easier to alter your object before you add it to your master group.

ALTER TABLE scott.bonus ADD (CONSTRAINT bonus_pk PRIMARY KEY(ename));

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'SCOTT_MG',
      type => 'TABLE',
      oname => 'bonus',
      sname => 'SCOTT',
      use_existing_object => TRUE,
      copy_rows => TRUE);
END;
/

--You will need to modify the SCOTT.SALGRADE object just as you altered the
--SCOTT.BONUS object in the previous step..

ALTER TABLE scott.salgrade ADD (CONSTRAINT salgrade_pk PRIMARY KEY(grade));

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'SCOTT_MG',
      type => 'TABLE',
      oname => 'salgrade',
      sname => 'SCOTT',
      use_existing_object => TRUE,
      copy_rows => TRUE);
END;
/

/*************************************************************************
STEP 4:
ADD ADDITIONAL MASTER SITES
*************************************************************************/

--After you have defined your master group at the MASTERDEF site (the
--site where the master group was created becomes the MASTER DEFINITION
--site by default), you can define the other sites that will participate
--in the replicated environment. You might have guessed that we will be
--adding the ORC2.WORLD and ORC3.WORLD sites to our replicated environment.

BEGIN
   DBMS_REPCAT.ADD_MASTER_DATABASE (
      gname => 'SCOTT_MG',
      master => 'ORC2.WORLD',
      use_existing_objects => TRUE,
      copy_rows => TRUE,
      propagation_mode => 'ASYNCHRONOUS');
END;
/

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

SELECT * FROM dba_repsites WHERE gname = 'SCOTT_MG';
*/
PAUSE Press <RETURN> to continue.
BEGIN
   DBMS_REPCAT.ADD_MASTER_DATABASE (
      gname => 'SCOTT_MG',
      master => 'ORC3.WORLD',
      use_existing_objects => TRUE,
      copy_rows => TRUE,
      propagation_mode => 'ASYNCHRONOUS');
END;
/

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

SELECT * FROM dba_repsites WHERE gname = 'SCOTT_MG';
*/
PAUSE Press <RETURN> to continue.

/*************************************************************************
STEP 5:
GENERATE REPLICATION SUPPORT
*************************************************************************/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'EMP', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

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

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'bonus', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'salgrade', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

/*
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 * FROM dba_repcatlog WHERE gname = 'SCOTT_MG';
*/
PAUSE Press <RETURN> to continue.

/*************************************************************************
STEP 6:
RESUME REPLICATION
*************************************************************************/

--After you have completed creating your master group, adding replication
--objects, generating replication support, and adding additional master
--databases, you need to resume replication activity. The 
--RESUME_MASTER_ACTIVITY procedure API will "turn on" replication for 
--the specified master group.

BEGIN 
   DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'SCOTT_MG'); 
END;
/

3Create a Master Group