Skip Headers

Oracle9i Replication Management API Reference
Release 2 (9.2)
Part Number A96568-01
Go To Documentation Library
Home		 Go To Product List
Book List		 Go To Table Of Contents
Contents		 Go To Index
Index
Master Index
Feedback

Go to previous page Go to next page

2
Create Replication Site

This chapter illustrates how to set up both a master site and a materialized view replication site using the replication management API.

This chapter contains these topics:

Overview of Setting Up Replication Sites

Before you build your replication environment, you need to set up the sites that will participate in the replication 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 materialized view site.

The examples in this chapter, and in other chapters, use the following nine databases:

Chapters 2 - 6 work with the replication environment illustrated in Figure 2-1. You start to create this environment using the instructions in this chapter. Notice that mv2.world is a materialized view based on the mv1.world materialized view, creating a multitier materialized view environment. The arrows in Figure 2-1 represent database links.

Figure 2-1 Three Master Sites and Two Materialized View Sites

Text description of rarreps3.gif follows
Text description of the illustration rarreps3.gif

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 materialized view site.

Figure 2-2 Setting Up Master Sites

Set Up Master Sites Connect as System at Master Site Create Replication Administrator Grant Privileges to Replication Administrator Register Propagator Register Receiver Schedule Purge at Master Site Create Proxy Master Site Users Create Database Links Between Master Sites Create Scheduled Links
Text description of the illustration rarreps2.gif

Setting Up Master Sites

The following sections contain step-by-step instructions for setting up the three master sites in our sample replication environment: orc1.world, orc2.world, and orc3.world.

Note:

If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line on this page to the "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment.

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

Setting Up orc1.world

Complete the following steps to set up the orc1.world master site.

Step 1 Connect as SYSTEM at a master site at orc1.world.

Connect as SYSTEM to the database that you want to set up for replication. After you set up orc1.world, begin again with Step 1 for site orc2.world and Step 1 for site orc3.world.

*/

SET ECHO ON

SPOOL setup_masters.out

CONNECT SYSTEM/MANAGER@orc1.world

/*
Step 2 Create replication administrator at orc1.world.

The replication administrator must be granted the necessary privileges to create and manage a replication environment. The replication administrator must be created at each database that participates in the replication environment.

*/

CREATE USER repadmin IDENTIFIED BY repadmin;

/*
Step 3 Grant privileges to replication administrator at orc1.world by completing the following steps:
  1. Execute the GRANT_ADMIN_ANY_SCHEMA procedure to grant the replication administrator powerful privileges to create and manage a replicated environment. 
    */

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

/*
  2. If you want your repadmin to be able to create materialized view logs for any replicated table, then grant COMMENT ANY TABLE and LOCK ANY TABLE to repadmin: 
    */

GRANT COMMENT ANY TABLE TO repadmin;
GRANT LOCK ANY TABLE TO repadmin;

/*
  3. If you want your repadmin to be able to connect to the Replication Management tool, then grant SELECT ANY DICTIONARY to repadmin: 
    */

GRANT SELECT ANY DICTIONARY TO repadmin;

/*
Step 4 Register propagator at orc1.world.

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 Register receiver at orc1.world.

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 Schedule purge at master site orc1.world.

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:

Date expressions are used for the NEXT_DATE and INTERVAL parameters. For example:

  • Now is specified as: SYSDATE
  • An interval of one hour is specified as: SYSDATE + 1/24
  • An interval of seven days could be specified as: SYSDATE + 7
 
*/

CONNECT repadmin/repadmin@orc1.world

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

/*
See Also:

Oracle9i Database Administrator's Guide and Oracle9i SQL Reference for more information about date expressions
Step 7 If you plan to create materialized view sites based on this master site, then create proxy master site users at orc1.world that correspond to users at the materialized view site by completing the following steps:
  1. Create proxy materialized view administrator.

    The proxy materialized view administrator performs tasks at the target master site on behalf of the materialized view administrator at the materialized view site.

    */

CONNECT SYSTEM/MANAGER@orc1.world

CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

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

-- Place GRANT SELECT_CATALOG_ROLE statement here if necessary.

/*

    If you want your materialized view administrator at materialized view sites to be able to perform administrative operations using the Replication Management tool, then grant SELECT_CATALOG_ROLE to proxy_mviewadmin:

    GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

    Granting this privilege to the proxy_mviewadmin is not required if you do not plan to use the Replication Management tool. However, if you plan to use the Replication Management tool, then move the GRANT statement to the line directly after the previous REGISTER_USER_REPGROUP statement.

    See Also:

    "Security Setup for Materialized View Replication"
  2. Create proxy refresher.

    The proxy refresher performs tasks at the master site on behalf of the refresher at the materialized view site.

    */

CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;

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

/*

Setting Up orc2.world

Complete the following steps to set up the orc2.world master site.

Step 1 Connect as SYSTEM at orc2.world.
Note:

Multiple master sites (multimaster replication) can only be used with Oracle Enterprise Edition. If you are not using Oracle Enterprise Edition, then skip to "Setting Up Materialized View Sites".

You must connect as SYSTEM to the database that you want to set up for replication. After you set up orc2.world, begin with Step 1 for site orc3.world.

*/

connect SYSTEM/MANAGER@orc2.world

/*
Step 2 Create replication administrator at orc2.world.

The replication administrator must be granted the necessary privileges to create and manage a replication environment. The replication administrator must be created at each database that participates in the replication environment.

*/

create user REPADMIN identified by REPADMIN;

/*
Step 3 Grant privileges to replication administrator at orc2.world by completing the following steps:
  1. Execute the GRANT_ADMIN_ANY_SCHEMA procedure to grant the replication administrator powerful privileges to create and manage a replicated environment. 
    */

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

/*
  2. If you want your repadmin to be able to create materialized view logs for any replicated table, then grant COMMENT ANY TABLE and LOCK ANY TABLE privileges to repadmin: 
    */

GRANT COMMENT ANY TABLE TO repadmin;
GRANT LOCK ANY TABLE TO repadmin;

/*
  3. If you want your repadmin to be able to connect to the Replication Management tool, then grant SELECT ANY DICTIONARY to repadmin: 
    */

GRANT SELECT ANY DICTIONARY TO repadmin;

/*
Step 4 Register propagator at orc2.world.

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 Register receiver at orc2.world.

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 Schedule purge at master site at orc2.world.

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.

*/

CONNECT repadmin/repadmin@orc2.world

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

/*
Step 7 If you plan to create materialized view sites based on this master site, then create proxy master site users at orc2.world that correspond to users at the materialized view site by completing the following steps:
  1. Create proxy materialized view administrator.

    The proxy materialized view administrator performs tasks at the target master site on behalf of the materialized view administrator at the materialized view site.

    */

CONNECT SYSTEM/MANAGER@orc2.world

CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

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

-- Place GRANT SELECT_CATALOG_ROLE statement here if necessary.

/*

    If you want your materialized view administrator at materialized view sites to be able to perform administrative operations using the Replication Management tool, then grant SELECT_CATALOG_ROLE to proxy_mviewadmin:

    */

GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

/*

    Granting this privilege to the proxy_mviewadmin is not required if you do not plan to use the Replication Management tool. However, if you plan to use the Replication Management tool, then move the GRANT statement to the line directly after the previous REGISTER_USER_REPGROUP statement.

    See Also:

    "Security Setup for Materialized View Replication"
  2. Create proxy refresher.

    The proxy refresher performs tasks at the master site on behalf of the refresher at the materialized view site.

    */

CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;

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

/*

Setting Up orc3.world

Complete the following steps to set up the orc3.world master site.

Step 1 Connect as SYSTEM at orc3.world.
Note:

Multiple master sites (multimaster replication) can be used only with Oracle Enterprise Edition. If you are not using Oracle Enterprise Edition, then skip to "Setting Up Materialized View Sites".

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

*/

connect SYSTEM/MANAGER@orc3.world

/*
Step 2 Create replication administrator at orc3.world.

The replication administrator must be granted the necessary privileges to create and manage a replication environment. The replication administrator must be created at each database that participates in the replication environment.

*/

create user REPADMIN identified by REPADMIN;

/*
Step 3 Grant privileges to replication administrator at orc3.world by completing the following steps:
  1. Execute the GRANT_ADMIN_ANY_SCHEMA procedure to grant the replication administrator powerful privileges to create and manage a replicated environment. 
    */

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

/*
  2. If you want your repadmin to be able to create materialized view logs for any replicated table, then grant COMMENT ANY TABLE and LOCK ANY TABLE to repadmin: 
    */

GRANT COMMENT ANY TABLE TO repadmin;
GRANT LOCK ANY TABLE TO repadmin;

/*
  3. If you want your repadmin to be able to connect to the Replication Management tool, then grant SELECT ANY DICTIONARY to repadmin: 
    */

GRANT SELECT ANY DICTIONARY TO repadmin;

/*
Step 4 Register propagator at orc3.world.

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 Register receiver at orc3.world.

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 Schedule purge at master site at orc3.world.

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);
END;
/

/*
Step 7 If you plan to create materialized view sites based on this master site, then create proxy master site users at orc1.world that correspond to users at the materialized view site by completing the following steps:
  1. Create proxy materialized view administrator.

    The proxy materialized view administrator performs tasks at the target master site on behalf of the materialized view administrator at the materialized view site.

    */

CONNECT SYSTEM/MANAGER@orc3.world

CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

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

-- Place GRANT SELECT_CATALOG_ROLE statement here if necessary.

/*

    If you want your materialized view administrator at materialized view sites to be able to perform administrative operations using the Replication Management tool, then grant SELECT_CATALOG_ROLE to proxy_mviewadmin:

    */

    GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

/*

    Granting this privilege to the proxy_mviewadmin is not required if you do not plan to use the Replication Management tool. However, if you plan to use the Replication Management tool, then move the GRANT statement to the line directly after the previous REGISTER_USER_REPGROUP statement.

    See Also:

    "Security Setup for Materialized View Replication"
  2. Create proxy refresher.

    The proxy refresher performs tasks at the master site on behalf of the refresher at the materialized view site.

    */

CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;

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


    /*

Creating Scheduled Links Between the Master Sites

Complete the following steps to create scheduled links between the master sites.

Step 1 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. 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.

See Also:

Oracle9i Database Administrator's Guide for more information about database links

 
*/

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 2 Define a schedule for each database link to create scheduled links.

Create a scheduled link by defining a database link when you execute the SCHEDULE_PUSH procedure. 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 1. The database link is specified in the destination parameter of the SCHEDULE_PUSH procedure.

Even when using Oracle's asynchronous replication mechanisms, you can configure a scheduled link to simulate continuous, real-time replication. The scheduled links in this example simulate continuous replication.

See Also:

Oracle9i Replication for more information about simulating continuous replication

 
*/

CONNECT repadmin/repadmin@orc1.world

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'orc2.world',
      interval => 'SYSDATE + (1/144)',
      next_date => SYSDATE,
      parallelism => 1,
      execution_seconds => 1500,
      delay_seconds => 1200);
END;
/

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'orc2.world',
      interval => 'SYSDATE + (1/144)',
      next_date => SYSDATE,
      parallelism => 1,
      execution_seconds => 1500,
      delay_seconds => 1200);
END;
/

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'orc3.world',
      interval => 'SYSDATE + (1/144)',
      next_date => SYSDATE,
      parallelism => 1,
      execution_seconds => 1500,
      delay_seconds => 1200);
END;
/

CONNECT repadmin/repadmin@orc2.world

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'orc1.world',
      interval => 'SYSDATE + (1/144)',
      next_date => SYSDATE,
      parallelism => 1,
      execution_seconds => 1500,
      delay_seconds => 1200);
END;
/

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'orc3.world',
      interval => 'SYSDATE + (1/144)',
      next_date => SYSDATE,
      parallelism => 1,
      execution_seconds => 1500,
      delay_seconds => 1200);
END;
/

CONNECT repadmin/repadmin@orc3.world

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'orc1.world',
      interval => 'SYSDATE + (1/144)',
      next_date => SYSDATE,
      parallelism => 1,
      execution_seconds => 1500,
      delay_seconds => 1200);
END;
/

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'orc2.world',
      interval => 'SYSDATE + (1/144)',
      next_date => SYSDATE,
      parallelism => 1,
      execution_seconds => 1500,
      delay_seconds => 1200);
END;
/

SET ECHO OFF

SPOOL OFF

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

Setting Up Materialized View Sites

Figure 2-3 Setting Up Materialized View Sites

Set Up Materialized View Sites Create Materialized View Site Users Create Database Links to Master Schedule Purge at Materialized View Site Schedule Push at Materialized View Site Connect as SYSTEM at Materialized View Site Create Proxy Users
Text description of the illustration rarrepsa.gif

Setting Up mv1.world

Complete the following steps to set up the mv1.world master materialized view site. mv1.world is a master materialized view site because mv2.world will be based on it.

Note:

If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line on this page to the "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment.

 
/************************* BEGINNING OF SCRIPT ******************************
Step 1 Connect as SYSTEM at materialized view site at mv1.world.

You must connect as SYSTEM to the database that you want to set up as a materialized view site.

*/

SET ECHO ON

SPOOL setup_mvs.out

CONNECT SYSTEM/MANAGER@mv1.world

/*
Step 2 Create materialized view site users at mv1.world.

Several users must be created at the materialized view site. These users are:

Complete the following steps to create these users.

    1. Create materialized view administrator.

      The materialized view administrator is responsible for creating and managing the materialized view site. Execute the GRANT_ADMIN_ANY_SCHEMA procedure to grant the materialized view administrator the appropriate privileges.

      */

create user MVIEWADMIN identified by MVIEWADMIN;

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

GRANT COMMENT ANY TABLE TO mviewadmin;

GRANT LOCK ANY TABLE TO mviewadmin;

/*
    2. If you want your mviewadmin to be able to connect to the Replication Management tool, then grant SELECT ANY DICTIONARY to mviewadmin: 
      */

GRANT SELECT ANY DICTIONARY TO mviewadmin;

/*
    3. 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;
/

/*
  2. Create refresher.

    The refresher is responsible for "pulling" changes made to the replicated tables at the target master site to the materialized view site. This user refreshes one or more materialized views. If you want the mviewadmin user to be the refresher, then this step is not required.

    */

CREATE USER refresher IDENTIFIED BY refresher;

GRANT CREATE SESSION TO refresher;

GRANT ALTER ANY MATERIALIZED VIEW TO refresher;

/*
  3. Register receiver.

    The receiver receives the propagated deferred transactions sent by the propagator from materialized view sites. The receiver is necessary only if the site will function as a master materialized view site for other materialized view sites.

    */

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

/*
Step 3 Create database links to the master site by completing the following steps.
  1. Create public database link. 
    */

CONNECT SYSTEM/MANAGER@mv1.world

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

/*
  2. Create materialized view administrator database link.

    You need to create a database link from the materialized view administrator at the materialized view site to the proxy materialized view administrator at the master site.

    */

CONNECT mviewadmin/mviewadmin@mv1.world;

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

/*
  3. Create propagator/receiver database link.

    You need to create a database link from the propagator at the materialized view site to the receiver at the master site. The receiver was defined when you created the master site.

    */

CONNECT propagator/propagator@mv1.world

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

/*
    See Also:

    Step 5
Step 4 Schedule purge at the mv1.world materialized view site.

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. If your materialized view site only contains "read-only" materialized views, then you do not need to execute this procedure.

*/

CONNECT mviewadmin/mviewadmin@mv1.world

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

/*
Step 5 If the materialized view site has a constant connection to its master site, then you can optionally schedule push at the mv1.world materialized view site. If the materialized view site is disconnected from its master site for extended periods of time, then it is typically better not to schedule push and refresh on demand, which pushes changes to the master site.

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

*/

CONNECT mviewadmin/mviewadmin@mv1.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;
/

/*
Step 6 Create proxy users at the mv1.world materialized view site by completing the following steps.
  1. Create proxy materialized view administrator.

    The proxy materialized view administrator performs tasks at the target master materialized view site on behalf of the materialized view administrator at the materialized view sites based on this materialized view site. This user is not required if the site will not function as a master materialized view site for other materialized view sites.

    */

CONNECT SYSTEM/MANAGER@mv1.world

CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

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

-- Place GRANT SELECT_CATALOG_ROLE statement here if necessary.

/*

    If you want your materialized view administrator at materialized view sites based on this materialized view site to be able to perform administrative operations using the Replication Management tool, then grant SELECT_CATALOG_ROLE to proxy_mviewadmin:

    GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

    Granting this privilege to the proxy_mviewadmin is not required if you do not plan to use the Replication Management tool. However, if you plan to use the Replication Management tool, then move the GRANT statement to the line directly after the previous REGISTER_USER_REPGROUP statement.

  2. Create proxy refresher.

    The proxy refresher performs tasks at the master materialized view site on behalf of the refresher at the materialized view sites based on this materialized view site. This user is not required if the site will not function as a master materialized view site for other materialized view sites.

    */

CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;

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

/*
    See Also:

    "Security Setup for Materialized View Replication"

Setting Up mv2.world

Complete the following steps to set up the mv2.world materialized view site. mv2.world is part of a multitier materialized view configuration because it is based on mv1.world, another materialized view.

Step 1 Connect as SYSTEM at level 2 materialized view site mv2.world.

You must connect as SYSTEM to the database that you want to set up as a level 2 materialized view site. This site, mv2.world, will be a materialized view site that is based on mv1.world.

*/

CONNECT SYSTEM/MANAGER@mv2.world

/*
Step 2 Create level 2 materialized view site users at mv2.world.

Several users must be created at the level 2 materialized view site. These users are:

Complete the following steps to create these users.

  1. Create materialized view administrator.

    The materialized view administrator is responsible for creating and managing the level 2 materialized view site. Execute the GRANT_ADMIN_ANY_SCHEMA procedure to grant the materialized view administrator the appropriate privileges.

    */

create user MVIEWADMIN identified by MVIEWADMIN;

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

/*
  2. If you want your mviewadmin to be able to connect to the Replication Management tool, then grant SELECT ANY DICTIONARY to mviewadmin: 
    */

GRANT SELECT ANY DICTIONARY TO mviewadmin;

/*
  3. Create propagator.

    The propagator is responsible for propagating the deferred transaction queue to the target master materialized view site.

    */

CREATE USER propagator IDENTIFIED BY propagator;

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

/*
  4. Create refresher.

    The refresher is responsible for "pulling" changes made to the replicated materialized views at the target master materialized view site to the level 2 materialized view site.

    */

CREATE USER refresher IDENTIFIED BY refresher;

GRANT CREATE SESSION TO refresher;
GRANT ALTER ANY MATERIALIZED VIEW TO refresher;

/*
Step 3 Create database links to master materialized view site by completing the following steps.
  1. Create public database link. 
    */

CONNECT SYSTEM/MANAGER@mv2.world

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

/*
  2. Create materialized view administrator database link.

    You need to create a database link from the materialized view administrator at the level 2 materialized view site to the proxy materialized view administrator at the master materialized view site.

    */

CONNECT mviewadmin/mviewadmin@mv2.world;

CREATE DATABASE LINK mv1.world 
  CONNECT TO proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

/*
  3. Create propagator/receiver database link.

    You need to create a database link from the propagator at the level 2 materialized view site to the receiver at the master materialized view site. The receiver was defined when you created the master materialized view site.

    */

CONNECT propagator/propagator@mv2.world

CREATE DATABASE LINK mv1.world 
  CONNECT TO mviewadmin IDENTIFIED BY mviewadmin;

/*
Step 4 Schedule purge at level 2 materialized view site at mv2.world.

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. If your level 2 materialized view site only contains "read-only" materialized views, then you do not need to execute this procedure.

*/

CONNECT mviewadmin/mviewadmin@mv2.world

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

/*
Step 5 If the materialized view site has a constant connection to its master materialized view site, then you can optionally schedule push at the mv2.world materialized view site. If the materialized view site is disconnected from its master materialized view site for extended periods of time, then it is typically better not to schedule push and refresh on demand, which pushes changes to the master materialized view site.

The SCHEDULE_PUSH procedure schedules when the deferred transaction queue should be propagated to the target master materialized view site.

*/

CONNECT mviewadmin/mviewadmin@mv2.world

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

SET ECHO OFF

SPOOL OFF

/************************* END OF SCRIPT **********************************/
Go to previous page Go to next page
Oracle
Copyright © 1996, 2002 Oracle Corporation.
All Rights Reserved.
Go To Documentation Library
Home		 Go To Product List
Book List		 Go To Table Of Contents
Contents		 Go To Index
Index
Master Index
Feedback