分类： Oracle
2008-12-21 23:35:49
Before you begin setting up your replication site, make sure you plan your replication environment so that it meets your needs. Planning considerations include:
2：Setting Up Replication Sites(Setting Up Master Sites)
其搭建的步骤，得一步步按其来做，很多E文掉了一些关键步骤，故老是让人不成功。
The examples in this chapter use the following databases:
orc1.world
orc2.world
orc3.world
orc4.world
orc5.world
mv1.world
mv2.world
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.
orc1.world,orc2.world,orc3.world都是master site.
一：完成上图的2,3,4,5,6步，创建各master site的repadmin的user.
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 for site orc2.world and Step for site orc3.world.
*/
SET ECHO ON
SPOOL setup_masters.out
CONNECT SYSTEM/MANAGER@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 repadmin;
/*
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
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 Replication Management tool, 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
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
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
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
delay_seconds => 0);
END;
/
/*
See Also:
and 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_mviewadmin IDENTIFIED BY proxy_mviewadmin;
BEGIN
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.
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 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.
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 for site orc3.world.
*/
CONNECT SYSTEM/MANAGER@orc2.world
/*
Step 2 Create the 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.
Execute the GRANT_ADMIN_ANY_SCHEMA procedure to grant the replication administrator powerful privileges to create and manage a replicated environment.
*/
BEGIN
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 Replication Management tool, 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
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
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
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/MANAGER@orc2.world
CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;
BEGIN
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.
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 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.
You must connect as SYSTEM to the database that you want to set up for replication.
*/
CONNECT SYSTEM/MANAGER@orc3.world
/*
Step 2 Create the 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.
Execute the GRANT_ADMIN_ANY_SCHEMA procedure to grant the replication administrator powerful privileges to create and manage a replicated environment.
*/
BEGIN
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 Replication Management tool, 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
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
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
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/MANAGER@orc3.world
CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;
BEGIN
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.
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;
/*
See Also:
"Security Setup for Materialized View Replication"
二：完成上图的7,8步，创建各master site的dblink
注：这个步骤中，很多E文没有把为什么把public和private DBlink的先后顺序说清，有时顺序错了，也让人搞不清，为什么错了？
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:
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 . 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:
for more information about simulating continuous replication
*/
CONNECT repadmin/repadmin@orc1.world
BEGIN
destination => 'orc2.world',
interval => 'SYSDATE + (1/144)',
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500,
delay_seconds => 1200);
END;
/
BEGIN
destination => 'orc2.world',
interval => 'SYSDATE + (1/144)',
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500,
delay_seconds => 1200);
END;
/
BEGIN
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
destination => 'orc1.world',
interval => 'SYSDATE + (1/144)',
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500,
delay_seconds => 1200);
END;
/
BEGIN
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
destination => 'orc1.world',
interval => 'SYSDATE + (1/144)',
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500,
delay_seconds => 1200);
END;
/
BEGIN
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***********************************/