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

  • Oracle
    • WebLogic Reset (212) Tue04,11:01am

      WebLogic起動するたびにユーザ名とパスワードを聞いてきて煩わしかったので回避したメモ。     securityフォルダを作成     $ mkdir $DOMAIN_HOME/servers/AdminServer/security     boot.propertiesを作成    boot.properties     username=weblogic    password=password     ※平文でOK     nohup & でWeblogicを起動     $ nohup sh $DOMAIN_HOME/startWebLogic.sh &     ※nohup.outに吐かれるのがいやだったら適当にリダイレクトさせる     boot.propertiesが暗号化されてることを確認     $ ca…

      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 “Kitab 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 “Data 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 “Listener Configuration”. Selanjutnya klik tombol Next.
  2. Berikutnya keluar form Listener Configuration. Lihat gambarnya di sini. Pilih “Add”. 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 “Database Services”. Selanjutnya klik tombol “Add 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-installdatabase 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 “Local Net Service Name configuration”. Selanjutnya klik tombol Next.
  2. Berikutnya muncul form Net Service Name Configuration. Lihat gambarnya di sini. Pilih “Add”. Selanjutnya klik tombol Next.
  3. Berikutnya masukkan “Service 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 “Yes”. 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 “Change 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 “No” 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_tablespacesdba_data_filesdba_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_filesv$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 “High Water Mark”.

Kalau size tablespace (datafile) tidak bisa dikurangi dengan “alter 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 “invalid 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(’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

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(’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 <>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;

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 “Restore dari offline backup”.
  2. Ingat, control file yang digunakan untuk menaikkan restored database adalah control file hasil dari “alter 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=’EXCLUSIVE’
    • log_archive_config=’DG_CONFIG=(tsprimary,tsstandby)’
    • log_archive_dest_1=’LOCATION=/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=’tsstandby’
    • fal_server=’tsprimary’
    • log_archive_dest_2=’service=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=’ts’
    • pga_aggregate_target=209715200
    • sga_target=1610612736
    • undo_management=’AUTO’
    • undo_tablespace=’UNDOTBS1′
  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.oratsprimary = 
    (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 “Services”-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 “alter 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 “execution plan” mana yang akan dipilih oleh Oracle. Caranya adalah dengan menjalankan “explain 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=’ROHMAD’, 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 “execution plan” yang mana. Sementara dengan SQL trace kita bisa mengetahui query yang sedang berjalan ini menggunakan “execution plan” yang mana. Jadi “explain plan” adalah untuk meramalkan, sedangkan “sql 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 “execution plan”. Biasanya yang paling penting adalah kalau ada “full 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 “arsitektur 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=’db_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 “Create 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 “Custom Database”. Dalam contoh ini saya memilih template “General 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 “dataku”. Selanjutnya klik tombol Next.
  5. Berikutnya mucul form Management Option. Lihat gambarnya di sini. Saya memilih “Configure 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 “File system”. Selanjutnya klik tombol Next.
  8. Berikutnya mucul form Database File Location. Lihat gambarnya di sini. Saya memilih “C:\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 “Create Database” karena kita akan membuat database. Pilih juga “Generate 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 “Creation Option”, ulangi (lanjutkan) ke step-step berikutnya. Bila berhasil, maka “Creation 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 ‘kitab suci’ (kitab suci pakai tanda petik) karena hampir setiap hari saya buka, saya baca, saya ‘mintai’ pertolongan, dan saya jadikan ’sandaran’. 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 “Oracle Documentation”

Apapun masalahnya, selama kita punya akses ke Metalink, pasti ada solusinya (bisa di-handle) karena kita punya senjata pamungkas yaitu “Minta 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 “create 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