Saminnet-Search Article Wiki Forum Piwigo SNS Cloud vtiger Sugar
DB Oracle

  • Data-Articles
    • Baja iwf (107) Sun09,20:21pm

      sesuai post saya yang terakhirツdisini, rumah dua pohon sedang dalam pembangunan tahap 2 dengan material utama menggunakan baja. baja adalah material konstruksi yang paling padat apabila dibandingkan dengan beton dan kayu karena memiliki berat satuan yang cukup besar. karena baja bisa difabrikasi di pabrik, pemasangan di site bisa lebih cepat dibandingkan dengan beton yang mesti dicetak dan ditun…

      Read More...

DB Oracle

Untuk mempermudah memahami tentang database Oracle, saya mencoba meng-organize tulisan-tulisan di blog ini. Ini sangat membantu bagi teman-teman yang sedang belajar database Oracle.

Yang pertama, baca dan pahami dulu konsep dasarnya. Konsep adalah bagian yang paling fundamental. Dengan menguasai konsep, apapun requirement yang diminta user, kita sebagai DBA pasti bisa memenuhinya. Dengan menguasai konsep, apapun masalah yang terjadi, kita pasti tahu solusinya. Berikut ini beberapa pengantar untuk mengerti konsep database Oracle. Nanti akan terus saya tambah artikelnya.ツ

  1. Arsitektur Database
  2. Perbedaan Instance dengan Database
  3. Sebagai penyemangat,ツbelajar Oracle itu mudah. Kita bisa melakukannya secara otodidak.
  4. Ini adalah senjata utama saya sebagai DBA. Tool yang wajib dikuasai DBA Oracle. Saya sebut juga sebagaiツ窶廳itab Suci DBA Oracle窶

Belajar, lebih baik sambil praktek. Persiapkan database yang akan dibedah (dioprek-oprek). Untuk belajar, cukuplah PC Windows dengan memory 500M, syukur-syukur 1G. Jaman sekarang memory komputer itu murah sekali, rasanya tidak terlalu berat untuk bisa memiliki memory 1G. Berikut ini tahap-tahap untuk mempersiapkan database latihan:

  1. Download dan Install software database (RDBMS) Oracle
  2. Membuat database
  3. Membuat dan configure listener
  4. Configure TNS Names di mesin client

Setelah punya database, lakukan apa yang ingin anda lakukan. Toh ini database testing, kalau rusak bisa dibuat lagi. Coba lakukan beberapa contoh administrasi berikut:

  1. Startup dan shutdown instance
  2. Ubah mode database dari NOARCHIVED LOG menjadi ARCHIVED LOG
  3. Cobalah untukツmembuat (administrasi) tablespace
  4. Cobalah untukツmembuat (administrasi) user
  5. Melihat dan memahami reserved word di database Oracle
  6. Iseng-iseng mencoba query untukツmenampilkan rownum ganjil dan genap
  7. Mengaudit operasi pada tabel (insert, delete, update, dll)

Pengin tahu tentang PL/SQL? Silahkan baca yang ini

  1. Contoh kasus di mana kita perlu menggunakan Pl/SQL
  2. Mengetahui struktur dan format PL/SQL

Tugas DBA tak lepas dari pekerjaan backup dan restore & recovery. Kita sih penginnya tidak pernah mendapat masalah dengan kerusakan atau kehilangan data. Ya, itu sekedar kepinginan. Faktanya, hampir tidak ada database (suatu company) yang tidak pernah mengalami masalah dengan kerusakan atau kehilangan data. Berikut ini beberapa guide untuk backup & recovery:

  1. Off line Backup
  2. Online Backup
  3. Restore dari off line backup
  4. Restore dan Recovery dari online backup
  5. Kalau recovery gagal, kita masih bisaツmemaksa database untuk naik (startup)

Query (proses) perlu di-tuning biar optimal (efektif dan efesien), tidak asal jalan.

  1. Tuning query dengan explain plan
  2. Tuning Query dengan SQL Trace dan tkprof

Feature Oracle berkenaan denga High Availability adalah 窶廛ata Guard窶

  1. Membuat Physical Standby DB : Pekerjaan persiapan
  2. Langkah Utama Membuat Physical Standby DB
  3. Me-manage Physical Standby DB

Hal-hal seputar pekerjaan serbagai DBA, misalnya gaji dan sertifikakasi, juga saya bahas di sini. Apalagi gaji, tentunya banyak yang pengin tahu, berapa sih gaji DBA Oracle.

  1. Gaji DBA Oracle di Indonesia
  2. Sertifikasi Database Oracle
  3. Ujian OCA dan OCP Database Oracle 10g

Membuat Listener

Setelahツinstall software Oracleツdanツmembuat database Oracle, kini saatnya kita untuk mengakses database lewat jaringan. Dari sisi server (Oracle database) diperlukanツlistener, sementara dari sisi client diperlukan Local Net Service Name (TNS Names).

Listener bisa dibuat dengan GUI (wizard) ataupun melalui command line. Di Oracle 8i ke atas, GUI (tool) tersebut adalahツnetca.ツSeperti tool-tool database Oracle yang lain, lokasinya ada di $ORACLE_HOME/bin.

Berikut ini langkah-langkah membuat listener pakai netca. Sebagai contoh saya menggunakan Oracle 10g. Secara umum adalah sama untuk Oracle versi lainnya.

  1. Jalankan command netca. Akan muncul form wellcome.ツLihat gambarnya di sini. Pilih 窶廰istener Configuration窶. Selanjutnya klik tombol Next.
  2. Berikutnya keluar form Listener Configuration.ツLihat gambarnya di sini. Pilih 窶廣dd窶. Selanjutnya klik tombol Next.
  3. Berikutnya keluar form Listener Name.ツLihat gambarnya di sini. Masukkan nama listener. Kita bisa menamakan apa saja. Di sini saya biarkan pakai nama default, yaitu LISTENER. Selanjutnya klik tombol Next.
  4. Berikutnya keluar form Select Protocol.ツLihat gambarnya di sini. Saya memilih TCP. Selanjutnya klik tombol Next.
  5. Kalau milih TCP, berikutnya akan keluar form TCP/IP Protocol.ツLihat gambarnya di sini. Kemudian pilih port yang akan digunakan. Saya menggunakan port default, 1521. Selanjutnya klik tombol Next.
  6. Berikutnya akan keluar form More Listener.ツLihat gambarnya di sini. Apakah kita ingin membuat listener yang lain lagi?. Saya pilih tidak (No). Selanjutnya klik tombol Next.
  7. Akhirnya selesai.ツLihat gambarnya di sini. Kalau ingin melakukan perkerjaan lainnya, klik tombol Next. Karena saya cukup membuat listener ini saja, ya sudah, klik tombol Cancel.

Pada dasarnya secara intrinsik, Oracle melakukan 2 hal berikut ini (membuat listener pakai command line ya dengan cara berikut ini):

  1. Membuat file konfigurasi untuk listener diツ
    $ORACLE_HOME/network/admin/listener.ora. Adapun isi file tersebut adalah:ツ
    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
    (PROGRAM = extproc)
    )
    )LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.21.108.70)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    )
  2. Menjalankan (menaikkan) proses listenerツ
    lsnrctl start LISTENER

Selanjutnya, masukkan database yang telah kita buat tadi ke dalam konfigurasi listener agar database bisa dilayani listener. Sebenarnya kalau listener dibuat (dan dinaikkan) dulu sebelum membuat database, konfigurasi ini dilakukan secara otomatis oleh dbca (bila kita membuat database menggunaka dbca).

Di Oracle 9i keatas, untuk melakukannya kita bisa menggunakan toolツnetmgrツyang ada di $ORACLE_HOME/bin. Di Windows sepertinya netmgr tidak dibuatkan exe-nya di %ORACLE_HOME%/bin, tapi dibuatkan sort cut-nya di menu program. Berikut ini step-step menggunakan netmgr:

  1. Jalankan command netmgr. Kemudian muncul Wizard-nya.ツLihat gambarnya di sini. Di tab, pilih 窶廛atabase Services窶. Selanjutnya klik tombol 窶廣dd Database窶.
  2. Kemudian masukkan ORACLE_HOME dan nama instance (SID) yang akan di-manage.ツLihat gambarnya di sini
  3. Setelah itu simpan konfigurasi tersebut.ツLihat gambarnya di sini. Selesai deh.
  4. Agar konfigurasi bisa update, restart listenerツ
    lsnrctl stop LISTENER
    lsnrctl start LISTENER

Secara intrinsik, netmgr menambahkan definisi konfigurasi di fileツ
$ORACLE_HOME/network/admin/listener.ora (di mana kita bisa melakukannya secara manual tanpa netmgr):ツ
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = dataku)
)
)

Membuat (mensetting) TNS Names

Software Oracle sudah di-install,ツdatabase sudah dibuat, danツlistener juga sudah dibuat. Untuk bisa mengakses database di mesin server, suatu client (misalnya PC) harus di-install software Oracle Client. Setelah itu, kita perlu mensetting TNS Names di client tersebut. Tool GUI (wizard) di Oracle versi 8i ke atas adalahツnetca.

Ketika install software database (RDBMS) Oracle, secara otomatis di-install juga Oracle client. Jadi, di PC yang telah saya install RDBMS Oracle itu juga automatically sudah terinstall Oracle client. So, untuk belajar (lagipula karena keterbatasan jumlah komputer) kita bisa memakai satu PC sebagai server dan client sekaligus.

Berikut ini langkah-langkah (step-step) nya:

  1. Jalankan command netca. Kemudian muncul form Welcome.ツLihat gambarnya di sini. Pilih 窶廰ocal Net Service Name configuration窶. Selanjutnya klik tombol Next.
  2. Berikutnya muncul form Net Service Name Configuration.ツLihat gambarnya di sini. Pilih 窶廣dd窶. Selanjutnya klik tombol Next.
  3. Berikutnya masukkan 窶彜ervice Name窶.ツLihat gambarnya di sini. Gampangnya saja, service name adalah sama dengan nama instance (SID). Di sini nama instance adalah dataku. Selanjutnya klik tombol Next.
  4. Berikutnya pilih protocol.ツLihat gambarnya di sini. Protokol adalah sama dengan protokol yang dipakai listener. Saya pakai TCP. Selanjutnya klik tombol Next.
  5. Berikutnya masukkan host name (alamat) database server.ツLihat gambarnya di sini. Bisa pakai IP dan bisa pakai nama komputer. Di sini saya memakai IP. Selanjutnya klik tombol Next.
  6. Berikutnya muncul pertanyaan, apakah akan kita lakukan test?.ツLihat gambarnya di sini. Sebaiknya pilih 窶弸es窶. Selanjutnya klik tombol Next.
  7. Test koneksi memakai user system dengan password manager (default). Karena password system adalah oracle, maka connection error.ツLihat gambarnya di sini. Selanjutnya klik tombol 窶廚hange Login窶.
  8. Masukkan user system dan password-nya yang benar.ツLihat gambarnya di sini. Selanjutnya klik tombol OK.
  9. Bagus, kini koneksi success.ツLihat gambarnya di sini. Selanjutnya klik tombol Next.
  10. Berikutnya masukkan nama TNS yang kita kehendaki.ツLihat gambarnya di sini. Namanya terserah kita, bebas. Di sini (by default) namanya sama dengan nama service (SID) yang tadi kita masukkan. Selanjutnya klik tombol Next.
  11. Apakah akan meng-configure TNS yang lainnya?.ツLihat gambarnya di sini. Pilih 窶廸o窶 dan klik tombol Next.
  12. Selanjutnya, berhasil.ツLihat gambarnya di sini. Kalau ingin mensetting yang lainnya pakai netca, klik Next. Kalau cukup sekian ya klik Cancel.

Administrasi Tablespace

Tablespace merupakan bagian dari arsitektur logic database Oracle [secara sekilas, struktur logik database Oracle adalah tablespace, segment, extent, dan block]. Tablespace digunakan sebagai tempat (storage) bagi segment. Segment adalah object database yang mempunyai data. Yang termasuk segment adalah table, index, cluster, rollback (undo), lobsegment, lobindex, table partition, index partition, lob partition, temporary segment, dll. Gunakan query berikut untuk melihat type-type segment yang ada di database kitaツSQL> select distinct SEGMENT_TYPE from dba_segments;

Secara fisik, tablespace terdiri atas satu atau lebih datafile. Informasi tentang tablespace ada di viewツv$tablespaceツ,ツdba_tablespaces,ツdba_data_files,ツdba_temp_files, dll.

Gunakan command berikut untuk melihat tipe-tipe tablespaceツSQL> select distinct CONTENTS from dba_tablespaces;ツBerdasarkan hasil query tersebut, berikut ini 3 tipe tablespace:

  • UNDO. Untuk menyimpan rollback (undo) segment
  • TEMPORARY. Untuk menyimpan temporary segment
  • PERMANENT. Untuk menyimpan segment selain dua di atas (contoh tabel, index)

UNDO TABLESPACE

  1. Contoh membuat Undo Tablespace dengan nama undotbs2, datafile /oradata/oracle/ts_bak/undotbs201.dbf, ukuran file sebesar 10M. Jangan lupa tambahkan optionツundoツsesudah create.ツSQL> createツundoツtablespace undotbs2
    datafile 窶/oradata/oracle/ts_bak/undotbs201.dbf窶 size 10m;
  2. Untuk menambah (menaikkan size/space) dapat dilakukan dengan manaikkan size dari datafile atau menambah datafileツSQL> alter database
    datafile '/oradata/oracle/ts_bak/undotbs201.dbf'resizeツ20m;SQL> alter tablespace undotbs2ツaddツ
    datafile 窶/oradata/oracle/ts_bak/undotbs202.dbf窶 size 10m;
  3. Untuk melihat datafile dan size dari tablespace UNDOTBS2ツ
    SQL> select file_name,bytes from dba_data_files
    where tablespace_name='UNDOTBS2窶;
  4. Untuk melihat free space tiap-tiap datafile dari tablespace UNDOTBS2ツ
    SQL> select a.name, sum(b.bytes) from v$datafile a, dba_free_space b where a.file#=b.file_id and b.TABLESPACE_NAME='UNDOTBS2' group by a.name;
  5. Untuk melihat undo tablespace yang aktif saat ini gunakanツSQL> show parameter undo_tablespaceUntuk mengubah undo_tablespace ke tablespace yang baru saja kita buatツSQL> alter system set undo_tablespace=UNDOTBS2;

TEMPORARY TABLESPACE

  1. Contoh membuat temporay tablespace dengan nama TEMP2, tempfile /oradata/oracle/ts/temp21.dbf, ukuran file sebesar 10M. Jangan lupa tambahkan option temporary sesudah create, dan gunakan tempfile bukan datafile.ツSQL> createツtemporaryツtablespace temp2
    tempfileツ窶/oradata/oracle/ts/temp21.dbf窶 size 10m;
  2. Untuk menambah (menaikkan size/space) dapat dilakukan dengan manaikkan size dari tempfile atau menambah tempfileツSQL> alter database
    tempfile '/oradata/oracle/ts/temp21.dbf' resize 20m;SQL> alter tablespace temp2 add
    tempfile '/oradata/oracle/ts/temp22.dbf' size 10m;
  3. Untuk melihat temp file (file-file milik TEMPORARY tablespace) dan sizenya. Contoh, misalkan nama TEMPORARY tablespace tersebut adalah TEMP:ツ
    SQL> select file_name,bytes from dba_temp_files where tablespace_name='TEMP窶;
  4. Untuk melihat free spacenyaツ
    SQL> select a.name, sum(b.BYTES_FREE) from v$tempfile a, V$TEMP_SPACE_HEADER b where a.file#=b.file_id and b.TABLESPACE_NAME='TEMP' group by a.name;
  5. Untuk melihat temporary tablespace yang digunakan sebagai DEFAULT di database adalahツSQL> select PROPERTY_VALUE from database_properties
    where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';Untuk mengubah default temporary tablespace menjadi tablespace yang baru saja kita buatツSQL> alter database default temporary tablespace temp2;

PERMANENT TABLESPACE

  1. Contoh membuat permanent tablespace dengan nama DATA, datafile /oradata/oracle/ts_bak/data01.dbf, ukuran file sebesar 10M.ツSQL> create tablespace DATA
    datafile '/oradata/oracle/ts_bak/data01.dbf' size 10m;
  2. Untuk menambah (menaikkan size/space) dapat dilakukan dengan manaikkan size dari datafile atau menambah datafile. Caranya sama persis seperti pada UNDO tablespaceツSQL> alter database
    datafile '/oradata/oracle/ts_bak/data01.dbf' resize 20m;SQL> alter tablespace DATA add
    datafile '/oradata/oracle/ts_bak/data02.dbf' size 10m;
  3. Untuk melihat datafile, size, dan free size dari PERMANENT tablespace; caranya seperti untuk UNDO tablespace, yaitu gunakan viewツdba_data_files,ツv$datafile, danツdba_free_space.
  4. Untuk melihat permanent tablespace yang digunakan sebagai DEFAULT di database adalahツ
    SQL> select PROPERTY_VALUE from database_properties where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';
    Untuk mengubah default permanent tablespace menjadi tablespace yang baru saja kita buatツ
    SQL> alter database default tablespace data;

MENGURANGI SIZE DARI TABLESPACE

  1. Dilakukan dengan mengurangi size dari datafilenya. Perintah untuk mengurangi size adalah sama dengan perintah untuk menambah size, intinya adalah mengubah size (RESIZE). Jangan lupa, untuk temporary tablespace gunakan TEMPFILE; untuk PERMANENT dan UNDO tablespace sama, gunakan DATAFILE.ツ
    SQL> alter database
    tempfile '/oradata/oracle/ts/temp21.dbf' resize 20m;
    SQL> alter database
    datafile '/oradata/oracle/ts/undotbs1.dbf' resize 20m;

Catatan penting
Penguranga size (resize) tidak bisa dilakukan pada block di bawahツ
high water mark. High water mark adalah posisi block tertinggi yang pernah dipakai untuk extent. Nanti kapan-kapan saya bahas tentangツhigh water markツini. Eksekusi akan error kalau resize dilakukan di bawah High water mark:ツ
ORA-03297: file contains used data beyond requested RESIZE value
Best practice-nya, kalau misalkan size datafile 4G, dan kita ingin menurunkan size-nya, lakukan secara gradual (diturunkan 100M - 100M) untuk menemukan size (high water mark) yang sesuai.

  1. Dilakukan dengan menghapus temp fileツ
    Untuk alasan keamanan, datafile tidak bisa dihapus. Ingat, yang dimaksud datafile adalah file-file milik tablespace PERMANENT dan UNDO.ツ
    SQL> alter database
    datafile '/oradata/oracle/ts/test02.dbf' drop;
    ERROR at line 1:
    ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected

Sedangkan temp file bisa dihapus (file milik tablespace TEMPORARY) karena file ini tidak berisi data. Dengan catatan, paling tidak sisakan 1 tempfile.ツ
SQL> alter database
tempfile '/oradata/oracle/ts/temp02.dbf2' drop;

  1. contoh kasus
    • Pertanyaan
      Bagaimana cara untuk resize tablepspace SYSTEM yang besar nya sudah 3G, padahal yang ke pakai cuma 500M, sudah di coba pake alter tablespace resize, tetapi tidak bisa .
    • Jawaban
      Resize tidak bisa dilakukan karena dulunya space 3G itu pernah kepakai. Mungkin dulu pernah sempat ada segment (table/index/temp segment) yang memakai tablespace SYSTEM, namun sekarang sudah dihapus.

Konsep yang berkaitan dengan hal ini adalahツ窶廩igh Water Mark窶.

Kalau size tablespace (datafile) tidak bisa dikurangi dengan 窶彗lter database datafile 窶倪ヲ窶 resize窶 sementara itu free space-nya masih sangat banyak, satu-satunya solusi adalah recreate tablespace yang bersangkutan. Caranya:ツ
- export data-data yang ada di tablespace tsbツ
- create tablespace baruツ
- import data-data tsb ke tablespace baruツ
- drop tablespace lama.

Namun sayangnya, tablespace SYSTEM tidak bisa di-recreate. Kalau masih mau dipaksa, ya dengan recreate database:ツ
- export database fullツ
- buat database baruツ
- import databaseツ
- drop database lama

MENGHAPUS (drop) TABLESPACE
Perintahnya sama untuk ketiga jenis tablespace tersebut.

Contoh

SQL> drop tablespace DATA;

Administrasi User

Waktu kita membuat database, secara otomatis Oracle membuat user sys dan system. User sys bisa melakukan apa saja, oleh karenanya disebut sebagai super user. User system digunakan untuk administrasi database sehari-hari, misalnya membuat user, tablespace, dan lain-lain. Kalau user sys bisa melakukan semuanya, kenapa mesti ada user system? Ya tentu saja, pertimbangannya adalah masalah security. Oke, mari kita mulai membahas administrasi user di database Oracle.

Create (membuat) user

Berikut ini format perintah SQL untuk membuat user [Gambar diambil dari dokumentasi Oracle 9i]ツ

Perintah membuat user yang paling sederhana adalah (contoh, saya akan membuat user dengan nama ROHMAD dan passwordnya PASSROH):ツ
SQL> conn system
SQL> create user ROHMAD identified by PASSROH;

Karena tidak di-specify, maka atribut-atribut yang lainnya memakai nilai default. Untuk melihatnya lihat di view dba_usersツ
SQL> select * from dba_users where username='ROHMAD';
Ini hasilnya:ツ
USERNAME: ROHMAD
ACCOUNT_STATUS : OPEN
EXPIRY_DATE :
DEFAULT_TABLESPACE : USERS
TEMPORARY_TABLESPACE : TEMP
PROFILE : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP : DEFAULT_CONSUMER_GROUP

Untuk melihat default tablespace dan default temporary tablespace dari suatu database, silahkan lihat diツAdministrasi Tablespace.

Berikut ini contoh membuat user dengan men-specify default tablespace, quota pemakaian di tablespace, dan temporary tablespace (ini contoh yang paling sering digunakan):ツ
SQL> CREATE USER rohmad
IDENTIFIED BY passroh
DEFAULT TABLESPACE users
QUOTA unlimited ON users
QUOTA 500K ON data_ts
TEMPORARY TABLESPACE temp;

Alter (mengubah) user

Semua atribut user bisa diubah (alter) kecuali username itu sendiri. Secara umum perintah alter sama dengan create, hanya mengganti kata create menjadi alter. Contoh:ツ
SQL> alter user ROHMAD identified by PASSROH2;

Grant User

Setelah user dibuat, user tersebut tidak bisa melakukan koneksi sebelum diberi grant (hak) untuk connect ke database.ツ
SQL> conn rohmad/passroh2
ERROR:
ORA-01045: user ROHMAD lacks CREATE SESSION privilege; logon denied

Berikut ini perintah untuk memberi grant ke user agar bisa connect ke databaseツ
SQL> conn system;
SQL> grant connect to rohmad;

Setelah itu, dengan user rohmad tersebut, mari kita coba membuat tableツ
SQL> conn rohmad/passroh2
SQL> create table tab_test (no number);
ERROR at line 1:
ORA-01031: insufficient privileges

OO窶ヲ ternyata user rohmad belum punya priviledges untuk membuat table. Coba beri privilege ke user rohmad agar bisa membuat tabelツ
SQL> conn system;
SQL> grant create table to rohmad;

Sekarang buat tableツ
SQL> conn rohmad/passroh2
SQL> create table tab_test (no number);
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

OO窶ヲ ternyata error. Walaupun default tablespace untuk user ROHMAD adalah tablespace USERS, ternyata ROHMAD masih belum bisa membuat table (menulis) di tablespace USERS. Beri quota ke user ROHMAD, bisa unlimited ataupun di-specify besarannya. Contoh, beri quota unlimited:ツ
SQL> conn system;
SQL> alter user rohmad quota unlimited on USERS;

Coba lagi, dan berhasil 窶ヲツ
SQL> conn rohmad/passroh2
SQL> create table tab_test (no number);
Table created.

Agar bisa membuat index, user rohmad harus diberi grant. Demikian juga untuk bisa membuat view. Wow窶ヲ dapat anda bayangkan, berapa banyak privilege yang mesti gita beri (grant)? Untuk mengatasi itu, Oracle membuatツrole. Role berisi beberapa (banyak) privilege. Contoh, role RESOURCE berisi grant untuk membuat table, membuat index, quota unlimited di default tablespace, dan lain-lain.

Berikut ini perintah untuk memberi hak (grant) role RESOURCE ke user ROHMADツ
SQL> conn system
SQL> grant RESOURCE to rohmad;

Umumnya, cukup dengan memberi grant CONNECT dan RESOURCE ke user yang baru dibuat, user tersebut sudah bisa melakukan banyak pekerjaan.ツ
SQL> create user ROHMAD identified by PASSROH;
SQL> grant connect to rohmad;
SQL> grant RESOURCE to rohmad;

Menghapus User
SQL> drop user rohmad;

Kalau user tersebut mempunyai object (misalnya tabel), maka akan muncul error berikutツ
ORA-01922: CASCADE must be specified to drop 'ROHMAD'

Kalau begitu, tambahkan parameter CASCADE untuk sekaligus menghapus semua object yang dimiliki user tersebutツ
SQL> drop user rohmad CASCADE;

Reserved Word di database Oracle

Reserved word adalah kata yang sudah di reserved (dikapling) oleh database Oracle. Contoh reserved word adalah select, delete, update, session, uid, key, rowid, dll. Daftar reserved word ada di view V$RESERVED_WORDS.

SQL> select * from V$RESERVED_WORDS;

Semua kata yang tercakup dalam reserved word TIDAK bisa digunakan untuk memberi nama object database, nama kolom pada tabel, dll. Contoh object database adalah: tabel, index, view, synonym, database link, dll. Gunakan query berikut untuk melihat tipe-tipe object database:ツ
SQL> select distinct OBJECT_TYPE from DBA_OBJECTS
order by OBJECT_TYPE;

Contoh masalah yang sering muncul berkaitan dengan reserved word ini adalah ketika kita migrasi database dari non Oracle (misal MySQL) ke database Oracle. Misalkan di MySQL kita punya tabel SESSION dengan kolom NO dan UID. Ketika kita migrasi ke Oracle, katakanlah kita membuat tabel dengan definisi yang sama:ツ

SQL> CREATE TABLE SESSION (NO NUMBER(2),UID VARCHAR2(20));
CREATE TABLE SESSION (NO NUMBER(2),UID VARCHAR2(20))
*
ERROR at line 1:
ORA-00903: invalid table name

SESSION dibilang sebagai 窶彿nvalid table name窶. Setelah kita check di V$RESERVED_WORDS, ternyata kata SESSION termasuk dalam daftar reserved word. Demikian juga kata UID yang kita pakai untuk nama kolom tersebut, juga termasuk reserved word.

Lantas, apa solusinya? Mau tidak mau kita harus mengubah nama table dan nama column tersebut.ツ
SQL> CREATE TABLE SESSION_NEW (NO NUMBER(2),
UID_NEW VARCHAR2(20));
Dengan mengubah nama tabel dan kolom tersebut, mau tidak mau kita harus mengedit script aplikasi kita. Wah窶ヲ berat sekali ya, kalau aplikasi kita banyak dan complicated.

Kalau masih memaksa pengin memakai kata yang termasuk dalam reserved word tersebut, gunakan kutip dua (double quote) pada kata tersebut. Contohツ
SQL> CREATE TABLE "SESSION" (NO NUMBER(2),
"UID" VARCHAR2(20));


Audit operasi di suatu table

Misalkan kita ingin tahu user-user mana saja yang melakukan perubahan (INSERT, UPDATE, DELETE) pada suatu tabel. Caranya, aktifkan parameter AUDIT_TRAIL, lakukan perintah audit, dan lihat hasil auditnya.ツ

  1. Aktifkan parameter AUDIT_TRAIL. Ada tiga pilihan value, yaitu DB,OS, dan NONE. By default nilainya adalah NONE. Pilih OS kalau ingin hasil audit disimpan di suatu file, dan pilih DB kalau ingin hasil audit disimpan di tabel (database). Dalam contoh ini saya memilih option DB. Alasannya: lebih mudah melihat (me-manage) hasil auditnya.ツ
    SQL> alter system set AUDIT_TRAIL=DB scope=spfile;
    SQL> shutdown immediate
    SQL> startup
  2. Lakukan perintah audit. Misalkan kita ingin mengaudit operasi INSERT, UPDATE, DELETE pada tabel pegawai (di schema test)ツ
    SQL> AUDIT INSERT, UPDATE, DELETE ON test.pegawai
    BY ACCESS WHENEVER SUCCESSFUL;
  3. Lihat hasilnyaツ
    SQL> select * FROM SYS.AUD$;
  4. Untuk meng-cancel auditツ
    SQL> NOAUDIT INSERT, UPDATE, DELETE ON test.pegawai;

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 <>

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 <>
  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(窶僖eleting sucess窶);
dbms_output.put_line(窶儁in RCG_ID 窶||V_rcg_min);
dbms_output.put_line(窶儁ax RCG_ID 窶||V_rcg_max);
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line(窶册rror 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 窶徭et serveroutput on窶 agar hasil dari 窶彭bms_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 窶徭et serveroutput on窶 sebelumnya, maka yang nampak di monitor hanyaツ
PL/SQL procedure successfully completed.

Bila tidak ingin muncul pesan 窶弃L/SQL procedure successfully completed窶, jalankan command 窶徭et feed off窶 di SQLPlusツ
SQL> set serveroutput on

Bersambung keツMengenal Oracle PL/SQL (2): Struktur

Mengenal Oracle PL/SQL (2): Struktur

Posted in Uncategorized on Jun 17, 2008

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(窶僖eleting sucess窶);
dbms_output.put_line(窶儁in RCG_ID 窶||V_rcg_min);
dbms_output.put_line(窶儁ax RCG_ID 窶||V_rcg_max);
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line(窶册rror 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 窶從ot 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 窶徭elect 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 <>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ツ窶連窶卍THENツv_predikat := 窶櫓xcellent窶;
WHENツ窶錬窶卍THENツv_predikat := 窶老ery Good窶;
WHENツ窶呂窶卍THENツv_predikat := 窶賂ood窶;
WHENツ窶魯窶卍THENツv_predikat := 窶炉air窶;
WHENツ窶炉窶卍THENツv_predikat := 窶榔oor窶;
ELSEツv_predikat := 窶朗othing窶;
END CASE;

Online backup database Oracle

Beberapa waktu yang lalu saya telah menulis tentang off line backup database Oracle di siniツhttp://rohmad.net/2008/04/18/off-line-backup-database-oracle/. Kali ini saya akan membahas tentang online backup. Beberapa keuntungan metode online backup adalah :

  1. Waktu backup, database tidak perlu dimatikan sehingga tidak ada downtime
  2. Bisa melakukan backup per tablespace, bahkan per datafile
  3. Bisa merestore data sampai terakhir sebelum masalah. Bahkan bisa merestore data sampai waktu yang ditentukan. Berbeda dengan restore hasil offline backup di mana yang bisa direstore hanya data terakhir melakukan backup saja.

Seperti yang sudah pernah dibahas, syarat online backup adalah database harus dalam modeツarchivelog. Untuk melihat atau men-setting mode database apakah archivelog atau noarchivelog, lihat artikel di sini
http://rohmad.net/2008/04/22/men-setting-database-menjadi-archivelog-mode/
.

Berikut ini step-step online backup.

  1. Backup control file
    SQL> alter database backup controlfile to
    '/backupdir/backupcontrol_22042008.bak';
  2. Lihat Tablespace yang berisi data
    SQL> select TABLESPACE_NAME from dba_tablespaces where CONTENTS <>'TEMPORARY';
    TABLESPACE_NAME
    ------------------------------
    SYSTEM
    UNDOTBS1
    SYSAUX
    USERS
    Temporary tablespace tidak perlu dibackup, karena bukan berisi data
  3. Ubah mode tablespace menjadiツbackup mode
    SQL> alter tablespace SYSTEM begin backup;
    SQL> alter tablespace UNDOTBS1 begin backup;
    SQL> alter tablespace SYSAUX begin backup;
    SQL> alter tablespace USERS begin backup;
  4. Lihat semua datafile yang perlu dibackup (selain file dari temporary tablespace)
    SQL> select name from v$datafile;
    NAME
    ---------------------------------------
    /oradata/oracle/ts/system01.dbf
    /oradata/oracle/ts/undotbs01.dbf
    /oradata/oracle/ts/sysaux01.dbf
    /oradata/oracle/ts/users01.dbf
  5. Backup datafile
    $ cp /oradata/oracle/ts/system01.dbf /backupdir/...
    $ cp /oradata/oracle/ts/undotbs01.dbf /backupdir/..
    $ cp /oradata/oracle/ts/sysaux01.dbf /backupdir/...
    $ cp /oradata/oracle/ts/users01.dbf /backupdir/..
  6. Ubah mode database kembali ke normal
    SQL> alter tablespace SYSTEM end backup;
    SQL> alter tablespace UNDOTBS1 end backup;
    SQL> alter tablespace SYSAUX end backup;
    SQL> alter tablespace USERS end backup;
  7. Archive current log
    SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
  8. Jangan lupa, backup juga archived log-nya. Archived log ini nanti kita butuhkan untuk recovery. Archive log yang masih dipakai adalah archived log sejak terakhir online (hot) backup. Archive log sebelum hot backup sudah tidak dipakai lagi.

Kesimpulannya, yang dibackup adalah:

  1. Control file
  2. Datafile
  3. Archived log file

Off line backup database Oracle

  1. Ada dua metode untuk membackup database Oracle, yaitu off line dan online. Off line backup dilakukan dengan mematikan database terlebih dahulu, baru kemudian membackup datafile. Online backup dilakukan tanpa mematikan database, jadi database masih bisa diakses selama proses backup.
  2. Online backup mensyaratkan database berada dalam mode archive log. Nanti akan saya bahas di artikel lain. Kali ini saya akan membahas tentang off line backup.
  3. Berikut ini step-step untuk off line backup:
  4. ツ(datafile, logfile, dan control file)ツ
    Gunakan query berikut ini untuk melihat semua fileツ
    SQL> select name as file_name from(select name from v$tempfile unionselect name from v$datafile unionselect name from v$controlfile unionselect member as name from v$logfile)SQL> shutdown immediate;$ cp /oradata/ts/control01.ctl /backup/ts/control01.ctl$ cp /oradata/ts/control02.ctl /backup/ts/control02.ctl$ cp /oradata/ts/control03.ctl /backup/ts/control03.ctl$ cp /oradata/ts/redo04.log /backup/ts/redo04.log$ cp /oradata/ts/redo05.log /backup/ts/redo05.log$ cp /oradata/ts/redo06.log /backup/ts/redo06.log$ cp /oradata/ts/sysaux01.dbf /backup/ts/sysaux01.dbf$ cp /oradata/ts/system01.dbf /backup/ts/system01.dbf$ cp /oradata/ts/temp01.dbf /backup/ts/temp01.dbf$ cp /oradata/ts/undotbs01.dbf /backup/ts/undotbs01.dbf$ cp /oradata/ts/users01.dbf /backup/ts/users01.dbfSQL> startup;
  5. Untuk me-restore dari offline backup ini, silahkan lihat di siniツ
    http://rohmad.net/2008/05/15/restore-dari-off-line-backup/

Restore dari off line backup

Posted in Uncategorized on May 15, 2008

Restore dari hasil offline backup adalah sangat sederhana, sesederhana backup-nya. Lihat offline backup di siniツhttp://rohmad.net/窶ヲ/off-line-backup-database-oracle/

Berikut ini step-step restore dengan memakai instance yang sama.ツ

  1. Siapkan file-file yang akan direstoreツ
    File-file tersebut adalah control, log, data, dan temp file
  2. Pastikan instance sudah matiツ
    SQL> shutdown immediate;
  3. Restore file-file backup ke directory asalnyaツ
    $ cp /backup/ts/control01.ctl /oradata/ts/control01.ctl
    $ cp /backup/ts/control02.ctl /oradata/ts/control02.ctl
    $ cp /backup/ts/control03.ctl /oradata/ts/control03.ctl
    $ cp /backup/ts/redo04.log /oradata/ts/redo04.log
    $ cp /backup/ts/redo05.log /oradata/ts/redo05.log
    $ cp /backup/ts/redo06.log /oradata/ts/redo06.log
    $ cp /backup/ts/sysaux01.dbf /oradata/ts/sysaux01.dbf
    $ cp /backup/ts/system01.dbf /oradata/ts/system01.dbf
    $ cp /backup/ts/temp01.dbf /oradata/ts/temp01.dbf
    $ cp /backup/ts/undotbs01.dbf /oradata/ts/undotbs01.dbf
    $ cp /backup/ts/users01.dbf /oradata/ts/users01.dbf
  4. Nyalakan databaseSQL> startup;

Jika karena suatu hal, kita tidak bisa merestore ke direktori asalnya, maka kita bisa merestore ke tempat (direktori) lain. Step 1 dan 2 masih seperti yang di atas. Step 3 dan seterusnya adalah berikut ini:

  1. Restore ke directory baruツ
    Control fileツ
    $ cp /backup/ts/control01.ctl /newdir/ts/control01.ctl
    $ cp /backup/ts/control02.ctl /newdir/ts/control02.ctl
    $ cp /backup/ts/control03.ctl /newdir/ts/control03.ctlLog fileツ
    $ cp /backup/ts/redo04.log /newdir/ts/redo04.log
    $ cp /backup/ts/redo05.log /newdir/ts/redo05.log
    $ cp /backup/ts/redo06.log /newdir/ts/redo06.logData fileツ
    $ cp /backup/ts/sysaux01.dbf /newdir/ts/sysaux01.dbf
    $ cp /backup/ts/system01.dbf /newdir/ts/system01.dbf
    $ cp /backup/ts/undotbs01.dbf /newdir/ts/undotbs01.dbf
    $ cp /backup/ts/users01.dbf /newdir/ts/users01.dbfTemp fileツ
    $ cp /backup/ts/temp01.dbf /newdir/ts/temp01.dbf
  2. Ubah konfigurasi control file. Edit init (instance parameter) file.ツ
    Filenya di $ORACLE_HOME/dbs/init[NAMAINSTANCE].oraツ
    Ganti lokasi control file dari yang lama ke yang baru.ツ
    Value yang lama:ツ
    control_files='/oradata/ts/control01.ctl', '/oradata/ts/control02.ctl','/oradata/ts/control03.ctl'Value yang baru:ツ
    control_files='/newdir/ts/control01.ctl', '/newdir/ts/control02.ctl','/newdir/ts/control03.ctl'
  3. Ubah konfigurasi file yang lainnya (log, data, dan temp file)ツ
    SQL> startup mountSQL> alter database rename file '/oradata/ts/redo04.log' to '/newdir/ts/redo04.log';
    SQL> alter database rename file '/oradata/ts/sysaux01.dbf' to '/newdir/ts/sysaux01.dbf';
    SQL> alter database rename file '/oradata/ts/temp01.dbf' to '/newdir/ts/temp01.dbf';
    dan seterusnya ...
  4. Open databaseツ
    SQL> alter database open;

Error yang terkait

  1. Jika control file tidak ada, atau ada tapi direktorinya berubah dan init file belum dieditツ
    SQL> startup
    ORACLE instance started.Total System Global Area 1610612736 bytes
    Fixed Size 2177912 bytes
    Variable Size 396149896 bytes
    Database Buffers 1207959552 bytes
    Redo Buffers 4325376 bytes
    ORA-00205: error in identifying control file, check alert log for more info
  2. Jika ada data file yang kelewatan, atau ada tapi direktorinya berubah dan belum di-alter/renameツ
    SQL> startup
    ORACLE instance started.Total System Global Area 1610612736 bytes
    Fixed Size 2177912 bytes
    Variable Size 396149896 bytes
    Database Buffers 1207959552 bytes
    Redo Buffers 4325376 bytes
    Database mounted.
    ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
    ORA-01110: data file 4: 窶/oradata/ts/users01.dbf窶

Restore dan Recovery dari online backup

Sebaiknya silahkan dibaca dulu online backup di siniツ
http://rohmad.net/2008/04/22/online-backup-database-oracle/
dan tentang archived log di siniツ
http://rohmad.net/2008/04/22/men-setting-database-menjadi-archivelog-mode/. Secara sederhana, kedua artikel tersebut membahas landasan teorinya.

Restoreツdari online backup adalah sama persis dengan restore dari offline backup, kecuali ada 1 tambahan step setelah melakukan restore dari online backup, yaitu kita WAJIB melakukanツrecovery. Lihat secara detail tentang restore dari offline backup di siniツhttp://rohmad.net/2008/05/15/restore-dari-off-line-backup/

Ringkasan restore dari offline backup:

  1. SQL> shutdown immediate
  2. Lakukan restore semua file-file yang bersangkutan
  3. SQL> startup

Sedangkan untuk restore dan recovery dari online backup adalah sbb:

  1. SQL> shutdown immediate
  2. Lakukan restore semua file-file yang bersangkutanツ
    Restore juga archived lognya
  3. SQL> startup mount
  4. SQL> recover database using BACKUP CONTROLFILE;ツNanti akan diminta memasukkan archive log. Bila archived log sudah di-restore ke lokasinya, pilih AUTO. Setelah semua archived log di-apply, dan database masih minta archived log lagi, pilih CANCEL.
  5. SQL> alter database open resetlogs;

Catatan-catatan:

  1. Bila file-file (data, control, dan log file) direstore ke tempat yang berbeda dari aslinya, lakukan step-step untuk mengubah konfigurasi file-file tersebut seperti yang sudah di bahas diツ窶彝estore dari offline backup窶.
  2. Ingat, control file yang digunakan untuk menaikkanツrestoredツdatabase adalah control file hasil dari 窶彗lter database backup controlfile to 窶/backupdir/backupcontrol_22042008.bak窶;窶

Dataguard 10g: Membuat Physical Standby DB (1)

Dataguard adalah solusi HIGH AVAILABILITY dari Oracle. Tujuannya adalah untuk membuat database standby (secondary/tambahan) agar bila sewaktu-waktu database production (primary) mati maka database standby itu bisa menggantikan posisi menjadi production.

Setiap ada perubahan di primary, maka standby segera diupdate, sebisa mungkin diusahakan agar delta (perbedaan data antara primary dan standby) kecil. Mekanisme update standby adalah dengan apply archived log. Archived log dikirim dari primary ke standby, kemudian di apply di standby.

Berdasarkan mekanisme apply archived log, database standby ada dua tipe:

  1. Physical standby database.ツ
    Archived log diapply secara konvensional (by block per block), sebagaimana kalau kita melakukan recovery database dengan archived log.
  2. Logical standby databaseツ
    Dari Archived log diextract SQL statement-nya, kemudian SQL statement itu di apply


Artikel ini berisi contoh membuat Physical standby database. Dalam contoh ini, struktur directory (untuk semua file Oracle) adalah sama persis antara database primary dan standby. Contoh ini menggunakan environment sebagai berikut:

  1. Nama instance dan database: ts
  2. Versi database: Oracle Database 10g Enterprise Edition Release 2
  3. OS: SunOS 5.10

Secara umum langkah-langkah ini sama untuk semua database 10 Release 2 di Operating System manapun baik Windows maupun Unix (Sun Solaris, IBM AIX, HP UX, Linux, dan lain-lain). Untuk instalasi versi lainnya (8i, 9i, dan 10g) silahkan lihat masing-masing dokumentasinya, caranya tidak berbeda jauh dengan ini.

PERSIAPAN Di PRIMARY DATABASE
Sebelum membuat standby database, persiapkan dulu segala persyaratan (environment) di database primary.

  1. Apply FORCE LOGGINGツ
    SQL> ALTER DATABASE FORCE LOGGING;
  2. Database harus sudahツarchived log
  3. Membuat password fileツ
    Cek apakah password file sudah ada. Di Windows lokasinya diツ%ORACLE_HOME%\database, biasanya berformatツPWD[namainstance].ora; contoh instance dengan nama DATAKU mempunyai password fileツPWDdataku.ora.ツDi UNIX lokasinya diツ$ORACLE_HOME/dbs, biasanya berformatツorapw[namainstance]; contoh instance dengan nama ts mempunyai password fileツorapwts. Kalau belum ada password file (atau anda ingin membuat ulang), buatlah dengan commandツorapwd. Ini adalah perintah bawaan Oracle, lokasinya standart yaitu diツ$ORACLE_HOME/bin. Berikut ini contoh membuat password file:ツ
    cd $ORACLE_HOME/dbs
    orapwd file=orapwts password=oracle entries=10 force=y
  4. Persiapkan initial parameter (cukup disebut init). Di Windows, init file ada diツ%ORACLE_HOME%\database\init[namainstance].ora. Di Unix ada diツ$ORACLE_HOME/dbs/init[namainstance].ora.ツBerikut ini init file yang berkaitan dengan dataguard (standby database)
    • log_file_name_convert = 窶/oradata/oracle/ts/窶
    • remote_login_passwordfile=窶僞XCLUSIVE窶
    • log_archive_config=窶僖G_CONFIG=(tsprimary,tsstandby)窶
    • log_archive_dest_1=窶儉OCATION=/oradata/oracle/ts/arc窶
    • log_archive_dest_state_1=enable
    • log_archive_dest_state_2=enable
    • log_archive_format=%s_%t_%r.arc
    • fal_client=窶冲sstandby窶
    • fal_server=窶冲sprimary窶
    • log_archive_dest_2=窶冱ervice=tsstandby optional LGWR ASYNC NOAFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=ts窶

Parameter lainnya biarkan sebagaimana adanya. Sebagai contoh, berikut ini adalah parameter-parameter dari instance yang saya pakai. Tampak settingannya sangat minimalis (banyak memakai nilai default), gak pa-pa, yang penting bisa digunakan untuk contoh.

    • audit_file_dest=窶/data1/oracle/admin/ts/adump窶
    • background_dump_dest=窶/data1/oracle/admin/ts/bdump窶
    • core_dump_dest=窶/data1/oracle/admin/ts/cdump窶
    • user_dump_dest=窶/data1/oracle/admin/ts/udump窶
    • control_files=窶/oradata/oracle/ts/control01.ctl窶, 窶/oradata/oracle/ts/control02.ctl窶, 窶/oradata/oracle/ts/control03.ctl窶
    • compatible=窶10.2.0.3.0窶イ
    • db_block_size=8192
    • db_domain=窶
    • db_name=窶冲s窶
    • pga_aggregate_target=209715200
    • sga_target=1610612736
    • undo_management=窶僊UTO窶
    • undo_tablespace=窶儷NDOTBS1窶イ
  1. Buat Oracle Net Service Name (TNS Names), masing-masing untuk primary dan standby database. Bisa denganツnetcaツ,atau langsung menambah entry berikut fileツ$ORACLE_HOME/network/admin/tnsnames.ora:ツtsprimary =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.21.106.161)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = ts)
    )
    )tsstandby =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.21.75.200)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = ts)
    )
    )

PERSIAPAN Di STANDBY DATABASE

  1. Persiapkan init file di %ORACLE_HOME%\database\initts.ora (untuk Windows) atau $ORACLE_HOME/db/initts.ora (di Unix). Isinya sama persis dengan yang di primary, kecuali untuk entry berikutツ
    log_archive_dest_2='service=tsprimary optional LGWR ASYNC NOAFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=ts'
  2. Buat password file. Password harus sama dengan database primary. Jadi, command-nya samakan dengan command untuk membuat password file primary database. Atau cara lain, copy (ftp) password file dari primary.
  3. Buat service (instance). Ini khusus di Windows, karena di Unix tidak perlu. Gunakan oradim, ini tool standar Oracle.ツ
    cd %ORACLE_HOME%\database
    oradim -NEW -SID ts
    Perintah di atas akan membaca file initts.ora yang telah di buat. Untuk melihat bahwa instance ts sudah ter-create, lihat di tool 窶彜ervices窶-nya Windows, atau coba login dengan SQL PLus
    sqlplus '/ as sysdba'
    SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 9 13:13:06 2008
    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
    Enter password:
    Connected to an idle instance.
    SQL>
  4. Buat Oracle Net Service Name (TNS Names), sama persis seperti di primary database, yaituツtsprimaryツdanツtsstandby
  5. Buat direktory untuk file-file dump dan databaseツ
    $ mkdir /data1/oracle/admin/ts/adump
    $ mkdir /data1/oracle/admin/ts/bdump
    $ mkdir /data1/oracle/admin/ts/cdump
    $ mkdir /data1/oracle/admin/ts/udump
    $ mkdir /oradata/oracle/ts/

Bersambung ke 窶ヲ
Dataguard 10g: Membuat Physical Standby DB (2)

Dataguard 10g: Membuat Physical Standby DB (2)

Setelahツpersiapan di mesin primary dan standbyツselesai, selanjutnya tinggal membuat standby database. Berikut ini langkah-langkah (step-step) nya:ツ

  1. Di primary database, buat standby control fileツ
    SQL> alter database create standby controlfile
    as '/oradata/oracle/ts/controltsstandby.ctl';
  2. Di primary database, lakukan backup full database.ツ
    Bisa secara online ataupun offline. Dalam contoh ini saya menggunakan metode online (hot) backup biar database production tidak perlu mati.List daftar tablespace yang bukan TEMPORARY
    SQL> select TABLESPACE_NAME from dba_tablespaces where CONTENTS <>'TEMPORARY';
    NAME
    ------------------------------
    SYSTEM
    UNDOTBS1
    SYSAUX
    USERS

    Jalankan perintah BEGIN BACKUPツ
    SQL> alter tablespace SYSTEM begin backup;
    SQL> alter tablespace UNDOTBS1 begin backup;
    SQL> alter tablespace SYSAUX begin backup;
    SQL> alter tablespace USERS begin backup;Lihat semua file yang perlu dibackup (datafile, tempfile, dan logfile):ツ
    SQL> select name as file_name from
    (select name from v$tempfile union
    select name from v$datafile union
    select member as name from v$logfile)
    order by file_name;
    FILE_NAME
    ---------------------------------------------------
    /oradata/oracle/ts/redo04.log
    /oradata/oracle/ts/redo05.log
    /oradata/oracle/ts/redo06.log
    /oradata/oracle/ts/sysaux01.dbf
    /oradata/oracle/ts/system01.dbf
    /oradata/oracle/ts/temp01.dbf2
    /oradata/oracle/ts/undotbs01.dbf
    /oradata/oracle/ts/users01.dbf2File-file tersebut bisa dibackup di TAPE, directory temporary, ataupun langsung ditaruh (ftp) di mesin standby.ツ
    Setelah file-file dibackup, Jalankan perintah END BACKUPツ
    alter tablespace SYSTEM end backup;
    alter tablespace UNDOTBS1 end backup;
    alter tablespace SYSAUX end backup;
    alter tablespace USERS end backup;
  3. Restore semua file ke mesin standby, taruh di directory yang samaツ
    Dalam contoh ini lokasi semua file adalah sama, yaitu /oradata/oracle/ts/ツ
    ls -la /oradata/oracle/ts/
    controltsstandby.ctl
    redo04.log
    redo05.log
    redo06.log
    sysaux01.dbf
    system01.dbf
    temp01.dbf2
    undotbs01.dbf
    users01.dbf2
  4. Di mesian standby, siapkan control file. Copy control file hasil dari 窶彗lter database create standby controlfile窶 ke directory control file (sebagimana yang ditunjuk dalam file init)ツ
    cd /oradata/oracle/ts/
    cp -rp controltsstandby.ctl control01.ctl
    cp -rp controltsstandby.ctl control02.ctl
    cp -rp controltsstandby.ctl control03.ctl
  5. Naikkan database standbyツ
    SQL> startup mount;
  6. Jalankan recovery di standby database untuk meng-apply arhived logツ
    SQL> alter database recover managed standby database disconnect;

Akhirnya standby database selesai di-create. Untuk melihat archived log yang telah di-apply di standby database gunakan command iniツ
SQL> set pages 100
SQL> col name for a50
SQL> select name,to_char(FIRST_TIME,'dd-mon-yy hh24:mi:ss') TIME ,SEQUENCE#,APPLIED from v$archived_log;
Pastikan colomn APPLIED bernilai YES.

Bersambung ke 窶ヲ
Dataguard 10g: Administrasi Physical Standby Database

Dataguard 10g: Administrasi Physical Standby DB

Posted in Uncategorized on Jun 10, 2008

Setelahツsemua persiapan beresツdanツstandby database selesai dibuat, sekarang kita coba administrasinya.

MONITOR ARCHIVED LOG DI STANDBY DATABASE
Di standby database, lihat archived log yang sudah dan belum diapply:ツ
SQL> set lines 120
SQL> col name for a50
SQL> select name,to_char(FIRST_TIME,'dd-mon-yy hh24:mi:ss') TIME ,SEQUENCE#,APPLIED from v$archived_log order by sequence#;

Misalkan hasilnya berikut ini (kolom TIME tidak ditampilkan, untuk menghemat space)ツ
NAME--------------------- SEQUENCE#------ APPLIED
=================================================
/oradata/oracle/ts/arc106_1_655805448.arc 106 YES
/oradata/oracle/ts/arc107_1_655805448.arc 107 YES
/oradata/oracle/ts/arc108_1_655805448.arc 108 YES
/oradata/oracle/ts/arc110_1_655805448.arc 110 NO
/oradata/oracle/ts/arc111_1_655805448.arc 111 NO
/oradata/oracle/ts/arc112_1_655805448.arc 112 NO
/oradata/oracle/ts/arc113_1_655805448.arc 113 NO
/oradata/oracle/ts/arc114_1_655805448.arc 114 NO

Berdasarkan hasil query di atas, pastikan bahwa:

  1. Sequence harus urut.ツ
    Kalau ada sequence yang tidak urut (atau ada gap), itu berarti ada archived log yang tidak terkirim, alasannya bisa bermacam-macam, misalnya karena network error atau standby database sempat mati. Pada contoh di atas, ada gap pada sequence 109. Kalau seperti itu:
    1. Archived log harus ada (dicopy manual dari database primary, atau di-restore dari backup)
    2. Register archived yang baru saja di-restore ituツ
      SQL> ALTER DATABASE REGISTER
      LOGFILE '/oradata/oracle/ts/arc109_1_655805448.arc';
      Setelah diregister, archived log sequence 109 harusnya sudah masuk kedalam list di view v$archived_log
  2. Kolom APPLIED harus bernilai YES, artinya archived log sudah di-applyツ
    Archived log belum di-apply itu ada 2 kemungkinan:
    1. Ada GAP. Contoh di atas SEQUENCE# 110 belum di-apply karena ada gap di sequence atasnya (SEQUENCE# 109). Solusinya sudah disebut di atas
    2. Proses recovery berhenti. Solusinya, jalankan lagiツ
      SQL> alter database recover managed standby database disconnect;

STARTUP DAN SHUTDOWN

  1. startupツ
    SQL> STARTUP MOUNT;Setelah itu, apply archived log secara backgroundツ
    SQL> alter database recover managed standby database disconnect;
  2. Shutdown. Matikan dulu proses recovery-nyaツ
    SQL> alter database recover managed standby database cancel;Setelah itu, baru shutdownツ
    SQL> SHUTDOWN IMMEDIATE;

Mode Recovery dan Open Read Only

  1. standby database bisa dibuat Open (hanya READ ONLY, tidak bisa READ WRITE) sehingga kita bisa melakukan query. Biasanya ini dilakukan untuk tujuan reporting. Untuk menjadilkan mode open read only, matikan dulu proses recoverynyaツ
    SQL> alter database recover managed standby database cancel;Setelah itu, alter database openツ
    SQL> alter database open read only;
  2. setelah selesai dengan query reporting, kita bisa mengembalikan database ke mode recovery lagi.ツ
    SQL> alter database close;
    SQL> alter database recover managed standby database disconnect;

Tuning Query dengan Explain Plan

Suatu proses (query) sebelum dijalankan, database Oracle menentukan dulu mana langkah-langkah yang paling optimal (efektif dan efesien) yang akan dipilih. Contoh query yang melibatkan 5 tabel, paling tidak ada 1テ2x3テ4x5 pilihan langkah (execution plan) tabel-table mana yang akan di-joint terlebih dahulu. Urutan join tentu saja menentukan resource (cost) yang akan dipakai.

Untuk database dengan query yang kecil, tuning query dengan explain plan mungkin tidak begitu kelihatan manfaatnya. Namun untuk query yang melibatkan data besar-besaran, wow窶ヲ benar-benar terasa.

Sebelum menjalankan query, kita bisa melihat 窶彳xecution plan窶 mana yang akan dipilih oleh Oracle. Caranya adalah dengan menjalankan 窶彳xplain plan窶. Untuk dapat memanfaatkan feature explain plan ini, berikut langkah-langkahnya:ツ

  1. Pastikan bahwa instance parameter OPTIMIZER_MODE tidak sama dengan RULE. (Pilihan value untuk OPTIMIZER_MODE adalah rule, choose, all_rows, first_rows , first_rows_n). Kalau nilainya RULE, maka Oracle tidak akan menentukan execution plan berdasarkan cost-nya, tapi berdasarkan aturan (rule) default-nya Oracle.
  2. Jalankan script utlxplan.sql untuk membuatツtable plan. Ini dijalankan satu kali saja oleh user yang akan melakukan Explain Plan.ツ
    SQL> @?/rdbms/admin/utlxplan.sql
  3. Berikut ini contoh command untuk membuat plan dari suatu queryツ
    SQL> explain plan for
    select * from b where owner='ROHMAD'
    union select * from c where owner='ROHMAD';
  4. Setelah itu, lihat execution plan-nyaツ
    Di Oracle 8iツ
    SQL> @?/rdbms/admin/utlxplsDi Oracle 9i ke atasツ
    SQL> select * from table(dbms_xplan.display);

Contoh kasus.ツSaya punya tabel A, B, dan C di schema TEST yang strukturnya sama persis

  • Tabel A yang berisi data TABEL dan INDEX dari database.
  • Tabel B yang berisi data TABEL dari database.
  • Tabel C yang berisi data INDEX dari database.

Jadi content (isi) tabel A adalah sama dengan content table B ditambah content tabel C. Masing-masing tabel punya index untuk kolom OWNER. Kalau saya ingin query data dengan OWNER=窶儚OHMAD窶, mana yang lebih cepat?

  1. query di A saja?ツ
    SQL> select * from a where owner='ROHMAD';
  2. atau query di tabel B kemudian di UNION (gabung) dengan query di tabel C?ツ
    SQL> select * from b where owner='ROHMAD' union select * from c where owner='ROHMAD';

Untuk mengetahuinya, kita perlu membuat explain plan untuk kedua pilihan query di atas.

  1. Buat ketiga tabel dan index contoh tersebutツ
    SQL> create table a as select * from dba_objects
    where OBJECT_TYPE in ('TABLE','INDEX');
    SQL> create table b as select * from dba_objects
    where OBJECT_TYPE in ('TABLE');
    SQL> create table c as select * from dba_objects
    where OBJECT_TYPE in ('INDEX');
    SQL> create index a_owner on a (owner);
    SQL> create index b_owner on b (owner);
    SQL> create index c_owner on c (owner);
    Kemudian buat statistiknya (gather statistic)ツ
    SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'TEST', TABNAME => 'A', CASCADE => TRUE, ESTIMATE_PERCENT => 5 , METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', DEGREE => 4, GRANULARITY=> 'DEFAULT');SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'TEST', TABNAME => 'B', CASCADE => TRUE, ESTIMATE_PERCENT => 5 , METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', DEGREE => 4, GRANULARITY=> 'DEFAULT');SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'TEST', TABNAME => 'C', CASCADE => TRUE, ESTIMATE_PERCENT => 5 , METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', DEGREE => 4, GRANULARITY=> 'DEFAULT');
  2. Jalankan explain plan untuk query pertamaツ
    SQL> explain plan for
    select * from a where owner='ROHMAD';
    Lihat execution plan-nyaツ
    SQL> set lines 120
    SQL> select * from table(dbms_xplan.display);
    Jalankan explain plan untuk query keduaツ
    SQL> explain plan for
    select * from b where owner='ROHMAD' union select * from c
    where owner='ROHMAD';
    Lihat execution plan-nyaツ
    SQL> set lines 120
    SQL> select * from table(dbms_xplan.display);
  3. Bandingkan kedua execution plan tersebut. OO窶ヲ ternyata query kedua lebih besar cost-nya dibandingkan query pertama. Perintahツunionツternyata menambah pekerjaan tambahan yaituツSORT UNIQUE.

Kesimpulannya, pilih query pertama yang cost-nya lebih kecil

Tuning Query dengan SQL Trace dan tkprof

Beberapa waktu yang lalu saya membahas tuning query denganツexplain plan. Dengan explain plan kita bisa tahu (meng-estimate)ツnantinyaツquery kita itu memakai 窶彳xecution plan窶 yang mana. Sementara dengan SQL trace kita bisa mengetahui query yangツsedangツberjalan ini menggunakan 窶彳xecution plan窶 yang mana. Jadi 窶彳xplain plan窶 adalah untuk meramalkan, sedangkan 窶徭ql trace窶 untuk melihat kejadian yang sesungguhnya.

Kelebihan SQL trace adalah SQL trace menampilkan informasi yang lebih banyak. Lebih detail tentang informasi yang bisa digali dari sql trace, silahkan lihat referensi di akhir tulisan ini. Berikut ini langkah-langkah (step-step) untuk mengaktifkan SQL trace:ツ

  1. Pastikan bahwa instance parameter TIMED_STATISTICS=true.
  2. Aktifkan instance parameter sql_trace=true. Kita cukup lakukan di level session saja.ツalter session set sql_trace=true;
  3. Jalankan query yang akan dianalisa.ツ
    SQL> select * from b where owner='ROHMAD'
    union select * from c where owner='ROHMAD';
  4. setelah selesai, disable sql_trace.ツalter session set sql_trace=false;
  5. Hasil trace ditaruh di directory udump. Untuk melihat lokasi udump, gunakan command ini (pakai user yang punya role dba)ツ
    SQL> sho parameter user_dump_destFile trace yang dihasilkan berformatツnamainstance_ora_OSID.trc. Dalam contoh ini nama instance adalah ts. File yang dihasilkan adalahツts_ora_14662.trc. OSID bisa diperoleh dengan command berikut:ツ
    SQL> select a.SPID from v$process a, v$session b
    where a.addr=b.paddr and
    b.username='nama_user_yang_menjalankan_sql_trace';Mungin kita tidak perlu report-report mencari OSID, kalau file-file di derektori udump tidak banyak, kita mungkin bisa langsung menemukan file trace tersebut.
  6. Untuk membaca file trace dengan format yang user fiendly, gunakan toolツtkprof. Berikut ini contohnyaツ
    cd lokasi_direkroty_user_dump_dest
    tkprof ts_ora_14662.trc output=ts_ora_14662.trc.logHasil yang diformat ditaruh di file yang ditunjukkan oleh parameter output, yaitu ts_ora_14662.trc.log.

Setelah dapat trace file (hasil tkprof), lihat bagian 窶彳xecution plan窶. Biasanya yang paling penting adalah kalau ada 窶彷ull table scan窶, nah kita bisa mencoba-coba gimana sih kalau pakai index.

Pengalaman saya, sebagian besar porsi tuning query adalah:ツ
- Menemukan bagian mana yang melakukan full table scanツ
- Memakai (membuat) index yang berkaitan dengan queryツ
- OO窶ヲ ternyata dengan memakai index, query jadi jauh lebih cepat

Arsitektur Database Oracle

Kataツdatabaseツdalam frasa 窶彗rsitektur database Oracle窶 seharusnya adalahツDatabase Management Systemツ(DBMS). Untuk penyederhanaan penyebutan,ツDatabase Management Systemツmemang sering cukup disebutツdatabaseツsaja.

Secara umum komponen DBMS Oracle terdiri atas memory, proses, dan file-file. Lebih jauh lagi, komponen-komponen tersebut dikelompokkan sebagai berikut:

  1. Instanceツ
    - Memory yang disebut sebagai System Global Area (SGA), terdiri atas: Shared Pool (Libary Cache and Data Dictionary Cache), Database Buffer Cache, Redolog Buffer Cache, Java Pool, Large Pool.ツ
    - Back ground process: PMON, SMON, DBWR, LGWR, CKPT, dan lain-lain
  2. Databaseツ
    - Datafileツ
    - Control fileツ
    - Redo log file
  3. Komponen lainツ
    - process: Server Process, user processツ
    - memory: Program Global Area (PGA)ツ
    - File: Archived log, parameter, dan password file

Perbedaan Instance dengan Database

Kebanyakan kita mungkin tidak tahu persis perbedaan instance dan database. Banyak yang mengira bahwa instance itu ya database. Bagi yang lebih kritis lagi lantas bertanya-tanya, kalau gitu apa gunanya Oracle memakai istilah instance dan database?

Kalau kita membuat database dengan DBCA, by default nama instance dan nama database itu memang sama. Apakah nama database yang saat ini kita maintain itu sama dengan nama instance?

  • Untuk mengecek nama instance, pastinya semua DBA Oracle sudah tahu. Value dari ORACLE_SID adalah nama dari instance juga.
  • Untuk mengecek nama database, gunakan commandツselect VALUE from v$parameter where NAME=窶囘b_name窶

Lebih jelasnya, ada baiknya kita lihat Arsitektur Database Oracle di siniツhttp://rohmad.net/2008/04/17/arsitektur-database-oracle/

Instance adalah strukturツprosesツdanツmemoryツyang menjalankan sistem database (DBMS, database management system). Sedangkan database adalah sekumpulan file yang menyimpan data (yang terdiri atas datafile, controlfile, dan redo log file).

IstilahツDatabase Oracleツyang sering kita pakai itu merujuk padaツdatabase management systemツ(DBMS)ツOracle. Ngucapin database management system mungkin terlalu panjang. Ngucapin DBMS mungkin kok tidak enak. Jadi gampang-gampangan, sebut saja database.

Step-step membuat database Oracle 10g

Setelahツmeng-install software database (RDBMS) Oracle, sekarang saatnya membuat database. Baik di Windows maupun Unix (Linux, Sun Solaris, IBM AIX, HP UX, dan lain-lain) caranya sama saja. Membuat database, bisa dengan mamakai SQL script (via SQLPlus) ataupun GUI (wizard) yang disediakan Oracle. Di versi 8i, tool GUI tersebut adalahツdbassist; sementara versi 9i ke atas adalahツdbca.

Untuk Windows lokasi dbca ada directory %ORACLE_HOME%/bin , sementara untuk Unix di $ORACLE_HOME/bin. Secara struktur, lokasi file-file software Oracle baik di Windows maupun Unix adalah sama saja. Yang berbeda hanya penulisan parameter. Di Windows, parameter diapit oleh tanda % (contoh %ORACLE_HOME%) sedangkan di Unix parameter didahului oleh tanda $ (contoh $ORACLE_HOME)

Berikut ini adalah langkah-langkah (step-step) untuk membuat database 10g:

  1. Jalankan command dbca. Akan muncul form wellcome.ツLihat gambarnya di sini. Selanjutnya klik tombol Next.
  2. Berikutnya keluar form pilihan option.ツLihat gambarnya di sini. Pilih 窶廚reate a Database窶. Selanjutnya klik tombol Next.
  3. Berikutnya keluar form Database template.ツLihat gambarnya di sini. Ada 3 pilihan template. Kalau tidak mau pakai templete, pilih 窶廚ustom Database窶. Dalam contoh ini saya memilih template 窶廨eneral Purpose窶. Selanjutnya klik tombol Next.
  4. Berikutnya muncul form Database Identification.ツLihat gambarnya di sini. Ini nama database. Terserah mau diberi nama apa. Di sini saya namai 窶彭ataku窶. Selanjutnya klik tombol Next.
  5. Berikutnya mucul form Management Option.ツLihat gambarnya di sini. Saya memilih 窶廚onfigure the Database with Enterprise Manager窶. Selanjutnya klik tombol Next.
  6. Berikutnya mucul form Database Credential.ツLihat gambarnya di sini. Masukkan password. Untuk mempermudah biar tidak gampang lupa, saya memilih password yang sama untuk semua account. Selanjutnya klik tombol Next.
  7. Berikutnya mucul form Storage Option.ツLihat gambarnya di sini. Saya memilih 窶廡ile system窶. Selanjutnya klik tombol Next.
  8. Berikutnya mucul form Database File Location.ツLihat gambarnya di sini. Saya memilih 窶廚:\oracle\oradata窶. Selanjutnya klik tombol Next.
  9. Berikutnya mucul form Recovery Configuration.ツLihat gambarnya di sini. Saya memilih untuk menggunakan Flash Recovery Area. Saya memakai direktori default. Selanjutnya klik tombol Next.
  10. Berikutnya mucul form Database Content.ツLihat gambarnya di sini. Sample schema berisi schema-schema (berserta object: table, index, view, dll) contoh dari Oracle. Ini bermanfaat bagi yang sedang belajar. Selanjutnya klik tombol Next.
  11. Berikutnya mucul form Initialization Parameter.ツLihat gambarnya di sini. Untuk memori, saya memilih custom, dengan shared memory management: Automatic. Nilai SGA sesuaikan dengan memory komputer kita. Dengan memory PC 1G, saya masukkan SGA: 274M. Selainnya saya memakai value (nilai) default. Selanjutnya klik tombol Next.
  12. Berikutnya mucul form Database storage.ツLihat gambarnya di sini. Lokasi file-file adalah sesuai dengan directory yang telah kita pilih tadi. Melalui wizard ini kita bisa mengubah ke direktori lain. Selanjutnya klik tombol Next.
  13. Berikutnya mucul form Creation Option.ツLihat gambarnya di sini. Pilih 窶廚reate Database窶 karena kita akan membuat database. Pilih juga 窶廨enerate database creation scripts窶. Bermanfaat bagi yang belajar, untuk memahai command-command apa yang dijalankan ketika membuat database. Selanjutnya klik tombol Finish.
  14. Berikutnya muncul form Confirmation.ツLihat gambarnya di sini. Berisi tentang resume database yang akan kita buat. Selanjutnya klik tombol OK.
  15. Berikutnya mucul Installation Progress. Kalau create di Windows mungkin muncul error,ツlihat gambarnya di sini. Kalau menemui error ini, tambahkan parameter berikut di file %ORACLE_HOME%/network/admin/sqlnet.oraツSQLNET.AUTHENTICATION_SERVICES = (NONE)ツBila file tersebut belum ada, buatlah. Jika file sudah ada dan nilai parameter tersebut bukan NONE, ubah menjadi NONE. Selanjutnya klik Ignore, nanti akan kembali ke form 窶廚reation Option窶, ulangi (lanjutkan) ke step-step berikutnya. Bila berhasil, maka 窶廚reation progress窶 akan menunjukkan proses pembuatan database yang sedang berjalan.ツlihat gambarnya di sini
  16. Akhirnya proses selesai.ツLihat gambarnya di sini. Selanjutnya klik tombol Exit. Setelah selesai, bila di WIndows, ubah nilai SQLNET.AUTHENTICATION_SERVICES dari NONE menjadi NTSSQLNET.AUTHENTICATION_SERVICES = (NTS)ツIni penting agar bisa mudah memanage database, kapan-kapan akan saya bahas.

Kitab Suci DBA Oracle

Posted in Uncategorized on May 30, 2008

Subject artikel ini mungkin kedengaran keren, ya. Saya menyebutnya 窶鰐itab suci窶 (kitab suci pakai tanda petik) karena hampir setiap hari saya buka, saya baca, saya 窶藁intai窶 pertolongan, dan saya jadikan 窶冱andaran窶. Selanjutnya untuk mempermudah penulisan saya tidak pakai tanda petik, namun maknanya tetap sebagai kitab suci pakai tanda petik.

Yang saya sebut sebagai kitab suci itu adalah view DICT, dokumentasi Oracle, dan Metalink. Mari kita bahas satu per satu.

1. View DICT
DICT adalah synonym dari view DICTIONARY. DICT berisi daftar view (view bawaan Oracle) yang berisi tentang semua informasi di database Oracle. DICT berisi kolom TABLE_NAME dan COMMENTS. Lihat definisi DICT dengan comand desc (describe).ツ
SQL> desc dict

Informasi apa saja bisa kita dapatkan di sini. Misalkan kita ingin tahu, informasi tentang partisi disimpan di VIEW apa saja kah? Caranya, select dari view DICT, masukkan kata kunci yang ingin kita cari dalam clause WHERE. Kata kunci pakai huruf besar. Formatnya adalahツselect table_name from dict where table_name like 窶%KATA KUNCI%窶卍. Dalam contoh ini kata kunci adalah PARTITION.

SQL> select table_name from dict
where table_name like '%PARTITION%' order by table_name;TABLE_NAME
------------------------------
ALL_IND_PARTITIONS
ALL_IND_SUBPARTITIONS
ALL_LOB_PARTITIONS
ALL_LOB_SUBPARTITIONS
ALL_SUBPARTITION_TEMPLATES
ALL_TAB_PARTITIONS
ALL_TAB_SUBPARTITIONS
USER_IND_PARTITIONS
USER_IND_SUBPARTITIONS
USER_LOB_PARTITIONS
USER_LOB_SUBPARTITIONS
USER_SUBPARTITION_TEMPLATES
USER_TAB_PARTITIONS
USER_TAB_SUBPARTITIONS

Daftar VIEW yang muncul adalah VIEW yang bisa diakses oleh user yang bersangkutan (user yang query ke DICT tersebut). Bila user tersebut mempunya ROLE DBA (misalnya SYS dan SYSTEM) maka daftar VIEW yang keluar lebih banyak lagi. Berikut ini daftar VIEW yang hanya bisa dilihat oleh user dengan role DBA:ツ
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_LOB_PARTITIONS
DBA_LOB_SUBPARTITIONS
DBA_SUBPARTITION_TEMPLATES
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS

2. Dokumentasi Database Oracle dari oracle.com
Dokumentasi yang paling sering saya pakai adalah Master Index. Istilah-istilah yang tidak saya pahami, bisa segara saya dapatkan pemahamannya melalui Master Index. Misalkan saya pengin tahu lebih banyak tentang dbms_resource_manager. Dari Master Index saya buka link yang menuju istilah-istilah yang dimulai huruf D. Kemudian saya search pakai kata kunci (keyword) dbms_resource_manager. Selanjutnya saya dapat banyak pilihan link (tema) yang mana yang saya butuhkan.

Dokumentasi berikutnya adalah Administrator Guide. Pokoknya, apa saja ada di dikumentasi Oracle. Meskipun database Oracle banyak versinya (8i, 9i, 10g, 11g), secara umum content dokumentasinya hampir sama kecuali yang version specific.

Berikut ini dokumentasi yang bisa kita dapatkan dari oracle.com. Kalau ingin akses yang lebih cepat, sebaiknya di-download dulu ke PC local kita.ツDocumentation Index ada di sini.

3. Metalink
Linknya di siniツ
https://metalink.oracle.com/. Ini adalah tool yang paling saya andalkan. Untuk bisa memanfaatkan Metalink, kita harus punya nomor CSI (Customer Support Identifier). Setiap company yang menggunakan database Oracle danツmembeli support Oracle (license)pasti mendapat nomor CSI. Jadi, tidak semua orang bisa akses ke sini.

Manfaat yang bisa kita dapatkan dari Metalink:
- Minta bantuan (support) team Oracle terhadap masalah yang kita hadapiツ
- Minta CD software Oracle (tanpa biaya apapun alias gatis tis 窶ヲ)ツ
- Download Patchツ
- Melihat technical documentation yang tidak di-share di 窶廾racle Documentation窶

Apapun masalahnya, selama kita punya akses ke Metalink, pasti ada solusinya (bisa di-handle) karena kita punya senjata pamungkas yaitu 窶廴inta bantuan (support) team Oracle terhadap masalah yang kita hadapi窶. Namun ya itu tadi, kita harus punya akses ke Metalink. Atau dengan kata lain, kita mesti membeli support (license) Oracle dulu.

Bagi yang tidak punya akses (account) ke Metalink, masih ada alternatif jalan lain untuk mencari solusi (meskipun tidak seampuh Metalink). Silahkan cari di:

Komputer Lambat setelah Install Database Oracle

Posted in Uncategorized on Jun 26, 2008

Untuk belajar database Oracle ataupun keperluan development, kita bisa menginstall software Oracle di komputer (PC) kita. Misalkan kita menginstall database Oracle di PC kita yang punya memory 1 GB dan hard disk puluhan GB. Terus, setelah install kok performance PC jadi lambat, ya. Kenapa?

Ketika instalasi, kita ditanya apakah akan membuat database sekalian? Lihat gambar di bawah ini.

Kalau 窶彡reate starter database窶 kita check, maka setelah installasi, installer akan membuat database. Pada contoh gambar di atas, pilihan ini tidak saya ambil.

Tentu saja database Oracle memakai memory. Kalau memory database Oracle terlalu besar untuk ukuran PC kita, jelas窶ヲ PC akan jadi lambat. Untuk melihat berapa memory yang sedang dipakai, gunakan windows Task Manager.

Kalau memory yang dipakai Oracle terlalu besar untuk ukuran PC kita, untuk itu kita bisa menurunkannya. Cara menurunkan alokasi memory di database Oracle:

  1. Edit init (instance) parameter.ツ
    Misalkan saya akan menurunkan memory menjadi 200MB. Buka command prompt, jalankan SQLPLUSツ
    C:\>sqlplus "/ as sysdba"
    SQL> alter system set sga_target=209715200 scope=spfile;
  2. Restart databaseツ
    SQL> shutdown immediate
    SQL> startup

Di Windows, database server (instance) kita dibuatkan service. Untuk melihatnya, dariツControl Panel, bukaツAdministrative Tools, kemudian bukaツServices. Lihat gambar di bawah ini (dalam contoh ini, nama database dan instance adalah DATAKU).

Startup Typeツservices punyanya Oracle biasanya dibuatツAutomatic. Dalam contoh ini, ada 3 yaitu:ツ
- OracleDBConsoleNAMADATABASE
- OracleNAMAHOMETNSListenerツ
- OracleServiceNAMAINSTANCE

Agar service-service tersebut tidak segera naik waktu PC baru startup, ubahツStartup TypemenjadiツManual.

Terus, kalau database Oracle sedang tidak kita pakai, service-service tersebut dimatikan saja. Dan nyalakan kalau sedang dipakai saja sehingga resource (khususnya memory) tidak terbuang sia-sia.

Comments   

 
0 #1 Guest 2018-07-25 07:19
Untuk melihat datafile dan size dari tablespace UNDOTBS2
SQL> select file_name,bytes from dba_data_files
where tablespace_name='UNDOTBS2窶;
Untuk melihat free space tiap-tiap datafile dari tablespace UNDOTBS2
SQL> select a.name, sum(b.bytes) from v$datafile a, dba_free_space b where a.file#=b.file_ id and b.TABLESPACE_NA ME='UNDOTBS2' group by a.name;
Quote
 

Category Database

TweetTweet Share on LinkedInShare on LinkedIn Share on Google+Google+ Submit to RedditReddit Publish on WordPress WordPress Send emailSend email