vicker313 tech blog

July 11, 2009

Setup Oracle Replication (Part 2: MV Site)

Filed under: Oracle — Tags: , — vicker313 @ 10:42 am

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

2 Comments »

  1. […] here to setup MV […]

    Pingback by Setup Oracle Replication (Part 3: Offline Export and Import) « vicker313 tech blog — July 19, 2009 @ 11:44 am

  2. […] Setup the Middle Tier MV Site Server. […]

    Pingback by Setup Oracle Replication (Part 4: 3 Tiers Replication) « vicker313 tech blog — August 1, 2009 @ 9:36 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: