Oracle trace event

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.

6210676-390x450.jpg

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26136400/viewspace-707478/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26136400/viewspace-707478/