Create Master Group
/*************************************************************************
STEP 1:
CREATE SCHEMA AT MASTER SITES
*************************************************************************/CONNECT system/[email protected];
CREATE USER scott IDENTIFIED BY tiger;
GRANT CONNECT, RESOURCE TO scott;CONNECT system/[email protected];
CREATE USER scott IDENTIFIED BY tiger;
GRANT CONNECT, RESOURCE TO scott;/*************************************************************************
STEP 2:
CREATE MASTER GROUP
*************************************************************************/--Use the CREATE_MASTER_REPGROUP API to define a new master group.
--When you add an object to your master group or perform other replication
--administrative tasks, you reference the master group name defined
--during this step. The following must be executed by the replication
--administrator.CONNECT repadmin/[email protected]
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => 'SCOTT_MG');
END;
//*************************************************************************
STEP 3:
ADD OBJECTS TO MASTER GROUP
*************************************************************************/--Use the CREATE_MASTER_REPOBJECT API to add an object to your master group.
--In most cases, you probably will be adding tables to your master group,
--but you can also add indexes, procedures, views, synonyms, and so on. See
--CREATE_MASTER_REPOBJECT procedure for additional
--information. BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'SCOTT_MG',
type => 'TABLE',
oname => 'EMP',
sname => 'SCOTT',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'SCOTT_MG',
type => 'TABLE',
oname => 'dept',
sname => 'SCOTT',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/--The tables EMP and DEPT have a primary key, but BONUS and SALGRADE do not have
--a primary key. For replication to work properly, each replicated table either
--needs a primary key or to have a "set column." The
--DBMS_REPCAT.SET_COLUMNS procedure is sufficient for multimaster replication
--only, but if you also want to support fast refreshable snapshots, you need a
--primary key. It is easier to alter your object before you add it to your
--master group.ALTER TABLE scott.bonus ADD (CONSTRAINT bonus_pk PRIMARY KEY(ename));BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'SCOTT_MG',
type => 'TABLE',
oname => 'bonus',
sname => 'SCOTT',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/--You must modify the SCOTT.SALGRADE object just as you altered the
--SCOTT.BONUS object in the previous step.ALTER TABLE scott.salgrade ADD (CONSTRAINT salgrade_pk PRIMARY KEY(grade));BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'SCOTT_MG',
type => 'TABLE',
oname => 'salgrade',
sname => 'SCOTT',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
//*************************************************************************
STEP 4:
ADD ADDITIONAL MASTER SITES
*************************************************************************/--After you have defined your master group at the MASTERDEF site (the
--site where the master group was created becomes the MASTER DEFINITION
--site by default), you can define the other sites that will participate
--in the replicated environment. You might have guessed that you will be
--adding the ORC2.WORLD and ORC3.WORLD sites to our replicated environment.BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => 'SCOTT_MG',
master => 'ORC2.WORLD',
use_existing_objects => TRUE,
copy_rows => TRUE,
propagation_mode => 'ASYNCHRONOUS');
END;
//*************************************************************************
NOTE: You should wait until ORC2.WORLD appears in the DBA_REPSITES view
before continuing. Execute the following SELECT statement in another
SQL*Plus session to make sure that ORC2.WORLD has appeared):SELECT * FROM dba_repsites WHERE gname = 'SCOTT_MG';
*************************************************************************/
PAUSE Press <RETURN> to continue.
BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => 'SCOTT_MG',
master => 'ORC3.WORLD',
use_existing_objects => TRUE,
copy_rows => TRUE,
propagation_mode => 'ASYNCHRONOUS');
END;
//*************************************************************************
NOTE: You should wait until ORC3.WORLD appears in the DBA_REPSITES view
before continuing. Execute the following SELECT statement in another
SQL*Plus session to make sure that ORC3.WORLD has appeared):SELECT * FROM dba_repsites WHERE gname = 'SCOTT_MG';
*************************************************************************/
PAUSE Press <RETURN> to continue./*************************************************************************
CAUTION: If you added one or more tables to a master group during creation
of the group, do not resume replication activity immediately. First consider
the possibility of replication conflicts, and configure conflict resolution
for the replicated tables in the group. See Chapter 6, "Conflict Resolution"
for more information about configuring conflict resolution for master group
objects.
*************************************************************************//*************************************************************************
STEP 5:
GENERATE REPLICATION SUPPORT
*************************************************************************/BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'SCOTT',
oname => 'EMP',
type => 'TABLE',
min_communication => TRUE);
END;
/
/*************************************************************************
STEP 1:
CREATE SCHEMA AT MASTER SITES
*************************************************************************/CONNECT system/[email protected];
CREATE USER scott IDENTIFIED BY tiger;
GRANT CONNECT, RESOURCE TO scott;CONNECT system/[email protected];
CREATE USER scott IDENTIFIED BY tiger;
GRANT CONNECT, RESOURCE TO scott;/*************************************************************************
STEP 2:
CREATE MASTER GROUP
*************************************************************************/--Use the CREATE_MASTER_REPGROUP API to define a new master group.
--When you add an object to your master group or perform other replication
--administrative tasks, you reference the master group name defined
--during this step. The following must be executed by the replication
--administrator.CONNECT repadmin/[email protected]
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => 'SCOTT_MG');
END;
//*************************************************************************
STEP 3:
ADD OBJECTS TO MASTER GROUP
*************************************************************************/--Use the CREATE_MASTER_REPOBJECT API to add an object to your master group.
--In most cases, you probably will be adding tables to your master group,
--but you can also add indexes, procedures, views, synonyms, and so on. See
--CREATE_MASTER_REPOBJECT procedure for additional
--information. BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'SCOTT_MG',
type => 'TABLE',
oname => 'EMP',
sname => 'SCOTT',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'SCOTT_MG',
type => 'TABLE',
oname => 'dept',
sname => 'SCOTT',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/--The tables EMP and DEPT have a primary key, but BONUS and SALGRADE do not have
--a primary key. For replication to work properly, each replicated table either
--needs a primary key or to have a "set column." The
--DBMS_REPCAT.SET_COLUMNS procedure is sufficient for multimaster replication
--only, but if you also want to support fast refreshable snapshots, you need a
--primary key. It is easier to alter your object before you add it to your
--master group.ALTER TABLE scott.bonus ADD (CONSTRAINT bonus_pk PRIMARY KEY(ename));BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'SCOTT_MG',
type => 'TABLE',
oname => 'bonus',
sname => 'SCOTT',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/--You must modify the SCOTT.SALGRADE object just as you altered the
--SCOTT.BONUS object in the previous step.ALTER TABLE scott.salgrade ADD (CONSTRAINT salgrade_pk PRIMARY KEY(grade));BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'SCOTT_MG',
type => 'TABLE',
oname => 'salgrade',
sname => 'SCOTT',
use_existing_object => TRUE,
copy_rows => TRUE);
END;
//*************************************************************************
STEP 4:
ADD ADDITIONAL MASTER SITES
*************************************************************************/--After you have defined your master group at the MASTERDEF site (the
--site where the master group was created becomes the MASTER DEFINITION
--site by default), you can define the other sites that will participate
--in the replicated environment. You might have guessed that you will be
--adding the ORC2.WORLD and ORC3.WORLD sites to our replicated environment.BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => 'SCOTT_MG',
master => 'ORC2.WORLD',
use_existing_objects => TRUE,
copy_rows => TRUE,
propagation_mode => 'ASYNCHRONOUS');
END;
//*************************************************************************
NOTE: You should wait until ORC2.WORLD appears in the DBA_REPSITES view
before continuing. Execute the following SELECT statement in another
SQL*Plus session to make sure that ORC2.WORLD has appeared):SELECT * FROM dba_repsites WHERE gname = 'SCOTT_MG';
*************************************************************************/
PAUSE Press <RETURN> to continue.
BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => 'SCOTT_MG',
master => 'ORC3.WORLD',
use_existing_objects => TRUE,
copy_rows => TRUE,
propagation_mode => 'ASYNCHRONOUS');
END;
//*************************************************************************
NOTE: You should wait until ORC3.WORLD appears in the DBA_REPSITES view
before continuing. Execute the following SELECT statement in another
SQL*Plus session to make sure that ORC3.WORLD has appeared):SELECT * FROM dba_repsites WHERE gname = 'SCOTT_MG';
*************************************************************************/
PAUSE Press <RETURN> to continue./*************************************************************************
CAUTION: If you added one or more tables to a master group during creation
of the group, do not resume replication activity immediately. First consider
the possibility of replication conflicts, and configure conflict resolution
for the replicated tables in the group. See Chapter 6, "Conflict Resolution"
for more information about configuring conflict resolution for master group
objects.
*************************************************************************//*************************************************************************
STEP 5:
GENERATE REPLICATION SUPPORT
*************************************************************************/BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'SCOTT',
oname => 'EMP',
type => 'TABLE',
min_communication => TRUE);
END;
/
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'SCOTT',
oname => 'dept',
type => 'TABLE',
min_communication => TRUE);
END;
/BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'SCOTT',
oname => 'bonus',
type => 'TABLE',
min_communication => TRUE);
END;
/BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'SCOTT',
oname => 'salgrade',
type => 'TABLE',
min_communication => TRUE);
END;
//*************************************************************************
NOTE: You should wait until the DBA_REPCATLOG view is empty before
resuming master activity. Execute the following SELECT statement
to monitor your DBA_REPCATLOG view:SELECT * FROM dba_repcatlog WHERE gname = 'SCOTT_MG';
*************************************************************************/
PAUSE Press <RETURN> to continue./*************************************************************************
STEP 6:
RESUME REPLICATION
*************************************************************************/--After you have completed creating your master group, adding replication
--objects, generating replication support, and adding additional master
--databases, you need to resume replication activity. The
--RESUME_MASTER_ACTIVITY procedure API "turns on" replication for
--the specified master group.BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => 'SCOTT_MG');
END;
/
*************************************************************************
STEP 1:
CREATE SNAPSHOT LOGS AT MASTER SITESee the CREATE SNAPSHOT LOG in the Oracle8i SQL Reference for
detailed information about this SQL statement.
*************************************************************************/--If you want one of your master sites to support a snapshot site, then
--you need to create snapshot logs for each master table that is
--replicated to a snapshot. Recall from Figure 2-1 that
--ORC1.WORLD serves as the target master site for the SNAP1.WORLD
--snapshot site. The required snapshot logs must be created at ORC1.WORLD.CONNECT scott/[email protected] SNAPSHOT LOG ON scott.emp;
CREATE SNAPSHOT LOG ON scott.dept;
CREATE SNAPSHOT LOG ON scott.bonus;
CREATE SNAPSHOT LOG ON scott.salgrade;
/*************************************************************************
STEP 2:
CREATE REPLICATED SCHEMA AND LINKS
*************************************************************************/--Before you begin building your snapshot group, you must make sure that
--the replicated schema exists at the remote snapshot site and that the
--necessary database links have been created.CONNECT system/[email protected] USER scott IDENTIFIED BY tiger;
GRANT connect, resource TO scott;CONNECT scott/[email protected] owner of the snapshots needs a database link pointing to the
--proxy_refresher that was created when the snapshot site was set up; see
--"CREATE MASTER SITE USERS" for information.CREATE DATABASE LINK orc1.world
CONNECT TO proxy_refresher IDENTIFIED BY proxy_refresher;/*************************************************************************
STEP 3:
CREATE SNAPSHOT GROUP
*************************************************************************/--The following procedures must be executed by the snapshot administrator
--at the remote snapshot site.CONNECT snapadmin/[email protected] master group that you specify in the GNAME parameter must match the
--name of the master group that you are replicating at the target master site.BEGIN
DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP (
gname => 'SCOTT_MG',
master => 'ORC1.WORLD',
propagation_mode => 'ASYNCHRONOUS');
END;
//*************************************************************************
STEP 4:
CREATE REFRESH GROUP
*************************************************************************/--All snapshots that are added to a particular refresh group are
--refreshed at the same time. This ensures transactional consistency
--between the related snapshots in the refresh group.BEGIN
DBMS_REFRESH.MAKE (
name => 'SNAPADMIN.SCOTT_RG',
list => '',
next_date => SYSDATE,
interval => 'sysdate + 1/24',
implicit_destroy => FALSE,
rollback_seg => '',
push_deferred_rpc => TRUE,
refresh_after_errors => FALSE);
END;
//*************************************************************************
STEP 5:
ADD OBJECTS TO SNAPSHOT GROUP
*************************************************************************/--Whenever you create a snapshot, always specify the schema name of the table
--owner in the query for the snapshot. In the examples below, SCOTT is specified
--as the owner of the table in each query.BEGIN
DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT (
gname => 'SCOTT_MG',
sname => 'SCOTT',
oname => 'BONUS',
type => 'SNAPSHOT',
ddl_text => 'create snapshot SCOTT.BONUS refresh fast with
primary key for update as select * from
[email protected]',
min_communication => TRUE);
END;
/BEGIN
DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT (
gname => 'SCOTT_MG',
sname => 'SCOTT',
oname => 'DEPT',
type => 'SNAPSHOT',
ddl_text => 'create snapshot SCOTT.DEPT refresh fast with
primary key for update as select * from
[email protected]',
min_communication => TRUE);
END;
/BEGIN
DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT (
gname => 'SCOTT_MG',
sname => 'SCOTT',
oname => 'EMP',
type => 'SNAPSHOT',
ddl_text => 'create snapshot SCOTT.EMP refresh fast with
primary key for update as select * from
[email protected]',
min_communication => TRUE);
END;
/BEGIN
DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT (
gname => 'SCOTT_MG',
sname => 'SCOTT',
oname => 'SALGRADE',
type => 'SNAPSHOT',
ddl_text => 'create snapshot SCOTT.SALGRADE refresh fast with
primary key for update as select * from
[email protected]',
min_communication => TRUE);
END;
//*************************************************************************
STEP 6:
ADD OBJECTS TO REFRESH GROUP
*************************************************************************/--All of the snapshot group objects that you add to the refresh group
--are refreshed at the same time to preserve referential integrity
--between related snapshots.BEGIN
DBMS_REFRESH.ADD (
name => 'SNAPADMIN.SCOTT_RG',
list => 'SCOTT.BONUS',
lax => TRUE);
END;
/BEGIN
DBMS_REFRESH.ADD (
name => 'SNAPADMIN.SCOTT_RG',
list => 'SCOTT.dept',
lax => TRUE);
END;
/BEGIN
DBMS_REFRESH.ADD (
name => 'SNAPADMIN.SCOTT_RG',
list => 'SCOTT.emp',
lax => TRUE);
END;
/BEGIN
DBMS_REFRESH.ADD (
name => 'SNAPADMIN.SCOTT_RG',
list => 'SCOTT.salgrade',
lax => TRUE);
END;
/
发现问题,请去
http://download-west.oracle.com/docs/cd/A87861_01/NT817EE/index.htm搜索 snapshot
一定有你的答案 :)