systemstate dump 와 hang analyze 수행

+ systemstate dump 및 hang analyze 뜨는 법

sqlplus '/as sysdba

oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266

oradebug hanganalyze 3
-- wait 60 sec
oradebug dump systemstate 266

oradebug hanganalyze 3
-- wait 60 sec
oradebug dump systemstate 266

oradebug hanganalyze 3

+ 10046 trace 수집 절차

oradebug setmypid

oradebug event 10046 trace name context forever, level 12
-- run query

oradebug event 10046 trace name context off;

+ SQLPLUS 접속 안될 경우 direct connection 방법

sqlplus -prelim / as sysdba
oradebug setmypid
<.. 이하 dump 수행 ..>

+ process state 및 errorstack 수집

oradebug setospid
oradebug unlimit
oradebug dump processstate 10
oradebug dump errorstack 3
<wait 3-4min.>
oradebug dump processstate 10
oradebug dump errorstack 3
<wait 3-4min.>
oradebug dump processstate 10
oradebug dump errorstack 3

+ 4031 Heap dump 수집

(주의 : 4031 heap dump 는 운영 중 수행할 경우에 수집 시간 동안 DB hang 이 걸릴 수 있음)

Ssqlplus /nolog
connect / as sysdba
alter system set events '10235 level 65536';
exit

Then wait a while for the ORA-4031

(or at least the memory allocations) to occur, and then execute:

sqlplus /nolog
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump heapdump 536870914
oradebug tracefile_name
oradebug close_trace

once done you can disable the event using

alter system set events '10235 off';

+ 4031 Heap dump 수집

alter session set TRACEFILE_IDENTIFIER ='ORA4030_trace';
alter session set events '4030 trace name heapdump level 536870917 ;name errorstack level 3' ;
--Run SQL--
alter session set events '4030 trace name errorstack off'
alter session set events '4030 trace name heapdump off'

+ 클라이언트 단 TNS trace 설정 방법

클라이언트 단 sqlnet.ora file 에 아래 추가

TRACE_LEVEL_client=16
TRACE_FILE_client=client
TRACE_DIRECTORY_client=c:\temp /* trace 파일이 생성될 폴더 이름 */
TRACE_UNIQUE_client=YES
TRACE_TIMESTAMP_client=ON

+ OS stack trace 수집

Sun Solaris truss, e.g.:
$ truss -adefo <output file> <executable>

For a running process:
$ truss -o <output file> -dealf -p <OS PID> For a program to be started: $ truss -o <output file> -dealf <command-line>

HP/UX tusc, eg :
$ tusc -afpo <output file> <pid> <executable>
or
$ truss -d -o <outfile> -p <pid>

For a running process:
$ tusc -o <output file> -eanfulp -T "%Y-%m-%d %H:%M:%S" <OS PID>
$ tusc -o tusc.out -enafulp -T hires <OS PID>

For a program to be started:
$ tusc -o <output file> -eanfulp -T "%Y-%m-%d %H:%M:%S" <command-line>

IBM AIX truss, e.g.:
$ truss -aefo <output file> <executable>
$ truss -aefo listener.log -p PID

Linux strace, eg :
$ strace -fo <output file> <executable>

Take pstack:

AIX : /bin/procstack <pid-of-crsd.bin>
Linux : /usr/bin/pstack <pid-of-crsd.bin>
Solaris : /usr/bin/pstack <pid-of-crsd.bin>