ORACLE中工具的使用06–ORADEBUG

Posted on by
oradebug工具的使用:

  1. oradebug主要是给oracle支持人员使用的,从8i开始,它是个sql*plus命令行工具,有sysdba的权限就可以使用,无需特别设置,主要用途为:
    1. 追踪进程,自己的或者是外部的;
    2. 确定进程写入哪个trc文件;
    3. 转储:数据文件头,内部oracle结构等;
    4. 暂时挂起进程;
    5. 确定实例使用了哪些共享内存块和信号量;
    6. 找出RAC实例使用了哪些互联地址和协议;
    7. 修改SGA中的数据结构;
  2. oradebug使用步骤:
    1. 启动sql*plus并以sysdba身份登入;
    2. 连接到一个进程;
    3. 设置一个事件或者进行诊断转储;
    4. 查看trc文件名;
    5. 与连接到的进程断开;
  3. oradebug的语法;
  4. 连接到一个进程的方法:
    1. oradebug setmypid:连接到当前的进程:
    2. oradebug setorapid pid:根据pid,连接到其它进程(v$process.pid);
    3. oradebug setospid spid:根据spid,连接到其它进程(v$process.spid);
  5. 查看共享信息内存方法:ipcs [-m|-s|-q],默认会列出共享内存,信号量,队列信息;
    1. 清除命令是ipcrm [-m|-s|-q] id;
    2. 参数:
      1. -m:列出共享内存信息;
      2. -s:列出共享信号量信息;
      3. -q:列出共享队列信息;
    3. 例子;
  6. 如果一个程序挂起,那么程序调用栈就可以显示它在调用路径中的哪一步挂起的:oradebug short_stack;                        
  7. 文件转储:
    1. 可以转储的类型:oradebug dmplist;
    2. 转储控制文件:oradebug dump controlf 10;
    3. 实现10046事件,例子;
    4. oradebug dump events的级别和范围:
      1. level 1,会话级别,对应alter session命令;
      2. level 2,进程级别,对应oradebug event命令;
      3. level 4,实例级别,对应alter system命令;
  8. oradebug对于解决问题和诊断性能是必不可少的工具;
——————————– oradebug语法 ——————————–
sqlplus / as sysdba
SQL> oradebug help
HELP           [command]                 Describe one or all commands
SETMYPID                                 Debug current process
SETOSPID       <ospid>                   Set OS pid of process to debug
SETORAPID      <orapid> [‘force’]        Set Oracle pid of process to debug
SETORAPNAME    <orapname>                Set Oracle process name to debug
SHORT_STACK                              Get abridged OS stack
CURRENT_SQL                              Get current SQL
DUMP           <dump_name> <lvl> [addr]  Invoke named dump
DUMPSGA        [bytes]                   Dump fixed SGA
DUMPLIST                                 Print a list of available dumps
EVENT          <text>                    Set trace event in process
SESSION_EVENT  <text>                    Set trace event in session
DUMPVAR        <p|s|uga> <name> [level]  Print/dump a fixed PGA/SGA/UGA variable
DUMPTYPE       <address> <type> <count>  Print/dump an address with type info
SETVAR         <p|s|uga> <name> <value>  Modify a fixed PGA/SGA/UGA variable
PEEK           <addr> <len> [level]      Print/Dump memory
POKE           <addr> <len> <value>      Modify memory
WAKEUP         <orapid>                  Wake up Oracle process
SUSPEND                                  Suspend execution
RESUME                                   Resume execution
FLUSH                                    Flush pending writes to trace file
CLOSE_TRACE                              Close trace file
TRACEFILE_NAME                           Get name of trace file
LKDEBUG                                  Invoke global enqueue service debugger
NSDBX                                    Invoke CGS name-service debugger
-G             <Inst-List | def | all>   Parallel oradebug command prefix
-R             <Inst-List | def | all>   Parallel oradebug prefix (return output
SETINST        <instance# .. | all>      Set instance list in double quotes
SGATOFILE      <SGA dump dir>         Dump SGA to file; dirname in double quotes
DMPCOWSGA      <SGA dump dir> Dump & map SGA as COW; dirname in double quotes
MAPCOWSGA      <SGA dump dir>         Map SGA as COW; dirname in double quotes
HANGANALYZE    [level] [syslevel]        Analyze system hang
FFBEGIN                                  Flash Freeze the Instance
FFDEREGISTER                             FF deregister instance from cluster
FFTERMINST                               Call exit and terminate instance
FFRESUMEINST                             Resume the flash frozen instance
FFSTATUS                                 Flash freeze status of instance
SKDSTTPCS      <ifname>  <ofname>        Helps translate PCs to names
WATCH          <address> <len> <self|exist|all|target>  Watch a region of memory
DELETE         <local|global|target> watchpoint <id>    Delete a watchpoint
SHOW           <local|global|target> watchpoints        Show  watchpoints
DIRECT_ACCESS  <set/enable/disable command | select query> Fixed table access
CORE                                     Dump core without crashing process
IPC                                      Dump ipc information
UNLIMIT                                  Unlimit the size of the trace file
PROCSTAT                                 Dump process statistics
CALL           [-t count] <func> [arg1]…[argn]  Invoke function with arguments
——————————– oradebug语法 ——————————–
——————————– 查看共享信息内存的例子 ——————————–
— 1.生成信息到trace文件;
1.操作系统层面查看信息:host ipcs -m | grep ora
2.设置进程为当前进程:oradebug setmypid
3.导出信息到trace文件:oradebug ipc
4.查看trace文件的路径:oradebug tracefile_name
— 2.查看相应的trace文件;
Processing Oradebug command ‘ipc’
Dump of unix-generic skgm context
areaflags            000000f7
realmflags           0000001f
mapsize              00000800
protectsize          00001000
lcmsize              00001000
seglen               00400000
largestsize  00000000ffffffff
smallestsize 0000000000400000
stacklimit         0xbdc6af20
stackdir                   -1
mode                      640
magic                acc01ade
Handle:              0xf66058 `/u01/app/oracle/product/11.2.0/db_1ORCL’
Dump of unix-generic realm handle `/u01/app/oracle/product/11.2.0/db_1ORCL’, flags = 00
000000
Area #0 `Fixed Size’ containing Subareas 0-0
Total size 0000000000149294 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
0        0   196608 0x00000020000000 0x00000020000000
Subarea size     Segment size
000000000014a000 0000000000800000
Area #1 `Variable Size’ containing Subareas 4-4
Total size 0000000032400000 Minimum Subarea size 00400000
Area  Subarea    Shmid      Stable Addr      Actual Addr
1        4   229377 0x00000020800000 0x00000020800000
Subarea size     Segment size
0000000032400000 0000000032400000
Area #2 `Redo Buffers’ containing Subareas 1-1
Total size 00000000004e3000 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
2        1   196608 0x0000002014a000 0x0000002014a000
Subarea size     Segment size
00000000004e3000 0000000000800000
Area #3 `Base Allocator Control’ containing Subareas 3-3
Total size 0000000000002000 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
3        3   196608 0x000000207fe000 0x000000207fe000
Subarea size     Segment size
0000000000002000 0000000000800000
Area #4 `Slab Allocator Control’ containing Subareas 2-2
Total size 00000000001d1000 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
4        2   196608 0x0000002062d000 0x0000002062d000
Subarea size     Segment size
00000000001d1000 0000000000800000
Area #5 `skgm overhead’ containing Subareas 5-5
Total size 0000000000002000 Minimum Subarea size 00000000
Area  Subarea    Shmid      Stable Addr      Actual Addr
5        5   262146 0x00000052c00000 0x00000052c00000
Subarea size     Segment size
0000000000002000 0000000000400000
Dump of Linux-specific skgm context
sharedmmu 00000001
shareddec        0
used region        0: start 0000000012000000 length 0000000000400000
used region        1: start 0000000020000000 length 0000000033000000
used region        2: start 00000000af800000 length 0000000010800000
Maximum processes:               = 1000
Number of semaphores per set:    = 125
Semaphores key overhead per set: = 4
User Semaphores per set:         = 121
Number of semaphore sets:        = 9
Semaphore identifiers:           = 9
Semaphore List=
491520
524289
557058
589827
622596
655365
688134
720903
753672
————– system semaphore information ————-
—— Shared Memory Segments ——–
key        shmid      owner      perms      bytes      nattch     status
0x00000000 196608     oracle    640        4096       0
0x00000000 229377     oracle    640        4096       0
0x671360a4 262146     oracle    640        4096       0
—— Semaphore Arrays ——–
key        semid      owner      perms      nsems
0x4c1391ac 491520     oracle    640        125
0x4c1391ad 524289     oracle    640        125
0x4c1391ae 557058     oracle    640        125
0x4c1391af 589827     oracle    640        125
0x4c1391b0 622596     oracle    640        125
0x4c1391b1 655365     oracle    640        125
0x4c1391b2 688134     oracle    640        125
0x4c1391b3 720903     oracle    640        125
0x4c1391b4 753672     oracle    640        125
—— Message Queues ——–
key        msqid      owner      perms      used-bytes   messages
——————————– 查看共享信息内存的例子 ——————————–
——————————– oradebug实现10046事件 ——————————–
— 1.设置某个进程;
session 1:
sqlplus / as sysdba
conn hr/hr
SELECT pid, spid FROM v$process WHERE addr = (SELECT paddr FROM v$session WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1));
— 29 22505;
session 2:
sqlplus / as sysdba
oradebug setorapid 29;
— 2.设置trace文件的大小:oradebug unlimit;
— 3.打开10046事件:oradebug event 10046 trace name context forever, level 12;
— 4.执行查询:SELECT COUNT(*) FROM hr.employees;(在session 1中)
— 5.查看trace文件的位置:oradebug tracefile_name;
— 6.关闭10046事件:oradebug event 10046 trace name context off;
— 6.查看trace文件,使用tkprof工具格式化;
——————————– oradebug实现10046事件 ——————————–

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注