1 oradebug介绍
oradebug主要是给oracle支持人员使用的,尽管很早便有,但oracle官网很少有记载。他是个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命令介绍
sys@ORCL> 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
SHORT_STACK Dump abridged OS stack
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
CORE Dump core without crashing process
IPC Dump ipc information
UNLIMIT Unlimit the size of the trace file
PROCSTAT Dump process statistics
CALL <func> [arg1] ... [argn] Invoke function with arguments
4 连接到一个进程
在oradebug命令执行之前,你必须加入一个目标进程。这个目标进程有如下3种情况:
命令
|
目的
|
oradebug setmypid
|
连接到为你的sql*plus提供服务的进程
|
oradebug setorapid pid
|
连接到一个外部服务进程,且pid=v$process.pid
|
oradebug setospid spid
|
连接到一个外部服务进程,且spid=v$process.pid
|
1)
setmypid
sys@ORCL> oradebug setmypid
Statement processed.
sys@ORCL> oradebug unlimit
Statement processed.
sys@ORCL> oradebug event 10046 trace name context forever,level 8
Statement processed.
sys@ORCL> select * from hr.departments where department_id=10;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
sys@ORCL> oradebug tracefile_name
/u01/app/oracle/admin/orcl/udump/orcl_ora_5234.trc
sys@ORCL> oradebug event 10046 trace name context off
Statement processed.
2)setospid(spid操作)
sys@ORCL> host top
top - 15:14:20 up 47 min, 2 users, load average: 0.22, 0.20, 0.26
Tasks: 171 total, 2 running, 168 sleeping, 0 stopped, 1 zombie
Cpu(s): 1.3%us, 1.3%sy, 0.0%ni, 97.4%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 678912k total, 671192k used, 7720k free, 16672k buffers
Swap: 0k total, 0k used, 0k free, 401584k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
5874 oracle 15 0 2444 1084 828 R 1.6 0.2 0:00.47 top
4781 root 15 0 53900 11m 6076 S 0.7 1.7 1:00.13 Xorg
5168 oracle 16 0 491m 17m 15m S 0.3 2.6 0:02.41 oracle
sys@ORCL> oradebug setospid 5168
Oracle pid: 7, Unix process pid: 5168, image: oracle@localhost.localdomain (CKPT)
3)setorapid(pid操作)
sys@ORCL> select sid from v$mystat where rownum=1;
SID
----------
159
sys@ORCL> select pid,spid from v$process p,v$session s where s.sid=159 and s.paddr=p.addr;
PID SPID
---------- ------------
15 5234
sys@ORCL> oradebug setorapid 15
Statement processed.
sys@ORCL> oradebug tracefile_name
/u01/app/oracle/admin/orcl/udump/orcl_ora_5234.trc
sys@ORCL> oradebug event 10046 trace name context off
Statement processed.
注意,在OEM的top session也可以等价实现上述效果,即:确定哪些数据库会话消耗了较多的资源
5 oradebug IPC
查看共享信息的内存的命令是ipcs [-m|-s|-q]。默认会列出共享内存、信号量,队列信息,-m列出共享内存,-s列出共享信号量,-q列出共享队列
sys@ORCL> host ipcs -m | grep ora
0xbe3bb918 5439503 oracle 640 419438592 20
sys@ORCL> oradebug setmypid
Statement processed.
sys@ORCL> oradebug ipc
Information written to trace file.
sys@ORCL> oradebug tracefile_name
/u01/app/oracle/admin/orcl/udump/orcl_ora_5234.trc
sys@ORCL> oradebug event 10046 trace name context off
Statement processed.
当一台电脑的资源有限时,失败的实例占用共享内存和信号量会导致资源紧张,从而使得新的oracle实例无法启动。
清除命令是ipcrm [-m|-s|-q] id。
-m 删除共享内存,-s删除共享信号量,-q删除共享队列
如:ipcrm -m 5439503
6oradebug SHORT_STACK
如果一个程序挂起,那么程序调用栈就可以显示它在调用路径中的哪一步挂起
sys@ORCL> oradebug setospid 5180
Oracle pid: 13, Unix process pid: 5180, image: oracle@localhost.localdomain (D000)
sys@ORCL> oradebug short_stack
ksdxfstk()+19<-ksdxcb()+1321<-sspuser()+81<-__kernel_vsyscall()+2<-ntevpque()+112<-ntevque()+606<-nsevwait()+821<-ksnwait()+111<-ksliwat()+770<-kslwaitns()+29<-
kskthbwt()+370<-kslwait()+57<-kmdmai()+1797<-kmmrdp()+432<-opirip()+1191<-opidrv()+864<-sou2o()+113<-opimai_real()+353<-main()+111<-__libc_start_main()+220
sys@ORCL> oradebug event 10046 trace name context off
Statement processed.
当前的调用步骤在输出的顶部
7 诊断转储
可转储类型如下:
sys@ORCL> oradebug dumplist
EVENTS
TRACE_BUFFER_ON
TRACE_BUFFER_OFF
HANGANALYZE
LATCHES
PROCESSSTATE
SYSTEMSTATE
INSTANTIATIONSTATE
.........
下面举例两个:
1)controlf
sys@ORCL> oradebug setmypid
Statement processed.
sys@ORCL> oradebug dump controlf 10
Statement processed.
sys@ORCL> oradebug tracefile_name
/u01/app/oracle/admin/orcl/udump/orcl_ora_5234.trc
2)events
这不是真正的转储,events只是将激活的事件写入trc文件。如果你不能确定一个会话、进程或者实例中哪个事件是活动的,下面有一种很好的方式去查询:
sys@ORCL> alter session set events '10046 trace name context forever,level 8';
Session altered.
sys@ORCL> alter session set events '4031 trace name heapdump level 3';
Session altered.
sys@ORCL> oradebug setmypid
Statement processed.
sys@ORCL> oradebug dump events 1
Statement processed.
sys@ORCL> oradebug tracefile_name
/u01/app/oracle/admin/orcl/udump/orcl_ora_5234.trc
sys@ORCL> oradebug event 10046 trace name context off
Statement processed.
oradebug dump events的级别和范围
级别 |
事件范围 |
使用的命令 |
1 |
会话 |
alter session |
2 |
进程 |
oradebug event |
4 |
实例 |
alter system |
8 小结
oradebug对于解决问题和诊断性能是必不可少的工具。本文只是讲述了一些最常见的命令,还有更多的功能,比如RAC环境下调用全局队列服务调试器的
oradebug lkdebug命令。
分享到:
相关推荐
专门介绍oracle oradebug工具的使用方法,涵盖了日常可能碰到的各种问题处理解决
oradebug的命令大全,在业务上很有帮助的
oradebug操作手册 oracle官方oradebug命令操作使用手册
oradebug诊断oracle resource竞争
oradebug使用简介(en),英文版,说明这个工具的使用方法。
10053事件是oracle提供的用于跟踪sql语句成本计算的内部事件,它能记载CBO模式下oracle优化器如何计算sql成本,生成相应的执行计划。
SQL> oradebug setmypid Statement processed. SQL> oradebug unlimit Statement processed. SQL> oradebug dump systemstate 266 Statement processed. SQL> oradebug tracefile_name /oracle/ora10g/admin/jscn/...
在一些情况下(特别是一些数据库非常规恢复场景中),需要修改oracle scn绕过一些错误,让数据库open成功,在以前的版本中我们可以通过event,隐含参数,oradebug等方法进行修改,在一些较新的版本中这些方法都被oracle屏蔽,...
oracle系统状态trace文件分析器 当系统hang住时,或者进程间有阻塞时,你可以产生下面两种跟踪文件,一种是进程状态跟踪文件,一种是系统状态跟踪文件: process state dumps ==> 一个进程的所有对象状态,...
专家调优秘密之改善Oracle数据库性能
非oracle原厂人员写的,关于latch相关的东西,如果没有更好的资料的话,还是值的一看的。
oracle scn分析及概念解释,及各种scn的查询sql语句
Windows环境下(同样适用于linux)Oracle非归档无备份控制文件损坏恢复过程包括后续处理-oradebug推进scn的详细说明和总结。
Oracle in Processes ........................................................................................................... 1 Oracle in Action ........................................................
- stack <os_pid> get process stack using oradebug - cursors [all] <match_str>: [all] parsed cursors - sharing <sql_id>: print why cursors are not shared - events [px]: events that someone is ...