Set up read-only materialized view replication

I was assigned a task to evaluate the feasibility of seting up materialized view replication for another team. Currently this team obtains the data from one of our reporting databases through export and import. So I started to understand materialized view replication. As the first step, I build a testing materialized view replication environment on my pc. Below are some notes as the result of this effort.

 

 =========================================
 == Set up  read-only MVIEW replication ==
 =========================================

Environment:
   Master site (DBT920)             : 9.2.0.4
   Materialized view site  (DBT10G) : 10.2.0.2
 
Reference:
1. Metalink Note 256235.1 Scripts to create Trusted / Untrusted ReadOnly MVIEW Replication Sites
2. http://www.hpfuchs.com/2008/02/06/materialized-view-replication/

Overview
~~~~~~~~~~
1. create users at master site
2. create users at mview site
3. create database link at mview site
4. create mview logs at master site
5. create mview at mview site
6. create mview groups
7. Some basic operations


Detailed Steps
~~~~~~~~~~~~~~~

1. create users at master site

   -- run as system

   create user mviewproxy identified by mviewproxy;

   grant create session to mviewproxy;
   grant create any table to mviewproxy;
   grant comment any table to mviewproxy;
   grant select any table to mviewproxy;

   BEGIN
     dbms_repcat_admin.register_user_repgroup(
        username =>  'mviewproxy',
        privilege_type => 'proxy_mviewadmin',
        list_of_gnames =>  NULL);
   END;
   /

   Note: Not sure if this register_user_repgroup is necessary


2. create users at mview site

   -- run as system

   create user mvowner identified by oracle
   default tablespace users temporary tablespace temp;
   grant connect, resource to mvowner;
   grant create materialized view to mvowner;
   grant create database link to mvowner;

   -- run as system
   
   CREATE USER mviewadmin IDENTIFIED BY mviewadmin;
   ALTER USER mviewadmin DEFAULT TABLESPACE users;
   ALTER USER mviewadmin TEMPORARY TABLESPACE temp;

   EXECUTE dbms_repcat_admin.grant_admin_any_schema('mviewadmin');
   GRANT comment any table TO mviewadmin;
   GRANT lock any table TO mviewadmin;

   GRANT create any materialized view TO mviewadmin;
   GRANT alter any materialized view TO mviewadmin;


3. create db link at mview site

   -- run as system
   CREATE PUBLIC DATABASE LINK DBT92.US.ORACLE.COM USING 'DBT92';

   Note: not sure why this is necessary.

   -- run as mvowner
     create database link DBT92.US.ORACLE.COM connect to mviewproxy identified by mviewproxy
     using 'DBT92';

4. create mview logs at master site

-- run as mviewproxy
create materialized view log on mstowner.big_table tablespace mviewlog;


Note:  1. mview logs residing in their own tablespace, i.e.
    create tablespace mviewlog datafile '/u03/oracle/oradata/DBT92/mviewlog01.dbf' size 50M;
       2. mviewproxy does not have the privs to alter/drop materialized view log

5. create mview  at mview site
  login as mvowner

-- run as mvowner
CREATE MATERIALIZED VIEW mvowner.t_mv REFRESH FAST AS SELECT * FROM mstowner.t@DBT92.US.ORACLE.COM;
CREATE MATERIALIZED VIEW mvowner.big_table_mv REFRESH FAST AS SELECT * FROM mstowner.big_table@DBT92.US.ORACLE.COM;

Note:
 Oracle will create three objects when executing the following command:
         CREATE MATERIALIZED VIEW mvowner.big_table_mv REFRESH FAST AS SELECT * FROM mstowner.big_table@DBT92.US.ORACLE.COM;

   OBJECT_NAME                    OBJECT_TYPE
   ------------------------------ -------------------
   BIG_TABLE_MV                   TABLE
   BIG_TABLE_PK                   INDEX
   BIG_TABLE_MV                   MATERIALIZED VIEW
    


6. create mview groups at mview site

-- create the refresh group for the mview to ensure transactional
-- consistency when refreshing nore than one mview in the group.

-- run as mviewadmin on the mview side
 
     begin
              dbms_refresh.make(
          name => 'RG_BIG_TABLE',
          list => 'mvowner.big_table_mv',
          next_date => sysdate,
          interval => 'sysdate + 1/24',
          implicit_destroy => true,
          lax => true);
     end;
     /                          


7. Some basic operations

  (1) Refresh the complete group
          execute dbms_refresh.refresh('RG_BIG_TABLE');
  (2) Refresh a single snapshot
          execute dbms_snapshot.refresh('mvowner.big_table_mv');


  (3) Check materialized veiw refresh status


set linesize 120
set pagesize 100
col owner format a20
col table_name format a20
col name format a20
col master_owner format a20
col master_link format a20
col next format a20

select owner
       ,name
--     ,table_name
--     ,master_owner
--     ,master
--     ,master_link
       ,to_char(last_refresh, 'YYYY-MON-DD HH24:MI:SS') lst_rfrsh
       ,next
       ,status
  from dba_snapshots
/
 

 (4)  views:
     dba_snapshots
     dba_mviews