[oracle@dbstyle1 ~]$ sqlplus sys/oracle@PROD AS sysdba

SQL*Plus: RELEASE 10.2.0.1.0 - Production ON Wed May 22 18:31:16 2013
Copyright (c) 1982, 2005, Oracle.  ALL rights reserved.

Connected TO:
Oracle DATABASE 10g Enterprise Edition RELEASE 10.2.0.1.0 - Production
WITH the Partitioning, Oracle Label Security, OLAP AND Data Mining Scoring Engine options

SYS@PROD> conn hr/hr@PROD
Connected.

HR@PROD> CREATE TABLE t AS SELECT * FROM dba_tables;
TABLE created.

HR@PROD> ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (owner,table_name);
TABLE altered.

HR@PROD> SELECT object_name,object_type FROM user_objects;

OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
PK_T                 INDEX
T                    TABLE

HR@PROD>

/************************* BEGINNING OF SCRIPT ******************************/
/*
Setting Up PROD
Complete the following steps to set up the prod master site.
Step 1 Connect as SYSTEM at a master site at prod.
Connect as SYSTEM to the database that you want to set up for replication.
*/

SET ECHO ON
SPOOL setup_masters.OUT
CONNECT SYSTEM/oracle@prod

/*
Step 2 Create the replication administrator at prod.
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 prod.
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 Replication Management tool,
then grant SELECT ANY DICTIONARY to repadmin:
*/

GRANT SELECT ANY DICTIONARY TO repadmin;

/*
Step 4 Register the propagator at prod.
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 prod.
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 prod.
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@prod
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 prod.
If you plan to create materialized view sites based on this master site, then create
proxy master site users at prod 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/oracle@prod
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.
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;
SET ECHO OFF
SPOOL OFF
/**************************END OF SCRIPT***********************************/

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

SET ECHO ON
SPOOL create_mg.OUT
CONNECT repadmin/repadmin@prod

/*
Step 1 Create the schema at master sites.
If the schema does not already exist at all of the master sites participating in the master
group, then create the schema now and grant it all of the necessary privileges. This
example uses the hr schema, which is one of the sample schemas that are installed by
default when you install Oracle. So, the hr schema should exist at all master sites.
*/

PAUSE Press <RETURN> TO continue WHEN the SCHEMA EXISTS AT ALL master sites.

/*
Step 2 Create the master group.
Use the CREATE_MASTER_REPGROUP procedure 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. This step must be
completed by the replication administrator.
*/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => 'hr_repg');
END;
/

/*
Step 3 Add objects to master group.
Use the CREATE_MASTER_REPOBJECT procedure to add an object to your master
group. In most cases, you probably will be adding tables and indexes to your master
group, but you can also add procedures, views, synonyms, and so on.
*/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
TYPE => 'TABLE',
oname => 't',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

/*
Only one master sites,this step is pass.
Step 4 Add additional master sites.
After you have defined your master group at the master definition 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 replication environment. You might
have guessed that you will be adding the orc2.world and orc3.world sites to the
replication environment. This example creates the master group at all master sites, but
you have the option of creating the master group at one master site now and adding
additional master sites later without quiescing the database. In this case, you can skip
this step.
In this example, the use_existing_objects parameter in the ADD_MASTER_
DATABASE procedure is set to TRUE because it is assumed that the hr schema already
exists at all master sites. In other words, it is assumed that the objects in the hr schema
are precreated at all master sites. Also, the copy_rows parameter is set to FALSE
because it is assumed that the identical data is stored in the tables at each master site.
*/

/*
Step 5 If conflicts are possible, then configure conflict resolution methods.
*/

PAUSE Press <RETURN> TO continue after configuring conflict resolution methods OR IF no conflict resolution methods are required.

/*
Step 6 Generate replication support.
*/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'hr',
oname => 't',
TYPE => 'TABLE',
min_communication => TRUE);
END;
/

PAUSE Press <RETURN> TO continue.

/*
Step 7 Start replication.
After creating your master group, adding replication objects, generating replication
support, and adding additional master databases, you need to start replication activity.
Use the RESUME_MASTER_ACTIVITY procedure to "turn on" replication for the
specified master group.
*/

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => 'hr_repg');
END;
/
SET ECHO OFF
SPOOL OFF

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

/************************* BEGINNING OF SCRIPT ******************************/
/*
Step 1 Connect as SYSTEM at materialized view site at emrep.
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/oracle@emrep

/*
Step 2 Create materialized view site users at emrep.
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 emrep 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 mviewadmin;
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 Replication Management
tool, 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 propagator;
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 refresher;
GRANT CREATE SESSION TO refresher;
GRANT ALTER ANY MATERIALIZED VIEW TO refresher;

/*
Register the 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.
Create a public database link.

*/

CONNECT SYSTEM/oracle@emrep
CREATE PUBLIC DATABASE LINK prod USING 'PROD';

/*
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/mviewadmin@emrep;
CREATE DATABASE LINK prod
CONNECT TO proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

/*
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/propagator@emrep
CREATE DATABASE LINK prod
CONNECT TO repadmin IDENTIFIED BY repadmin;

/*
Step 4 Schedule purge at the emrep 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@emrep
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 emrep 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 emrep 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@emrep
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'prod',
INTERVAL => 'SYSDATE + 1/24',
next_date => SYSDATE,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 0);
END;
/

/*
Step 6 Create proxy users at the emrep 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/oracle@emrep
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.
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 proxy_refresher;
GRANT CREATE SESSION TO proxy_refresher;
GRANT SELECT ANY TABLE TO proxy_refresher;
SET ECHO OFF
SPOOL OFF
/************************* END OF SCRIPT **********************************/

/************************* BEGINNING OF SCRIPT ******************************/
/*
Creating the Materialized View Group at emrep
Complete the following steps to create the hr_repg materialized view group at the
emrep materialized view site. This materialized view group is based on the hr_
repg master group at the orc1.world master site.
Step 1 Create materialized view logs at the master site.
If you want one of your master sites to support a materialized view site, then you need
to create materialized view logs for each master table that is replicated to a
materialized view. Recall from Figure 2–1 on page 2-2 that orc1.world serves as the
target master site for the emrep materialized view site. The required materialized
view logs must be created at orc1.world.
*/

SET ECHO ON
SPOOL create_mv_group.OUT
CONNECT hr/hr@prod
CREATE MATERIALIZED VIEW LOG ON hr.t;

/*
Step 2 If they do not already exist, then create the replicated schema and its
database link.
Before building your materialized view group, you must make sure that the replicated
schema exists at the remote materialized view site and that the necessary database
links have been created.
In this example, if the hr schema does not exist, then create the schema. If the hr
schema already exists at the materialized view site, then grant any necessary privileges
and go to the next task in this step.
*/

CONNECT SYSTEM/oracle@emrep
CREATE TABLESPACE demo_mv1
DATAFILE 'demo_mv1.dbf' SIZE 10M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE TEMPORARY TABLESPACE temp_mv1
TEMPFILE 'temp_mv1.dbf' SIZE 5M AUTOEXTEND ON;
CREATE USER hr IDENTIFIED BY hr;
ALTER USER hr DEFAULT TABLESPACE demo_mv1
QUOTA UNLIMITED ON demo_mv1;
ALTER USER hr TEMPORARY TABLESPACE temp_mv1;

GRANT
CREATE SESSION,
CREATE TABLE,
CREATE PROCEDURE,
CREATE SEQUENCE,
CREATE TRIGGER,
CREATE VIEW,
CREATE SYNONYM,
ALTER SESSION,
CREATE MATERIALIZED VIEW,
ALTER ANY MATERIALIZED VIEW,
CREATE DATABASE LINK
TO hr;

/*
If it does not already exist, then create the database link for the replicated schema.
Before building your materialized view group, you must make sure that the necessary
database links exist for the replicated schema. The owner of the materialized views
needs a database link pointing to the proxy_refresher that was created when the
master site was set up.
*/

CONNECT hr/hr@emrep
CREATE DATABASE LINK prod
CONNECT TO proxy_refresher IDENTIFIED BY proxy_refresher;

/*
Step 3 Create the materialized view group.
The following procedures must be executed by the materialized view administrator at
the remote materialized view site.
*/

CONNECT mviewadmin/mviewadmin@emrep

/*
The 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_MVIEW_REPGROUP (
gname => 'hr_repg',
master => 'prod',
propagation_mode => 'ASYNCHRONOUS');
END;
/

/*
Step 4 Create the refresh group.
All materialized views that are added to a particular refresh group are refreshed at the
same time. This ensures transactional consistency between the related materialized
views in the refresh group.
*/

BEGIN
DBMS_REFRESH.MAKE (
name => 'mviewadmin.hr_refg',
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 the materialized view group.
Create the materialized views based on the master tables.
Whenever you create a materialized view, always specify the schema name of the table
owner in the query for the materialized view. In the following examples, hr is
specified as the owner of the table in each query.
*/

CREATE MATERIALIZED VIEW hr.t_mv1
REFRESH FAST WITH PRIMARY KEY FOR UPDATE
AS SELECT * FROM hr.t@prod;

/*
Add the objects to the materialized view group.
*/

BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
gname => 'hr_repg',
sname => 'hr',
oname => 't_mv1',
TYPE => 'SNAPSHOT',
min_communication => TRUE);
END;
/

/*
Step 6 Add objects to the refresh group.
All of the materialized view group objects that you add to the refresh group are
refreshed at the same time to preserve referential integrity between related
materialized views.
*/

BEGIN
DBMS_REFRESH.ADD (
name => 'mviewadmin.hr_refg',
list => 'hr.t_mv1',
lax => TRUE);
END;
/

SET ECHO OFF
SPOOL OFF

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

[oracle@dbstyle1 ~]$ sqlplus hr/hr@PROD

SQL*Plus: RELEASE 10.2.0.1.0 - Production ON Wed May 22 19:42:26 2013
Copyright (c) 1982, 2005, Oracle.  ALL rights reserved.

Connected TO:
Oracle DATABASE 10g Enterprise Edition RELEASE 10.2.0.1.0 - Production
WITH the Partitioning, Oracle Label Security, OLAP AND Data Mining Scoring Engine options

HR@PROD> SELECT * FROM tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T                              TABLE
MLOG$_T                        TABLE
RUPD$_T                        TABLE

HR@PROD> INSERT INTO t(owner,table_name) VALUES('test','test');

1 ROW created.

HR@PROD> SELECT * FROM mlog$_t;

OWNER      TABLE_NAME       SNAPTIME$ D O CHANGE_VECTOR$$
---------- ---------------- --------- - - ----------------
test       test             01-JAN-00 I N FEFFFFFFFFFFFF

HR@PROD> COMMIT;

COMMIT complete.

HR@PROD> SELECT * FROM t WHERE owner='test1' AND table_name='test1';

no ROWS selected

[oracle@dbstyle1 ~]$ sqlplus hr/hr@EMREP

SQL*Plus: RELEASE 10.2.0.1.0 - Production ON Wed May 22 22:10:53 2013
Copyright (c) 1982, 2005, Oracle.  ALL rights reserved.

Connected TO:
Oracle DATABASE 10g Enterprise Edition RELEASE 10.2.0.1.0 - Production
WITH the Partitioning, Oracle Label Security, OLAP AND Data Mining Scoring Engine options

HR@EMREP> SELECT * FROM tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T_MV1                          TABLE
USLOG$_T_MV1                   TABLE

HR@EMREP> SELECT * FROM t_mv1 WHERE owner='test' AND table_name='test';

no ROWS selected

HR@EMREP> INSERT INTO t_mv1(owner,table_name) VALUES('test1','test1');

1 ROW created.

HR@EMREP> COMMIT;

COMMIT complete.

HR@EMREP> conn mviewadmin/mviewadmin@EMREP

Connected.

MVIEWADMIN@EMREP> EXEC dbms_refresh.refresh('hr_refg');

PL/SQL PROCEDURE successfully completed.

HR@emrep> SELECT owner,table_name FROM t_mv1 WHERE owner='test' AND table_name='test';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
test                           test

HR@PROD> SELECT * FROM mlog$_t;

no ROWS selected

HR@PROD> SELECT owner,table_name FROM t WHERE owner='test1' AND table_name='test1';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
test1                          test1

BEGIN
DBMS_REPCAT.DROP_MASTER_REPOBJECT (
TYPE => 'TABLE',
oname => 't',
sname => 'hr');
END;
/

BEGIN
DBMS_REPCAT.drop_MASTER_REPGROUP (
gname => 'hr_repg');
END;
/

conn mviewadmin/mviewadmin@emrep
EXEC dbms_refresh.refresh('hr_refg');