jump to navigation

ADVANCE REPLIKASI with LIMITED BANDWIDTH Agustus 29, 2009

Posted by setijoagus in Mikrotik, REPLICATION, oracle.
Tags: , ,
1 comment so far

Seberapa banyak bandwidth yang dibutuhkan untuk melakukan replikasi sebenarnya ?, sejujurnya saya juga nggak tahu, walaupun pertimbangannya adalah berapa banyak jumlah transaksi yang terjadi dalam satuan waktu tertentu.
Berikut saya melakukan percobaan kecil – kecilan dengan kondisi seperti skema ini

replication_01
dari hasil test bandwidth yang sudah saya lakukan didapat angka bandwidth yang tidak pernah mencapai lebih dari 16 Kbps

replication_02
Test segera dimulai
a. Pada server ORCL1, dilakukan perintah insert
SQL> insert into dept values (51,’dept’,'dept’);
SQL> insert into dept values (52,’dept’,'dept’);
………………………………………..
SQL> insert into dept values (96,’dept’,'dept’);
SQL> insert into dept values (97,’dept’,'dept’);
SQL> insert into dept values (98,’dept’,'dept’);
SQL> insert into dept values (99,’dept’,'dept’);
SQL> commit;

SQL> select to_char(sysdate, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sat 29-Aug-2009 14:44:40

SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
51 dept dept
52 dept dept
53 dept dept
54 dept dept
55 dept dept
56 dept dept
57 dept dept
58 dept dept
………………….
97 dept dept
98 dept dept
99 dept dept
53 rows selected.

b. Pada server ORCL2, beberapa saat kemudian [belum terjadi replikasi data]
SQL> select to_char(sysdate, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sat 29-Aug-2009 14:45:34

SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

c. Pada server ORCL2, dulangi langkah diatas [sudah terjadi replikasi data]
SQL> select to_char(sysdate, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sat 29-Aug-2009 14:45:36

SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
51 dept dept
52 dept dept
53 dept dept
54 dept dept
55 dept dept
56 dept dept
57 dept dept
58 dept dept
………………….
97 dept dept
98 dept dept
99 dept dept
53 rows selected.

jadi [Sat 29-Aug-2009 14:24:40] – [Sat 29-Aug-2009 14:25:36] = 56 detik
jadi 53 rows – 4 rows = 49 rows

d. Pada server ORCL1, dilakukan perintah delete
SQL> delete dept where deptno > 40;
49 rows deleted.
SQL> commit;
Commit complete.
SQL> select to_char(sysdate, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sat 29-Aug-2009 14:34:17
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

e. Pada server ORCL2, beberapa saat kemudian
SQL> select to_char(sysdate, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sat 29-Aug-2009 14:34:35

SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
51 dept dept
52 dept dept
53 dept dept
54 dept dept
55 dept dept
56 dept dept
57 dept dept
58 dept dept
………………….
97 dept dept
98 dept dept
99 dept dept
53 rows selected.

f. Pada server ORCL2, dulangi langkah diatas
SQL> select to_char(sysdate, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sat 29-Aug-2009 14:34:36
SQL> select * from dept order by deptno;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

jadi [Sat 29-Aug-2009 14:34:17] – [Sat 29-Aug-2009 14:34:36] = 19 detik
jadi 53 rows – 4 rows = 49 rows

Create Advance Replication [AGAIN & SIMPLE] Agustus 29, 2009

Posted by setijoagus in REPLICATION, oracle.
Tags: ,
add a comment

Ini tulisan Create Advance Replication, yang ketiga kali di blog ini, ada beberapa pertanyaan soal create replikasi yang sulit.
Berikut cara cepat create replikasi [2 server Oracle, tabel scott.DEPT & scott.EMP] , dengan syarat :
a. 2 oracle db server telah disetup dengan nama SID = ORCL1 & ORCL2
b. 2 oracle db telah startup
c. tnsnames.ora di masing – masing server telah tersetup dengan benar, contoh

Picture0001d. jalankan script, di ORCL1

Picture0002e. periksa apakah replikasi sudah bisa dieksekusi ?, jika COUNT masih belum bernilai = 0, maka replikasi belum dpt dijalankan

Picture0003f. tunggu beberapa saat, periksa kembali sampai dengan COUNT bernilai = 0

Picture0004g. start replikasi

Picture0005e. lampiran script bikin_replikasi.sql
————————————————————————
– orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1
————————————————————————
– 1. unlock user scott & setup global
————————————————————————
conn system/oracle007@orcl1
alter user scott account unlock;
alter user scott identified by tiger;
conn scott/tiger;

conn system/oracle007@orcl1
alter system set global_names=TRUE;
alter system set job_queue_processes = 1;

————————————————————————
– 2. create admin replikasi
– Grant privs to the propagator, to propagate changes to remote sites
– Grant privs to the receiver to apply deferred transactions
– Authorise the administrator to administer replication groups and schemas
————————————————————————
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;
/

————————————————————————————-
– 3. Schedule job to purge
————————————————————————————-
CONNECT repadmin/repadmin@orcl1
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
– interval 1 menit
interval => ’sysdate + 1*(1/(24*60))’,
delay_seconds => 0);
END;
/

————————————————————————
– 4. Create private db links for all repadmin users
————————————————————————
conn system/oracle007@orcl1
create public database link orcl2 using ‘orcl2′;
conn repadmin/repadmin@orcl1
create database link orcl2 connect to repadmin identified by repadmin;

————————————————————————
– 5. Schedule job to push
————————————————————————
conn repadmin/repadmin@orcl1
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘orcl2′,
– interval 1 menit
interval => ’sysdate + 1*(1/(24*60))’,
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500,
delay_seconds => 1200);
END;
/

————————————————————————
– 6. Create replication group for MASTERDEF site
– Register objects within the group
————————————————————————
conn repadmin/repadmin@orcl1
begin
dbms_repcat.create_master_repgroup (
gname => ’scott_repg’);
end;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ’scott_repg’,
type => ‘TABLE’,
oname => ‘EMP’,
sname => ’scott’,
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ’scott_repg’,
type => ‘TABLE’,
oname => ‘DEPT’,
sname => ’scott’,
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

conn repadmin/repadmin@orcl1
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ’scott’,
oname => ‘emp’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ’scott’,
oname => ‘dept’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

————————————————————————
– orcl2 orcl2 orcl2 orcl2 orcl2 orcl2 orcl2 orcl2 orcl2 orcl2 orcl2
————————————————————————
– 6. unlock user scott & setup global
————————————————————————
conn system/oracle007@orcl2
alter user scott account unlock;
alter user scott identified by tiger;
conn scott/tiger;

conn system/oracle007@orcl2
alter system set global_names=TRUE;
alter system set job_queue_processes = 1;

————————————————————————
– 7. create admin replikasi
– Grant privs to the propagator, to propagate changes to remote sites
– Grant privs to the receiver to apply deferred transactions
– Authorise the administrator to administer replication groups and schemas
————————————————————————
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;
/

————————————————————————————-
– 8. Schedule job to purge
————————————————————————————-
CONNECT repadmin/repadmin@orcl2
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
– interval 1 menit
interval => ’sysdate + 1*(1/(24*60))’,
delay_seconds => 0);
END;
/

————————————————————————
– 9. Create private db links for all repadmin users
————————————————————————
conn system/oracle007@orcl2
create public database link orcl1 using ‘orcl1′;
conn repadmin/repadmin@orcl2
create database link orcl1 connect to repadmin identified by repadmin;

————————————————————————
– 10. Schedule job to push
————————————————————————
conn repadmin/repadmin@orcl2
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘orcl1′,
– interval 1 menit
interval => ’sysdate + 1*(1/(24*60))’,
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500,
delay_seconds => 1200);
END;
/

————————————————————————
– 11. Add master desination sites
————————————————————————
conn repadmin/repadmin@orcl1
BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => ’scott_repg’,
master => ‘orcl2′,
use_existing_objects => TRUE,
copy_rows => FALSE,
propagation_mode => ‘ASYNCHRONOUS’);
END;
/

Demo Advance Replication with Metode Conflicts Resolution Agustus 29, 2009

Posted by setijoagus in REPLICATION, oracle.
Tags: , , ,
add a comment

Setelah dilakukan perubahan pada setting Advance Replication, terutama dengan penambahan kolom TIMESTAMP, seperti yang terlihat pada gambar di bawah ini
Picture001
Sekarang waktunya untuk melakukan test.
a. di sisi server ORCL1 dilakukan perubahan data pada baris yang bersesuaian
SQL> conn scott/tiger@orcl2
SQL> update dept set dname = ‘ORCL1′, timestamp = systimestamp
where deptno = 40 ;
SQL> commit;

b. demikiam juga di sisi server ORCL2 dilakukan perubahan data pada baris yang bersesuaian
SQL> conn scott/tiger@orcl1
SQL> update dept set dname = ‘ORCL1′, timestamp = systimestamp
where deptno = 40 ;
SQL> commit;

c. setelah dilakukan test hasilnya seperti terlihat dibawah ini, terlihat ada perbedaan isi.
Picture002d. beberapa saat kemudian hasil yang dianggap valid adalah “LATEST TIMESTAMP” pada orcl1, yang pada akhirnya terupdate hasil server ORCL2

Picture003

Create Advance Replication with Metode Conflicts Resolution Agustus 28, 2009

Posted by setijoagus in REPLICATION, oracle.
Tags: ,
add a comment

Untuk membuat agar tabel – tabel yang telah di replikasi, memiliki Metode Conflicts Resolution, maka perlu ada penambahan kolom, pada contoh di bawah ini ditunjukkan bagaimana membuat Metode Conflicts Resolution – LATEST TIMESTAMP.
Jika pada contoh http://www.dba-oracle.com/art_select_repl.pdf, menggunakan sysdate sebagai Metode Conflicts Resolution – LATEST TIMESTAMP, pada contoh ini saya menggunakan systimestamp.

a. siapkan terlebih dulu agar server – server memiliki ukuran waktu yang sama, bisa menggunakan teknologi ntp server, detail ntp server bisa dilihat di http://setijoagus.wordpress.com/2009/03/27/membangun-server-client-network-time-protocol-ntp/

setup_01

b. melakukan re-konfigurasi kembali agar server oracle support Metode Conflicts Resolution – LATEST TIMESTAMP, langkah – langkahnya :
1. stop replikasi
2. alter tabel TIMESTAMP
3. Register objects within the group
4. conflict resolution & column group
5. start replikasi

setup_02

setup_03

c. Script lengkap

————————————————————————
– orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1 orcl1
————————————————————————
– 1. stop replikasi
————————————————————————
CONNECT repadmin/repadmin@orcl1
BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => ’scott_repg’);
END;
/

————————————————————————
– 2. alter tabel TIMESTAMP
————————————————————————
BEGIN
DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
sname => ’scott’,
oname => ‘emp’,
type => ‘TABLE’,
ddl_text => ‘ALTER TABLE SCOTT.EMP ADD (TIMESTAMP TIMESTAMP(6) WITH LOCAL TIME ZONE)’);
END;
/

BEGIN
DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
sname => ’scott’,
oname => ‘dept’,
type => ‘TABLE’,
ddl_text => ‘ALTER TABLE SCOTT.DEPT ADD (TIMESTAMP TIMESTAMP(6) WITH LOCAL TIME ZONE)’);
END;
/

————————————————————————
– 3. Register objects within the group
————————————————————————
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ’scott’,
oname => ‘emp’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ’scott’,
oname => ‘dept’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

————————————————————————
– 4. conflict resolution & column group
————————————————————————
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ’scott_repg’,
type => ‘TRIGGER’,
oname => ‘insert_time’,
sname => ’scott’,
ddl_text => ‘CREATE OR REPLACE TRIGGER scott.insert_time
BEFORE
INSERT OR UPDATE ON scott.emp FOR EACH ROW
BEGIN
IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
:NEW.TIMESTAMP := SYSTIMESTAMP;
END IF;
END;’);
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ’scott_repg’,
type => ‘TRIGGER’,
oname => ‘insert_time2′,
sname => ’scott’,
ddl_text => ‘CREATE OR REPLACE TRIGGER scott.insert_time2
BEFORE
INSERT OR UPDATE ON scott.dept FOR EACH ROW
BEGIN
IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
:NEW.TIMESTAMP := SYSTIMESTAMP;
END IF;
END;’);
END;
/

BEGIN
DBMS_REPCAT.MAKE_COLUMN_GROUP (
sname => ’scott’,
oname => ‘emp’,
column_group => ‘emp_timestamp_cg’,
list_of_column_names => ‘ename,job,mgr,hiredate,sal,comm,deptno,timestamp’);
END;
/

BEGIN
DBMS_REPCAT.MAKE_COLUMN_GROUP (
sname => ’scott’,
oname => ‘dept’,
column_group => ‘dept_timestamp_cg’,
list_of_column_names => ‘dname,loc,timestamp’);
END;
/

BEGIN
DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
sname => ’scott’,
oname => ‘emp’,
column_group => ‘emp_timestamp_cg’,
sequence_no => 1,
method => ‘LATEST TIMESTAMP’,
parameter_column_name => ‘timestamp’);
END;
/

BEGIN
DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
sname => ’scott’,
oname => ‘dept’,
column_group => ‘dept_timestamp_cg’,
sequence_no => 1,
method => ‘LATEST TIMESTAMP’,
parameter_column_name => ‘timestamp’);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ’scott’,
oname => ‘emp’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ’scott’,
oname => ‘dept’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

————————————————————————
– 5. start replikasi
————————————————————————
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ’scott_repg’);
END;
/

Advance Replication Metode Conflicts Resolution Agustus 28, 2009

Posted by setijoagus in REPLICATION, oracle.
Tags: , ,
add a comment

Menurut http://www.dba-oracle.com/art_select_repl.pdf, untuk mengatasi Conflicts, pada Advance Replication ada beberapa cara :
art_01

Latest Timestamp Value

art_02

Site Priority Value

art_03

Advance Replication Conflicts Agustus 27, 2009

Posted by setijoagus in REPLICATION, oracle.
Tags:
add a comment

menurut http://download-east.oracle.com/docs/cd/B13789_01/server.101/b10732/repconfl.htm , jika menggunakan Advance Replication maka akan terjadi Conflict, type – tye conflict yang mungkin terjadi adalah :
1. Uniqueness Conflicts
2. Update Conflicts
3. Delete Conflicts

contoh Uniqueness Conflicts
misalkan pada saat bersamaan [selisih milli second / second / belum sampai schedulle push terjadi] di sisi kantor pusat [sid = orcl1] dengan kantor cabang [sid = orcl2], dilakukan insert data dengan kolom PK yang sama
SQL> conn scott/tiger@orcl1
SQL> insert into dept values (60,’orcl1′,’orcl1′);
SQL> commit;

SQL> conn scott/tiger@orcl2
SQL> insert into dept values (60,’orcl2′,’orcl2′);
SQL> commit;

maka akan terjadi Uniqueness Conflicts
replikasi_conflicts_01

replikasi_conflicts_02

replikasi_conflicts_03

Advance Replication Server Down ? Agustus 27, 2009

Posted by setijoagus in Linux, REPLICATION, oracle.
Tags:
add a comment

Apa yang terjadi jika Advance Replikasi, yang menghubungkan kantor pusat [sid = orcl1] dengan kantor cabang [sid = orcl2] salah satu servernya Down ? berikut demonya.
a. dimana server kantor cabang [sid = orcl2] down
replikasi_db_down_01
b. server kantor pusat [sid = orcl1] up dan sedang melakukan transaksi [insert data]
SQL> conn scott/tiger@orcl1
SQL> insert into dept values (51,’dept’,'dept’,systimestamp);
SQL> insert into dept values (52,’dept’,'dept’,systimestamp);
SQL> insert into dept values (53,’dept’,'dept’,systimestamp);
SQL> insert into dept values (54,’dept’,'dept’,systimestamp);
SQL> insert into dept values (55,’dept’,'dept’,systimestamp);
SQL> commit;
replikasi_db_down_02
c. beberapa saat kemudian server kantor cabang [sid = orcl2] up kembali
replikasi_db_down_03
d. jreng – jreng ……
replikasi_db_down_04

OEM Java Console 10G Agustus 7, 2009

Posted by setijoagus in REPLICATION, oracle.
Tags: , , ,
add a comment

untuk me-menage replikasi, akan lebih “pas”, jika menggunakan OEM, untuk Oracle 10G karena OEM dalam masa peralihan (dari java ke web based), ada beberapa fungsi yang belum ada di web based salah satunya “replication”.
untuk itu kita perlu melakukan install oem Java Console untuk 10G

caranya adalah sebagai berikut :
1. download dari otn
http://download-llnw.oracle.com/otn/nt/oracle10g/10201/10201_client_win32.zip

2. install

oem01

3. jalankan

oem02

4. contoh – contoh tampilan OEM untuk replication

oem03

oem04

BASIC REPLICATION MASTER – VIEW (Materialized View) September 9, 2008

Posted by setijoagus in REPLICATION.
Tags: , ,
2 comments

########################################
# Materialized View
# Setup di sisi MASTER
########################################
1. Materialized View Log at Master Site
[oracle@iscdd ~]$ sqlplus scott/tiger@iscdd
SQL> CREATE TABLE dept_ku AS SELECT * FROM dept;
SQL> CREATE MATERIALIZED VIEW LOG ON dept_ku;
CREATE MATERIALIZED VIEW LOG ON dept_ku
*
ERROR at line 1:
ORA-12014: table ‘DEPT_KU’ does not contain a primary key constraint

— Materialized view log tidak dapat dibuat pada tables tanpa primary key.

SQL> desc dept_ku;
Name                                      Null?    Type
—————————————– ——– —————————-
DEPTNO                                             NUMBER(2)
DNAME                                              VARCHAR2(14)
LOC                                                VARCHAR2(13)

SQL> ALTER TABLE dept_ku ADD CONSTRAINT pk_dept_ku PRIMARY KEY (deptno);
SQL> CREATE MATERIALIZED VIEW LOG ON dept_ku;

########################################
# Setup di sisi VIEW SITE
########################################
2. Set up Materialized View Site
[oracle@iscdd2 ~]$ sqlplus system@iscdd2
SQL> CREATE USER remote_user IDENTIFIED BY abc DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
SQL> GRANT connect, resource, create materialized view, create database link TO remote_user;

3. Database Link
SQL> connect remote_user/abc@iscdd2
SQL> CREATE DATABASE LINK iscdd_ku CONNECT TO scott IDENTIFIED BY tiger USING ‘iscdd’;

4. Materialized View
SQL> CREATE MATERIALIZED VIEW mv1_ku REFRESH FAST AS SELECT * FROM scott.dept_ku@iscdd_ku;

5. Refresh Group
SQL> BEGIN
dbms_refresh.make(
name => ‘mv1_refgroup’,
list => ‘mv1_ku’,
next_date => sysdate,
—- 5 menit refresh
interval => ’sysdate + 1 / ((24*60)/5)’,
implicit_destroy => true,
lax => true);
END;
/

6. Refresh Manual
SQL> exec dbms_refresh.refresh(name => ‘mv1_refgroup’);

########################################
# TEST — TEST
########################################
########################################
# Master Site
########################################
7. test update di master site, hasil lihat di view site
[oracle@iscdd dbs]$ sqlplus scott/tiger@iscdd
SQL> select * from dept_ku;
DEPTNO DNAME          LOC
———- ————– ————-
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
SQL> insert into dept_ku values(50,’IT’,'Lamongan’);
SQL> commit;
SQL> select to_char(sysdate , ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Mon 08-Sep-2008 21:42:09
SQL> select to_char(sysdate + 1/((24*60)/5), ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Mon 08-Sep-2008 21:47:09

########################################
# View Site
########################################
[oracle@iscdd2 ~]$ sqlplus remote_user/abc@iscdd2
SQL> select * from mv1_ku;
DEPTNO DNAME          LOC
———- ————– ————-
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
SQL> select to_char(sysdate , ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Mon 08-Sep-2008 21:42:59
SQL> select to_char(sysdate , ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Mon 08-Sep-2008 21:50:32
SQL>  select * from mv1_ku;

DEPTNO DNAME          LOC
———- ————– ————-
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
50 IT             Lamongan

materialized view September 5, 2008

Posted by setijoagus in REPLICATION, oracle.
Tags:
add a comment

materialized view adalah sebuah database object yang berisi hasil dari sebuah query, yang
membedakan dengan view adalah hasil query disimpan secara fisik.

syntax MATERIALIZED VIEW :
CREATE MATERIALIZED VIEW <view_name>
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS (<Sql Query >)

1. menyiapkan kelinci percobaan
SQL> conn scott/tiger
SQL> select * from dept;

DEPTNO DNAME          LOC
———- ————– ————-
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

SQL> insert into dept values(41,’IT’,'Surabaya’);
SQL> insert into dept values(42,’IT’,'Jakarta’);
SQL> commit;
SQL> select * from dept;

DEPTNO DNAME          LOC
———- ————– ————-
41 IT             Surabaya
42 IT             Jakarta
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

2. memberi grant untuk create VIEW dan MATERIALIZED VIEW
SQL> conn system
SQL> grant create any VIEW to scott;
SQL> grant drop any VIEW to scott;
SQL> grant create any MATERIALIZED VIEW to scott;
SQL> grant drop any MATERIALIZED VIEW to scott;

SQL> conn scott/tiger
SQL> create view view_dept as select * from  dept;
SQL> create materialized view mv_dept as select * from scott.dept;

3. Rowid dept & view_dept sama, sedangkan mv_dept tidak sama
SQL> select ROWID from dept;
ROWID
——————
AAAMfKAAEAAAAAQAAA
AAAMfKAAEAAAAAQAAB
AAAMfKAAEAAAAAQAAC
AAAMfKAAEAAAAAQAAD
AAAMfKAAEAAAAANAAA
AAAMfKAAEAAAAANAAB

6 rows selected.

SQL>  select ROWID from view_dept;
ROWID
——————
AAAMfKAAEAAAAAQAAA
AAAMfKAAEAAAAAQAAB
AAAMfKAAEAAAAAQAAC
AAAMfKAAEAAAAAQAAD
AAAMfKAAEAAAAANAAA
AAAMfKAAEAAAAANAAB

6 rows selected.

SQL> select ROWID from mv_dept;
ROWID
——————
AAANDsAAEAAAAG0AAC
AAANDsAAEAAAAG0AAD
AAANDsAAEAAAAG0AAE
AAANDsAAEAAAAG0AAF
AAANDsAAEAAAAG0AAA
AAANDsAAEAAAAG0AAB

6 rows selected.

SQL>

4. apakah MATERIALIZED VIEW, terupdate ?, jika isi table dihapus
SQL> delete dept where deptno = 42;
SQL> commit;
SQL> select * from dept;

DEPTNO DNAME          LOC
———- ————– ————-
41 IT             Surabaya
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

SQL> select * from view_dept;

DEPTNO DNAME          LOC
———- ————– ————-
41 IT             Surabaya
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

SQL> select * from mv_dept;

DEPTNO DNAME          LOC
———- ————– ————-
41 IT             Surabaya
42 IT             Jakarta
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

5. agar MATERIALIZED VIEW, terupdate manual.
SQL> execute dbms_mview.refresh( ‘mv_dept’ );
SQL> select * from mv_dept;
DEPTNO DNAME          LOC
———- ————– ————-
41 IT             Surabaya
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

6. contoh syntac MATERIALIZED VIEW lainnya
SQL> CREATE MATERIALIZED VIEW mv_dept2
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * from dept;

7. test MATERIALIZED VIEW contoh no 6

SQL> delete dept where deptno = 41;
SQL> select * from mv_dept;
 DEPTNO DNAME          LOC
———- ————– ————-
41 IT             Surabaya
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

SQL> select * from mv_dept2;
DEPTNO DNAME          LOC
———- ————– ————-
41 IT             Surabaya
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
— mv_dept dan mv_dept2 sama – sama belum terupdate

SQL> commit;
SQL> select * from mv_dept;
DEPTNO DNAME          LOC
———- ————– ————-
41 IT             Surabaya
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

SQL> select * from mv_dept2;
DEPTNO DNAME          LOC
———- ————– ————-
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

— mv_dept belum terupdate, mv_dept2 sudah terupdate sebab telah di-commit

8. contoh syntac MATERIALIZED VIEW lainnya, update dengan interval waktu
SQL> CREATE MATERIALIZED VIEW mv_dept3
BUILD IMMEDIATE
REFRESH FAST START WITH SYSDATE NEXT SYSDATE + (1/72)
AS SELECT * from dept;

9. test contoh no 8
SQL> insert into dept values(43,’IT’,'Semarang’);
SQL> commit;
SQL> select to_char(sysdate , ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sun 07-Sep-2008 19:03:29
SQL> select to_char(sysdate + 1/72, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sun 07-Sep-2008 19:23:38

— kurang lebih data akan terupdate setelah 20 menit kemudian

SQL> select to_char(sysdate , ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sun 07-Sep-2008 19:23:19
SQL> select * from mv_dept3;
DEPTNO DNAME          LOC
———- ————– ————-
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
43 IT             Semarang

10. test contoh no 8, dengan memeriksa job kapan diupdate
SQL> insert into dept values(44,’IT’,'MADIUN’);
SQL> COMMIT;
SQL> select * from mv_dept3;
DEPTNO DNAME          LOC
———- ————– ————-
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
43 IT             Semarang

SQL> conn system
Enter password:
SQL> SELECT
SUBSTR(job,1,4) “Job”, SUBSTR(log_user,1,5)          “User”,
SUBSTR(schema_user,1,5)                              “Schema”,
SUBSTR(TO_CHAR(last_date,’Dy DD-Mon-YYYY HH24:MI:SS’),1,30) “Last Date”,
SUBSTR(TO_CHAR(next_date,’Dy DD-Mon-YYYY HH24:MI:SS’),1,30) “Next Date”,
SUBSTR(broken,1,2) “B”, SUBSTR(failures,1,6)        “Failed”,
SUBSTR(what,1,20)                                    “Command”
FROM dba_jobs;

Job  User  Schem Last Date                Next Date                B Failed
—- —– —– ———————— ———————— – ——
Command
——————–
1    SYSMA SYSMA Sun 07-Sep-2008 21:05:54 Sun 07-Sep-2008 21:06:54 N 0
EMD_MAINTENANCE.EXEC
21   MVIEW MVIEW Sun 07-Sep-2008 20:23:02 Sun 07-Sep-2008 21:23:02 N 0
dbms_refresh.refresh
41   SCOTT SCOTT Sun 07-Sep-2008 21:03:09 Sun 07-Sep-2008 21:23:09 N 0
dbms_refresh.refresh

—terlihat data akan terupdate setelah 20 menit kemudian