Replikasi: Membuat Materialized View (Snapshot)

Solusi replikasi dari Oracle di antaranya adalah Oracle Stream dan Advanced Replication . Advanced Replication meliputi Multimaster, Materialized View, dan hybrid (antara Multimaster replication dan materialized view).

Istilah Materialized View (MV) dipakai Oracle sejak versi 9i. Di versi 8i ke bawah di sebut sebagai snapshot. MV merupakan View yang dimaterialisasi. View konvensional tidak menyimpan data, dia hanya menyimpan definisi (nama kolom, table) sementara data secara fisik masih ada di tabel source-nya. Saya punya contoh 2 view:

  1. Query ke suatu tabel di database lain melalui database link (db link)
  2. Query yang join ke beberapa tabel

Kalau kedua View itu sering diakses sementara datanya sangat besar, dapat kita bayangkan betapa beratnya pekerjaan itu. Seandainya hasil view itu ditaruh ke dalam tabel dummy, dan akses ke view selanjutnya diarahkan ke tabel dummy tersebut, sungguh akan sangat mempercepat proses (tanpa query melalui database link yang dibatasi oleh bandwidth network, dan tanpa perlu melakukan join query yang berulang-ulang). Inilah yang mendasari Oracle untuk mematerialisasi view (semacam membuat tabel dummy untuk view tersebut).

MV dengan database link biasanya digunakan untuk replikasi (replication) dan distribusi data (distributed Database). Sementara MV dengan multi join (ke banyak tabel) digunakan untuk data warehouse.

Berikut ini langkah-langkah membuat MV dengan database link. Dalam script ini kata MATERIALIZED VIEW saya ganti SNAPSHOT, di mana dua terminologi ini mempunyai arti dan fungsi yang sama.

ENVIRONMENT
Database source
– IP: 10.21.106.81
– Nama Instance: DBSOURCE
– Nama Table: TBLMV
– Nama Owner: OWNSOURCE

Di Database yang akan kita buat Snapshot:
– Nama Owner: SNAPSHOT_USER
– Nama snapshot: TBLMV. Refresh (sinkronisasi) dilakukan tiap malam
– TNS Name untuk koneksi ke database DBSOURCE: DBSOURCE
– Nama db link: LINKDBSOURCE

Persiapan di Database Source
Buat MV log (snapshot log)
SQL> conn OWNSOURCE
SQL> create snapshot log on TBLMV tablespace USERS;

Snapshot log menyimpan delta (perubahan) data di tabel source. Dengan adanya log ini, ketika snapshot di-refresh, maka snapshot hanya mengambil delta yang ada di source tersebut. Inilah yang disebut dengan refresh fast. Kalau tidak menggunakan snapshot log, kita tidak bisa melakukan refresh fast, dengan kata lain ketika refresh maka yang dilakukan adalah melakukan query ulang ke tabel source.

Ini step-step di database tempat Snapshot

  1. Siapkan tablespace untuk menyimpan data milik schema SNAPSHOT_USER
    Create tablespace snapshot_tbs
    datafile '/data/snapshot_tbs01.dbf' size 8192M;
    alter tablespace snapshot_tbs add
    datafile '/data/snapshot_tbs02.dbf' size 8192M;
  2. Buat user
    SQL> conn / as sysdba
    SQL> create user snapshot_user identified by napshot_passwd default tablespace snapshot_tbs temporary tablespace temp;
    SQL> grant resource to snapshot_user;
    SQL> grant connect to snapshot_user;
    SQL> grant create snapshot to snapshot_user;
    SQL> grant create database link to snapshot_user;
    SQL> grant create public synonym to snapshot_user;
  3. Buat TNS Names. Bisa pakai netca atau manual dengam menambahkan entry berikut di file $ORACLE_HOME/network/admin/tnsnames.ora
    DBSOURCE =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS=(PROTOCOL=TCP)(HOST=10.21.106.81)(PORT=1521))
    )
    (CONNECT_DATA =
    (service_name = DBSOURCE)
    )
    )
  4. Buat database link
    SQL> conn SNAPSHOT_USER
    SQL> create database link LINKDBSOURCE connect to OWNSOURCE identified by OWNSOURCE_PASSWD using 'DBSOURCE';
  5. Buat Snapshot
    SQL> conn SNAPSHOT_USER
    SQL> create snapshot TBLMV
    refresh fast
    start with sysdate next trunc(sysdate + 1)
    as select * from TBLMV@LINKDBSOURCE;
    Snapshot di atas akan direfresh mulai nanti malam jam 12, trunc(sysdate + 1). Pekerjaan refresh ini dilakukan oleh job Oracle, lihat jobnya di view USER_JOBS atau DBA_JOBS. Dengan DBMS_JOB kita bisa merubah schedule refresh.Secara fisik, snapshot TBLMV mempunyai tabel dengan name TBLMV (disimpan di default tablespace: snapshot_tbs).
  6. Kalau diperlukan, kitapun bisa membuat index di tabel TBLMV sesuai kehendak kita (tidak ada aturan untuk menyamakan index di sini dengan yang di SOURCE). Contoh, kita akan membuat index untuk kolom nilai:
    SQL> create index TBLMV_01_IDX on TBLMV (nilai)

Referensi

  1. Oracle® Database Advanced Replication – 10g Release 2 (10.2)
    Materialized View Concepts and Architecture
  2. Oracle® Database Data Warehousing Guide – 10g Release 2 (10.2)
    Basic Materialized Views
  3. Oracle® Database Data Warehousing Guide – 10g Release 2 (10.2)
    Advanced Materialized Views
This entry was posted in Administration and tagged , , , , . Bookmark the permalink.

7 Responses to Replikasi: Membuat Materialized View (Snapshot)

  1. abip says:

    wah mas rohmad, makasih bgt artikelnya,

    dari dulu g ngerti ttg mview nih (lum pernah baca sih sebenernya, hhe)….

  2. upxfcwl bshfcmtgy uobyjc vkoemwuf qivjng jnzkpbgwu jsivq

  3. Deni_k says:

    Makasih Mas, kebetulan sekalih lagi saya pelajari. cocok banget.
    Tapi mendukung ga ya, untuk type data blob …?

  4. dedyandrian says:

    nanya mas, proses refresh dalam mv berlangsung dua arah nggak?? jadi klo saya replikasi databases A ke database B , trus database B saya update record misalnya, apakah data di source replikasi (db A) ikut berubah???,

  5. jiyofine says:

    Terima kasih artikelnya mas. 🙂
    saya rencana mau ngerjakan TA dengan replikasi oracle ini.

    regards!

  6. dino says:

    nanya pk rahmat, saya punya tabel2 snapshot, ketika saya refresh dg perintah berikut:
    execute dbms_snapshot.refresh (‘nama tabel’,’f’);
    mengalami kegagalan, dg pesan eror sbb:
    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 794
    ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 851
    ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 832
    ORA-06512: at line 1

    padahal tabelnya semuanya ada…mohon pencerahan pak…trima kasih

  7. Adam says:

    makasih mas, artikel mas rohmad sangat berguna sekali..

Leave a Reply

Your email address will not be published. Required fields are marked *