10g Release 1 (10.1) Part Number B10733-01 |
|
|
View PDF |
10g Release 1 (10.1) Part Number B10733-01 |
|
|
View PDF |
This chapter illustrates how to set up both a master site and a materialize d view replication site using the replication management API.
This chapter contains these top ics:
Before you build your replication environment, you need to set u p the sites that will participate in the replication environment. As illustrated in Figure 2-2 a nd Figure 2-3, there are separate processes for setting up a master site versus setting up a ma terialized view site.
The examples in this chapter use the following 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 vie
w environment. The arrows in Figure 2-1 represent database links.
T ext 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 w hen you build a new materialized view site.
Text description of the illustration rarreps2.gif
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<
/code>. Before you set up the master sites, configure your network and Oracle Net so that all three databases can communicate with ea
ch other.
/************************* BEGINNING OF SCRI PT ******************************
Complete the following s
teps to set up the orc1.world master site.
Connect as SYST
EM 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 /*
The replication administrator must be granted the necessary privileges to create and manage a replication environm ent. The replication administrator must be created at each database that participates in the replication environment.
*/ CREATE USER repadmin IDENTIFIED BY repadmin; /*
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 w
ant 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 Replication Management tool, then grant SELECT <
code>ANY DICTIONARY to repadmin:
*/ GRANT SELECT ANY DICTIONARY TO repadmin; /*
The propagator is responsible for propagating the deferred transaction queue to other master sites.
*/ BEGIN DBMS_DEFER_S YS.REGISTER_PROPAGATOR ( username => 'repadmin'); END; / /*
The receiver receives the propagated deferred tr ansactions 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; / /*
In order to keep the size of the deferred transaction queue in check, you should purge successfu
lly completed deferred transactions. The SCHEDULE_PURGE procedure automates the purge process for you. You must execute
this procedure as the replication administrator.
*/ CONNECT repadmin/repa dmin@orc1.world BEGIN DBMS_DEFER_SYS.SCHEDUL E_PURGE ( next_date => SYSDATE, interval => 'SYSDATE + 1/24', delay_seconds => 0); END; / /*
See Also: Oracle Database Administrato r's Guide and Oracle Database SQL Refere nce 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/MANAGER@orc1.world CREATE USER proxy_mvi ewadmin 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 admini strator at materialized view sites to be able to perform administrative operations using the Replication Management tool, then grant
SELECT_CATALOG_ROLEtoproxy_mviewadmin:GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;Granting this privilege to the
proxy_mview adminis not required if you do not plan to use the Replication Management tool. However, if you plan to use the Replication M anagement tool, then move theGRANTstatement to the line directly after the previousREGISTER_USER_REPGROUPstatement.Create the proxy refresher.
The proxy re fresher 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
C omplete the following steps to set up the
orc2.worldmaster site.Step 1 Connect as SYSTEM at orc2.world.
You must connec t as
SYSTEMto the database that you want to set up for replication. After you set uporc2.world, begin wit h Step 1 for siteorc3.world.*/ CONNECT SYSTEM/MANAGER@orc2.world /*Step 2 Create the replication administrat or at orc2.world.
The replication administrator must be granted the necessary privile ges to create and manage a replication environment. The replication administrator must be created at each database that participates in the replication environment.
*/ create user REPAD MIN identified by REPADMIN; /*Step 3 Grant privileges to replication administrator at orc2.world.
Execute the
GRANT_ADMIN_ANY_SCHEMAprocedure to grant the replication administrator powerful privileges to crea te and manage a replicated environment.*/ BEGIN DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA ( username => 'repadmin'); END; / /*If you want your
repadminto be able to create materialized view logs for any replic ated table, then grantCOMMENTANYTABLEandLOCKANYTABLE privileges torepadmin:*/ GRANT C OMMENT ANY TABLE TO repadmin; GRANT LOCK ANY TABLE TO repadmin; /*If you want your
repadminto be able to connect to the Replic ation Management tool, then grantSELECTANYDICTIONARYtorepadmin:*/ GRANT SELECT ANY DICTIONARY TO repadmin; /*Step 4 Register the pr opagator at orc2.world.
The propagator is responsible for propagating the deferred tr ansaction queue to other master sites.
*/ BEGIN DBMS_DEFER_SYS.REGISTER_PROPAGATOR ( username => 'repad min'); 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_ty pe => 'receiver', list_of_gnames => NULL); END; / /*Step 6 Schedu le purge at master site at orc2.world.
In order to keep the size of the deferred tran saction queue in check, you should purge successfully completed deferred transactions. The
SCHEDULE_PURGEprocedure auto mates 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 => SYSDA TE, 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.worldthat correspond to users at the materialized view site.Create the proxy materialized view admini strator.
The proxy materialized view administrator performs tasks at the target master site o n 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', pri vilege_type => 'proxy_snapadmin', list_of_gnames => NULL); END; < /a>/ -- 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
S ELECT_CATALOG_ROLEtoproxy_mviewadmin:*/ GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin; /*Granting this privilege to the
proxy_mviewadminis not required if you do not plan to u se the Replication Management tool. However, if you plan to use the Replication Management tool, then move theGRANTsta tement to the line directly after the previousREGISTER_USER_REPGROUPstatement.Create the proxy refresher.
The proxy refresher performs tasks at the master site on behalf of the refresher at the materialized view site.
*/ C REATE USER proxy_refresher IDENTIFIED BY proxy_refresher; GRANT CREATE SESSION TO proxy_refr esher; GRANT SELECT ANY TABLE TO proxy_refresher; /*
