Checking User Lock

Seringkali dalam menangani sebuah server database, seorang DBA mengalami/mendapatkan server-nya
crash/hang. Ini pula yang terkadang saya alami, ada laporan dari department lain bahwa database tidak
dapat diakses untuk input data maupun melihat data (database hanya diam tidak menunjukkan reaksi apapun juga).

Dan ini merupakan tanggung jawab seorang DBA untuk melakukan analisa serta mencari tahu apa permasalahannya.
Singkat cerita, saya langsung melakukan pemeriksaan dan berdasarkan pengalaman biasanya yang menyebabkan
hal ini adalah masalah di size, SGA memori, Object Lock, User Lock, dll.

Dari semua kemungkinan tersebut didapatkan bahwa permasalahan disebabkan oleh User Lock, maksudnya disini
ada user-user yang menggunakan resource pada database tetapi proses dari user tersebut terkunci oleh database
dan hal ini akan membuat database menjadi crash.

Technical Review :

1. Seperti biasa jalankan SQlplus dan login ke database dengan role DBA
2. Lihat field-field yang ada pada tabel sistem dba_locks

SQL> desc dba_locks;
Name Null? Type
----------------------------------------- -------- ----------------------------
SESSION_ID NUMBER
LOCK_TYPE VARCHAR2(26)
MODE_HELD VARCHAR2(40)
MODE_REQUESTED VARCHAR2(40)
LOCK_ID1 VARCHAR2(40)
LOCK_ID2 VARCHAR2(40)
LAST_CONVERT NUMBER
BLOCKING_OTHERS VARCHAR2(40)

3. Lihat session-session pada database yang memiliki kemungkinan terkunci

SQL> select * from dba_locks where mode_held = ‘Exclusive’;

SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS

3 Redo Thread Exclusive None 1 0 38934 Not Blocking
9 Transaction Exclusive None 196645 3730106 6714 Not Blocking
13 Transaction Exclusive None 1703965 3655 0 Not Blocking
13 Distributed Xaction Exclusive None 66 0 0 Not Blocking
274 Transaction Exclusive None 458799 2187103 7527 Blocking
282 Transaction Exclusive None 131115 3026301 5534 Not Blocking
329 Transaction Exclusive None 589833 2876171 4621 Not Blocking
377 Transaction Exclusive None 393240 2944259 4041 Not Blocking
467 Transaction Exclusive None 983041 12005 724 Not Blocking

9 rows selected.

Terlihat ada 9 session yang memiliki kemunginan dan kita lihat pada field BLOCKING_OTHERS, session yang sudah pasti terkunci
dan kemungkinan besar penyebab permasalahan ini. Dari data yang ada terlihat bahwa session dengan ID 274 memilki type transaksi
dan status ‘BLOCKING’

4. Dari sini kita lanjutkan dengan mencari siapa pemilik session ID tersebut, Lihat field-field pada
tabel sistem v$session

SQL> desc v$session;

SQL> select * from v$session where sid = ‘274’;

SADDR SID SERIAL# AUDSID PADDR USER# USERNAME STATUS SERVER SCHEMA# SCHEMANAME OSUSER PROCESS MACHINE TERMINAL
---------------- ---------- ---------- ---------- ---------------- ---------- ------------------------------ ---------- ---------- ---------------- ---------------- -------- -----
00000003C7E8B458 274 70 4700007 00000003C7CC8E88 795 XXXXX INACTIVE DEDICATED 795 XXXXX Gugun 5916:5248 DATABASE\GUNAWAN GUNAWAN

Ok...tidak semua data pada field di perlihatkan karena terlalu banyak dan juga user saya edit, tetapi ini sudah cukup untuk mendapatkan data yang diperlukan:

Dari data ini bisa terlihat bahwa status user ini Tidak Aktif/INACTIVE.

5. Langkah terakhir adalah kita ‘membunuh’ session yang tidak diperlukan ini,
dan parameter yang diperlukan adalah SID dan SADDR.

SQL> alter system kill session ‘274,00000003C7E8B458’; --> alter system kill session ‘SID,SADDR’;

Selesai....akhirnya database dapat berjalan lagi dengan lancar.

PS : 1. Sebelum kill session kita harus yakin bahwa session tersebut sudah tidak aktif
2. Setelah proses ini berjalan lakukan analisa mengapa session ini bermasalah agar tidak terulang kembali.

Resizing TableSpace on Oracle

Dear all lagi.........

Sesuai janji gw, gw bikin catatan kecil lagi...yang ini khusus ngebahas soal mengatasi ukuran tablespace yang kritis.
Masih ingatkan sama catatan gw yang terdahulu.

langsung aja maaaang....

Pertama2 kita check dengan script yang sudah kita bikin -->

the code :

## Cek TABLESPACE By Command Line Sql ##:

set linesize 128 echo off feedback on heading on verify off pagesize 35
col megs_alloc format 999,999,999 heading “MB ALLOC”
col megs_free format 999,999,999 heading “MB FREE”
col megs_used format 999,999,999 heading “MB USED”
col pct_free format 999 heading “% FREE”
col pct_used format 999 heading “% USED”
--col bsize format 99999 new_value bsize heading “BLOCK SIZE”

--set termout off
--select value bsize from v$parameter
--where name = ‘db_block_size’;
--set termout on

break on report skip 1
compute sum label “TOTAL” of megs_alloc on report
compute sum of megs_free on report
compute sum of megs_used on report

select c.tablespace_name,
round(a.bytes/1048576,2) megs_alloc,
round(b.bytes/1048576,2) megs_free,
round((a.bytes-b.bytes)/1048576,2) megs_used,
round(b.bytes/a.bytes * 100,2) pct_free,
round((a.bytes-b.bytes)/a.bytes,2) * 100 pct_used
from (select tablespace_name,
sum(a.bytes) bytes,
min(a.bytes) minbytes,
max(a.bytes) maxbytes
from sys.dba_data_files a
group by tablespace_name) a,
(select a.tablespace_name,
nvl(sum(b.bytes),0) bytes
from sys.dba_data_files a,
sys.dba_free_space b
where a.tablespace_name = b.tablespace_name (+)
and a.file_id = b.file_id (+)
group by a.tablespace_name) b,
sys.dba_tablespaces c
where a.tablespace_name = b.tablespace_name(+)
and a.tablespace_name = c.tablespace_name
union
select tablespace_name, round((sum(bytes_used) + sum(bytes_free)) / 1048576,2) meg_alloc,
round(sum(bytes_free) / 1048576,2) megs_free,
round(sum(bytes_used) / 1048576,2) megs_used,
round(sum(bytes_free) / (sum(bytes_used) + sum(bytes_free)) * 100,2) pct_free,
round(sum(bytes_used) / (sum(bytes_used) + sum(bytes_free)) * 100,2) pct_used
from v$temp_space_header
group by tablespace_name
order by tablespace_name;

terus kita running lewat sqlplus-->

SQL> @ c:\Data\script\mccserver\tablespacecheck.sql Tablespace Size

TABLESPACE_NAME MB ALLOC MB FREE MB USED % FREE % USED
----------------- ------------ ------------ ------------ ------ ------
CWMLITE 20 3 18 13 88
DRSYS 20 10 10 52 48
EXAMPLE 149 0 149 0 100
======================= potong ============
MAA_TRXP 12,048 255 11,793 2 98
MAA_TRXE 14,048 2,395 11,653 17 83
======================= potong ============
SYSTEM 560 135 425 24 76
TEMP 8,099 0 8,099 0 100
TOOLS 10 10 0 99 1
UNDOTBS1 5,951 5,477 474 92 8
USERS 25 24 1 98 3
======================= potong ============
XDB 38 38 0 100 0
------------ ------------ ------------
TOTAL 121,338 51,027 70,311

34 rows selected.

Disini terlihat ada 2 buah tablespace yang kritis(lebih dari 80%)....
Sekarang kita akan menambah size dari tablespace tersebut, oh iyah...semua command ini full dari sqlplus yah

langkah berikutnya adalah kita lihat dulu nama2 datafile dari tablespace yang akan kita resize (apa sih datafile?
cari tahu aja yah digoogle..atau tanya lagi ntar insya allah gw jawab)

SQL> select * from dba_data_files where tablespace_name = ‘MAA_TRXP’;

FILE_NAME
---------------------------------------
/m02/oradata/maa/maa_trxp01.dbf
/m02/oradata/maa/maa_trxp02.dbf
/m02/oradata/maa/maa_trxp03.dbf

3 rows selected.

hmmmm...ada yang kurang, apa yah? oh iya jumlah/size dari masing2 datafile belum tampak nih…

sekarang kita lihat field2 pada object datafile biar hasilnya spesifik:

SQL> desc dba_data_files;

Name
---------------------------------------
FILE_NAME
FILE_ID
TABLESPACE_NAME
BYTES
BLOCKS
STATUS
RELATIVE_FNO
AUTOEXTENSIBLE
MAXBYTES
MAXBLOCKS
INCREMENT_BY
USER_BYTES
USER_BLOCKS

SQL> select substr(file_name,1,50),sum(bytes) from dba_data_files where tablespace_name = ‘MAA_TRXP’
group by file_name;

SUBSTR(FILE_NAME,1,50) SUM(BYTES)
-------------------------------------------------- ----------
/m02/oradata/maa/maa_trxp01.dbf 5242880000
/m02/oradata/maa/maa_trxp02.dbf 5242880000
/m02/oradata/maa/maa_trxp03.dbf 2147483648

3 rows selected.

Sip...sekarang kita resize tablespace dengan membuat datafile yang baru.... sebenarnya penentuan besaran kapasitas
dari datafile ada analisa tersendiri tapi...seperti biasa akan dibahas pada catatan mengenai Database analysis...hehehe...ngeles.

SQL> alter tablespace maa_trxp add datafile ‘/m02/oradata/maa/maa_trxp04.dbf’ size 5000 M;

Tablespace altered.

Check lagi datafile pada tablespace yang bersangkutan:

SQL> select substr(file_name,1,50),sum(bytes) from dba_data_files where tablespace_name = ‘MAA_TRXP’
group by file_name;

SUBSTR(FILE_NAME,1,50) SUM(BYTES)
-------------------------------------------------- ----------
/m02/oradata/maa/maa_trxp01.dbf 5242880000
/m02/oradata/maa/maa_trxp02.dbf 5242880000
/m02/oradata/maa/maa_trxp03.dbf 2147483648
/m02/oradata/maa/maa_trxp04.dbf 5242880000

4 rows selected.

Nah loooh...dah nambah kan??? siipp, mission succesed.

Sekarang check tablespace-nya, dah ngga kritis lagi atau masih kritis.

SQL> @ c:\Data\script\mccserver\tablespacecheck.sql

Tablespace Size

TABLESPACE_NAME MB ALLOC MB FREE MB USED % FREE % USED
----------------- ------------ ------------ ------------ ------ ------
CWMLITE 20 3 18 13 88
DRSYS 20 10 10 52 48
EXAMPLE 149 0 149 0 100
======================= potong ============
MAA_TRXP 17,048 5,245 11,803 31 69
MAA_TRXE 14,048 2,395 11,653 17 83
======================= potong ============
SYSTEM 560 135 425 24 76
TEMP 8,099 0 8,099 0 100
TOOLS 10 10 0 99 1
UNDOTBS1 5,951 5,477 474 92 8
USERS 25 24 1 98 3
======================= potong ============
XDB 38 38 0 100 0
------------ ------------ ------------
TOTAL 121,338 51,027 70,311

34 rows selected.

Ok deh kaka...sip kan, sekarang dah 69%, dan untuk tablespace lainnya ngga perlu deh gw jelasin lagi step2-nya,
selain ngga perlu juga akan menambah pusing para pembaca sekalian...apalagi gw yang ngetik.... smile

Pemeriksaan Oracle Server

Dear all member...para modie n semua administrator.

Seperti biasa nih, gw mau bikin catatan kecil...dan catatan ini adalah mengenai bagaimana gw melakukan
pemeriksaan rutin terhadap server database gw.(mau cuti panjang nih...tapi gw tetep harus stand by jadi PIC...hik2)

Ok..langsung aja yah, hal-hal yang gw check adalah :
catt : Banyak yang gw edit yah...jangan protes....

1. Cek Utilization Memory

login as: xxxxxx
xxxxxx@192.xxx.0.xxx's password:
Last login: Wed Sep 26 15:22:58 2007 from xxx.17.xx.153
Sun Microsystems Inc. SunOS x.x Generic May 2002
Sun Fire V880 S/N xxxxxxx PT. xxxxx xxxx xxxxx 11/11/2003

# sar -r 10 5

SunOS FV880R x.x Generic_xxxxxx-08 sun4u 10/01/2007

12:14:14 freemem freeswap
12:14:24 180058 18268192
12:14:34 179741 18249747
12:14:44 179677 18286104
12:14:54 179346 18285995
12:15:04 178601 18249875

Average 179484 18267968

2. Cek Utilization Performance CPU

# sar -u 10 5

SunOS FV880R x.x Generic_xxxxxx-08 sun4u 10/01/2007

12:15:48 %usr %sys %wio %idle
12:15:58 10 2 17 71
12:16:08 1 1 4 95
12:16:18 1 1 8 90
12:16:28 13 4 8 75
12:16:38 43 17 25 15

Average 13 5 12 69

Note: Angka-angka tersebut boleh diubah-ubah sesuai kebutuhan yang mengartikan bahwa
setiap 10 detik akan tercatat sebanyak 5 kali

3. Cek Kapasitas Disk

# df -h

Filesystem size used avail capacity Mounted on
/dev/dck/c1t0d0s0 59G 2.2G 57G 4% /
/proc 0K 0K 0K 0% /proc
mnttab 0K 0K 0K 0% /etc/mnttab
fd 0K 0K 0K 0% /dev/fd
swap 8.7G 168K 8.7G 1% /var/run
swap 8.7G 2.1M 8.7G 1% /tmp
/dev/dck/c1t1d0s6 67G 22G 45G 33% /m01
/dev/dck/c1t2d0s6 67G 29G 37G 45% /m02
/dev/dck/c1t4d0s6 67G 31G 36G 46% /m04
/dev/dck/c1t5d0s6 67G 25G 42G 38% /m05
/dev/dck/c1t3d0s6 67G 37G 30G 56% /m03

4. Cek Tablespace dari database --> ini nih yang penting banget, karena kalau diabaikan bisa ngga running tuh database.

pertama2 kita bikin script dulu, beri nama tablespacecheck.sql:

## Cek TABLESPACE By Command Line Sql ##:

set linesize 128 echo off feedback on heading on verify off pagesize 35
col megs_alloc format 999,999,999 heading "MB ALLOC"
col megs_free format 999,999,999 heading "MB FREE"
col megs_used format 999,999,999 heading "MB USED"
col pct_free format 999 heading "% FREE"
col pct_used format 999 heading "% USED"
--col bsize format 99999 new_value bsize heading "BLOCK SIZE"

--set termout off
--select value bsize from v$parameter
--where name = 'db_block_size';
--set termout on

break on report skip 1
compute sum label "TOTAL" of megs_alloc on report
compute sum of megs_free on report
compute sum of megs_used on report

select c.tablespace_name,
round(a.bytes/1048576,2) megs_alloc,
round(b.bytes/1048576,2) megs_free,
round((a.bytes-b.bytes)/1048576,2) megs_used,
round(b.bytes/a.bytes * 100,2) pct_free,
round((a.bytes-b.bytes)/a.bytes,2) * 100 pct_used
from (select tablespace_name,
sum(a.bytes) bytes,
min(a.bytes) minbytes,
max(a.bytes) maxbytes
from sys.dba_data_files a
group by tablespace_name) a,
(select a.tablespace_name,
nvl(sum(b.bytes),0) bytes
from sys.dba_data_files a,
sys.dba_free_space b
where a.tablespace_name = b.tablespace_name (+)
and a.file_id = b.file_id (+)
group by a.tablespace_name) b,
sys.dba_tablespaces c
where a.tablespace_name = b.tablespace_name(+)
and a.tablespace_name = c.tablespace_name
union
select tablespace_name, round((sum(bytes_used) + sum(bytes_free)) / 1048576,2) meg_alloc,
round(sum(bytes_free) / 1048576,2) megs_free,
round(sum(bytes_used) / 1048576,2) megs_used,
round(sum(bytes_free) / (sum(bytes_used) + sum(bytes_free)) * 100,2) pct_free,
round(sum(bytes_used) / (sum(bytes_used) + sum(bytes_free)) * 100,2) pct_used
from v$temp_space_header
group by tablespace_name
order by tablespace_name;

terus kita running lewat sqlplus-->

SQL> @ c:\Data\script\mccserver\tablespacecheck.sql

Tablespace Size

TABLESPACE_NAME MB ALLOC MB FREE MB USED % FREE % USED
----------------- ------------ ------------ ------------ ------ ------
CWMLITE 20 3 18 13 88
DRSYS 20 10 10 52 48
EXAMPLE 149 0 149 0 100
======================= potong ============
MAA_TRXP 12,048 255 11,793 2 98
MAA_TRXE 14,048 2,395 11,653 17 83
======================= potong ============
SYSTEM 560 135 425 24 76
TEMP 8,099 0 8,099 0 100
TOOLS 10 10 0 99 1
UNDOTBS1 5,951 5,477 474 92 8
USERS 25 24 1 98 3
======================= potong ============
XDB 38 38 0 100 0
------------ ------------ ------------
TOTAL 121,338 51,027 70,311


34 rows selected.

uuuppsss....ternyata ada tablespace yang kritis, penggunaan diatas 80% itu sudah kritis. Jadi gimana solusinya?
nanti gw tulis lagi catatan yang khusus ngebahas ini, kira2 title catatannya Resizing Tablespace on Oracle.. :)

Nah...terakhir adalah kita check Locking Object yang dapat membuat performance database jadi down..

5. ## Cek Locking Object yang dapat membuat applikasi Database menjadi lambat.by sql #

SQL> select * from v$locked_object;

XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCE
-------- ---------- ---------- ---------- ---------- -------------------- --------------
1 46 2197363 30223 307 XXXXXXX XXX 42933008
5 36 2739445 3255683 65 XXXXXXX XXX 4752:604

Ternyata ada 2 object yang membuat aplikasi database jadi lambat...solusinya....itu ada pembahasan khusus dibagian
Performance Tuning, tapi kalau darurat sih, bisa langsung di Kill aja tuh proses. hehehe.... :)

OK...deh pemeriksaan selesai, ini sebagian kecil dari pemeriksaan rutin gw (jadi yang utama2nya aja)

Membuat File Control Pada Oracle 10g

gw curhat dikit nih man....pas gw mo mounted dan opened DB gw..ternyata si oracle nampilin message kl database gw mengalami inconsistensi antara file control01.ctl dan control02.ctl…

SQL*Plus: Release 10.1.0.3.0 - Production on Thu Jan 4 11:01:12 2007

Copyright (c) 1982, 2004, Oracle. All rights reserved.

SQL> Connected.
SQL> SQL> ALTER DATABASE mount
*
ERROR at line 1:
ORA-00214: controlfile ‘/app/oracle/oradata/cad2006/control02.ctl’ version
14033 inconsistent with file ‘/app/oracle/oradata/cad2006/control01.ctl’
version 14030

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

apaan tuh file control01.ctl....? tanya dong kl penasaran ntar gw jawab deh!!!

Mana gw belom bikin backup controlfile pula…
walhasil gw bikin ulang deh file-file tersebut, gini nih caranya..:

CREATE CONTROLFILE REUSE DATABASE “cad2006” NORESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXDATAFILES 300
MAXINSTANCES 8
MAXLOGHISTORY 500
LOGFILE
GROUP 1 ‘/app/oracle/oradata/cad2006/redo01.log’ SIZE 1M,
GROUP 2 ‘/app/oracle/oradata/cad2006/redo02.log’ SIZE 1M,
GROUP 3 ‘/app/oracle/oradata/cad2006/redo03.log’ SIZE 1M
DATAFILE
‘/app/oracle/oradata/cad2006/sysaux01.dbf’ SIZE 210M,
‘/app/oracle/oradata/cad2006/system01.dbf’ SIZE 440M,
‘/app/oracle/oradata/cad2006/temp01.dbf’ SIZE 20M,
‘/app/oracle/oradata/cad2006/undotbs01.dbf’ SIZE 25M,
‘/app/oracle/oradata/cad2006/users01.dbf’ SIZE 5M;

Untuk lebih amannya lakukan backup terhadap file control secara rutin.