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.
- 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;
- Create the propagator (SYSTEM).
CREATE USER propagator IDENTIFIED BY propagator;
EXECUTE DBMS_DEFER_SYS.REGISTER_PROPAGATOR ('propagator');
- 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;
- Create database links to the master site (SYSTEM).
CREATE [PUBLIC] DATABASE LINK [master site db name] USING '[net_service_name]';
- Create the materialized view administrator database link (MVIEWADMIN).
CREATE DATABASE LINK
[master site db name]
CONNECT TO proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;
- Create the propagator/receiver database link (PROPAGATOR).
CREATE DATABASE LINK
[master site db name]
CONNECT TO repadmin IDENTIFIED BY repadmin;
- 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];
- Create database link at the materialized view site (SCHEMA).
CREATE DATABASE LINK [master site db name] CONNECT TO [master site schema] IDENTIFIED by [password];
- 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]; - 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;
/
- 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;
/
- 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;
/
- Add the materialized views to the refresh group (MVIEWADMIN).
BEGIN
DBMS_REFRESH.ADD(
name => '"MVIEWADMIN"."MVIEW_REFG"',
list => '"[SCHEMA]"."[TABLE NAME]"',
lax => TRUE);
END;
/
- 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
[…] here to setup MV […]
Pingback by Setup Oracle Replication (Part 3: Offline Export and Import) « vicker313 tech blog — July 19, 2009 @ 11:44 am
[…] 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