LaGiPusHinGWitH ---- IT

Navigation

Home |

Monthly Archives

Most recent entries

Syndicate

RSS 2.0
Atom

Site Credits

Design by:
BlogMoxie

Pemeriksaan Oracle Server

tutor_check_oracle.txt

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.xxxAlamat Email inidilindungi dari bot spam, Anda Harus Mengaktifkan Javascript Untuk Melihatnya ‘s password:
Last login: Wed Sep 26 15:22:58 2007 from xxx.17.xx.153
Sun Microsystems Inc. SunOS x.x Generic May xxxx

Sun Fire V880 S/N xxxxxxx PT. xxxxxx 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 &#xus;r &#xsy;s &#xwi;o &#xid;le
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)</pre>

Posted by roninmorgue on 10/12 at 11:39 PM
Computer DataBase • (0) Comments • (249) Trackbacks • Permalink