DBA Notes: 2011/09/14
Cheng Li
Oracle notes: sharing a article about oracle events
Reference of Oracle event dump
1 Oracle background traces files
Oracle has three types of trace files:
1) Background Alert file
Alert file records database startup, shut down, and other DDL activities, such as tablespace creation, rollback segment creation, alert command, log switch, error message, etc.
Storage Path: BACKGROUND_DUMP_DEST
File format: SIDALRT.LOG
2) Background trace file
Background trace file records background process such as DBWR.
Storage Path: BACKGROUND_DUMP_DEST
File format: siddbwr.trc、sidsmon.trc, etc。
3) User trace file
User trace file records user process for error message or warning
Storage path: USER_DUMP_DEST
File format: oraxxxxx.trc,(xxxxx, process or thread number)
2 Oracle trace events
Oracle provides command interface to enable the functionalities’ to dump internal process information to trace. And trace is used to debug and issues resolving.
1) Set up trace event in init.ora file. After database startup, all sessions will be traced. Usage: EVENT="eventnumber trace name eventname [forever,] [level levelnumber] : ......."
“:” is used for define and separate different trace event.
2) Set up in session: set up trace event in session only affect current session. Usage: alter session set events '[eventnumber|immediate] trace name eventname [forever] [, level
levelnumber] : .......'
Usage Format:
eventnumber, event number to trigger dump event, such as Oracle error number(when Oracle generate related errors to trigger trace event) or Oracle internal event. Internal event is numbered between: 10000 and 10999. Internal event can’t be used with “immediate” key word. When “immediate” key word is used, trace event is triggered immediate. This key word is only used in session, and it is not used with other key words including “eventnumber”、”forever”.
trace name,key word
eventname: event name to be dumped. If the eventname is defined as context means internal event is traced.
Forever: “forever key words means the trace event is active for whole session.
Levelnumber: means event is leveled as 1 to 10.1 means only header is dumped. And 10 means dump all information.
3 Oracle internal event numbers
Oracle internal defined event can be traced also, such as
10046:trace SQL language
alter session set events '10046 trace name context forever, level 4'; --trace SQL for binding variable
alter session set events '10046 trace name context forever, level 8'; --trace SQL for waiting event
10262:allow memory leakage during session connection
alter session set events '10262 trace name context forever, level 300'; -- allow 300 bytes memory leakage.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26136400/viewspace-707478/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26136400/viewspace-707478/