Replikasi Data dengan Oracle Updatable materialized views [3]

Replikasi Data dengan Oracle Updatable materialized views  [3]

langkah berikutnya setup Create MV MASTER VIEW SITE      : PMR02

—————————————————
– 8. Create MV MASTER VIEW SITE      : PMR02
—————————————————
conn system/oracle0@pmr02;
alter system set global_names=TRUE;
alter system set job_queue_processes = 1;
CONNECT sys/oracle0@pmr02 as sysdba;
CREATE USER mviewadmin IDENTIFIED BY mviewadmin
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 0 ON system
PROFILE default;
GRANT CONNECT,RESOURCE TO mviewadmin;

BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => ‘mviewadmin’);
END;
/

GRANT
LOCK ANY TABLE,
COMMENT ANY TABLE,
CREATE SESSION,
SELECT ANY TABLE,
CREATE TABLE,
CREATE PROCEDURE,
CREATE SEQUENCE,
CREATE TRIGGER,
CREATE VIEW,
CREATE SYNONYM,
ALTER SESSION,
CREATE MATERIALIZED VIEW,
CREATE DATABASE LINK,
SELECT ANY DICTIONARY
TO mviewadmin;

CREATE USER propagator IDENTIFIED BY propagator
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 0 ON system
PROFILE default;
GRANT CONNECT,RESOURCE TO propagator;

BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
username => ‘propagator’);
END;
/

BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => ‘mviewadmin’,
privilege_type => ‘receiver’,
list_of_gnames => NULL);
END;
/

CONNECT sys/oracle0@pmr02 as sysdba;
CREATE PUBLIC DATABASE LINK PMR01 USING ‘PMR01′;

CONNECT mviewadmin/mviewadmin@pmr02;
CREATE DATABASE LINK RAC
CONNECT TO proxyadmin
IDENTIFIED BY proxyadmin
USING ‘PMR01′;

CONNECT propagator/propagator@pmr02
CREATE DATABASE LINK RAC
CONNECT TO repadmin
IDENTIFIED BY repadmin
USING ‘PMR01′;

———————————–
– 9. SCHEDULE_PURGE & SCHEDULE_PUSH
———————————–
CONNECT mviewadmin/mviewadmin@pmr02
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
– interval 1 detik
interval => ‘SYSDATE + 1/(60*60*24)’,
delay_seconds => 0,
rollback_segment => ”);
END;
/

CONNECT mviewadmin/mviewadmin@pmr02
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘PMR01′,
– interval 1 detik
interval => ‘SYSDATE + 1/(60*60*24)’,
next_date => SYSDATE,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 0);
END;
/

———————————–
– 10. CREATE_MVIEW_REPGROUP & REFRESH
———————————–
CONNECT mviewadmin/mviewadmin@pmr02;
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
gname  => ‘REPG’,
master => ‘PMR01′,
propagation_mode => ‘ASYNCHRONOUS’);
END;
/

CONNECT mviewadmin/mviewadmin@pmr02;
BEGIN
DBMS_REFRESH.MAKE (
name => ‘REVG’,
list => ”,
next_date => SYSDATE,
– interval 1 detik
interval => ‘SYSDATE + 1/(60*60*24)’,
implicit_destroy => FALSE,
rollback_seg => ”,
push_deferred_rpc => TRUE,
refresh_after_errors => TRUE);
END;
/

———————————–
– 11. grant MVIEW
———————————–
CONNECT sys/oracle0@pmr02 as sysdba;
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 SCOTT;

CONNECT SCOTT/tiger@pmr02;
CREATE DATABASE LINK pmr01
CONNECT TO proxyrefresher IDENTIFIED BY proxyrefresher
USING ‘PMR01′;

———————————–
– 12. Create MATERIALIZED VIEW
———————————–
CONNECT SCOTT/tiger@pmr02;
CREATE MATERIALIZED VIEW T_BARANG    REFRESH FAST WITH PRIMARY KEY FOR UPDATE    AS SELECT * FROM  SCOTT.T_BARANG@PMR01;
CREATE MATERIALIZED VIEW T_JEN        REFRESH FAST WITH PRIMARY KEY FOR UPDATE    AS SELECT * FROM  SCOTT.T_JEN@PMR01;
CREATE MATERIALIZED VIEW T_SUP        REFRESH FAST WITH PRIMARY KEY FOR UPDATE    AS SELECT * FROM  SCOTT.T_SUP@PMR01;
CREATE MATERIALIZED VIEW T_BELI        REFRESH FAST WITH PRIMARY KEY FOR UPDATE    AS SELECT * FROM  SCOTT.T_BELI@PMR01;
CREATE MATERIALIZED VIEW T_DBELI    REFRESH FAST WITH PRIMARY KEY FOR UPDATE    AS SELECT * FROM  SCOTT.T_DBELI@PMR01;

———————————–
– 13. Create SNAPSHOT
———————————–
conn mviewadmin/mviewadmin@pmr02
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
gname => ‘REPG’,
Type  => ‘SNAPSHOT’,
sname  => ‘SCOTT’,
oname => ‘T_BARANG’,
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
gname => ‘REPG’,
Type  => ‘SNAPSHOT’,
sname  => ‘SCOTT’,
oname => ‘T_JEN’,
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
gname => ‘REPG’,
Type  => ‘SNAPSHOT’,
sname  => ‘SCOTT’,
oname => ‘T_SUP’,
min_communication => TRUE);
END;
/
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
gname => ‘REPG’,
Type  => ‘SNAPSHOT’,
sname  => ‘SCOTT’,
oname => ‘T_BELI’,
min_communication => TRUE);
END;
/
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
gname => ‘REPG’,
Type  => ‘SNAPSHOT’,
sname  => ‘SCOTT’,
oname => ‘T_DBELI’,
min_communication => TRUE);
END;
/

———————————–
– 14. Create Group Refresh
———————————–
conn mviewadmin/mviewadmin@pmr02
BEGIN
DBMS_REFRESH.ADD (
name => ‘REVG’,
list => ‘SCOTT.T_BARANG’,
lax => TRUE);
END;
/

BEGIN
DBMS_REFRESH.ADD (
name => ‘REVG’,
list => ‘SCOTT.T_JEN’,
lax => TRUE);
END;
/

BEGIN
DBMS_REFRESH.ADD (
name => ‘REVG’,
list => ‘SCOTT.T_SUP’,
lax => TRUE);
END;
/

BEGIN
DBMS_REFRESH.ADD (
name => ‘REVG’,
list => ‘SCOTT.T_BELI’,
lax => TRUE);
END;
/

BEGIN
DBMS_REFRESH.ADD (
name => ‘REVG’,
list => ‘SCOTT.T_DBELI’,
lax => TRUE);
END;
/

Advertisement

Tags: materialized view, MV, Oracle Updatable materialized View, replikasi, Replikasi Data, Updatable materialized View