OS(리눅스) 5초단위로 서버 및 DB 상태 출력 스크립트

OS(리눅스) 5초단위로 서버 및 DB 상태 출력 스크립트

---

1. OS VMSTAT 5초단위로 상태 출력

while true

do

 

cpu=`/usr/bin/vmstat 1 2 | sed 1,3d | awk '{u=100-$15;printf("%s %s %s %s %s %s %s %s",u,$1,$3,$4,$9,$10,$13,$14)}'`

wasmq=`netstat -na | grep EST | grep 70.121.238.221 | wc -l`

echo "$cpu" "$wasmq"

sleep 5

done

2. DB Session 5초 단위로 상태 출력

while true; do

sh /oracle/test/mon.sh

sleep 5

done

1) mon.sh

col sid for 9999

col waiting_on for a75

col serial# for 999999

col prg for a13

col OraPid for 9999

col command for a10

col aa Heading "DB-User" for a8

col bb Heading "OS-Pid" for a9

 

set pau off

select s.sid SID,s.serial#,

decode(s.command,

        '0','NO',

        '1','Create Tab',

        '2','Insert',

        '3','Select',

        '6','Update',

        '7','Delete',

        '9','Create Idx',

        '10','Drop Idx',

        '15','Alter Tbl',

        '24','Create Proc',

        '32','Create Link',

        '33','Drop Link',

        '36','Create RBS',

        '37','Alter RBS',

        '38','Drop RBS',

        '40','Alter TBS',

        '41','Drop TBS',

        '42','Alter Sess',

        '45','Rollback',

        '47','PL/SQL Exe',

        '62','Anal Table',

        '63','Anal Index',

        '85','Truncate') Command,

 substr(s.program, 1, 13) prg,

p.spid bb,

substr(

rtrim(w.event) || ': ' ||

rtrim(w.p1text,' ') || ' ' || to_char(w.p1) || ',' ||

rtrim(w.p2text,' ') || ' ' || to_char(w.p2) || ',' ||

rtrim(w.p3text,' ') || ' ' || to_char(w.p3),1,75) as waiting_on

from v$process p, v$session s, v$session_wait w

where

-- w.wait_time=0

w.sid = s.sid

and s.paddr = p.addr

and w.event not like '%pmon timer%'

and w.event not like '%rdbms ipc%'

and w.event not like '%smon timer%'

and w.event not like '%SQL*Net%'

and w.event not like '%lock manager wait for%'

and w.event not like '%slave wait%'

and w.event not like '%io done%'

and w.event not like '%pipe get%'

and w.event not like '%Streams AQ%'

order by 6,1

/