Replikasi Data dengan Oracle Updatable materialized views [2]

Replikasi Data dengan Oracle Updatable materialized views  [2]

langkah berikutnya setup Create MV MASTER SITE      : PMR01

—————————————–
– 3. Create MV MASTER SITE      : PMR01
——————————————
conn system/oracle0@pmr01;
alter system set global_names=TRUE;
alter system set job_queue_processes = 1;
CREATE USER repadmin IDENTIFIED BY repadmin;
BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => ‘repadmin’);
END;
/
GRANT SELECT ANY DICTIONARY TO repadmin;

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

CONNECT repadmin/repadmin@pmr01;
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
– interval 1 detik
interval => ‘SYSDATE + 1/(60*60*24)’,
rollback_segment => ”,
delay_seconds => 0);
END;
/

———————————————
— 4. TAMBAHAN UNTUK MV MASTER SITE
—    dibandingkan advance-replication
———————————————
conn system/oracle0@pmr01;
CREATE USER proxyadmin IDENTIFIED BY proxyadmin
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 0 ON system
PROFILE default;

GRANT CONNECT,RESOURCE TO proxyadmin;

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

GRANT
CREATE SESSION,
SELECT ANY TABLE,
SELECT_CATALOG_ROLE
TO proxyadmin;

CREATE USER proxyrefresher IDENTIFIED BY proxyrefresher
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 0 ON system
PROFILE default;

GRANT CREATE SESSION TO proxyrefresher;
GRANT SELECT ANY TABLE TO proxyrefresher;

———————————–
— 5. create master group
———————————–
CONNECT repadmin/repadmin@pmr01
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => ‘REPG’);
END;
/

———————————————————————
— 6. KONFIGURASI REPLIKASI TABEL
———————————————————————
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => ‘”REPG”‘,
type => ‘TABLE’,
oname => ‘”T_BARANG”‘,
sname => ‘”SCOTT”‘,
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => ‘”REPG”‘,
type => ‘TABLE’,
oname => ‘”T_BELI”‘,
sname => ‘”SCOTT”‘,
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => ‘”REPG”‘,
type => ‘TABLE’,
oname => ‘”T_DBELI”‘,
sname => ‘”SCOTT”‘,
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => ‘”REPG”‘,
type => ‘TABLE’,
oname => ‘”T_JEN”‘,
sname => ‘”SCOTT”‘,
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
gname => ‘”REPG”‘,
type => ‘TABLE’,
oname => ‘”T_SUP”‘,
sname => ‘”SCOTT”‘,
copy_rows => TRUE,
use_existing_object => TRUE);
END;
/

———————————–
— 7. create MATERIALIZED VIEW LOG ON
———————————–
CONNECT SCOTT/tiger@pmr01;
CREATE MATERIALIZED VIEW LOG ON SCOTT.T_BARANG  TABLESPACE USERS;
CREATE MATERIALIZED VIEW LOG ON SCOTT.T_JEN      TABLESPACE USERS;
CREATE MATERIALIZED VIEW LOG ON SCOTT.T_SUP      TABLESPACE USERS;
CREATE MATERIALIZED VIEW LOG ON SCOTT.T_BELI      TABLESPACE USERS;
CREATE MATERIALIZED VIEW LOG ON SCOTT.T_DBELI      TABLESPACE USERS;

Advertisement

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