Struktur database ORACLE

Struktur database ORACLE

Struktur database ORACLE

ini catatan kecil ketika belajar memahami oracle, jadi kalo ada yg kurang tepat mohon koreksi nya . Struktur komponen database oracle itu apa saja sih?

pengetahuan dasar mengenai arsitektur database Oracle merupakan modal dasar untuk memahami fitur-fitur Oracle selanjutnya.

Oracle Database dibangun menggunakan tiga struktur komponen, yaitu :

• Struktur Memori
• Struktur Porses
• Struktur File(storage/penyimpanan)

nah dari ketiga bagian tersebut memiliki fungsi dan kegunaan masing seperti berikut ini

Struktur Memori

struktur memori Oracle terdiri dari dua area memori yang dikenal dengan SGA (System Global Area) dan PGA (Program Global Area).

SGA adalah memori yang dialokasikan ketika database startup. SGA merupakan komponen dasar dari Oracle Instance. SGA terdiri dari beberapa elemen dengan masing-masing elemen didesain untuk tujuan tertentu.

PGA adalah memori yang dialokasikan ketika user proses yang melakukan koneksi database.

Struktur Proses

Ada tiga jenis proses dalam database Oracle, yaitu User process, Server process dan Background process

Struktur File

struktur penyimpanan Database Oracle dilihat dari dua sisi yaitu struktur logik dan fisik.

struktur logik merepresentasikan komponen di dalam database oracle seperti tabel, index dan cluster. Sedangkan struktur fisik merepresentasikan metode penyimpanan yang digunakan oleh database secara internal. struktur fisik berupa berbagai jenis file yang digunakan untuk menyimpan data.

struktur fisik datbase oracle terdiri dari tiga file utama yaitu control file, datafile, redolog file dan ditambah file pendukung lainnya yaitu parameter file, password file dan archive log file.

Struktur Database ORACLE

Dalam artikel sebelumnya, telah dibahas penjelasan antara oracle instance dan oracle database. Kali ini akan kita bahas struktur database yang dimiliki oleh oracle. Secara garis besar, database oracle memiliki tiga buah struktur utama yaitu memory structure, process structure, dan strorage structure. Oracle Instance menggunakan memory dan process structure untuk dapat berjalan sedangkan oracle database menggunakan storage structure untuk dapat menyimpan data file maupun control file di dalam database tersebut. Tentu ketiga struktur utama ini sangat penting karena tanpa adanya salah satu komponen saja, maka oracle instance tidak dapat mengakses oracle database atau sebaliknya oracle database tidak dapat diakses oleh oracle instance.


Memory Stucture
Di dalam memory structure terdapat dua buah komponen yaitu :
a. System Global Area (SGA)
SGA merupakan shared memory yang dapat digunakan oleh seluruh server process dan background process. Di dalamnya tersimpan informasi-informasi yang berupa data file dan control file dari instance.
Di dalam SGA terdapat struktur data sebagai berikut :
- Database Buffer Cache : berisi cache blok data yang berisi hasil pengembalian dari proses query database.
- Redo log buffer : menyimpan redo information yang digunakan untuk instance recovery. Informasi tersebut akan disimpan hingga ditulis ke dalam physical redo log file di dalam storage disk.
- Shared Pool : menyimpan berbagai macam konstruksi / informasi yang dapat dishare di antara user database.
- Large Pool : merupakan area optional yang dibutuhkan jika ada proses-proses yang memakan memory cukup besar seperti backup and recovery dan proses server I/O.
- Java Pool : digunakan oleh java code atau data yang menggunakan Java Virtual Machine (JVM).
- Stream Pool : Digunakan oleh Oracle Stream untuk proses streaming.
-
b. Process Global Area (PGA)
Berbeda dengan SGA, PGA lebih spesifik hanya digunakan secara khusus oleh masing-masing server dan background process. Minimal ada satu PGA yang dimiliki oleh tiap server process. Setiap PGA menyimpan data dan control information dari masing-masing proses yang menyediakan layanan dari tiap request oracle client.

Process Structure
Ketika sebuah program aplikasi atau tool oracle meminta service kepada oracle server, maka akan dibuatlah sebuah server process yang digunakan untuk mengeksekusi perintah yang dikeluarkan oleh program aplikasi tersebut. Masing-masing server process memiliki sebuah PGA tersendiri. Selain itu, oracle server akan membentuk beberapa background process untuk oracle instance yang akan berinteraksi antara satu dengan yang lainnya dan juga dengan sistem operasi untuk mengatur penggunaan memory structure, proses penulisan data I/O ke dalam disk, maupun tugas-tugas lainnya. Background process tersebut meliputi :
a. System Monitor (SMON) : Melakukan crash recovery jika pada start saat oracle instance terjadji kesalahan.
b. Process monitor (PMON) : Melakukan process cleanup ketika user process gagal dieksekusi.
c. Database writer (DBWn) : Menulis block-block yang telah berubah / termodifikasi dari database buffer cache ke dalam data file di dalam storage structure.
d. Checkpoint (CKPT) : Melakukan update seluruh data file dan control file yang berdasarkan check point yang paling akhir.
e. LogWritter (LGWR) : Menulis redo log entry yang berasal dari redo log buffer ke dalam redo log file di dalam storage structure.
f. Archiver (ARCn) : Mengopy redo log file ke dalam archival strorage pada saat log switch terjadi.
Pada saat program aplikasi menjalankan query ke dalam oracle server, maka oracle server akan mencari block data yang dimaksud ke dalam database buffer terlebih dahulu. Jika data yang dicari ditemukan maka data tersebut akan langsung dikembalikan ke program aplikasi, namun jika ternyata data yang dimaksud tidak ada di dalam database buffer, maka oracle server akan melakukan pencarian data ke dalam datafile di storage structure dan mengcopy data tersebut ke dalam database buffer sebelum dikembalikan ke program aplikasi yang merequest. Oracle server telah memiliki algoritma least recently used yang digunakan dalam pengaturan data mana saja yang masih dapat disimpan di dalam database buffer cache.

Physical Database Structure
Sebenarnya ada tiga file utama yang dapat dikatakan sebagai dasar pembentuk dari sebuah oracle database. Tanpa adanya salah satu file ini, maka database oracle tidak akan dapat digunakan. File-file tersebut adalah:
a. Control File : Control file merupakan file yang sangat penting karena berisi informasi mengenai physical database structure oracle. Tanpa adanya control file ini, maka kita tidak akan dapat mengakses data-data yang berada pada data file karena letak physical dari data file di dalam disk tersebut disimpan di dalam control file ini. Biasanya dalam oracle server terdapat lebih dari satu (biasanya 3) control file yang diletakkan pada directory yang berbeda agar jika suatu saat salah satu control file rusak, masih dapat direcovery dari control file yang lain.
b. Data File : berisi data user maupun aplikasi database.
c. Online redo log file : digunakan dalam proses instance recovery database apabila telah terjadi kerusakan. Jika datafile masih ada, maka oracle instance akan dapat merecovery database sesuai dengan data yang tersimpan di dalam online redo log file. Namun jika ternyata setelah terjadi kerusakan datafile hilang, maka database sudah tidak dapat direcovery lagi.
Selain ketiga file di atas, ada beberapa file tambahan yang juga sangat penting dalam oracle server.
a. Parameter file : berisi informasi konfigurasi oracle instance pada saat start up.
b. Password file : berisi informasi user agar user dapat melakukan koneksi dan administrasi ke dalam database server.
c. Backup file : Digunakan dalam proses database recovery apabila file database asli rusak.
d. Archive log file : Digunakan untuk menyimpan history operasi-operasi yang dilakukan terhadap database.
e. Trace dan alert log file : Berisi informasi-informasi kesalahan apabila terjadi internal error pada server dan background proses.

Mengenal Oracle PL/SQL (1): Contoh Kasus

PL/SQL (Procedural Language/Structured Query Language) merupakan pengembagan SQL oleh Oracle. Prasyarat mempelajari PL/SQL adalah paling tidak mengetahui dasar-dasar SQL. Sebagai awalan belajar PLSQL, mari kita lihat contoh kasus dan contoh blok PL/SQL berikut ini.

Misalkan saya punya tabel MYTAB. Tabel ini berisi data transaksi. Kolom rcg_id (menjadi PRIMARY KEY) berisi transaksi ID yang digenerate oleh SEQUENCE, jadi nilainya urut (karena digenerate oleh sequence) dan unique (karena primary key).

Saya ingin menghapus data transaksi di bawah tanggal 17-JUN-08. Kita bisa saja men-delete dengan perintah SQL berikut:

delete from MYTAB where
SYS_CREATION_DATE < to_date('17-JUN-08','DD-MON-YY');

Karena datanya sangat banyak, maka akan diperlukan undo (rollback) segment yang besar karena COMMIT dilakukan setelah proses delete selesai. Saya tidak ingin ada konsumsi rollback segment yang besar, karena akan mempengaruhi performa database dan tentu saja perlu UNDO space yang besar. Sebagai alternatif lainnya, saya ingin mendelete (dan commit) data secara per record. Nah, sekarang saatnya saya memakai PL/SQL.

Saya akan mendelete per record (baris). Acuan yang saya gunakan adalah kolom rcg_id, karena nilainya unique (primary key) dan urut (digenerate oleh sequence). Ini langkah-langkahnya

1. Saya perlu mendapat rcg_id minimal dan maximal untuk data transaksi di bawah tanggal 17-JUN-08
   SQL> select min (rcg_id),max(rcg_ID) from MYTAB where
   SYS_CREATION_DATE < to_date('17-JUN-08','DD-MON-YY');
2. Setelah mendapat rcg_id minimal dan maximal, selanjutnya saya akan buat PROSES-nya. Proses delete dimulai dari rcg_id minimal, kemudian rcg_id minimal + 1, kemudian rcg_id minimal + 2, dan seterusnya hingga mencapai rcg_id maximal
3. Selanjutnya saya akan buat program PL/SQL nya

Berikut ini block PL/SQL yang telah saya buat

DECLARE
V_rcg_min NUMBER;
v_rcg_max number;
V_iterasi NUMBER;
BEGIN
V_rcg_min:= &1;
v_rcg_max:= &2;
V_iterasi:=V_rcg_min;
WHILE V_iterasi <= v_rcg_max LOOP
delete from MYTAB where rcg_id=V_iterasi;
commit;
V_iterasi:=V_iterasi+1;
END LOOP;
dbms_output.put_line(’Deleting sucess’);
dbms_output.put_line(’Min RCG_ID ‘||V_rcg_min);
dbms_output.put_line(’Max RCG_ID ‘||V_rcg_max);
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line(’error here’);
END;
/

Penjelasan
Block PL/SQL di atas bisa kita jalankan langsung di SQLPlus. Bisa juga kita taruh di file dan kemudian dari SQLPlus kita panggil file tersebut. Contoh, block PL/SQL ini saya taruh di file roh.sql. Berikut ini cara manggil dari SQLPlus: (Ups, jangan lupa untuk menjalankan perintah “set serveroutput on” agar hasil dari “dbms_output.put_line” bisa tampak di monitor)

SQL> set serveroutput on
SQL> @roh.sql

Begitu script roh.sql kita jalankan, maka kita akan diminta memasukkan nilai untuk parameter &1 dan &2. Seperti ini tampilannya
SQL> @roh.sql
Enter value for 1: 305206565
old 6: V_rcg_min:= &1;
new 6: V_rcg_min:= 305206565 ;
Enter value for 2: 305209524
old 7: v_rcg_max:= &2;
new 7: v_rcg_max:= 305209524;

Anda bisa juga langsung menyertakan nilai &1 (305206565) dan &2 (305209524) ketika memanggil roh.sql
SQL> @roh.sql 305206565 305209524

Hasilnya akan nampak di monitor seperti berikut ini
==========================
Deleting sucess
Min RCG_ID 305206565
Max RCG_ID 305209524
PL/SQL procedure successfully completed.

Bila kita tidak menjalankan “set serveroutput on” sebelumnya, maka yang nampak di monitor hanya
PL/SQL procedure successfully completed.

Bila tidak ingin muncul pesan “PL/SQL procedure successfully completed”, jalankan command “set feed off” di SQLPlus
SQL> set serveroutput on

Mengenal Oracle PL/SQL (2): Struktur

Setelah melihat contoh penggunaannya, sekarang mari kita bahas dasar-dasar PL/SQL. Silahkan lihat block PL/SQL yang telah kita bahas tersebut:

DECLARE
V_rcg_min NUMBER;
v_rcg_max number;
V_iterasi NUMBER;
BEGIN
V_rcg_min:= &1;
v_rcg_max:= &2;
V_iterasi:=V_rcg_min;
WHILE V_iterasi <= v_rcg_max LOOP
delete from MYTAB where rcg_id=V_iterasi;
commit;
V_iterasi:=V_iterasi+1;
END LOOP;
dbms_output.put_line(’Deleting sucess’);
dbms_output.put_line(’Min RCG_ID ‘||V_rcg_min);
dbms_output.put_line(’Max RCG_ID ‘||V_rcg_max);
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line(’error here’);
END;
/


Struktur pokok PL/SQL adalah sebagai berikut
DECLARE
BEGIN
END;
/

DECLARE
Berisi deklarasi variabel. Adapun isi dari deklarasi variabel adalah nama variable, tipe data, constraint, dan default value. Setiap satu deklarasi diakhiri dengan tanda ; (titik koma). Contoh:

1. Yang wajib ada : nama variabel dan tipe data
   v_rcg_min NUMBER;
2. Dengan menambahkan nilai default
   v_jam_kerja INTEGER DEFAULT 40;
   v_jam_kerja INTEGER := 0;
3. Dengan menambahkan constraint “not null” dan nilai default
   v_acc_id INTEGER(4) NOT NULL := 9999;
4. Deklarasi konstanta
   v_jumlah_hari_pertahun CONSTANT INTEGER := 366;
   v_wni CONSTANT BOOLEAN := FALSE;
5. Memakai tipe data dari suatu kolom di tabel (contoh, tabel: tbl emp, kolom: empid)
   v_empid tbl_emp.empid%TYPE;
6. Contoh lain, silahkan lihat referensi

Bagian Utama
Bagian Utama ada di antara BEGIN dan END. Setelah END, tambahkan tanda ; (titik koma). Agar block PL/SQL bisa dieksekusi, tambahkan baris baru di bawah END dan beri tanda / (slash atau garis miring).

Bagian utama berisi operasi (pekerjaan) yang kita lakukan. Contoh block PL/SQL di atas berisi:
- Memberi nilai variable
- Operasi SQL (delete dan commit)
- Operasi aritmatika (penjumlahan )
- Control struktur ( LOOP dan WHILE)
- EXCEPTION (error handler)

Memberi nilai variabel
Cara memberi nilai pada variabel adalah memakai := (titik dua dan sama dengan). Contoh:
v_rcg_min:= &1;
v_rcg_max:= 100;
v_iterasi:=v_rcg_min;

Kita juga bisa memberi nilai ke variabel melalui SQL command. Lihat contoh berikut ini, nilai untuk variabel v_job adalah hasil dari “select job from emp where EMPID=10″
DECLARE
v_job VARCHAR2(9);
BEGIN
select job into v_job from emp where EMPID=10;
dbms_output.put_line(v_job);
END;
/

Operasi SQL
Hampir semua perintah SQL bisa dijalankan di sini. Iya dong, khan sesuai dengan namanya, di mana PL/SQL adalah pengembangan dari SQL.

Control Struktur
Macam-macam control structure adalah:
- Testing Conditions: IF dan CASE
- Controlling Loop Iterations: LOOP dan EXIT
- Sequential Control: GOTO dan NULL

Menggunakan LOOP
Contoh yang telah saya pakai di atas adalah WHILE …LOOP
WHILE V_iterasi <= v_rcg_max LOOP
delete from MYTAB where rcg_id=V_iterasi;
commit;
V_iterasi:=V_iterasi+1;
END LOOP;

Dengan hasil yang sama, kita bisa menggunakan LOOP … EXIT WHEN
LOOP
delete from MYTAB where rcg_id=V_iterasi;
commit;
V_iterasi:=V_iterasi+1;
EXIT WHEN V_iterasi > v_rcg_max;
END LOOP;

Bisa juga dengan memakai LOOP dan di dalamnya ada IF … THEN
LOOP
delete from MYTAB where rcg_id=V_iterasi;
commit;
V_iterasi:=V_iterasi+1;
IF (V_iterasi > v_rcg_max) THEN
exit;
END IF;
END LOOP;

Contoh Menggunakan IF … THEN

IF (v_gaji > v_umr) THEN
v_bonus:=v_gaji*2;
END IF;

IF (v_gaji < v_umr) THEN
v_bonus:=v_gaji*4;
ELSIF (v_gaji = v_umr) THEN
v_bonus:=v_gaji*3;
ELSE
v_bonus:=v_gaji*2;
END IF;

Contoh Mengguakan CASE

v_nilai := 'B';
CASE v_nilai
WHEN ‘A’ THEN v_predikat := ‘Excellent’;
WHEN ‘B’ THEN v_predikat := ‘Very Good’;
WHEN ‘C’ THEN v_predikat := ‘Good’;
WHEN ‘D’ THEN v_predikat := ‘Fair’;
WHEN ‘F’ THEN v_predikat := ‘Poor’;
ELSE v_predikat := ‘Nothing’;
END CASE;