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)