oracle 접속 잘 안될때 system state dump 뜨기

 
oracle 운영 중 DB hang 이나 slowdown(거의 hang) 현상이 발생하면, 사실 거의 할 수 있는 일이 없다. monitoring이나 trace 등의 데이터는 resource 부족으로 수행이 잘 안되며, 운영 부서에서 빨리 문제 해결해 달라고 엄청 push 하기 마련이다. 대부분 resource 문제는 database restart하면 풀린다. 물론 recovery 등의 작업은 다시 올리면 또 돌긴하지만 이런 작업은 심각한 hang이나 slowdown 현상을 대개 보이지는 않는다.

암튼 조치 후에 문제 분석을 위해서는 반드시 system state dump를 수행해야 한다. 요게 없으면 문제에 대한 기본적인 분석이 어려워 어떤때는 현상 파악 자체도 잘 안되는 경우가 있다. (10g에서는 active session history나 AWR 등의 데이터로 인해 좀 나아졌다)

system state dump는 몇분 주기로 여러번 수행해야 특정 resource의 변동사항을 유추해 낼 수 있다.
일반적으로 system state dump는 두가지의 방법으로 수행된다.

1.  alter session set events 'immediate trace name SYSTEMSTATE level 10';

2.  $ sqlplus (svrmgrl)
      connect internal
      oradebug setospid <process ID>
      oradebug unlimit
      oradebug dump systemstate 10

드물지만 어떤 경우엔 database로의 접근 자체가 안되는 경우가 발생한다(max process 등..). 이럴 경우 당연히 alter session이나 oradebug를 이용해 system dump 수행 자체가 안된다.

이러한 상황에서 system dump state dump를 수행할 수 있는 방법이 있는데, OS의 debug tool을 이용해 직접 dump를 수행하는 function call을 수행하는 방법과 10g에서 소개된 sqlplus의 -prelim option을 사용하는 방법이 그것이다.

OS debuger를 이용해 function call을 하는 경우 조심할 점은 attach 한 process가 terminated 될 수 있으므로 가급적 oracle background process에 대한 수행은 안하는 게 좋다.

dbx -a PID (where PID = any oracle shadow process)
dbx() print ksudss(10)
...return value printed here
dbx() detach

(saki) % dbx -a 28348
Waiting to attach to process 28348 ...
Successfully attached to oracle.
warning: Directory containing oracle could not be determined.
Apply 'use' command to initialize source path.

Type 'help' for help.
reading symbolic information ...
stopped in read at 0xd016fdf0
0xd016fdf0 (read+0x114) 80410014        lwz   r2,0x14(r1)
(dbx) print ksudss(10)
2
(dbx) detach

oracle 10g를 사용하는 경우 사용할 수 있는 option이 하나 있는데, sqlplus의 prelim option이다.
이 방법은 기존의 oradebug를 사용하는 방법과 동일하며 단지 sqlplus 수행시 option으로 지정하면 된다.
 
prelim option에 관한 내용은 10g, 11g sqlplus manual에도 나와 있지 않으므로.. 생략 !!

sqlplus -prelim / as sysdba

export ORACLE_SID=PROD                                 ## Replace PROD with the SID you want to trace
sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 10

참고한 자료들 입니다.

Note 121779.1 - Taking Systemstate Dumps when You cannot Connect to Oracle
http://oraclue.com/2008/09/25/sqlplus-preliminary-connection/
http://yaping123.wordpress.com/2008/08/30/sqlplus-using-prelim-connection/

2009년 8월 26일 추가.
sqlplus의 relim option 관련해서 참고할 만한 블로그 입니다.

oradeblog : SQL*Plus 'prelim' connection

2009년 10월 09일 추가
-relim option은 정상적인 세션이 아니기 때문에 systemstate dump 명령이 수행이 되더라도 dump가 끝까지 떨어졌는지 확인이 필요합니다. trace file을 여셔서 맨 끝에 "end of system state dump(?)"라고 되어 있는지 확인해 보세요.