2
Create Replication Site

This chapter illustrates how to setup both a master and a snapshot replication site using the Replication Management API. The following topics will be discussed:

• Setting Up a Replication Site Overview

• Setup Master Site

• Setup Snapshot Site

Setting Up a Replication Site Overview

Before you begin building your replicated environment, you need to setup the sites that will participate in the replicated environment. As illustrated in Figure 2-2 and Figure 2-3, there are separate processes for setting up a master site versus setting up a snapshot site.

This chapter assumes that you have the following:

• 4 Databases:
  • ORC1.WORLD

  • ORC2.WORLD

  • ORC3.WORLD

  • SNAP1.WORLD

Chapters 2 -- 6 will work with the replication environment illustrated in Figure 2-1 that you will begin to create during this chapter.

Figure 2-1 Three Master Sites and One Snapshot Site

You will need to follow the procedures identified in Figure 2-2 when you build a new master site or in Figure 2-3 when you build a new snapshot site.

Figure 2-2 Setup Master Sites

Setup Master Site

/*************************************************************************
STEP 1 @ ORC1.WORLD:
CONNECT AS SYSTEM AT MASTER SITE
*************************************************************************/

--You will need to connect as SYSTEM to the database that you want to 
--setup for replication. After you setup ORC1.WORLD, you will need to
--begin again with STEP 1 for sites ORC2.WORLD and 
--ORC3.WORLD.

CONNECT SYSTEM/MANAGER@ORC1.WORLD

/*************************************************************************
STEP 2 @ ORC1.WORLD:
CREATE REPLICATION ADMINISTRATOR
*************************************************************************/

--The replication administrator will be granted the necessary privileges
--to create and manage a replicated environment. The replication 
--administrator needs to be created at each database that will participate
--in the replicated environment.

CREATE USER repadmin IDENTIFIED BY repadmin;

/*************************************************************************
STEP 3 @ ORC1.WORLD: 
GRANT PRIVILEGES TO REPLICATION ADMINISTRATOR

For additional information about the GRANT_ADMIN_ANY_SCHEMA API, see "GRANT_
ADMIN_ANY_SCHEMA procedure".
*************************************************************************/

--Executing the GRANT_ADMIN_ANY_SCHEMA API will grant the replication
--administrator powerful privileges to create and manage a replicated
--environment.

BEGIN
   DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
      USERNAME => 'repadmin');
END;
/

--If you want your REPADMIN to be able to create snapshot logs for any 
--replicated table, grant COMMENT ANY TABLE and LOCK ANY TABLE to REPADMIN.
/*************************************************************************
STEP 4 @ ORC1.WORLD: 
REGISTER PROPAGATOR

For additional information about the REGISTER_PROPAGATOR API, see 
"REGISTER_PROPAGATOR procedure".
*************************************************************************/

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

BEGIN
   DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
      USERNAME => 'repadmin');
END;
/

/*************************************************************************
STEP 5 @ ORC1.WORLD:
REGISTER RECEIVER

For additional information about the REGISTER_USER_REPGROUP API, 
see "REGISTER_USER_REPGROUP procedure".
*************************************************************************/

--The receiver will receive the propagated deferred transactions sent
--by the propagator from other master sites.

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

/*************************************************************************
STEP 6 @ ORC1.WORLD:
SCHEDULE PURGE AT MASTER SITE

For additional information about the SCHEDULE_PURGE API, see "SCHEDULE_PURGE 
procedure".
*************************************************************************/

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

CONNECT repadmin/repadmin@orc1.world

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PURGE (
      next_date => SYSDATE,
      interval => 'sysdate + 1/24',
      delay_seconds => 0,
      rollback_segment => '');
END;
/

/*************************************************************************
STEP 7:
CREATE MASTER SITE USERS
*************************************************************************/

--STEP 7a: CREATE PROXY SNAPSHOT ADMINISTRATOR
--The proxy snapshot administrator performs tasks at the target master
--site on behalf of the snapshot administrator at the snapshot
--site.  See "Security Setup for Snapshot Replication" in the
--Oracle8i Replication manual.

CONNECT system/manager@orc1.world

CREATE USER proxy_snapadmin IDENTIFIED BY proxy_snapadmin;

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

--STEP 7b: CREATE PROXY REFRESHER
--The proxy refresher performs tasks at the master site on behalf of
--the refresher at the snapshot site.

CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;

GRANT CREATE SESSION TO proxy_refresher;
GRANT SELECT ANY TABLE TO proxy_refresher;

/*************************************************************************
STEP 1  @ ORC2.WORLD:
CONNECT AS SYSTEM
*************************************************************************/

--NOTE:
--Multiple master sites (multimaster replication) can only be used with 
--Oracle8i Enterprise Edition. If you are not using Oracle8i Enterprise
--Edition, skip to step 8. 

--You will need to connect as SYSTEM to the database that you want to 
--setup for replication. After you setup ORC2.WORLD, you will need to
--begin again with STEP 1 for site ORC3.WORLD.

connect SYSTEM/MANAGER@ORC2.WORLD

/*************************************************************************
STEP 2  @ ORC2.WORLD:
CREATE REPLICATION ADMINISTRATOR
*************************************************************************/

--The replication administrator will be granted the necessary privileges
--to create and manage a replicated environment. The replication 
--administrator needs to be created at each database that will participate
--in the replicated environment.

create user REPADMIN identified by REPADMIN;

/*************************************************************************
STEP 3  @ ORC2.WORLD: 
GRANT PRIVILEGES TO REPLICATION ADMINISTRATOR

For additional information about the GRANT_ADMIN_ANY_SCHEMA API, see "GRANT_
ADMIN_ANY_SCHEMA procedure".
*************************************************************************/

--Executing the GRANT_ADMIN_ANY_SCHEMA API will grant the replication
--administrator powerful privileges to create and manage a replicated
--environment.

BEGIN
   DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
      username => 'REPADMIN');
END;
/

--If you want your REPADMIN to be able to create snapshot logs for any 
--replicated table, grant COMMENT ANY TABLE and LOCK ANY TABLE to REPADMIN.
/*************************************************************************
STEP 4  @ ORC2.WORLD: 
REGISTER PROPAGATOR

For additional information about the REGISTER_PROPAGATOR API, see "REGISTER_
PROPAGATOR procedure".
*************************************************************************/

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

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

/*************************************************************************
STEP 5  @ ORC2.WORLD:
REGISTER RECEIVER

For additional information about the REGISTER_USER_REPGROUP API, see "REGISTER_
USER_REPGROUP procedure".
*************************************************************************/

--The receiver will receive the propagated deferred transactions sent
--by the propagator from the other master sites.

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

/*************************************************************************
STEP 6  @ ORC2.WORLD:
SCHEDULE PURGE AT MASTER SITE

For additional information about the SCHEDULE_PURGE API, see "SCHEDULE_PURGE 
procedure".
*************************************************************************/

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

CONNECT repadmin/repadmin@orc2.world

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PURGE (
      next_date => SYSDATE,
      interval => 'sysdate + 1/24',
      delay_seconds => 0,
      rollback_segment => '');
END;
/

/*************************************************************************
STEP 1  @ ORC3.WORLD:
CONNECT AS SYSTEM
*************************************************************************/

--NOTE:
--Multiple master sites (multimaster replication) can only be used with 
--Oracle8i Enterprise Edition. If you are not using Oracle8i Enterprise
--Edition, skip to step 8. 

--You will need to connect as SYSTEM to the database that you want to 
--setup for replication.

connect SYSTEM/MANAGER@ORC3.WORLD

/*************************************************************************
STEP 2 @ ORC3.WORLD:
CREATE REPLICATION ADMINISTRATOR
*************************************************************************/

--The replication administrator will be granted the necessary privileges
--to create and manage a replicated environment. The replication 
--administrator needs to be created at each database that will participate
--in the replicated environment.

create user REPADMIN identified by REPADMIN;

/*************************************************************************
STEP 3 @ ORC3.WORLD: 
GRANT PRIVILEGES TO REPLICATION ADMINISTRATOR

For additional information about the GRANT_ADMIN_ANY_SCHEMA API, see "GRANT_
ADMIN_ANY_SCHEMA procedure".
*************************************************************************/

--Executing the GRANT_ADMIN_ANY_SCHEMA API will grant the replication
--administrator powerful privileges to create and manage a replicated
--environment.

BEGIN
   DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
      username => 'REPADMIN');
END;
/

--If you want your REPADMIN to be able to create snapshot logs for any 
--replicated table, grant COMMENT ANY TABLE and LOCK ANY TABLE to REPADMIN.

/*************************************************************************
STEP 4 @ ORC3.WORLD: 
REGISTER PROPAGATOR

For additional information about the REGISTER_PROPAGATOR API, see "REGISTER_
PROPAGATOR procedure".
*************************************************************************/

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

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

/*************************************************************************
STEP 5 @ ORC3.WORLD:
REGISTER RECEIVER

For additional information about the REGISTER_USER_REPGROUP API, see "REGISTER_
USER_REPGROUP procedure".
*************************************************************************/

--The receiver will receive the propagated deferred transactions sent
--by the propagator from the other master sites.

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

/*************************************************************************
STEP 6 @ ORC3.WORLD:
SCHEDULE PURGE AT MASTER SITE

For additional information about the SCHEDULE_PURGE API, see "SCHEDULE_PURGE 
procedure".
*************************************************************************/

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

CONNECT repadmin/repadmin@orc3.world

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PURGE (
      next_date => SYSDATE,
      interval => 'sysdate + 1/24',
      delay_seconds => 0,
      rollback_segment => '');
END;
/

/*************************************************************************
STEP 8:
CREATE DATABASE LINKS BETWEEN MASTER SITES

The database links provide the necessary distributed mechanisms to allow
the different replication sites to replicate data between themselves. See
the Oracle8i Distributed Database Systems for more information.
*************************************************************************/

--Before you create any private database links, you need to create the 
--public database links that each private database link will use.
--You will then need to create a database link between all replication
--administrators at each of the master sites that you have setup.

CONNECT system/manager@orc1.world
CREATE PUBLIC DATABASE LINK orc2.world USING 'orc2.world';
CREATE PUBLIC DATABASE LINK orc3.world USING 'orc3.world';

CONNECT repadmin/repadmin@orc1.world
CREATE DATABASE LINK orc2.world CONNECT TO repadmin IDENTIFIED BY repadmin;
CREATE DATABASE LINK orc3.world CONNECT TO repadmin IDENTIFIED BY repadmin;

CONNECT system/manager@orc2.world
CREATE PUBLIC DATABASE LINK orc1.world USING 'orc1.world';
CREATE PUBLIC DATABASE LINK orc3.world USING 'orc3.world';

CONNECT repadmin/repadmin@orc2.world
CREATE DATABASE LINK orc1.world CONNECT TO repadmin IDENTIFIED BY repadmin;
CREATE DATABASE LINK orc3.world CONNECT TO repadmin IDENTIFIED BY repadmin;

CONNECT system/manager@orc3.world
CREATE PUBLIC DATABASE LINK orc1.world USING 'orc1.world';
CREATE PUBLIC DATABASE LINK orc2.world USING 'orc2.world';

CONNECT repadmin/repadmin@orc3.world
CREATE DATABASE LINK orc1.world CONNECT TO repadmin IDENTIFIED BY repadmin;
CREATE DATABASE LINK orc2.world CONNECT TO repadmin IDENTIFIED BY repadmin;

/*************************************************************************
STEP 9:
CREATE SCHEDULED LINKS

Create a scheduled link by defining a database link when you execute the
SCHEDULE_PUSH procedure (see "SCHEDULE_PUSH procedure" 
for more information).
*************************************************************************/

--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 7; the database link is specified in the DESTINATION parameter
--of the SCHEDULE_PUSH procedure.

CONNECT repadmin/repadmin@orc1.world

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'ORC2.WORLD',
      interval => 'SYSDATE + 10 / (24 * 60)',
      next_date => SYSDATE);
END;
/

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'ORC3.WORLD',
      interval => 'SYSDATE + 10 / (24 * 60)',
      next_date => SYSDATE);
END;
/

CONNECT repadmin/repadmin@orc2.world

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'ORC1.WORLD',
      interval => 'SYSDATE + 10 / (24 * 60)',
      next_date => SYSDATE);
END;
/

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'ORC3.WORLD',
      interval => 'SYSDATE + 10 / (24 * 60)',
      next_date => SYSDATE);
END;
/

CONNECT repadmin/repadmin@orc3.world

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'ORC1.WORLD',
      interval => 'SYSDATE + 10 / (24 * 60)',
      next_date => SYSDATE);
END;
/

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'ORC2.WORLD',
      interval => 'SYSDATE + 10 / (24 * 60)',
      next_date => SYSDATE);
END;
/

Setup Snapshot Site

Figure 2-3 Setup Snapshot Sites

/*************************************************************************
STEP 1:
CONNECT AS SYSTEM AT SNAPSHOT SITE
*************************************************************************/
--You will need to connect as SYSTEM to the database that you want to 
--setup as a snapshot site.

CONNECT system/manager@snap1.world

/*************************************************************************
STEP 2:
CREATE SNAPSHOT SITE USERS
*************************************************************************/

--There are several users that need to be created at the snapshot site.
--These users are:
--  SNAPSHOT ADMINISTRATOR
--  PROPAGATOR
--  REFRESHER

--STEP 2a: CREATE SNAPSHOT ADMINISTRATOR
--The snapshot administrator is responsible for creating and managing
--the snapshot site. Execute the GRANT_ADMIN_ANY_SCHEMA
--procedure to grant the snapshot administrator the appropriate privileges.

create user SNAPADMIN identified by SNAPADMIN;

BEGIN
   DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
      username => 'SNAPADMIN');
END;
/

--STEP 2b: CREATE PROPAGATOR
--The propagator is responsible for propagating the deferred transaction
--queue to the target master site.

CREATE USER propagator IDENTIFIED BY propagator;

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

--STEP 2c: CREATE REFRESHER
--The refresher is responsible for "pulling" changes made to the replicated
--tables at the target master site to the snapshot site.

create user REFRESHER identified by REFRESHER;

GRANT CREATE SESSION TO refresher;
GRANT ALTER ANY SNAPSHOT TO refresher;

/*************************************************************************
STEP 3:
CREATE DATABASE LINKS TO MASTER SITE
*************************************************************************/

--STEP 3A: CREATE PUBLIC DATABASE LINK

CONNECT system/manager@snap1.world

CREATE PUBLIC DATABASE LINK orc1.world USING 'orc1.world';

--STEP 3b: CREATE SNAPSHOT ADMINISTRATOR DATABASE LINK
--You need to create a database link from the snapshot administrator at
--the snapshot site to the proxy snapshot administrator at
--the master site.

CONNECT snapadmin/snapadmin@snap1.world;

CREATE DATABASE LINK orc1.world 
  CONNECT TO proxy_snapadmin IDENTIFIED BY proxy_snapadmin;

--STEP 3c: CREATE PROPAGATOR/RECEIVER DATABASE LINK
--You need to create a database link from the propagator at the
--snapshot site to the receiver at the master site (the receiver was defined
--when you created the master group - see "REGISTER RECEIVER"
--for more information).

CONNECT propagator/propagator@snap1.world

CREATE DATABASE LINK orc1.world 
  CONNECT TO repadmin IDENTIFIED BY repadmin;

/*************************************************************************
STEP 4:
SCHEDULE PURGE AT SNAPSHOT SITE

For additional information about the SCHEDULE_PURGE API, see "SCHEDULE_PURGE 
procedure".
*************************************************************************/

--In order to keep the size of the deferred transaction queue in check,
--you should purge successfully completed deferred transactions. The
--SCHEDULE_PURGE API will automate the purge process for you. If your snapshot 
--site will only contain "read-only" snapshots, then you will not need to
--execute this procedure.

CONNECT snapadmin/snapadmin@snap1.world

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PURGE (
   next_date => SYSDATE,
   interval => 'sysdate + 1/24',
   delay_seconds => 0,
   rollback_segment => '');
END;
/

/*************************************************************************
STEP 5:
SCHEDULE PUSH AT SNAPSHOT SITE

For additional information about the SCHEDULE_PUSH API, see "SCHEDULE_PUSH 
procedure".
*************************************************************************/

--The SCHEDULE_PUSH API schedules when the deferred transaction queue 
--should be propagated to the target master site.

CONNECT snapadmin/snapadmin@snap1.world

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'ORC1.WORLD',
      interval => 'sysdate + 1/24',
      next_date => SYSDATE,
      stop_on_error => FALSE,
      delay_seconds => 0,
      parallelism => 0);
END;
/

---