Techdata VINA 지식 공유

오라디버그는 데이터베이스 내부 정보를 얻을 때 유용하게 사용된다.

1. oradebug dump   

덤프 수행

[ Syntax ] 

oradebug dump  <dump_name> <level> [addr] 

[ Parameter ]

<dump_name>    

<level> 

[<addr>]   

SQL> oradebug dumplist  ( Oradebug 에서 덤프할 수 있는 Named Event)

EVENTS 

TRACE_BUFFER_ON 

TRACE_BUFFER_OFF 

HANGANALYZE 

LATCHES 

PROCESSSTATE 

SYSTEMSTATE 

INSTANTIATIONSTATE 

REFRESH_OS_STATS 

CROSSIC 

CONTEXTAREA 

HEAPDUMP 

HEAPDUMP_ADDR 

POKE_ADDRESS 

POKE_LENGTH ………. 

2.  oradebug  dump systemstate

시스템 상태 정보 덤프

EX)

SQL> oradebug setmypid 

Statement processed. 

SQL> oradebug unlimit 

Statement processed. 

SQL> oradebug setinst all 

Statement processed. 

SQL> oradebug -g def dump systemstate 10 

Statement processed. 

3. oradebug  dump processstate  

프로세스 상태 정보 덤프

EX)

SQL> oradebug setmypid 

Statement processed. 

SQL> oradebug unlimit 

Statement processed. 

SQL> oradebug setinst all 

Statement processed. 

SQL> oradebug -g def dump systemstate 10 

Statement processed. 

4. oradebug  dump errorstack 

error stack 덤프

Dump Level                        Dump Contains 

0                              dump error buffer 

1                              level 0 + call stack 

2                              level 1 + process state objects 

3                              level 2 + context area 

EX)

SQL> oradebug setospid 13446 

Oracle pid: 12, Unix process pid: 13446, image: oracle@apollo (TNS V1-V3) 

SQL> oradebug unlimit 

Statement processed. 

SQL> oradebug dump errorstack 3 

Statement processed. 

5. oradebug dump controlf

데이터파일과 콘트롤파일 헤더를 덤프하는 것으로 리커버리나 데이터베이스 오픈시 파일들 상태가 일치하지 않는다는 

ORA-1113, ORA-376 등의 에러가 발생하는 경우 CONTROLF 와 FILE_HDRS 를 사용하여 각 파일의 헤더를 덤프하여 파일들의 상태와 업데이트 시간을 비교

Dump Level                                  Dump Contains 

 1                                      only the file header 

 2                                      just the file header, the database info record, and checkpoint progress records  

 3                                      all record types, but just the earliest and latest records for circular reuse record types 

 4                                      as above, but includes the 4 most recent records for circular reuse record types 

 5+                                    as above, but the number of circular reuse records included doubles with each level 

6. Memory Dumps 

버퍼캐시 덤프 수행

Dump Level                            Dump Contains 

1                                      dump the buffer headers only   

2                                      include the cache and transaction headers from each block   

3                                      include a full dump of each block   

4                                      dump the working set lists and the buffer headers and the cache header for each block  

5                                      include the transaction header from each block   

6                                      include a full dump of each block   

7. oradebug dump library_cache

라이브러리 캐시 통계 덤프

Dump Level                            Dump Contains 

1                                      dump libracy cache statistics 

2                                             also include a hash table  

3                                      level 2 + dump of the library  object handles 

4                                      Level 3 + dump of the heap 

 EX) 

 SQL> oradebug setmypid 

Statement processed. 

 SQL> oradebug dump library_cache 2 

Statement processed. 

8. oradebug dump heapdump 

 특정 오라클 프로세스의 메모리가 지속적으로 증가만하거나 한번 증가한 후 줄어들지 않는 경우 메모리 정보를 덤프하여, 

 어느 메모리 세그먼트 부분이 많이 차지하는지 분석하기 위해 HEAPDUMP 를 수행

Dump Level               Dump Contains 

1                      include PGA heap 

2                      include Shared Pool 

4                      include UGA heap  

8                      include CGA heap 

16                     include Top CGA 

32                     include Large Pool 

9. oradebug dump heapdump_addr

Dump Level          Dump Contains 

1                  dump structure 

2                  also include contents 

EX)

SQL> oradebug setmypid 

Statement processed. 

SQL> oradebug unlimit 

Statement processed. 

SQL> oradebug dump heapdump 2 

Statement processed. 

------------------      part of the trace file       -------------------- 

*** SESSION ID:(7.14111) 2003-09-03 13:01:21.412 

****************************************************** 

HEAP DUMP heap name="sga heap"  desc=0x80000030 

extent sz=0xfc4 alt=48 het=32767 rec=9 flg=2 opc=0 

parent=0 owner=0 nex=0 xsz=0x3d2bdf4 

EXTENT 0 

 Chunk b5815ff8 sz= 41699524    perm      "perm           "  alo=19395672 

 Chunk b7fda8bc sz=   887380    free      "               " 

 Chunk b80b3310 sz=      560    freeable  "library cache  "  ds=b80b5a0c 

 Chunk b80b3540 sz=     2588    freeable  "sql area         "  ds=b80b5898 

 Chunk b80b3f5c sz=      732    freeable  "sql area           "  ds=b80ba69c 

 Open the trace file and look for the ds (descriptor  address).Convert hex to decimal and do the 

dump. 

SQL> oradebug dump heapdump_addr 1 3087751692 

--------------------------- part of the trace file generated using command above ------------- 

Statement processed. 

*** 2003-09-03 13:12:35.330 

*** SESSION ID:(8.20679) 2003-09-03 13:12:35.322 

HEAP DUMP heap name="library cache"  desc=0xb80b5a0c 

extent sz=0x224 alt=32767 het=8 rec=9 flg=2 opc=0 

parent=80000030 owner=b80b5804 nex=0 xsz=0x224 

EXTENT 0 

 Chunk b80b3324 sz=      464    perm      "perm           "  alo=176 

 Chunk b80b34f4 sz=       76    freeable  "kgltbtab       " 

EXTENT 1 

 Chunk b80b55b4 sz=      500    perm      "perm           "  alo=500 

 Chunk b80b57a8 sz=       40    free      "               " 

EXTENT 2 

 Chunk b80b57f4 sz=      244    perm      "perm           "  alo=244 

 Chunk b80b58e8 sz=       52    free      "               " 

 Chunk b80b591c sz=       76    freeable  "kgltbtab       " 

 Chunk b80b5968 sz=       76    freeable  "kgltbtab       " 

10 . INDEX DUMP

oradebug dump treedump

인덱스 구조를 덤프 

EX)

select  object_id  from sys.dba_objects 

where owner = upper('&Owner') and 

object_name = upper('&IndexName'); 

SQL> oradebug setmypid 

Statement processed. 

SQL> oradebug dump treedump 40 

Statement processed.