Set Up Master Sites

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

--Connect as SYSTEM to the database that you want to 
--setup for replication. After you setup ORC1.WORLD,
--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 must be granted the necessary privileges
--to create and manage a replicated environment. The replication 
--administrator must be created at each database that participates
--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 grants 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 receives 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 automates 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
--Oracle8i Replication.

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 must connect as SYSTEM to the database that you want to 
--set up for replication. After you set up ORC2.WORLD, 
--begin again with STEP 1 for site ORC3.WORLD.

connect SYSTEM/MANAGER@ORC2.WORLD

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

--The replication administrator must be granted the necessary privileges
--to create and manage a replicated environment. The replication 
--administrator must be created at each database that participates
--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 grants 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 receives 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 automates 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 be used only with 
--Oracle8i Enterprise Edition. If you are not using Oracle8i Enterprise
--Edition, skip to step 8. 

--You must connect as SYSTEM to the database that you want to 
--set up for replication.

connect SYSTEM/MANAGER@ORC3.WORLD

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

--The replication administrator must be granted the necessary privileges
--to create and manage a replicated environment. The replication 
--administrator must be created at each database that participates
--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 grants 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 receives 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 automates 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 7:
CREATE DATABASE LINKS BETWEEN MASTER SITES

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

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

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 8:
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;
/