Shulou(Shulou.com)06/01 Report--

1. Oracle tracking file

There are three types of Oracle trace files. One is the background alarm log file, which records the activities of background processes during database startup, shutdown and operation, such as tablespace creation, rollback segment creation, some alter commands, log switching, error messages, and so on. In the event of a database failure, you should first view the file, but the information in the file is not necessarily related to 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 background trace files created by background processes such as DBWR, LGWR, SMON, and so on. The background trace file is generated according to the running condition of the background process, and the background trace file is also saved in the directory specified by the BACKGROUND_DUMP_DEST parameter, and the file format is siddbwr.trc, sidsmon.trc and so on. There is also a type of user trace file generated by a user process (Server Processes) connected to Oracle. These files are generated only when errors are encountered during the user session. In addition, the user can generate this type of file by executing the oracle trace event (see below). The user trace file is saved in the directory specified by the USER_DUMP_DEST parameter, and the file format is oraxxxxx.trc,xxxxx, which is the process number (or thread number) that created the file.

2. Oracle tracking events

Oracle provides a class of commands that can dump the information contained in various internal structures of Oracle into a tracking file, so that users can solve various failures according to the contents of the file. There are two ways to set trace events. One is to set events in the init.ora file, so that all sessions will be affected after the open database. The format is as follows:

EVENT= "eventnumber trace name eventname [forever,] [level levelnumber]:."

Through the: symbol, you can set multiple events sequentially, or you can set multiple events by using event in succession.

Another way is to use the alter session set events command during the session, which only affects the current session. The format is as follows:

Alter session set events'[eventnumber | immediate] trace name eventname [forever] [, level levelnumber]:.

Through the: symbol, you can set multiple events sequentially, or you can set multiple events by using alter session set events in succession.

Format description: eventnumber refers to the event number that triggers dump. The event number can be Oracle error number (tracking the specified event when a corresponding error occurs) or oralce internal event number. The internal event number is between 10000 and 10999 and cannot be used with the immediate keyword.

The immediate keyword means to dump the specified structure to the trace file immediately after the command is issued. This keyword is used only in alter session statements and cannot be used with the eventnumber or forever keywords.

Trace name is the keyword.

Eventname refers to the name of the event (see below), that is, the actual structure name of the dump to be performed. If eventname is context, the trace is based on the internal event number.

The forever keyword indicates that the event remains in a valid state during the cycle of the instance or session and cannot be used with immediate.

Level is the event level keyword. However, there is no level in the dump error stack (errorstack).

Levelnumber represents the event level number. Generally, from 1 to 10, 1 represents only the header information of the dump structure, and 10 represents all the information of the dump structure.

1. Buffers event: the db buffer structure in the dump SGA buffer

Alter session set events' immediate trace name buffers level 1 buffer;-- represents the header of the dump buffer.

2. Blockdump events: dump data file, index file, rollback segment file structure

Alter session set events' immediate trace name blockdump level 66666blocks;-represents a data block with an address of 6666 for the dump block.

After Oracle 8, the command has been changed to:

Alter system dump datafile 11 block 9;-- indicates that the dump data file number is the ninth block in 11.

3. Controlf event: dump control file structure

Alter session set events' immediate trace name controlf level 10 files;-- represents all the contents of the dump control file.

4. Locks event: lock information of the dump LCK process

Alter session set events' immediate trace name locks level 5'

5. Redohdr event: header information of dump redo log

Alter session set events' immediate trace name redohdr level 1 entries;-- represents the control file entry in the dump redo log header.

Alter session set events' immediate trace name redohdr level 2 entries;-- represents the generic file header of the dump redo log.

Alter session set events' immediate trace name redohdr level 10 entries;-- represents the complete header of the dump redo log.

Note: the contents of the redo log dump can use 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 entries;-- indicates only the earliest and latest log history entries of dump.

When levelnumber is greater than or equal to 2, it represents 2 to the levelnumber power of log history entries.

Alter session set events' immediate trace name loghist level 4 entries;-represents 16 log history entries for dump.

7. File_hdrs event: header information of all dump data files

Alter session set events' immediate trace name file_hdrs level 1 header;-- represents the control file entry for all dump data file headers.

Alter session set events' immediate trace name file_hdrs level 2 files;-- represents the common header of all dump data files.

Alter session set events' immediate trace name file_hdrs level 10 files;-- represents the complete header of all dump data files.

8. Errorstack event: dump error stack information. Usually, when an error occurs in Oracle, the foreground process will get an error message, but in some cases it will not get the error message, so you can get the Oracle error in this way.

Alter session set events' 604 trace name errorstack forever';-indicates the dump error stack and 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 states;-- represents all system and process states of the dump.

10. Coalesec event: dump specifies a free interval in the tablespace

When levelnumber is expressed in hexadecimal, two high-order bytes represent the number of free intervals, and two low-order bytes represent table space numbers. For example, 0x00050000 represents five free intervals in the table space of the dump system, which translates to 327680 in decimal, that is:

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 events: information in dump PGA, SGA, UGA

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: used to monitor transaction recovery

2. 10015: dump the UNDO SEGMENT header

Event = "10015 trace name context forever"

3. 10029: used to give login information during the session

4. 10030: used to give logout information during the session

5. 10032: statistics of dump sort

6. 10033: statistics on the growth of dump sorting

7. 10045: track Freelist management operations

8. 10046: trace SQL statements

Alter session set events' 10046 trace name context forever, level 4 variables;-- tracks SQL statements and displays binding variables

Alter session set events' 10046 trace name context forever, level 8 minutes;-- tracks SQL statements and displays wait events

9. 10053: dump optimization strategy

10, 10059: simulate creation and cleanup errors in redo logs

11, 10061: prevent 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 rest and recuperation information

16, 10200: dump consistent read information

17. 10201: Undo application in dump consistency reading

18, 10209: allow errors to be simulated in control files

19, 10210: triggers a block check event

Event = "10210 trace name context forever, level 10"

20, 10211: trigger index check event

21, 10213: the simulation crashes after writing the control file

22, 10214: simulate write errors in the control file

Levelnumber from 1 to 9 indicates the block number that caused the error. If it is greater than or equal to 10, each control file will make an error.

23, 10215: simulation of read errors in control files

24, 10220: dump Undo header changes

25,10221; dump Undo changes

26, 10224: separation and deletion of dump indexes

27, 10225: dump changes in intervals based on dictionary management

28, 10229: imitate the Icano error on the data file

29, 10231: set to ignore corrupted blocks during a full table scan

Alter session set events' 10231 trace name context off';-turns off block checking during a session

Event = "10231 trace name context forever, level 10"-- checks the blocks of data that any process reads into SGA

30, 10232: dump blocks that are set to soft corruption (when DBMS_REPAIR package setting or DB_BLOCK_CHECKING is TRUE) to the trace file

31, 10235: for memory heap check

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 the MTS process

36, 10252: analog write data file header error

37, 10253: error simulating writing redo log file

38, 10262: memory leaks when connections are allowed

Alter session set events' 10262 trace name context forever, level 300 bytes;-- allows for memory leaks of 300 bytes

39, 10270: dump shared cursors

40, 10285: simulation control file header damage

41, 10286: simulation control file open error

42, 10287: simulation archiving error

43, 10357: debug the direct path mechanism

44, 10500: track the SMON process

45, 10608: track the creation of bitmap indexes

46, 10704: tracking enqueues

47, 10706: track global enqueues

48, 10708: buffer cache tracking RAC

49, 10710: track access to bitmap indexes

50, 10711: track bitmap index merge operations

51, 10712: track bitmap index OR operations

52, 10713: track bitmap index AND operations

53, 10714: track bitmap index MINUS operations

54, 10715: track the conversion of bitmap indexes to ROWID

55, 10716: track the compression and decompression of bitmap indexes

56, 10719: track changes to bitmap indexes

57, 10731: trace cursor declaration

58, 10928: track PL/SQL execution

59, 10938: dump PL/SQL execution statistics

Finally, the above syntax may change due to different versions, but most of them are available.

Complete tracking event list is attached, 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 session; query the sid,serial# of session

2.exec dbms_system.set_sql_trace_in_session (141focus 6 true); start tracking

3.exec dbms_system.set_sql_trace_in_session (141focus 6 true) stop tracking

4.tkprof .trc .txt

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

2.exec dbms_system.set_sql_trace_in_session (141focus 6 true); start tracking

3.exec dbms_system.set_sql_trace_in_session (141focus 6 true) stop tracking

4.tkprof .trc .txt

5. View Fil

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.