1. HOME
  2. [종료]구루비 DB 스터디
  3. 2015년 하반기 - 오라클 데이터베이스 스터디
  4. 덤프 및 디버깅

덤프 및 디버깅 (by openlsw) [2015.12.19]

oradebug

  • 다른 세션에 영향을 줄 때 쓰는 유틸리티
  • sysdba 권한 필요, 대부분 이벤트 설정으로 대체 가능

프로세스 서스펜딩

  • DBWR, LGWR 상세 분석 시 프로세스 STOP 필요 (suspend, resume)
  • Recursive SQL 에 의한 세션 hang 가능 (sys.aud$)
  • Deadlock 의 pmon 에 의한 감지 여부 증명 가능 (pmon 이 아님)

-- 오라클 프로세스 Attach (v$process.pid)
-- 세션1
SQL> select prc.pid from v$bgprocess bgp, v$process prc where bgp.name = 'LGWR' and prc.addr = bgp.paddr;

       PID
----------
        11

SQL> oradebug setorapid 11
Oracle pid: 11, Unix process pid: 17642, image: oracle@test11g.oracle.com (LGWR)
SQL> oradebug suspend
Statement processed.

-- Thu Dec 17 02:28:51 2015
-- Unix process pid: 17642, image: oracle@test11g.oracle.com (LGWR) flash frozen [ command #1 ]

-- 세션2
create table emp (empno number);
-- HANG / LGWR 대기 발생/ log file sync) 
-- insert into emp values (1);
-- commit;

-- 세션1
SQL> select event from v$session where sid = 72;

EVENT
----------------------------------------------------------------
log file sync

SQL> oradebug resume
Statement processed.

-- Thu Dec 17 02:32:47 2015
-- Unix process pid: 17642, image: oracle@test11g.oracle.com (LGWR) resumed

-- 세션2
SQL> create table emp (empno number);

Table created.

Elapsed: 00:00:34.04

덤프

  • 메모리 구조를 trace file 로 만듦

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/home/oracle/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_19502.trc
SQL> alter session set tracefile_identifier = xxx;

Session altered.

SQL> oradebug tracefile_name
/home/oracle/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_19502_XXX.trc

SQL> oradebug dumplist
TRACE_BUFFER_ON
TRACE_BUFFER_OFF
LATCHES
PROCESSSTATE
SYSTEMSTATE
INSTANTIATIONSTATE
REFRESH_OS_STATS
CROSSIC
CONTEXTAREA
HANGDIAG_HEADER
HEAPDUMP
HEAPDUMP_ADDR
POKE_ADDRESS
POKE_LENGTH
POKE_VALUE
POKE_VALUE0
GLOBAL_AREA
REALFREEDUMP
FLUSH_JAVA_POOL
POOL_SIMULATOR
PGA_DETAIL_GET
PGA_DETAIL_DUMP
PGA_DETAIL_CANCEL
PGA_SUMMARY
MODIFIED_PARAMETERS
EVENT_TSM_TEST
ERRORSTACK
CALLSTACK
TEST_STACK_DUMP
TEST_GET_CALLER
RECORD_CALLSTACK
EXCEPTION_DUMP
BG_MESSAGES
ENQUEUES
KSTDUMPCURPROC
KSTDUMPALLPROCS
KSTDUMPALLPROCS_CLUSTER
SIMULATE_EOV
KSFQP_LIMIT
KSKDUMPTRACE
DBSCHEDULER
LDAP_USER_DUMP
LDAP_KERNEL_DUMP
DUMP_ALL_OBJSTATS
DUMPGLOBALDATA
HANGANALYZE
HANGANALYZE_PROC
HANGANALYZE_GLOBAL
GES_STATE
OCR
CSS
CRS
SYSTEMSTATE_GLOBAL
GIPC
MMAN_ALLOC_MEMORY
MMAN_CREATE_DEF_REQUEST
MMAN_CREATE_IMM_REQUEST
MMAN_IMM_REQUEST
DUMP_ALL_COMP_GRANULE_ADDRS
DUMP_ALL_COMP_GRANULES
DUMP_ALL_REQS
DUMP_TRANSFER_OPS
DUMP_ADV_SNAPSHOTS
ADJUST_SCN
NEXT_SCN_WRAP
CONTROLF
FLUSH_CACHE
FULL_DUMPS
BUFFERS
RECOVERY
SET_TSN_P1
BUFFER
PIN_BLOCKS
BC_SANITY_CHECK
PIN_RANDOM_BLOCKS
SET_NBLOCKS
CHECK_ROREUSE_SANITY
DUMP_PINNED_BUFFER_HISTORY
KCBO_OBJ_CHECK_DUMP
KCB_WORKING_SET_DUMP
REDOLOGS
ARCHIVE_ERROR
LOGHIST
REDOHDR
LOGERROR
OPEN_FILES
DATA_ERR_ON
DATA_READ_ERR_ON
DATA_ERR_OFF
BLK0_FMTCHG
UPDATE_BLOCK0_FORMAT
TR_SET_BLOCK
TR_SET_ALL_BLOCKS
TR_SET_SIDE
TR_CRASH_AFTER_WRITE
TR_READ_ONE_SIDE
TR_CORRUPT_ONE_SIDE
TR_RESET_NORMAL
TEST_DB_ROBUSTNESS
LOCKS
GC_ELEMENTS
FILE_HDRS
KRB_CORRUPT_INTERVAL
KRB_CORRUPT_SIZE
KRB_CORRUPT_REPEAT
KRB_CORRUPT_OFFSET
KRB_PIECE_FAIL
KRB_OPTIONS
KRB_FAIL_INPUT_FILENO
KRB_SIMULATE_NODE_AFFINITY
KRB_TRACE
KRB_BSET_DAYS
KRB_SET_TIME_SWITCH
KRB_OVERWRITE_ACTION
KRB_CORRUPT_SPHEADER_INTERVAL
KRB_CORRUPT_SPHEADER_REPEAT
KRB_CORRUPT_SPBITMAP_INTERVAL
KRB_CORRUPT_SPBITMAP_REPEAT
KRB_CORRUPT_SPBAD_INTERVAL
KRB_CORRUPT_SPBAD_REPEAT
KRB_UNUSED_OPTION
KRBMRSR_LIMIT
KRBMROR_LIMIT
KRBABR_TRACE
KRDRSBF
KRC_TRACE
KRA_OPTIONS
KRA_TRACE
FBTAIL
FBINC
FBHDR
FLASHBACK_GEN
KTPR_DEBUG
DUMP_TEMP
DROP_SEGMENTS
TEST_SPACEBG
TREEDUMP
LONGF_CREATE
KDLIDMP
ROW_CACHE
LIBRARY_CACHE
LIBRARY_CACHE_OBJECT
CURSORDUMP
CURSORTRACE
CURSOR_STATS
XS_SESSION_STATE
SHARED_SERVER_STATE
LISTENER_REGISTRATION
JAVAINFO
KXFPCLEARSTATS
KXFPDUMPTRACE
KXFPBLATCHTEST
KXFXSLAVESTATE
KXFXCURSORSTATE
KXFRHASHMAP
WORKAREATAB_DUMP
KUPPLATCHTEST
OBJECT_CACHE
SAVEPOINTS
RULESETDUMP
RULESETDUMP_ADDR
FAILOVER
OLAP_DUMP
SELFTESTASM
ASMDISK_ERR_ON
ASMDISK_READ_ERR_ON
ASMDISK_ERR_OFF
IOERREMUL
IOERREMULRNG
ALRT_TEST
AWR_TEST
AWR_FLUSH_TABLE_ON
AWR_FLUSH_TABLE_OFF
ASHDUMP
MMON_TEST
ATSK_TEST
HM_FW_TRACE
HM_FDG_VERS
IR_FW_TRACE
KSDTRADV_TEST
  • 주요 oradebug 덤프 명령어 모음
옵션수행결과
BUFFERS버퍼, 버퍼 헤더 및 다양한 링크드 리스트에 대한 정보
ENQUEUES리소스와 enqueue 에 대한 정보(레벨 3)
FILE_HDRS데이터 파일 헤더
REDOHDR온라인 리두 로그 파일의 헤더
CONTROLF컨트롤 파일의 정보
LIBRARY_CACHE라이브러리 캐시 정보
LIBRARY_CACHE_OBJECTLCO 상세 내용 덤프(11.2)
HEADDUMPTOP-LEVEL 힙 정보 덤프(PGA, SGA, UGA)

메모리 덤프

  • 특정한 메모리 위치에 대한 정보 조사
  • 이름 확인 후 메모리 덤프(dumpvar {영역} {변수명})
  • SGA 내 kcbnhb 변수 (kcb:버퍼캐시, nhb:해시버킷수)

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kcbnhb
uword kcbnhb_ [06001A330, 06001A334) = 00008000
  • 현재 세션의 use_stored_outlines : ugauso_p

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar uga ugauso_p
qolprm ugauso_p [7F21F21C6CC0, 7F21F21C6CE4) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

SQL> alter session set use_stored_outlines = rule_based;

Session altered.

SQL> oradebug dumpvar uga ugauso_p
qolprm ugauso_p [7FE9FE4C6CC0, 7FE9FE4C6CE4) = 00000001 5552000A 425F454C 44455341 00000000 00000000 00000000 00000000 00000000
-- ASCII :                                              U R      B _ E L  D E S A
  • SGA 내 변수 목록 : x$ksmfsv (kernel services - memory - fixed SGA variables)
  • 변수의 시작 주소 정보 있음, ksmfsnam 컬럼 값 맨뒤의 _ 는 제거

col indx format 9999
col inst_id format 999
col ksmfsnam format a15
col ksmfstyp format a10
col ksmfsadr format a16
col ksmfssiz format 999

SQL> select addr, indx, inst_id, ksmfsnam, ksmfstyp, ksmfsadr, ksmfssiz from x$ksmfsv where ksmfsnam like 'kcbn%';

ADDR             INDX    INST_ID KSMFSNAM        KSMFSTYP   KSMFSADR         KSMFSSIZ
---------------- ---- ---------- --------------- ---------- ---------------- --------
000000000880ED80  2962       1 kcbnbh_         sword      0000000060018460        4
000000000880EDC0  2964       1 kcbnwp_         sword      0000000060018470        4
0000000008812000  3366       1 kcbnbf_         sword      000000006001A328        4
0000000008812020  3367       1 kcbnhb_         uword      000000006001A330        4
0000000008812040  3368       1 kcbnhbsft_      uword      000000006001A338        4
0000000008812100  3374       1 kcbnhl_         uword      000000006001A368        4
00000000088123C0  3396       1 kcbnpg_         ub4        000000006001A460        4
0000000008812460  3401       1 kcbnl2b_        uword      000000006001A488        4
0000000008812540  3408       1 kcbnchkl_       ub1        000000006001A4C0        1
0000000008812560  3409       1 kcbnchk_        boolean    000000006001A4C8        4
0000000008812680  3418       1 kcbnumaactive_  ub1        000000006001A588        1
0000000008814DC0  3732       1 kcbnf01_        uword      000000006001B388        4
0000000008814DE0  3733       1 kcbnf02_        uword      000000006001B390        4

13 rows selected.
  • oradebug dump global_area N : 1:PGA, 2:SGA, 4:UGA

SQL> oradebug dump global_area 2
Statement processed.
SQL> oradebug tracefile_name
/home/oracle/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_21502.trc

[oracle@test11g trace]$ cat ORCL_ora_21502.trc | grep kcbn
sword kcbnbh_ [060018460, 060018464) = 0000398A
sword kcbnwp_ [060018470, 060018474) = 00000001
sword kcbnbf_ [06001A328, 06001A32C) = 000002EE
uword kcbnhb_ [06001A330, 06001A334) = 00008000
uword kcbnhbsft_ [06001A338, 06001A33C) = 00000011
uword kcbnhl_ [06001A368, 06001A36C) = 00000400
ub4 kcbnpg_ [06001A460, 06001A464) = 00000001
uword kcbnl2b_ [06001A488, 06001A48C) = 00000000
ub1 kcbnchkl_ [06001A4C0, 06001A4C4) = 00000000
boolean kcbnchk_ [06001A4C8, 06001A4CC) = 00000001
ub1 kcbnumaactive_ [06001A588, 06001A58C) = 00000001
uword kcbnf01_ [06001B388, 06001B38C) = 00000736
uword kcbnf02_ [06001B390, 06001B394) = 00000737
  • 해시버킷(kcbnhb) 개수 확인

SQL> oradebug setmypid
Statement processed.

-- dumpvar
SQL> oradebug dumpvar sga kcbnhb
uword kcbnhb_ [06001A330, 06001A334) = 00008000

-- peek
SQL> oradebug peek 0x6001A330 4
[06001A330, 06001A334) = 00008000

-- x$ksmmem (SGA 의 메모리 맵을 간접적으로 나타냄)
SQL> select * from x$ksmmem where addr = hextoraw('6001A330');
select * from x$ksmmem where addr = hextoraw('6001A330')
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 21502
Session ID: 192 Serial number: 171
  • 퍼블릭 리두 스레드에 대한 상세 내용 추출

SQL> select first_buf_kcrfa			-- 첫번째 버퍼 주소
          , last_buf_kcrfa			-- 마지막 버퍼 주소
          , pnext_buf_kcrfa_cln		-- 다음번 사용될 버퍼 주소
          , next_buf_num_kcrfa_cln
       from x$kcrfstrand
      where last_buf_written_kcrfa != '00'
      order by indx;  2    3    4    5    6    7

FIRST_BUF_KCRFA  LAST_BUF_KCRFA   PNEXT_BUF_KCRFA_ NEXT_BUF_NUM_KCRFA_CLN
---------------- ---------------- ---------------- ----------------------
000000006021D000 00000000605FCE00 00000000604DCA00                   5628
00000000605FD000 00000000609DCE00 0000000060606C00                     77

-- oradebug 활용하여 로그 버퍼 내용 확인 (로그 버퍼 페이지 512 byte / 0x200)
SQL> oradebug peek 0x604DCA00 16
[0604DCA00, 0604DCA10) = 00002201 0000924D 00000012 C3858010
SQL> oradebug peek 0x604DCC00 16
[0604DCC00, 0604DCC10) = 00002201 0000924E 00000012 13628010
SQL> oradebug peek 0x604DCE00 16
[0604DCE00, 0604DCE10) = 00002201 0000924F 00000012 D8808010
-- VAL2 : 로그 파일 내 BLOCK ID
-- VAL3 : 00000012 : v$log.status = 'CURRENT' 의 v$log.sequence#

SQL> select group#, sequence#, status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1         16 INACTIVE
         2         17 INACTIVE
         3         18 CURRENT

SQL을 이용한 덤프방법

oradebug 대체

  • 주석 : dbms_system.ksdwrt (1:TRC, 2:ALERT.LOG, 3:둘다)
  • oradebug 에서만 정상적인 덤프가 가능한 경우가 있음 (예: 개별 LCO 덤프 / LIBRARY_CACHE)

SQL> alter session set tracefile_identifier = 'alter_session';

Session altered.

SQL> execute dbms_system.ksdwrt(1, 'Example using alter session');

PL/SQL procedure successfully completed.

SQL> alter session set events 'immediate trace name buffers level 1';

Session altered.
  • dbms_system.set_ev()

SQL> @$ORACLE_HOME/rdbms/admin/dbmssupp.sql

Package created.


Package body created.

SQL> alter session set tracefile_identifier = 'dbms_system';

Session altered.

SQL> execute dbms_system.ksdwrt(1, 'Example using dbms_system');

PL/SQL procedure successfully completed.

SQL> declare
        m_serial number(15, 0);
begin
        select serial# into m_serial from v$session where sid = dbms_support.mysid;
        dbms_system.set_ev(si => dbms_support.mysid, se => m_serial, ev => 65536, le => 1, nm => 'buffers');
end;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

[oracle@test11g trace]$ cat ORCL_ora_21747_dbms_system.trc
Trace file /home/oracle/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_21747_dbms_system.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0
System name:    Linux
Node name:      test11g.oracle.com
Release:        2.6.32-300.39.1.el5uek
Version:        #1 SMP Wed Nov 7 18:13:56 PST 2012
Machine:        x86_64
Instance name: ORCL
Redo thread mounted by this instance: 1
Oracle process number: 27
Unix process pid: 21747, image: oracle@test11g.oracle.com (TNS V1-V3)


*** 2015-12-17 07:00:15.541
*** SESSION ID:(192.195) 2015-12-17 07:00:15.541
*** CLIENT ID:() 2015-12-17 07:00:15.541
*** SERVICE NAME:(SYS$USERS) 2015-12-17 07:00:15.541
*** MODULE NAME:(sqlplus@test11g.oracle.com (TNS V1-V3)) 2015-12-17 07:00:15.541
*** ACTION NAME:() 2015-12-17 07:00:15.541

Example using dbms_system

*** 2015-12-17 07:00:30.441
Example using dbms_system

데이터 파일 블록


-- block dump
alter system dump datafile {file_no} block {block_no};
alter system dump datafile 4 block 129;
alter system dump datafile {file_no} block min {block_min} block max {block_max};
alter system dump datafile 4 block min 129 block max 133;

-- file_name
alter system dump datafile '{file_name}' block {block_no};
alter system dump datafile '{file_name}' block min {block_min} block max {block_max};

-- undo
alter system dump undo header '{segment_name}';

로그 파일

  • datafile_no 는 relative 가 아닌 absolute 파일 번호 임
  • opcode : 5.4 → commit record
  • layer : 5:TX관리, 10:인덱스, 11:테이블

alter system dump logfile '{file_name}';
alter system dump logfile '/home/oracle/app/oracle/oradata/ORCL/redo01.log';

-- MOS 1031381.6 / 다양한 옵션 존재 (block, object_id, transaction_id)
alter system dump logfile '{file_name}'
		scn min {first scn to dump}
		scn max {last scn to dump}
		time min {seconds since an oddly arbitrary point in time}
		time max {ditto}
		layer {integer}
opcode {integer}
		xid {usn} {slot} {sequence} -- 10g 이상
		objno {object_id}
		dba min {datafile_no} . {block_no} -- 10g 이상 . 제거
		dba max {datafile_no} . {block_no} -- 10g 이상 . 제거
		rba min {log file seq#} . {block_no} -- 10g 이상 . 제거
		rba max {log file seq#} . {block_no} -- 10g 이상 . 제거

-- 예제
alter system dump logfile '/home/oracle/app/oracle/oradata/ORCL/redo01.log'
dba min 11 . 10 dba max 11 . 10;

alter system dump logfile '/home/oracle/app/oracle/oradata/ORCL/redo01.log'
layer 10 opcode 11;
-- 이전 리프 블록에 대한 포인터 설정 (인덱스 리프 블록 플릿 발생) 동작만 덤프 (분석을 원하는 동작(opcode)만 분석 가능)
-- layer 9999 opcode 9999 덤프 : 전체 리두 로그 스캔 (정합성 입증)

추측

  • 트레이스 파일과 x$ 구조를 분석하기 위해 기이한 이름의 의미를 알 수 없을 때
  • MOS 175982.1 ORA-600 Lookup Error Categories
  • bug 에 대한 workaround 를 통해 동작 원리 확인
  • 인터넷/MOS 문서의 대부분은 가설, 추측 임 (좋은 정보는 날짜, 오라클 버전, 증명을 포함)
  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2015년 하반기 - 오라클 데이터베이스 스터디
  4. 덤프 및 디버깅