Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to understand ORACLE event tracking. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

ORACLE event tracking

There are three types of Oracle trace files:

1: warning log file. Record the activity when the database starts, runs, and shuts down. When there is a problem with the database, first look at the file, which is called alert_sid.log

2: background tracking file. Records the running status of the oracle background process, which is named sid_processname_processid.trc and the storage directory is BACKGROUND_dump_dest

3: user tracking file. The running information of the user process connected to the database is recorded, the name is SID_ORA_PROCESSID.TRC, and the storage directory is USER_DUMP_DEST.

In oracle11g, these three files are located in the same directory:

Tracking event

Setting a trace event causes oracle to dump information related to that event into a trace file. Depending on how events are set, trace events can be divided into two categories: PROCESS-EVENT (events set through initialization parameter files) and SESSION-EVENT (times set through alter session set events or alter system set events). For the PROCESS-EVENT event to take effect, the database instance must be restarted, and the PROCESS-EVENT event affects every user process that connects to the database. Multiple events can be set in the parameter file with the ":" delimiter, such as event= "event1:event2:event3". You can also set multiple event in turn, such as:

Event= "event1"

Event= "event2"

Note that there are no other parameters between event1 and event2, otherwise only the last event, event2, will take effect.

Oracle produces different behaviors for different tracking events. According to the different behaviors, we can roughly classify tracking events into four categories:

1. Dump some part of the oracle, such as data blocks, control file contents, and so on. The dump activity is executed at the same time as the trace event command is received. Such events cannot be set through parameter files.

two。 Track error messages. By setting such events, oracle dumps the stack information for a specific error into the trace file.

3. Change the execution path of oracle. Such events affect the execution path of oracle code snippets, such as the 10269 event, which causes the smon process to stop merging with free-space.

4. Track information about the process. For example, 10046 events and 10053 events. This type of event dumps specific information in the process context into a trace file.

Grammatical format

Alter system | session set events'[eventnumber | immediate] trace nameeventname [forever] [, level levelnumber]:. .s

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 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 that immediately after the command is issued, the specified structure is dump to the tracking file, this keyword is only used in the alter session statement, 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.

Events set by alter session are only valid for the current session. Events set by alter system are also valid for newly logged in session in addition to the current session, but are not valid for session that was opened before the alter system command was executed.

IMMEDIATE related events

I. Memory Dumps

1) .Global Area

Alter SESSION SET EVENTS 'immediate trace name global_area level n'

1 contains PGA

2 contains SGA

4 including UGA

8 contains indrect memory

2). Library Cache

Alter SESSION SET EVENTS 'immediate trace name library_cache level n'

1 library cache Statistics

2 contains hash table histogram

3 including object handle

4 contains object structure (Heap 0)

3) .Row Cache

Alter SESSION SET EVENTS 'immediate trace name row_cache level n'

1 row cache Statistics

2 contains hash table histogram

8 contains object structure

4). Buffers

Alter SESSION SET EVENTS 'immediate trace name buffers level n'

1 buffer header

2 level 1 + block header

3 level 2 + block contents

4 level 1 + hash chain

5 level 2 + hash chain

6 level 3 + hash chain

8 level 4 + users/waiters

9 level 5 + users/waiters

10 level 6 + users/waiters

5). Buffer

Alter SESSION SET EVENTS 'immediate trace name buffer level n'

N is the rdba of a specified block, and this command dumps all versions of a block in buffer.

6). Heap

Alter SESSION SET EVENTS 'immediate trace name heapdump level level'

1 PGA Summary

2 SGA Summary

4 UGA Summary

8 Current call (CGA) Summary

16 User call (CGA) Summary

32 Large call (LGA) Summary

1025 PGA content

2050 SGA content

4100 UGA content

8200 Current call content

16400 User call content

32800 Large call content

7) .Sub Heap

Before oracle version 9.0.1

Alter SESSION SET EVENTS 'immediate trace name heapdump_addr level n'

If n is the address of subheap, the summary information of subheap is dumped.

If n is the address of subheap + 1, the content of subheap is dumped.

After oracle version 9.2.0

Alter SESSION SET EVENTS 'immediate trace name heapdump_addr level n, addr m'

Where m is the address of subheap

N is the summary of 1 dump subheap, n is 2 dump subheap content

8) .Process State

Alter SESSION SET EVENTS 'immediate trace name processstate level n'

9). System State

Alter SESSION SET EVENTS 'immediate trace name systemstate level n'

10) .Error State

Alter SESSION SET EVENTS 'immediate trace name errorstack level n'

0 Error stack

1 level 0 + function call stack

2 level 1 + process state

3 level 2 + context area

11). Hang Analysis

Alter SESSION SET EVENTS 'immediate trace name hanganalyze level n'

12) .Work Area

Alter SESSION SET EVENTS 'immediate trace name workareatab_dump level n'

1 SGA Information

2 Workarea Table summary information

3 Workarea Table details

13). Latches

Alter SESSION SET EVENTS 'immediate trace name latches level n'

1 latch Information

2 Statistical information

14). Events

Alter SESSION SET EVENTS 'immediate trace name events level n'

1 session

2 process

3 system

15). Locks

Alter SESSION SET EVENTS 'immediate trace name locks level n'

16). Shared Server Process

Alter SESSION SET EVENTS 'immediate trace name shared_server_state level n'

The value of n is 1 to 14

17). Background Messages

Alter SESSION SET EVENTS 'immediate trace name bg_messages level n'

N is pid+1

II. File Dumps

1) .Block

Before oracle 7

Alter SESSION SET EVENTS 'immediate trace name blockdump level n'

N is the rdba of block

After oracle8

Alter SYSTEM DUMP DATAFILE file# BLOCK block#

Alter SYSTEM DUMP DATAFILE file#

BLOCK MIN minimum_block#

BLOCK MAX maximum_block#

2). Tree Dump to view the structure of the index

Alter SESSION SET EVENTS 'immediate trace name treedump level n'

N is object_id

Object_id can be obtained from select object_id from user_objects where object_name = 'name of the index'.

3) .Undo Segment Header to view rollback segment header information

Alter SYSTEM DUMP UNDO_HEADER 'segment_name'

4). Undo for a Transaction

Alter SYSTEM DUMP UNDO BLOCK 'segment_name' XID xidusn xidslot xidsqn

5). File Header

Alter SESSION SET EVENTS 'immediate trace name file_hdrs level n'

1 controls the header information in the file

2 level 1 + generic file header information

3 level 2 + data complete file header information

10 level 3

6). Control file

Alter SESSION SET EVENTS 'immediate trace name controlf level n'

1 controls the header information in the file

2 level 1 + database information + checkpoint information

3 level 2 + reusable section information

10 level 3

7) .Redo log Header

Alter SESSION SET EVENTS 'immediate trace name redohdr level n'

1 control the redo log information in the file

2 level 1 + generic file header information

3 level 2 + full log file header information

10 level 3

8) .Redo log

Alter SYSTEM DUMP LOGFILE 'FileName'

Alter SYSTEM DUMP LOGFILE 'FileName'

SCN MIN MinimumSCN

SCN MAX MaximumSCN

TIME MIN MinimumTime

TIME MAX MaximumTime

LAYER Layer

OPCODE Opcode

DBA MIN FileNumber. BlockNumber

DBA MAX FileNumber. BlockNumber

RBA MIN LogFileSequenceNumber. BlockNumber

RBA MAX LogFileSequenceNumber. BlockNumber

Where time = (yyyy-1988) * 12 + mm-1) * 31 + dd-1) * 24 + hh) * 60 + mi) * 60 + ss

For more information, please see: http://blog.csdn.net/yidian815/article/details/12568027

9). Loghist

Alter SESSION SET EVENTS 'immediate trace name loghist level n'

The earliest and latest log history entries in the dump control file

N > 1 dump 2 ^ n log history entries

Get diagnostic event number

Most diagnostic events are in the range of 10000 to 10999, and all diagnostic events can be viewed using the following script:

[sql]

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

/

The above is the editor for you to share how to understand ORACLE event tracking, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.