Setup Oracle Replication (Part 2: MV Site)

Now continue to setup the Materialized View site (MV site). Please refer here to setup master site.

BEWARE AGAIN: Master Site Server needs to be stay static (no data changes) during the whole setup process, from Master Site Setup to MV Site Setup.

1. Create materialized view administrator (Connect as SYSTEM).
   CREATE USER mviewadmin IDENTIFIED BY mviewadmin;
EXECUTE DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA ('mviewadmin');
GRANT COMMENT ANY TABLE TO mviewadmin;
GRANT LOCK ANY TABLE TO mviewadmin;
GRANT SELECT ANY DICTIONARY TO mviewadmin;

2. Create the propagator (SYSTEM).
   CREATE USER propagator IDENTIFIED BY propagator;
EXECUTE DBMS_DEFER_SYS.REGISTER_PROPAGATOR ('propagator');

3. Create the refresher, responsible for “pulling” changes made to the replicated tables at the target master site to the materialized view site (SYSTEM).
   CREATE USER refresher IDENTIFIED BY refresher;
GRANT CREATE SESSION TO refresher;
GRANT ALTER ANY MATERIALIZED VIEW TO refresher;

4. Create database links to the master site (SYSTEM).
   CREATE [PUBLIC] DATABASE LINK [master site db name] USING '[net_service_name]';

5. Create the materialized view administrator database link (MVIEWADMIN).
   CREATE DATABASE LINK [master site db name]
CONNECT TO proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

6. Create the propagator/receiver database link (PROPAGATOR).
   CREATE DATABASE LINK [master site db name]
CONNECT TO repadmin IDENTIFIED BY repadmin;

7. Create necessary schema at the materialized view site (SYSTEM).
   CREATE USER [schema] IDENTIFIED BY [password];
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 [schema];

8. Create database link at the materialized view site (SCHEMA).
   CREATE DATABASE LINK [master site db name] CONNECT TO [master site schema] IDENTIFIED by [password];
9. Create necessary materialized view (SCHEMA).
   CREATE MATERIALIZED VIEW [schema].[mv name]
REFRESH FAST WITH PRIMARY KEY AS SELECT *
FROM [master site schema].[table name]@[master site db link];
10. Create an empty materialized view group (MVIEWADMIN).
    BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
gname => 'mview_repg',
master => '[master site db name]',
propagation_mode => 'ASYNCHRONOUS');
END;
/

11. Create an empty refresh group. We can refresh (or replicate) the data for a single table, but if it is more than 1 table, we can create a refresh group so that we can schedule and refresh the data in 1 go (MVIEWADMIN).
    BEGIN
DBMS_REFRESH.MAKE (
name => 'mview_refg',
list => '',
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
implicit_destroy => FALSE,
rollback_seg => '',
push_deferred_rpc => FALSE,
refresh_after_errors => FALSE);
END;
/

12. Add materialized views to the materialized view group (MVIEWADMIN).
    BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
gname => 'mview_repg',
sname => '[schema]',
oname => '[object name]',
type => '[INDEX|TABLE|VIEW|FUNCTION]',
min_communication => TRUE);
END;
/

13. Add the materialized views to the refresh group (MVIEWADMIN).
    BEGIN
DBMS_REFRESH.ADD(
name => '"MVIEWADMIN"."MVIEW_REFG"',
list => '"[SCHEMA]"."[TABLE NAME]"',
lax => TRUE);
END;
/

14. Refresh materialized views to register them at master site. This is also method to manually refresh the refresh group (MVIEWADMIN).
    EXECUTE DBMS_REFRESH.REFRESH ('mview_refg');

Advertisements