{"id":495,"date":"2015-10-30T09:05:49","date_gmt":"2015-10-30T09:05:49","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=495"},"modified":"2015-10-30T09:05:49","modified_gmt":"2015-10-30T09:05:49","slug":"oradebug-%ec%82%ac%ec%9a%a9%eb%b2%95-1","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=495","title":{"rendered":"ORADEBUG \uc0ac\uc6a9\ubc95 – 1"},"content":{"rendered":"

[oradebug \uc0ac\uc6a9\ubc95 – 1]<\/b><\/p>\n

ORADUBUG<\/span>\ub294 ORACLE\uc5d0\uc11c \uc81c\uacf5\ud558\ub294 \ub514\ubc84\uae45\uc744 \uc704\ud55c \ubaa9\uc801\uc73c\ub85c \ub9cc\ub4e4\uc5b4\uc9c4 \uc720\ud2f8\ub9ac\ud2f0\ub85c <\/p>\n

DB<\/span>\uac00 \uba48\ucd94\uac70\ub098 Hang <\/span>\uc0c1\ud0dc\uc77c \ub54c \ub610\ub294 ORA-600 <\/span>\uc5d0\ub7ec\uac00 \ubc1c\uc0dd\ud588\uc744 \uacbd\uc6b0, <\/p>\n

\ud2b9\uc815 \ud504\ub85c\uc138\uc2a4\uac00 \ub8e8\ud551\uc744 \ub3c4\ub294 \uac83 \ub610\ub294 \uba48\ucdb0\uc788\ub294 \uac83 \uac19\uc774 \ubcf4\uc77c \ub54c \uad00\ub9ac\uc790 \uad8c\ud55c\uc73c\ub85c \uc2e4\ud589\ud558\uc5ec \uc0ac\uc6a9\ud560 \uc218 \uc788\ub2e4.<\/span><\/p>\n

\uc989 \ud504\ub85c\uc138\uc2a4, \uc138\uc158, \uba54\ubaa8\ub9ac, SQL \ub4f1\uc758 \ub9ac\uc18c\uc2a4\ub97c \ub364\ud504\ud560 \uc218\uc788\uace0, OS \ub514\ubc84\uac70\ub4e4\uacfc \ube44\uc2b7\ud558\uac8c \uc624\ub77c\ud074 \ub808\ubca8\uc5d0\uc11c CALL\ub2e8\uc704 \ub514\ubc84\uae45 \uae30\ub2a5\uc744 \uc81c\uacf5\ud55c\ub2e4.<\/span><\/p>\n

\ub9ac\uc18c\uc2a4\ub97c \uac1c\ubcc4\ub85c \ub364\ud504\ud560 \uc218 \uc788\uc73c\uba70 \uc778\uc2a4\ud134\uc2a4\ub97c \ub364\ud504\ud560 \uc218 \uc788\uc73c\uba70 DB HANG \ub610\ub294 \ud06c\ub9ac\ud2f0\uceec \uc7a5\uc560 \ubd84\uc11d \uc2dc \uc0ac\uc6a9\ub41c\ub2e4. <\/p>\n

–      
\n<\/span><\/span>\ub2e4\ub978 \uc720\uc800 \uc138\uc158\uc73c\ub85c SQL Tracing <\/span>\uc124\uc815
\n\uac00\ub2a5<\/p>\n

–      
\n<\/span><\/span>\ud504\ub85c\uc138\uc2a4 \uc77c\uc2dc\uc911\ub2e8<\/p>\n

–      
\n<\/span><\/span>\uacf5\uc720\uba54\ubaa8\ub9ac\uc640 \uc138\ub9c8\ud3ec\uc5b4 \uc815\ubcf4\uac80\uc0c9<\/p>\n

–      
\n<\/span><\/span>\uc0c8\ub85c \uc0dd\uc131\ub41c \ud2b8\ub808\uc774\uc2a4\ud30c\uc77c closing<\/span><\/p>\n

–      
\n<\/span><\/span>\ub0b4\ubd80 \uad6c\uc870 \uc870\uc791 \ubc0f dump <\/span>\uae30\ub2a5<\/p>\n

–      
\n<\/span><\/span>\ud504\ub85c\uc138\uc2a4 \uc2dc\ub3d9<\/p>\n

Oradebug
\nCommands  <\/span><\/b><\/p>\n

–      
\n<\/span><\/span>Oradebug help<\/span>\ub97c \ud1b5\ud574 \ucee4\ub9e8\ub4dc\ub97c \uac80\uc0c9\ud558\uc5ec
\n\ud65c\uc6a9<\/p>\n\n\n\n
\n

SQL> oradubug help<\/span><\/p>\n

 <\/span><\/p>\n

<\/p>\n

<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/span><\/p>\n

1.    
\n<\/span><\/span>Oradebug setmypid<\/span><\/p>\n

\ud604\uc7ac
\n\ud504\ub85c\uc138\uc2a4\ub85c \ub514\ubc84\uadf8<\/p>\n<\/p>\n\n\n
<\/p>\n

SQL> oradebug setmypid<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/span><\/p>\n

2.    
\n<\/span><\/span>Oradebug setospid<\/span><\/p>\n

\ub514\ubc84\uadf8\ud560 \ud504\ub85c\uc138\uc2a4\uc758 OS Process id \uc124\uc815<\/p>\n<\/p>\n\n\n
<\/p>\n

SQL> oradebug setospid <pid><\/span><\/p>\n

 <\/span><\/p>\n

Ex) <\/span><\/p>\n

SQL> oradebug setospid 19592 <\/span><\/p>\n

Oracle pid: 18, Unix process pid: 19592, image:
\n oracle@apollo(TNS V1-V3)<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/span><\/p>\n

3.    
\n<\/span><\/span>Oradebug setospid<\/span><\/p>\n

\ub514\ubc84\uadf8\ud560 Oracle Pid \uc124\uc815<\/span><\/span><\/p>\n<\/p>\n\n\n
<\/p>\n

SQL> oradebug setorapid <orapid><\/span><\/p>\n

 <\/span><\/p>\n

Ex) <\/span><\/p>\n

SQL> oradebug setorapid 18  (use pid from the query above) <\/span><\/p>\n

Unix process pid: 19592, image: oracle@apollo (TNS
\n V1-V3) or  using force option  <\/span><\/p>\n

SQL> oradebug setorapid 18 force <\/span><\/p>\n

Statement processed.<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/span><\/p>\n

4.    
\n<\/span><\/span>Oradebug event<\/span><\/p>\n

\ud504\ub85c\uc138\uc2a4\uc758 Trace event \uc124\uc815<\/p>\n<\/p>\n\n\n
<\/p>\n

SQL> oradebug event <text><\/span><\/p>\n

 <\/span><\/p>\n

\uc774\ubca4\ud2b8\ub118\ubc84\ub294 $ORACLE_HOME\/rdbms\/mesg\/oraus.msg<\/span>\uc5d0\uc11c \ud655\uc778\uac00\ub2a5<\/p>\n

Ex) <\/span>\uc5d0\ubca4\ud2b8 \ub118\ubc84\uac00 10046<\/span>\uc77c \uacbd\uc6b0
\n \ub2e4\ub978 \uc138\uc158\uc5d0 \ub300\ud55c \ucd94\uc801\uc744 \uc704\ud574 PID<\/span>\uc640 OS PID<\/span>\ub97c
\n \uc54c\uace0 \uc788\uc5b4\uc57c\ud558\uba70 \uc774\ub294 v$process view<\/span>\uc5d0\uc11c \ucd94\uc801\ud560 \uc218 \uc788\ub2e4.<\/span><\/p>\n

 <\/span><\/p>\n

SQL> oradebug setospid 10929 <\/span><\/p>\n

Oracle pid: 91, Unix process pid: 10929, image:
\n oracleorcl <\/span><\/p>\n

SQLPLUS> oradebug EVENT 10046 trace name context
\n forever, level 12 <\/span><\/p>\n

Statement processed.<\/span><\/p>\n

 <\/span><\/p>\n<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
<\/p>\n

Trace Name<\/span><\/p>\n<\/td>\n

<\/p>\n

Level<\/span><\/p>\n<\/td>\n

<\/p>\n

Description<\/span><\/p>\n<\/td>\n<\/tr>\n

<\/p>\n

TRACE_ACALL<\/span><\/p>\n<\/td>\n

<\/p>\n

1<\/span><\/p>\n<\/td>\n

<\/p>\n

Trace all calls<\/span><\/p>\n<\/td>\n<\/tr>\n

<\/p>\n

TRACE_ECALL<\/span><\/p>\n<\/td>\n

<\/p>\n

2<\/span><\/p>\n<\/td>\n

<\/p>\n

Trace “enabled” calls<\/span><\/p>\n<\/td>\n<\/tr>\n

<\/p>\n

TRACE_AEXCP<\/span><\/p>\n<\/td>\n

<\/p>\n

4<\/span><\/p>\n<\/td>\n

<\/p>\n

Trace all exceptions<\/span><\/p>\n<\/td>\n<\/tr>\n

<\/p>\n

TRACE_EEXCP<\/span><\/p>\n<\/td>\n

<\/p>\n

8<\/span><\/p>\n<\/td>\n

<\/p>\n

Trace “enabled” exceptions<\/span><\/p>\n<\/td>\n<\/tr>\n

<\/p>\n

TRACE_CIRCULAR<\/span><\/p>\n<\/td>\n

<\/p>\n

16<\/span><\/p>\n<\/td>\n

<\/p>\n

Trace w\/ circular buffer<\/span><\/p>\n<\/td>\n<\/tr>\n

<\/p>\n

TRACE_BIND_VARS<\/span><\/p>\n<\/td>\n

<\/p>\n

32<\/span><\/p>\n<\/td>\n

<\/p>\n

Trace bind variables<\/span><\/p>\n<\/td>\n<\/tr>\n

<\/p>\n

Level<\/span><\/p>\n<\/td>\n

<\/p>\n

Description<\/span><\/p>\n<\/td>\n<\/tr>\n

<\/p>\n

17<\/span><\/p>\n<\/td>\n

<\/p>\n

Trace all calls, using the buffer<\/span><\/p>\n<\/td>\n<\/tr>\n

<\/p>\n

22<\/span><\/p>\n<\/td>\n

<\/p>\n

Trace enabled calls and all exceptions
\n using the buffer<\/span><\/p>\n<\/td>\n<\/tr>\n

<\/p>\n

32<\/span><\/p>\n<\/td>\n

<\/p>\n

Trace bind variables, without using
\n the buffer<\/span><\/p>\n<\/td>\n<\/tr>\n

<\/p>\n

53<\/span><\/p>\n<\/td>\n

<\/p>\n

Yields the maximum level of tracing,
\n using the buffer<\/span><\/p>\n<\/td>\n<\/tr>\n

<\/p>\n

37<\/span><\/p>\n<\/td>\n

<\/p>\n

Yields the maximum level of tracing,
\n without using the buffer<\/span><\/p>\n<\/td>\n<\/tr>\n

<\/p>\n

<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n


\n <\/tbody>\n<\/table>\n

<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/span><\/p>\n

5.    
\n<\/span><\/span>Oradebug Session event<\/span><\/p>\n

\uc138\uc158\uc758 Trace event \uc124\uc815<\/p>\n<\/p>\n\n\n
<\/p>\n

SQL> oradebug session_event <text><\/span><\/p>\n

 <\/span><\/p>\n

Ex)<\/span><\/p>\n

SQL>oradebug session_event 10046 trace name
\n context forever,level 12 <\/span><\/p>\n

Statement proceed. <\/span><\/p>\n

SQL > oradebug session_event 10046 trace name
\n context off <\/span><\/p>\n

Statement proceed.<\/span><\/p>\n

 <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/span><\/p>\n

6.    
\n<\/span><\/span>Oradebug dumpvar<\/span><\/p>\n

PGA\/UGA\/SGA\ub97c \ub364\ud504\ub610\ub294 \ud504\ub9b0\ud2b8<\/span><\/p>\n<\/p>\n\n\n
<\/p>\n

SQL> oradebug dumpvar <p|s|uga>
\n <name> [level]<\/span><\/p>\n

<p|s|uga>  
\n PGA,SGA or UGA <\/span><\/p>\n

<name>   
\n Variable name <\/span><\/p>\n

[level]     
\n Level<\/span><\/p>\n

 <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/span><\/p>\n

7.    
\n<\/span><\/span>Orade setvar<\/span><\/p>\n

PGA\/UGA\/SGA\ub97c \ubcc0\uacbd<\/span><\/p>\n<\/p>\n\n\n
<\/p>\n

SQL> oradebug setmypid <\/span><\/p>\n

Statement processed. <\/span><\/p>\n

 <\/span><\/p>\n

SQL> oradebug dumpvar sga
\n kcfdfk <\/span><\/p>\n

kfil kcfdfk_ [2000F6B0, 2000F6B4)
\n = 00000190<\/span><\/p>\n

 <\/span><\/p>\n

SQL> show parameter db_files;<\/span><\/p>\n

 <\/span><\/p>\n

NAME                                 TYPE          VALUE <\/span><\/p>\n

————————————
\n ———– ——————-<\/span><\/p>\n

db_files                               integer          200 <\/span><\/p>\n

 <\/span><\/p>\n

SQL> oradebug setvar sga kcfdfk
\n 200 <\/span><\/p>\n

BEFORE: [2000F6B0, 2000F6B4) =
\n 00000190 <\/span><\/p>\n

AFTER:  [2000F6B0, 2000F6B4)   = 000000C8<\/span><\/p>\n

 <\/span><\/p>\n

SQL> oradebug dumpvar sga
\n kcfdfk <\/span><\/p>\n

kfil kcfdfk_ [2000F6B0, 2000F6B4)
\n = 000000C8<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/span><\/p>\n

8.    
\n<\/span><\/span>Oradebug peek<\/span><\/p>\n

\uba54\ubaa8\ub9ac \uc8fc\uc18c\ub97c user dump \uc704\uce58\uc5d0 \ud2b8\ub808\uc774\uc2a4 \ud30c\uc77c\ub85c \ub364\ud504\uc2dc\ucf1c\uc8fc\ub294 \uba85\ub839\uc5b4<\/p>\n<\/p>\n\n\n
<\/p>\n

select fsv.KSMFSNAM,sga.* <\/span><\/p>\n

 from x$ksmfsv fsv, x$ksmmem
\n sga <\/span><\/p>\n

 where sga.addr=fsv.KSMFSADR
\n <\/span><\/p>\n

 and fsv.ksmfsnam like
\n ‘kgl%’ <\/span><\/p>\n

SQL> \/<\/span><\/p>\n

KSMFSNAM   ADDR      INDX   
\n INST_ID KSMMM   VAL<\/span><\/p>\n

—————————————- ———- ——–<\/span><\/p>\n

kglt1_       200150F8     21566         1   
\n 00000  11C<\/span><\/p>\n

kgllat_      20015F64     22489          1     00<\/span><\/p>\n

kglpnl_      2001602C     22539         1    
\n 00<\/span><\/p>\n

kglpal_      200160F4    22589           1     00<\/span><\/p>\n

kglllt_       200161BC     22639          1     00<\/span><\/p>\n

SQL> oradebug peek 536957176 4 <\/span><\/p>\n

[200150F8, 200150FC) = 0000011C<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/span><\/p>\n

9.    
\n<\/span><\/span>Oradebug poke<\/span><\/p>\n

\uba54\ubaa8\ub9ac \ubcc0\uacbd<\/span><\/p>\n<\/p>\n\n\n
<\/p>\n

SQL> oradebug help poke <\/span><\/p>\n

POKE           <addr>
\n <len> <value>      Modify
\n memory <\/span><\/p>\n

SQL> oradebug poke 536957176 4 668 <\/span><\/p>\n

BEFORE: [200150F8, 200150FC) = 0000011C <\/span><\/p>\n

AFTER:  [200150F8, 200150FC)
\n = 0000029C <\/span><\/p>\n

 <\/span><\/p>\n

 select fsv.KSMFSNAM,sga.* <\/span><\/p>\n

 from x$ksmfsv fsv, x$ksmmem
\n sga <\/span><\/p>\n

 where sga.addr=fsv.KSMFSADR
\n <\/span><\/p>\n

 and fsv.ksmfsnam like
\n ‘kgl%’ <\/span><\/p>\n

SQL> \/ <\/span><\/p>\n

 <\/span><\/p>\n

KSMFSNAM   ADDR          INDX    INST_ID     KSMMMVAL <\/span><\/p>\n

————————————— ——– ———-<\/span><\/p>\n

kglt1_      200150F8       21566           1     
\n 0000029C <\/span><\/p>\n

kgllat_      20015F64       22489          1       00 <\/span><\/p>\n

kglpnl_     2001602C     22539            1        00 <\/span><\/p>\n

kglpal_     200160F4      22589           1        00 <\/span><\/p>\n

kglllt_      200161BC     22639            1        00<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/span><\/p>\n

10.    
\n<\/span><\/span>Oradebug wakeup<\/span><\/p>\n

\ud504\ub85c\uc138\uc2a4 wake up\ud574\uc900\ub2e4.<\/span><\/p>\n<\/p>\n\n\n
<\/p>\n

SQL> select pid <\/span><\/p>\n

    from v$process p,
\n v$bgprocess b <\/span><\/p>\n

    where b.paddr = p.addr <\/span><\/p>\n

    and   name=’SMON’; <\/span><\/p>\n

  PID <\/span><\/p>\n

———- <\/span><\/p>\n

   6 <\/span><\/p>\n

 <\/span><\/p>\n

SQL> oradebug wakeup 6<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/p>\n

<\/span><\/p>\n

 <\/span><\/p>\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

[oradebug \uc0ac\uc6a9\ubc95 – 1] ORADUBUG\ub294 ORACLE\uc5d0\uc11c \uc81c\uacf5\ud558\ub294 \ub514\ubc84\uae45\uc744 \uc704\ud55c \ubaa9\uc801\uc73c\ub85c \ub9cc\ub4e4\uc5b4\uc9c4 \uc720\ud2f8\ub9ac\ud2f0\ub85c  DB\uac00 \uba48\ucd94\uac70\ub098 Hang \uc0c1\ud0dc\uc77c \ub54c \ub610\ub294 ORA-600 \uc5d0\ub7ec\uac00 \ubc1c\uc0dd\ud588\uc744 \uacbd\uc6b0,  \ud2b9\uc815 \ud504\ub85c\uc138\uc2a4\uac00 \ub8e8\ud551\uc744 \ub3c4\ub294 \uac83 \ub610\ub294 \uba48\ucdb0\uc788\ub294 \uac83 \uac19\uc774 \ubcf4\uc77c \ub54c \uad00\ub9ac\uc790 \uad8c\ud55c\uc73c\ub85c \uc2e4\ud589\ud558\uc5ec \uc0ac\uc6a9\ud560 \uc218 \uc788\ub2e4. \uc989 \ud504\ub85c\uc138\uc2a4, \uc138\uc158, \uba54\ubaa8\ub9ac, SQL \ub4f1\uc758 \ub9ac\uc18c\uc2a4\ub97c \ub364\ud504\ud560 \uc218\uc788\uace0, OS \ub514\ubc84\uac70\ub4e4\uacfc \ube44\uc2b7\ud558\uac8c \uc624\ub77c\ud074 \ub808\ubca8\uc5d0\uc11c CALL\ub2e8\uc704 \ub514\ubc84\uae45 \uae30\ub2a5\uc744 \uc81c\uacf5\ud55c\ub2e4. \ub9ac\uc18c\uc2a4\ub97c […]<\/p>\n","protected":false},"author":1,"featured_media":496,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[9],"tags":[253],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/1\/cfile1.uf.2544A53556332E3F01298F.png?fit=583%2C769","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/495"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=495"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/495\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/496"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=495"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=495"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=495"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}