꿈꾸는 개발자, 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 덤프와 같이 데이터를 수집하기 위한 용도록 제공됩니다.