Oracle trace file

Jan 3 posted in Oracle (RSS), 296 views.

1, Oracle trace file
Oracle trace file is divided into three types, one is the background alarm log files, records database startup, shutdown and run the activities during the background processes, such as table space creation, rollback segments to create, some alter command, log switch, error messages and so on. In the database fails, should first view the file, but the file information and any error states are not necessarily linked. Background alarm log file is saved BACKGROUND_DUMP_DEST parameter specifies the directory, the file format SIDALRT.LOG. Another type is the DBWR, LGWR, SMON background process, such as the background trace file created. The background trace file generated according to background process operation, the background trace files are also saved in the BACKGROUND_DUMP_DEST parameter specifies the directory, the file format siddbwr.trc, sidsmon.trc so. Yet another type is connected to the Oracle by the user process (Server Processes) generated by the user trace file. These documents only during a user session generated when an error is encountered. In addition, users can perform oracle trace events (see below) to generate the class file, a user track file is saved in USER_DUMP_DEST parameter specifies the directory, the file format oraxxxxx.trc, xxxxx a process for creating the file number (or thread ID) .
2, Oracle trace events
Oracle offers a class of commands, can be all kinds of internal structure of the Oracle in the information contained in the dump (dump) to the trace file, so that users according to the document to address a variety of failures. There are two ways to set trace event, one is in the init.ora file, set up the event, so open the database, it will affect all of the sessions. Formatting is as follows:
EVENT = "eventnumber trace name eventname [forever,] [level levelnumber]: ......."
Pass: symbol, you can set the number of consecutive events, you can also use the event to set the number of consecutive events.
Another method is to use during a session alter session set events command, only affect the current session. Formatting is as follows:
alter session set events' [eventnumber | immediate] trace name eventname [forever] [, level levelnumber]: .......'
Pass: symbol, you can set up multiple consecutive events can also be used continuously alter session set events to set up multiple events.
Format Description: eventnumber number of events that trigger the dump, the event numbers can be Oracle error number (with a corresponding error tracking specified events) or oralce number of internal events, internal events between the numbers in the 10000-10999, the same can not be immediate keyword use.
immediate keyword that command is issued, immediately dump the specified structure to the trace file, this keyword is only used in the alter session statement, and can not be eventnumber, forever with the same keywords.
trace name is the keyword.
eventname refers to the event name (see below), that is, to conduct dump the actual structure of the name. If the eventname the context, refers to the incident according to internal numbers to track.
forever keyword that event in the instance or session of the cycle to maintain an effective state, the same can not be immediate use.
level for the event-level keywords. But dump the error stack (errorstack) does not exist when the level.
levelnumber said that the incident-level number, usually from 1 to 10,1 just dump the structure, said header information, 10 indicated that the structure of all the information dump.
1, buffers Event: dump SGA buffer in the db buffer structure
alter session set events 'immediate trace name buffers level 1'; - said the head of the buffer dump.
2, blockdump Event: dump data files, index files, rollback segment file structure
alter session set events 'immediate trace name blockdump level 66666'; - said the dump block address of 6666 data blocks.
In Oracle 8 after the order has been changed to:
alter system dump datafile 11 block 9; - said the dump data file No. 11 in the first nine blocks of data.
3, controlf Event: dump control file structure
alter session set events 'immediate trace name controlf level 10'; - that dump all the contents of control file.
4, locks the event: dump LCK process lock information
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'; - said the head of the control dump redo log file entry.
alter session set events 'immediate trace name redohdr level 2'; - said that a common dump redo log file header.
alter session set events 'immediate trace name redohdr level 10'; - said that the full dump redo log file header.
Note: redo log the contents of the dump can be used the following statement:
alter system dump logfile 'logfilename';
6, loghist Event: dump control file entries in the log history
alter session set events 'immediate trace name loghist level 1'; - indicated that only dump the log history of the earliest and the latest entries.
levelnumber greater than or equal 2, said that two of the levelnumber th power a log history of items.
alter session set events 'immediate trace name loghist level 4'; - the history of blogs, said dump 16 entries.
7, file_hdrs Event: dump all the data file header information
alter session set events 'immediate trace name file_hdrs level 1'; - all data files, said the head dump control file entries.
alter session set events 'immediate trace name file_hdrs level 2'; - that dump all the data files of the common header.
alter session set events 'immediate trace name file_hdrs level 10'; - that dump all the data integrity of the document header.
8, errorstack Event: dump the error stack information, usually when an error occurs Oracle foreground process will get an error message, but not an error message under certain circumstances, can be used in this way to be Oracle error.
alter session set events '604 trace name errorstack forever '; - that when there is 604 errors, dump the error stack and process stack.
9, systemstate Event: dump all system status and process status
alter session set events 'immediate trace name systemstate level 10'; - that dump all the system state and the process state.
10, coalesec Event: dump the specified table space, free range
levelnumber in hexadecimal, the two high-order byte, said the number of free range, two low-order byte, said tablespace number, such as 0x00050000, said dump system table space in the five free interval, converted to decimal is 327.68 thousand, that is, :
alter session set events 'immediate trace name coalesec level 327680';
11, processsate events: dump process state
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 in the
alter session set events 'immediate trace name heapdump level 1';
14, row_cache Event: dump the data dictionary information in the buffer
alter session set events 'immediate trace name row_cache level 1';

3, the internal event ID
1,10013: used to monitor transaction recovery
2,10015: Dump UNDO SEGMENT head
event = "10015 trace name context forever"
3,10029: for the landing of the information given during the session
4,10030: For cancellation information given during the session
5,10032: dump sort of statistical information
6,10033: dump sort of statistical information growth
7,10045: Track Freelist management operations
8,10046: Tracking SQL Statement
alter session set events '10046 trace name context forever, level 4 '; - trace SQL statements and show bind variables
alter session set events '10046 trace name context forever, level 8 '; - trace SQL statements and displays the wait event
9,10053: Dump Optimization Strategy
10,10059: Analog redo log to create and remove errors
11,10061: to prevent SMON process, remove the temporary segment at startup
12,10079: Dump SQL * NET Statistics
13,10081: Dump high-water mark changes in
14,10104: Dump Hash connection statistics
15,10128: dump partition break information
16,10200: dump consistent read the information
17,10201: dump coherence reading Undo Application
18,10209: Allow to simulate an error in the control file
19,10210: Trigger data block checking event
event = "10210 trace name context forever, level 10"
20,10211: Trigger Index Check Event
21,10213: Simulation in writing after the collapse of the control file
22,10214: Simulation in the control file write error
levelnumber from 1-9, said a wrong block number, greater than or equal to 10 for each control file error
23,10215: Simulation in the control file read error
24,10220: Dump Undo Changes Head
25,10221; Dump Undo Changes
26,10224: Dump the index of separation and delete
27,10225: Dump Interval-based dictionary management changes
28,10229: Simulation in the data file on the I / O error
29,10231: Set in the full table scans bypass the corrupted data block
alter session set events '10231 trace name context off '; - close the session data blocks during the inspection
event = "10231 trace name context forever, level 10" - for any process of reading the data blocks into the SGA inspection
30,10232: will be set to soft damage (DBMS_REPAIR package is set to TRUE, or DB_BLOCK_CHECKING settings) data blocks to the trace file dump
31,10235: for the memory heap checking
alter session set events '10235 trace name context forever, level 1 ';
32,10241: dump a remote SQL implementation of the
33,10246: Track PMON process
34,10248: Track dispatch process
35,10249: Track MTS Process
36,10252: Analog to write the data file header error
37,10253: Analog to write redo log file error
38,10262: Allow to connect a memory leak
alter session set events '10262 trace name context forever, level 300 '; - allowed for 300 bytes of memory leak
39,10270: Dump shared cursor
40,10285: Analog control file header is damaged
41,10286: Analog Control File Open Error
42,10287: Simulation archive error
43,10357: Debugging direct path mechanism
44,10500: Track SMON process
45,10608: Tracking the creation of bitmap indexes
46,10704: Track enqueues
47,10706: tracking the global enqueues
48,10708: tracking RAC's buffer cache
49,10710: track bitmap index access
50,10711: Track bitmap index merge operations
51,10712: Bitmap Index OR Operation Track
52,10713: Bitmap Index AND Operation Track
53,10714: Track MINUS operation bitmap indexes
54,10715: bitmap indexes tracking the conversion of the ROWID
55,10716: Track bitmap index compression and decompression
56,10719: Tracking changes to the bitmap index
57,10731: tracking the cursor declaration
58,10928: Tracking PL / SQL implementation of the
59,10938: Dump PL / SQL implementation of the statistical information Finally, a note that, due to the different versions of the above may be some changes in grammar, but the majority is still available.

Attached a complete list of trace events, event No.10000 to 10999

SET SERVEROUTPUT ON
DECLARE
err_msg VARCHAR2 (120);
BEGIN
dbms_output.enable (1000000);
FOR err_num IN 10000 .. 10999
LOOP
err_msg: = SQLERRM (-err_num);
IF err_msg NOT LIKE '% Message' | | err_num | | 'not found%' THEN
dbms_output.put_line (err_msg);
END IF;
END LOOP;
END;
/

1.select sid, serial #, username, osuser, machine from v $ session; query session of sid, serial #

2.exec dbms_system.set_sql_trace_in_session (141,6, true); began tracking

3.exec dbms_system.set_sql_trace_in_session (141,6, true) to stop the trace

4.tkprof *. trc *. txt

1.select sid, serial #, username, osuser, machine from v $ session; query session of sid, serial #

2.exec dbms_system.set_sql_trace_in_session (141,6, true); began tracking

3.exec dbms_system.set_sql_trace_in_session (141,6, true) to stop the trace

4.tkprof *. trc *. txt

5. View Files

6.get trace file name
select
d.value ||'/'|| lower (rtrim (i.instance, chr (0 )))||'_ ora_ '| | p.spid | |'. trc 'trace_file_name
from
(Select p.spid
from sys.v $ mystat m, sys.v $ session s, sys.v $ process p
where m.statistic # = 1 and s.sid = m.sid and p.addr = s.paddr) p,
(Select t.instance from sys.v $ thread t, sys.v $ parameter v
where v.name = 'thread' and (v.value = 0 or t.thread # = to_number (v.value))) i,
(Select value from sys.v $ parameter where name = 'user_dump_dest') d
/

This article comes from CSDN blog, reproduced please indicate source: http://blog.csdn.net/kdnuggets/archive/2008/04/24/2321208.aspx