Oracle Trace Event Set event

One, Oracle trace file
There are three types of Oracle trace files, a background alarm log file that records the activity of the background process during startup, shutdown, and operation of the database, such as tablespace creation, rollback segment creation, some alter commands, log switchover, error messages, and so on. In the event of a database failure, the file should be viewed first, but the information in the file is not necessarily associated with any error state. The background alarm log file is saved in the directory specified by the Background_dump_dest parameter, and the file format is SIDALRT.LOG. Another type is a background trace file created by a background process such as DBWR, LGWR, Smon, and so on. Background tracking files based on background process run, background tracking files are also saved in the directory specified by the Background_dump_dest parameter, the file format is SIDDBWR.TRC, SIDSMON.TRC and so on. There is also a type of user trace file that is generated by the user process (Server Processes) that is connected to Oracle. These files are generated only when an error is encountered during a user session. In addition, the user can generate the class file by performing an Oracle trace event (see later), which is saved in the directory specified by the User_dump_dest parameter, with the file format oraxxxxx.trc,xxxxx the process number (or thread number) that created the file.


II. Oracle Tracking Events
Oracle provides a class of commands that can dump (dump) information contained in Oracle's various internal structures to trace files so that users can resolve various failures based on the content of the file. There are two ways to set up a trace event, which is to set the event in the Init.ora file so that all sessions are affected after the open database. Set the format as follows:
event= "Eventnumber trace Name EventName [Forever,] [level Levelnumber]: ..."
Through: Symbols, you can set multiple events consecutively, or you can set multiple events by using event continuously.
Another approach is to use the ALTER session SET EVENTS command during a session to only affect the current session. Set the format as follows:
Alter session SET Events ' [Eventnumber|immediate] Trace name eventname [Forever] [, Level Levelnumber]: ... '
Through: Symbols, you can set multiple events consecutively, or you can set multiple events by using ALTER session set events continuously.
Format Description: Eventnumber refers to the event number that triggers the dump, which can be an Oracle error number (the specified event is tracked when a corresponding error occurs) or an internal Oralce event number that is between 10000 and 10999 and cannot be used with the immediate keyword.
The immediate keyword indicates that the specified structure is immediately dump into the trace file after the command is issued, which is used only in the ALTER session statement and cannot be used with the eventnumber, forever keywords.
Trace name is a keyword.
EventName refers to the event name (see later), which is the actual structure name of the dump to be made. If EventName is a context, it is tracked according to the internal event number.
The Forever keyword indicates that the event remains valid for the period of the instance or session and cannot be used with immediate.
Level is a keyword for the event. However, there is no level at the dump error stack (errorstack).
Levelnumber represents the event level number, typically from 1 to 10, 1 for dump-only structure header information, and 10 for all information about the dump structure.

1. Buffers Event: DB buffer structure in the dump SGA buffer
Alter session SET Events ' immediate trace name buffers Level 1 '; --Represents the head of the dump buffer.
2. Blockdump Event: Dump data file, index file, rollback segment file structure
Alter session SET Events ' immediate trace name blockdump level 66666 '; --Represents a block of data with a dump block address of 6666.
After Oracle 8, the command has been changed to:
Alter system dump datafile Block 9; --Represents the 9th block of data in the Dump data file Number 11.
3. Controlf Event: Dump control file structure
Alter session SET Events ' immediate trace name Controlf level 10 '; --Represents all the contents of the dump control file.
4. Locks Event: Lock information for dump LCK process
Alter session SET Events ' immediate Trace name locks Level 5 ';
5, REDOHDR event: Dump Redo Log Header information
Alter session SET Events ' immediate trace name REDOHDR Level 1 '; --Represents the control file entry for the dump redo log header.
Alter session SET Events ' immediate trace name REDOHDR Level 2 '; --Represents the general file header of the dump redo log.
Alter session SET Events ' immediate trace name REDOHDR level 10 '; --Represents the full file header of the dump redo log.
Note: The contents of the Redo log dump can take the following statement:
alter system dump logfile ' logfilename ';
6. Loghist Event: Log history entry in dump control file
Alter session SET Events ' immediate trace name loghist Level 1 '; --Indicates only the earliest and latest log history entries for the dump.
Levelnumber is greater than or equal to 2 o'clock, which represents a levelnumber log history entry for 2.
Alter session SET Events ' immediate trace name loghist level 4 '; --Represents a dump of 16 log history entries.
7. File_hdrs Event: Dump header information for all data files
Alter session SET Events ' immediate trace name File_hdrs Level 1 '; --Represents the control file entry for all data file headers of the dump.
Alter session SET Events ' immediate trace name File_hdrs Level 2 '; --Represents the generic file header for all data files for dump.
Alter session SET Events ' immediate trace name File_hdrs level 10 '; --Represents the full file header of all data files for dump.
8, Errorstack event: Dump error stack information, usually Oracle error occurs when the foreground process will get an error message, but in some cases the error message can be used in this way to get Oracle error.
Alter session SET Events ' 604 Trace name Errorstack forever '; --Indicates the dump error stack and the process stack when a 604 error occurs.
9. systemstate Event: Dump all system status and process status
Alter session SET Events ' immediate trace name systemstate level 10 '; --Represents the dump of all system state and process state.
10. Coalesec Event: dump specifies the free interval in the tablespace
Levelnumber in hexadecimal notation, two high-order bytes represent the number of free intervals, and two low-level bytes represent tablespace numbers, such as 0x00050000, which represents 5 free intervals in the dump system tablespace, converted to decimal is 327680, namely:
Alter session SET Events ' immediate trace name coalesec level 327680 ';
11. Processsate Event: Dump process status
Alter session SET Events ' immediate trace name processsate level 10 ';
12. Library_cache Event: Dump Library cache information
Alter session SET Events ' immediate trace name Library_cache level 10 ';
13. Heapdump Event: Dump PGA, SGA, UGA information
Alter session SET Events ' immediate trace name heapdump Level 1 ';
14. Row_cache Event: Information in the Dump data dictionary buffer
Alter session SET Events ' immediate trace name Row_cache Level 1 ';

Iii. Internal Event Number
1, 10013: for monitoring transaction recovery
2, 10015: Dump Undo Segment Head
event = "10015 Trace name Context Forever"
3, 10029: Used to give the login information during the session
4, 10030: Used to give the logoff information during the session
5, 10032: statistics for dump sort
6, 10033: Dump sort growth statistics
7, 10045: Track Freelist management operations
8, 10046: Trace SQL statements
Alter session SET Events ' 10046 Trace name Context forever, Level 4 '; --Track SQL statements and display bound variables
Alter session SET Events ' 10046 Trace name Context forever, Level 8 '; --Track SQL statements and display wait events
9, 10053: Dump optimization strategy
10, 10059: Create and purge errors in the simulated redo log
11, 10061: Prevents the Smon process from clearing temporary segments at startup
12, 10079: Dump sql*net Statistics
13, 10081: Dump high water mark change
14, 10104: Dump Hash Connection statistics
15, 10128: Dump partition information
16, 10200: Dump consistent read information
17, 10201: Dump consistent read in undo application
18, 10209: Allow impersonation of errors in control files
19, 10210: Trigger data Block Check Event
event = "10210 Trace Name" Context Forever, Level 10 "
20, 10211: Triggers an index check event
21, 10213: Simulation crashes after writing a control file
22, 10214: Simulate write errors in the control file
Levelnumber from 1-9 indicates the block number that generated the error, greater than or equal to 10, each control file will have an error
23, 10215: Analog read errors in the control file
24, 10220: Dump Undo head Change
25, 10221; dump undo Change
26, 10224: Separation and deletion of dump indexes
27, 10225: Dump changes based on the dictionary management interval
28, 10229: Simulate I/O errors on a data file
29, 10231: Set to ignore corrupted data blocks during full table scan
Alter session SET Events ' 10231 Trace name context off '; --Block check during session closure
event = "10231 Trace name Context forever, Level 10"--Check the data block for any process read into the SGA
30, 10232: Data block set to soft damage (Dbms_repair package set or Db_block_checking true when set) dump to trace file
31, 10235: for memory heap checking
Alter session SET Events ' 10235 Trace name Context forever, Level 1 ';
32, 10241: Dump remote SQL execution
33, 10246: Track the Pmon process
34, 10248: Track the Dispatch process
35, 10249: Track MTS Process
36, 10252: Analog Write Data file header error
37, 10253: Analog write redo log file error
38, 10262: Memory leak when connection is allowed
Alter session SET Events ' 10262 Trace name Context forever, Level 300 '; --Allow 300 bytes of memory leak
39, 10270: Dump shared cursors
40, 10285: Analog control file head damage
41, 10286: Analog control File Open error
42, 10287: Error in Analog archive
43, 10357: Debug Direct path mechanism
44, 10500: Track the Smon process
45, 10608: Tracking the creation of bitmap indexes
46, 10704: Tracking enqueues
47, 10706: Track Global enqueues
48, 10708: tracks the buffer cache for RAC
49, 10710: Track access to a bitmap index
50, 10711: Track bitmap index merge operations
51, 10712: Track bitmap index or operation
52, 10713: Track bitmap index and operations
53, 10714: Track bitmap index minus operation
54, 10715: Track the conversion of bitmap index to ROWID
55, 10716: The compression and decompression of the tracking bitmap index
56, 10719: Track Changes to bitmap indexes
57, 10731: Tracking Cursor Declaration
58, 10928: Track PL/SQL execution
59, 10938: Dump PL/SQL Execution statistics
Last but not least, most of the syntax may vary, but most are available.

Four. Example

4.1 Viewing the current TRC file
Select
U_dump.value | | '/' | |
Db_name.value | | ' _ora_ ' | |
V$process.spid | |
NVL2 (V$process.traceid, ' _ ' | | V$process.traceid, NULL)
|| '. TRC ' "Trace File"
From
V$parameter U_dump
Cross Join V$parameter Db_name
Cross Join V$process
Join V$session
On v$process.addr = V$session.paddr
where
U_dump.name = ' user_dump_dest ' and
Db_name.name = ' db_name ' and
V$session.audsid=sys_context (' Userenv ', ' SessionID ');
eg
/fsdb1/oracle/admin/newccs/udump/newccs_ora_4862050.trc

4.2 Execute Event

Alter session SET Events ' 10046 Trace name Context forever, Level 1 ';
Select COUNT (1) from All_tables;
Alter session SET Events ' 10046 Trace name context off ';

4.3 In view newccs_ora_4862050.trc
$more NEWCCS_ORA_4862050.TRC

Ext.: http://blog.csdn.net/tianlesoftware/article/details/4977827

Oracle Trace Event Set event