꿈꾸는 개발자, DBA 커뮤니티 구루비

oradebug

: oradebug 는 말 그대로 Oracle 을 디버깅 하는 용도로 고안된 툴

프로세스 바인딩 : 특정 서버 프로세스에 바인딩을 해야 함.
SYSDBA 권한 필요
바인딩 대상 :
1. 서버 프로세스 ( 1. OS 프로세스 아이디(V$PROCESS.SPID)
2. 오라클 프로세스 아이디((V$PROCESS.PID) )
3. 백그라운드 프로세스
진단 이벤트 : 특정 이벤트에 대한 활성화 / 비활성화 ex) ORA-04030
11g 특성 : 보다 직관적인 명령어,
기타 : 파일명(tracfile_name), 파일 사이즈(unlimit), 덤프 목록(dumplist)

oradebug 기본



EX )

SQL> show user
USER is "SYS"

SQL> col sid new_value sid
SQL>  select distinct sid from v$mystat where rownum < 2 ;

       SID
----------
        30



SQL> col spid new_value ospid
SQL> col pid new_value orapid
SQL> select pid, spid
  2  from v$process
  3  where addr = ( select paddr from v$session where sid = &sid ) ;
old   3: where addr = ( select paddr from v$session where sid = &sid )
new   3: where addr = ( select paddr from v$session where sid =         30 )

       PID SPID
---------- ------------------------------------------------------------------------
        19 8625



SQL> oradebug setospid &ospid
Oracle pid: 19, Unix process pid: 8625, image: oracle@IIG.com (TNS V1-V3)


SQL> oradebug setorapid &orapid
Oracle pid: 19, Unix process pid: 8625, image: oracle@IIG.com (TNS V1-V3)


SQL> oradebug setmypid
Statement processed.



SQL> oradebug setorapname smon
Oracle pid: 13, Unix process pid: 7972, image: oracle@IIG.com (SMON)


SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/iignf/IIGNF/trace/IIGNF_ora_8625.trc


SQL> oradebug unlimit
Statement processed.

oradebug dumplist



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

ordebug 기타




SQL> show user
USER is "SYS"


SQL> oradebug setmypid
Statement processed.


SQL> oradebug dump heapdump 1
Statement processed.


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

Session altered.

Elapsed: 00:00:00.05

SQL> oradebug dump processstate 266
Statement processed

ordebug 진단 이벤트




SQL> oradebug event 10046 trace name context forever, level 12 ;
Statement processed.
SQL> oradebug event 10046 trace name context off ;
Statement processed.
==

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.00
SQL> alter session set events '10046 trace name context off';

Session altered.



SQL> oradebug setmypid
Statement processed.

SQL> oradebug event sql_trace wait=true, bind=true ;
Statement processed.
SQL> oradebug event sql_trace off ;
Statement processed.



SQL> col sql_id new_value sql_id
SQL> select sql_id from v$sqlarea where rownum = 1 ;

SQL_ID
---------------------------------------
1fkh93md0802n

Elapsed: 00:00:00.03


SQL> oradebug setmypid
Statement processed.


SQL> oradebug event sql_trace [sql:&sql_id] wait=true ;
Statement processed.
SQL> oradebug event sql_trace [sql:&sql_id] off ;
Statement processed.


SQL> define sql_id2 = &sql_id
SQL> oradebug event sql_trace [sql:&sql_id|&sql_id2] wait=true ;
Statement processed.
SQL> define
DEFINE _DATE           = "29-FEB-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "IIGNF" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)
DEFINE SQL_ID          = "1fkh93md0802n" (CHAR)
DEFINE SQL_ID2         = "1fkh93md0802n" (CHAR)




SQL> oradebug event 4030 trace name heapdump level 0x20000001
Statement processed.
SQL> oradebug event 4030 trace name context off
Statement processed.


SQL> alter system set events '4030 trace name heapdump level 0x20000001';

System altered.

SQL> alter system set events '4030 trace name context off';

System altered.

hanganalyze 샘플


cat 01_hanganalyze_lib.sh 
ORACLE_HOME=/app/oracle/product/10.2.0; export ORACLE_HOME
echo "====================================== hanganalyze ====================================="
#sqlplus -s -prelim "/as sysdba"<<EOF 
sqlplus -s "/as sysdba"<<EOF
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
oradebug dump systemstate 10
!echo " >>> Wait !!! First HANGANALYZE has been taken and next one is after 30 seconds";sleep 30
oradebug hanganalyze 3
oradebug dump systemstate 10
!echo " >>> Wait !!! First HANGANALYZE has been taken and next one is after 30 seconds";sleep 30
oradebug hanganalyze 3
oradebug dump systemstate 10
EOF
echo " >>>>>>> Completed !!!! <<<<<< "

만일 SQL*PLUS 를 통해 SYSDBA 유저로 로그인 하는 것조차 불가능할 정도의 상황이라면 어떻게 해야 할까요 ?
예비 접속(Preliminary Connection) 을 사용하면 됩니다.
예비 접속이란 오라클 데이터베이스에 직접 접속하지는 않지만, 일부 제한된 명령은 사용 가능한 접속 형태를 말합니다.
정상적인 접속이 불가능한 비상상황에서 SYSTEMSTATE 덤프와 같이 데이터를 수집하기 위한 용도록 제공됩니다.

oradebug (by darkturtle) [2012.02.29]

oradebug

oradebug 기본

oradebug dumplist

ordebug 기타

ordebug 진단 이벤트

hanganalyze 샘플