2 Configuring the Replication Sites

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
Setting Up Master Sites
Setting Up Materialized View Sites

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 book use the following databases:

orc1.world
orc2.world
orc3.world
orc4.world
orc5.world
mv1.world
mv2.world

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

Description of "Figure 2-1 Three Master Sites and Two Materialized View Sites"

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

Description of "Figure 2-2 Setting Up Master Sites"

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. Before you set up the master sites, configure your network and Oracle Net so that all three databases can communicate with each other.

Note:

If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note 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/user-password@orc1.world

/*

Step 2 Create the 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 user-password;

/*

Note:

Enter an appropriate password for the administrative user.

See Also:

Oracle Database 2 Day + Security Guide for guidelines for choosing passwords

Step 3 Grant privileges to the replication administrator at orc1.world.

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

/*

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;

/*

If you want your repadmin to be able to connect to the Advanced Replication interface in Oracle Enterprise Manager, then grant SELECT ANY DICTIONARY to repadmin:

*/

GRANT SELECT ANY DICTIONARY TO repadmin;

/*

Step 4 Register the 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 the 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/user-password@orc1.world

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

/*

See Also:

Oracle Database Administrator's Guide and Oracle Database SQL Language Reference for more information about date expressions

Step 7 Create proxy master site users at orc1.world.

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.

Create the 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/user-password@orc1.world

CREATE USER proxy_mviewadmin IDENTIFIED BY user-password;

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 Advanced Replication interface in Oracle Enterprise Manager, 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 Advanced Replication interface in Oracle Enterprise Manager. However, if you plan to use the Advanced Replication interface, then move the GRANT statement to the line directly after the previous REGISTER_USER_REPGROUP statement.

Create the 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 user-password;

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.

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/user-password@orc2.world

/*

Step 2 Create the replication administrator at orc2.world.

*/

CREATE USER repadmin IDENTIFIED BY user-password;

/*

Note:

Enter an appropriate password for the administrative user.

See Also:

Oracle Database 2 Day + Security Guide for guidelines for choosing passwords

Step 3 Grant privileges to replication administrator at orc2.world.

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

/*

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;

/*

If you want your repadmin to be able to connect to the Advanced Replication interface in Oracle Enterprise Manager, then grant SELECT ANY DICTIONARY to repadmin:

*/

GRANT SELECT ANY DICTIONARY TO repadmin;

/*

Step 4 Register the 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 the 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.

*/

CONNECT repadmin/user-password@orc2.world

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

/*

Step 7 Create proxy master site users at orc2.world.

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.

Create the 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/user-password@orc2.world

CREATE USER proxy_mviewadmin IDENTIFIED BY user-password;

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.

/*

*/

GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

/*

Create the 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 user-password;

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.

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

*/

CONNECT system/user-password@orc3.world

/*

Step 2 Create the replication administrator at orc3.world.

*/

CREATE USER repadmin IDENTIFIED BY user-password;

/*

Note:

Enter an appropriate password for the administrative user.

See Also:

Oracle Database 2 Day + Security Guide for guidelines for choosing passwords

Step 3 Grant privileges to replication administrator at orc3.world.

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

/*

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;

/*

If you want your repadmin to be able to connect to the Advanced Replication interface in Oracle Enterprise Manager, then grant SELECT ANY DICTIONARY to repadmin:

*/

GRANT SELECT ANY DICTIONARY TO repadmin;

/*

Step 4 Register the 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 the 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/user-password@orc3.world

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

/*

Step 7 Create proxy master site users at orc1.world.

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.

Create the 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/user-password@orc3.world

CREATE USER proxy_mviewadmin IDENTIFIED BY user-password;

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.

/*

*/

GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

/*

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 user-password;

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:

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

*/

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

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

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

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

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

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

/*

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:

Oracle Database Advanced Replication for more information about simulating continuous replication

*/

CONNECT repadmin/user-password@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/user-password@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/user-password@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

Description of "Figure 2-3 Setting Up Materialized View Sites"

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. Before you set up the materialized sites, configure your network and Oracle Net so that all mv1.world can communicate with orc1.world and mv2.world can communicate with mv1.world.

Note:

/************************* 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/user-password@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:

Materialized view administrator
Propagator
Refresher
Receiver (if the site will serve as a master materialized view site for other materialized views, as mv1.world is)

Complete the following tasks to create these users.

Create the 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 user-password;

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

GRANT COMMENT ANY TABLE TO mviewadmin;

GRANT LOCK ANY TABLE TO mviewadmin;

/*

If you want your mviewadmin to be able to connect to the Advanced Replication interface in Oracle Enterprise Manager, then grant SELECT ANY DICTIONARY to mviewadmin:

*/

GRANT SELECT ANY DICTIONARY TO mviewadmin;

/*

Create the propagator.

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

*/

CREATE USER propagator IDENTIFIED BY user-password;

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

/*

Create the 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 user-password;

GRANT CREATE SESSION TO refresher;

GRANT ALTER ANY MATERIALIZED VIEW TO refresher;

/*

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

/*

Note:

Enter appropriate passwords for the administrative users.

See Also:

Oracle Database 2 Day + Security Guide for guidelines for choosing passwords

Step 3 Create database links to the master site.

Create a public database link.

*/

CONNECT system/user-password@mv1.world

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

/*

Create the 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/user-password@mv1.world;

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

/*

Create the 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/user-password@mv1.world

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

/*

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/user-password@mv1.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 the mv1.world materialized view site (optional).

If the materialized view site has a constant connection to its master site, then you optionally can 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/user-password@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.

Create the 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/user-password@mv1.world

CREATE USER proxy_mviewadmin IDENTIFIED BY user-password;

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 Advanced Replication interface in Oracle Enterprise Manager, then grant SELECT_CATALOG_ROLE to proxy_mviewadmin:

GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

Create the 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 user-password;

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

/*

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/user-password@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:

Materialized view administrator
Propagator
Refresher

Complete the following tasks to create these users.

Create the 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 user-password;

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

/*

If you want your mviewadmin to be able to connect to the Advanced Replication interface in Oracle Enterprise Manager, then grant SELECT ANY DICTIONARY to mviewadmin:

*/

GRANT SELECT ANY DICTIONARY TO mviewadmin;

/*

Create the propagator.

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

*/

CREATE USER propagator IDENTIFIED BY user-password;

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

/*

Create the 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 user-password;

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

/*

Note:

Enter appropriate passwords for the administrative users.

See Also:

Oracle Database 2 Day + Security Guide for guidelines for choosing passwords

Step 3 Create database links to master materialized view site .

Create a public database link.

*/

CONNECT system/user-password@mv2.world

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

/*

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/user-password@mv2.world;

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

/*

Create a 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/user-password@mv2.world

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

/*

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/user-password@mv2.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 the mv2.world materialized view site (optional).

If the materialized view site has a constant connection to its master materialized view site, then you optionally can 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/user-password@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 **********************************/