Detailed Oracle's trace files and dump commands

Oracle Trace Files

Oracle trace files are grouped into three categories:

One is the background alarm log file that records the activity of the background process during startup, shutdown, and running of the database, such as table space creation, rollback segment creation, some alter commands, log switches, error messages, and so on. When a database fails, the file should be viewed first, but the information in the file is not necessarily associated with any error status. 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 background processes such as DBWR, LGWR, and Smon. Background tracking files are generated according to the background process, and the background trace files are saved in the directory specified by the Background_dump_dest parameter, the file format is SIDDBWR.TRC, SIDSMON.TRC, etc.

There is also a type of user trace file generated by the user process (Server processes) 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 in the file format oraxxxxx.trc,xxxxx as the process number (or thread number) for creating the file.

Second, Oracle tracking events

Oracle provides a class of commands to dump (dump) information contained within Oracle's various internal structures into trace files so that users can resolve various failures based on the contents of the file. There are two ways to set up trace events, one of which is to set up events in the Init.ora file, so that after the open database, all sessions will be affected. Set the format as follows:

event= "Eventnumber trace Name EventName [Forever,] [level Levelnumber]: ..."

With: Symbols, you can set multiple events consecutively, or you can set multiple events by using the event continuously.

Another approach is to use the ALTER session SET EVENTS command during a conversation and only affect the current session. Set the format as follows:

Alter session SET Events ' [Eventnumber|immediate] Trace name eventname [Forever] [, Level Levelnumber]: ... '

By: Symbols, you can set up multiple events consecutively, or you can set multiple events by using the ALTER session set events continuously.

Format Description: Eventnumber refers to the event number that triggers the dump, the event number can be an Oracle error number (trace the specified event when the corresponding error occurs) or the Oralce internal event number, which is between 10000 and 10999 and cannot be used with the immediate keyword.

After the immediate keyword indicates that the command is issued, dump the specified structure immediately into the trace file, which is used only in the ALTER session statement and cannot be used with eventnumber, forever keywords.

Trace name is a keyword.

EventName refers to the event name (see later), which is the actual structure name to be dump. If the eventname is the context, the tracking is based on the internal event number.

The Forever keyword indicates that the event remains in effect during the instance or session cycle and cannot be used with immediate.

Levels are event-level keywords. However, there is no level at the dump error stack (errorstack).

Levelnumber represents event level numbers, typically from 1 to 10, 1 for Dump 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: Dump LCK process Lock information

Alter session SET Events ' immediate Trace name locks Level 5 ';

5, REDOHDR event: Dump Redo Log Header information

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45450.htm

Alter session SET Events ' immediate trace name REDOHDR Level 1 '; --A control file entry that represents the header of the dump redo log.

Alter session SET Events ' immediate trace name REDOHDR Level 2 '; --The common file header that represents 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 content dump of the redo log can take the following statement:

alter system dump logfile ' logfilename ';

6, Loghist event: The log history item in the dump control file

Alter session SET Events ' immediate trace name loghist Level 1 '; --Represents the earliest and latest log history entries for the dump.

Levelnumber is greater than or equal to 2 o'clock, representing the Levelnumber log history entry for 2.

Alter session SET Events ' immediate trace name loghist level 4 '; --Represents a dump of 16 log history items.

7, File_hdrs event: Dump all data file header information

Alter session SET Events ' immediate trace name File_hdrs Level 1 '; --A control file item that represents the head of all data files on dump.

Alter session SET Events ' immediate trace name File_hdrs Level 2 '; --Represents a common file header for all data files in dump.

Alter session SET Events ' immediate trace name File_hdrs level 10 '; --Represents the full file header for all data files in dump.

8, Errorstack event: Dump error stack information, usually when Oracle error, the foreground process will get an error message, but in some cases can not get the error message, this is the way to get Oracle errors.

Alter session SET Events ' 604 Trace name Errorstack forever '; --Represents the dump error stack and the process stack when a 604 error occurs.

9, systemstate event: Dump all system State and process state

Alter session SET Events ' immediate trace name systemstate level 10 '; --Represents dump all system state and process state.