3
Create a Master Group
This chapter illustrates how to create a master group at a master replicat ion site.

This chapter contains these topics:

Overview of Creating a Master Group

Creating a Master Group

After you have set up your master sites, you are ready to build a master group. As illustr ated in Figure 3-2, you need to follow a specific sequence to successfully build a replicatio n environment.

See Also:
"Create Replication Site" fo r information about setting up master sites

In this chapter, you cr eate the `hr_repg` master group and replicate the objects illustrated in Figure 3-1.

Figure 3-1 Replicate the Tables in the hr Schema Between All Sites

Text description of rarmasta.gif follows

Text description of the illustration rarmasta.gif

Before You Start

In order for the script in this chapter to work as designed, it is assumed that the hr schema exists at orc1.world, orc2.world, and orc3.world. The hr schema includes the following database objects:

countries table
departments table
employees table
jobs table
job_history table
loca tions table
regions table
dept_location_ix index
emp_departmen t_ix index
emp_job_ix index
emp_manager_ix index
jhist_departm ent_ix index
jhist_employee_ix index
jhist_job_ix index
loc_co untry_ix index

The indexes listed are the indexes based on foreign key columns in the hr schema. When replicating tables with foreign key referential constraints, Oracle Corporation recommends that you always index foreign key columns and replicate these indexes, unless no updates and deletes are allowed in the parent table. Inde xes are not replicated automatically.

By default, the hr schema is installed a utomatically when you install an Oracle database. The example script in this chapter assumes that the hr schema exists at all master sites and that the schema contains all of these database objects at each site. The example script also assumes that the tables contai n the data that is inserted automatically during Oracle installation. If the hr schema is not installed at your replicat ion sites, then you can install it manually.

Figure 3-2 Creating a Master Group

Text description of rarmast2.gif
follows

Text description of the illustration rarmast2.gif

See Also:

Oracle Database Sample Schemas for infor mation about the hr schema and the other sample schemas, and for information about installing the sample schemas manuall y

Creating a Master Group

Complete the following step s to create the hr_repg master group.

Note:

If you are viewing this document online, then you can copy the t ext from the "BEGINNING OF SCRIPT" line after this note to the "END OF SCRIPT" line into a text ed itor and then edit the text to create a script for your environment.

/************************* BEGINNING OF SCRIPT ******************************/

SE
T ECHO ON

SPOOL create_mg.out

CONNECT repad
min/repadmin@orc1.world

/*

Step 1 Create the schema at master sites.

If the schema does not already exist at all of the master sites participating in the master group, then create the schema now and grant it all of the necessary privileges. This example uses the hr schema, which is one of the sample schemas that are installed by defa ult when you install Oracle. So, the hr schema should exist at all master sites.

<
/a>*/

PAUSE Press <RETURN> to continue when the schema exists at all master sites.


/*

Step 2 Create the master group.

Use the CREATE_MASTER_REPGROUP pr ocedure to define a new master group. When you add an object to your master group or perform other replication administrative tasks, you reference the master group name defined during this step. This step must be completed by the replication administrator.

*/

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

/*

Step 3 Add objects to master group.

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 y our master group, but you can also add procedures, views, synonyms, and so on.

*/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'hr_repg',
      type => 'TABLE',
      onam
e => 'countries',
      sname => 'hr',
      use_existing_object => TRUE,
      copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

   gname => 'hr_repg',
      type => 'TABLE',
      oname => 'departments',
      sname => 'hr',
      use_existing_object => TRUE,
      cop
y_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'hr_repg',

      type => 'TABLE',
      oname => 'employees',
      sna
me => 'hr',
      use_existing_object => TRUE,
      copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'hr_repg',

  type => 'TABLE',
      oname => 'jobs',
      sname => 'hr',
      use_existing_object => TRUE,
      copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE
_MASTER_REPOBJECT (
      gname => 'hr_repg',
      type => 'TABLE',
      oname => 'job_history',
      sname => 'hr',
      use_existing_o
bject => TRUE,
      copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
<
a name="1004822">      gname => 'hr_repg',
      type => 'TABLE',
      oname =
> 'locations',
      sname => 'hr',
      use_existing_object => TRUE,
      copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

gname => 'hr_repg',
      type => 'TABLE',
      oname => 'regions',
      sname => 'hr',
      use_existing_object => TRUE,
      copy_rows
=> FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'hr_repg',
      type => 'INDEX',
      oname => 'dept_location_ix',
      sna
me => 'hr',
      use_existing_object => TRUE,
      copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'hr_repg',

  type => 'INDEX',
      oname => 'emp_department_ix',
      sname => 'hr',
      use_existing_object => TRUE,
      copy_rows => FALSE);
END
;
/

BEGIN
DBMS_
REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'hr_repg',
      type => 'INDEX
',
      oname => 'emp_job_ix',
      sname => 'hr',
      us
e_existing_object => TRUE,
      copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOB
JECT (
      gname => 'hr_repg',
      type => 'INDEX',

     oname => 'emp_manager_ix',
      sname => 'hr',
      use_existing_object =>
; TRUE,
      copy_rows => FALSE);
END;
/


BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'hr_repg',
      type => 'INDEX',
      oname => 'jhis
t_department_ix',
      sname => 'hr',
      use_existing_object => TRUE,
      copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (

gname => 'hr_repg',
      type => 'INDEX',
      oname => 'jhist_employee_ix',
<
a name="1004923">      sname => 'hr',
      use_existing_object => TRUE,

copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'hr_rep
g',
      type => 'INDEX',
      oname => 'jhist_job_ix',

   sname => 'hr',
      use_existing_object => TRUE,
      copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'hr_repg',
      type => 'INDEX',
      oname => 'loc_country_ix',
      sname => 'hr',
<
a name="1004952">      use_existing_object => TRUE,
      copy_rows => FALSE);

END;
/

/*

Step 4 Add additional master sites.

After you have defined your master group at the master definition site (the site where the master group was created becomes the master definition s ite by default), you can define the other sites that will participate in the replication environment. You might have guessed that you will be adding the orc2.world and orc3.world sites to the replication environment. This example creates th e master group at all master sites, but you have the option of creating the master group at one master site now and adding additional master sites later without quiescing the database. In this case, you can skip this step.

See Also:

"Adding New Master Sites Without Quiescing the Master Group" for more information< /p>

In this example, the use_existing_objects parameter in the ADD_MASTER_DATABASE procedure is set to TRUE because it is assumed that the hr schema already exists a t all master sites. In other words, it is assumed that the objects in the hr schema are precreated at all master sites. Also, the copy_rows parameter is set to FALSE because it is assumed that the identical data is stored in th e tables at each master site.

Note:
When adding a master site to a master group that contains tables with circular depend encies or a table that contains a self-referential constraint, you must precreate the table definitions and manually load the data at the new master site. The following is an example of a circular dependency: Table A has a foreign key constraint on table B, and tabl e B has a foreign key constraint on table A.

*/

BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
      gname => 'hr_repg',
      master => 'orc2.world',
      use_ex
isting_objects => TRUE,
      copy_rows => FALSE,
      propagation_mode => 'ASY
NCHRONOUS');
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 DBLINK FROM DBA_REPSITES WHERE GNAME = 'HR_REPG';

*/

PAUSE Press <RETURN> to continue.


BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
      gname => 'hr_repg',
      master => 'orc3.world',
      use_existing_o
bjects => TRUE,
      copy_rows => FALSE,
      propagation_mode => 'ASYNCHRONOU
S');
END;
/

/*

Note:

You shou ld wait until orc3.world appears in the DBA_REPSITES view before continuing. Execute the following SE LECT statement in another SQL*Plus session to make sure that orc3.world has appeared:

SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = 'HR_REPG';

*/

PAUSE Press <RETURN> to continue.

/*

Step 5 If conflicts are p ossible, then configure conflict resolution methods.

Caution:

If you added one or more tables to a master group d uring creation of the group, then do not resume replication activity immediately. First consider the possibility of replication confl icts, and configure conflict resolution for the replicated tables in the group.

See Also:

Chapter 6, "Configure Conflict Resolution" for information about configuring conflict resol ution methods

*/

PAUSE
 Press <RETURN> to continue after configuring conflict resolution methods 
or if no conflict resolution methods are required.
<
a name="1005053">
/*

Step 6 Generate replication support.

*/

BEGIN 
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (

     sname => 'hr',
      oname => 'countries', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

BEGIN 
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'hr',
      oname => 'departments', 
      type => 'TABL
E',
      min_communication => TRUE); 
END;
/


BEGIN 
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
<
a name="1005094">      sname => 'hr',
      oname => 'employees', 
      type =
> 'TABLE',
      min_communication => TRUE); 
END;
/

BEGIN 
DBMS_REPCAT.GENERATE_REPLICATION_SUPPO
RT (
      sname => 'hr',
      oname => 'jobs', 
      t
ype => 'TABLE',
      min_communication => TRUE); 
END;
/

BEGIN 
DBMS_REPCAT.GENERATE_REPLICATION_
SUPPORT (
      sname => 'hr',
      oname => 'job_history', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

BEGIN 
DBMS_REPCAT.GENERATE_
REPLICATION_SUPPORT (
      sname => 'hr',
      oname => 'locations', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

BEGIN 
DBMS_REPCAT
.GENERATE_REPLICATION_SUPPORT (
      sname => 'hr',
      oname => 'regions',

      type => 'TABLE',
      min_communication => TRUE); 
EN
D;
/

/*

Note:

You should wait until the DBA_R EPCATLOG 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 = 'HR_REPG';

*/

PAUSE Pre
ss <RETURN> to continue.

/*

Step 7 Start replication.

After creating your ma ster group, adding replication objects, generating replication support, and adding additional master databases, you need to start rep lication activity. Use the RESUME_MASTER_ACTIVITY procedure to "turn on" replication for the specified master group.

*/

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

SET ECHO OFF

SPOOL
OFF

/************************* END OF SCRIPT **********************************/