ORACLE event tracking

There are three types of tracking files: 1: Warning log files. Activities during database startup, running, and shutdown. When a problem occurs in the database, you must first check the file named alert_sid.log2: Background trace file. Records the running status of the oracle background process, named sid_processname_processid.t

There are three types of tracking files: 1: Warning log files. Activities during database startup, running, and shutdown. When a problem occurs in the database, you must first view the file named alert_sid.log 2: Background trace file. Records the running status of the oracle background process, named sid_processname_processid.t

Tracking File

There are three types of Oracle tracking files:

1: Warning log file. Activities during database startup, running, and shutdown. When a problem occurs in the database, first check the file named alert_sid.log.

2: background tracking file. The running status of the oracle background process is recorded. The name is 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 events

Setting a trail event causes oracle to dump information related to the event to the trail file. Based on the method of setting events, tracking events can be divided into two categories: PROCESS-EVENT (events set by initializing parameter files) SESSION-EVENT (the time set by alter session set events or alter system set events ). To make the PROCESS-EVENT take effect, you must restart the database instance. The PROCESS-EVENT affects every user PROCESS connected to the database. You can use the ":" separator to set multiple events in the parameter file, for example, event = "event1: event2: event3 ". You can also set multiple events in sequence, for example:

Event = "event1"

Event = "event2" (note that no other parameter exists between event1 and event2; otherwise, only the last event, that is, event2, takes effect ).

Oracle generates different behaviors for different tracking events. Based on Different behaviors, We can roughly classify tracking events into four categories:

1. Dump a part of the content in oracle, such as data blocks and control files. The dump activity is executed when the trace event command is received. Such events cannot be set through parameter files.

2. trace error information. When this type of event is set, oracle dumps stack information for a specific error to the trace file.

3. Change the execution path of oracle. Such events will affect the execution path of the oracle code segment. For example, the 10269 event will cause the smon process to stop merging the free-space.

4. Trace process information. For example, 10046 events and 10053 events. This type of event dumps specific information in the process context to the trace file.

Syntax format

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

Through the symbol, you can set multiple events consecutively, or you can use alter session set events consecutively to set multiple events.

Format description: eventnumber indicates the event number that triggers dump. The event number can be an Oracle error number or an internal event number of oralce. The internal event number is between 10000 and 10999 and cannot be used with the immediate keyword; the immediate keyword indicates that the specified structure is immediately dumped to the trace file after the command is issued. This keyword is only used in the alter session Statement and cannot be used with the eventnumber or forever keyword. Trace name is a keyword. Eventname refers to the event name (see the following section), that is, the actual structure name for dump. If eventname is context, internal event numbers are used for tracking. The forever keyword indicates that the event remains valid during the instance or session period and cannot be used with immediate. Level is the event-level keyword. But the error stack (errorstack) does not have a level. Levelnumber indicates the event level number. Generally, from 1 to 10, 1 indicates only the dump structure header information, and 10 indicates all the information of the dump structure.

The event set by alter session is only valid for the current session. For the event set by alter system, except for the current session, the event is equally valid for the newly logged-on session, but the session opened before the alter system command is executed is invalid.

IMMEDIATE-related events

1. Memory Dumps

1). Global Area

Alter session set events 'immediate trace name global_area level n ';

1 Including PGA

2 contains SGA

4 include 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 include object handle

4 contains the 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 the 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. This command can be used to dump all versions of a block in the buffer.

6). Heap

Alter session set events 'immediate trace name heapdump level level ';

1 PGA Summary

2 SGA Abstract

4 UGA Summary

8 Current call (CGA) Abstract

16 User call (CGA) Abstract

32 Large call (LGA) Abstract

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 9.0.1

Alter session set events 'immediate trace name heapdump_addr level n ';

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

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

Versions later than oracle 9.2.0

Alter session set events 'immediate trace name heapdump_addr level n, addr m ';

M is the subheap address.

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

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

3. Workarea Table details

13). Latches

Alter session set events 'immediate trace name latches level n ';

1. latch Information

2. Statistics

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 ';

N is 1 ~ 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 ';

Rdba whose n is 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 index structure

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 = 'index name.

3). view the rollback Segment Header in the Undo Segment Header.

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. File Header Information in the control file

2 level 1 + general file header information

3 level 2 + complete data file header information

10 level 3

6). Control file

Alter session set events 'immediate trace name controlf level n ';

1. File Header Information in the control 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. redo log information in the control file

2 level 1 + general file header information

3 level 2 + complete 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;

See: http://blog.csdn.net/yidian815/article/details/12568027 for details

9). Loghist

Alter session set events 'immediate trace name loghist level n ';

N = 1 dump control file's earliest and latest log history items

N> 1 dump 2 ^ n log history items

Obtain the diagnosis event number

Most diagnostic events are in the range of 10000 to 10999. You can use the following script to view all diagnostic events:

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; 

Related PLSQL packages

DBMS_MONITOR package

Subprogram	Description
CLIENT_ID_STAT_DISABLE Procedure	Disables statistic gathering previusly enabled for a given Client Identifier
CLIENT_ID_STAT_ENABLE Procedure	Enables statistic gathering for a given Client Identifier
CLIENT_ID_TRACE_DISABLE Procedure	Disables the trace previusly enabled for a given Client Identifier globally for the database
CLIENT_ID_TRACE_ENABLE Procedure	Enables the trace for a given Client Identifier globally for the database
DATABASE_TRACE_DISABLE Procedure	Disables SQL trace for the whole database or a specific instance
DATABASE_TRACE_ENABLE Procedure	Enables SQL trace for the whole database or a specific instance
SERV_MOD_ACT_STAT_DISABLE Procedure	Disables statistic gathering enabled for a given combination of Service Name,MODULEAndACTION
SERV_MOD_ACT_STAT_ENABLE Procedure	Enables statistic gathering for a given combination of Service Name,MODULEAndACTION
SERV_MOD_ACT_TRACE_DISABLE Procedure	Disables the trace for ALL enabled instances for a or a given combination of Service Name,MODULEAndACTIONName globally
SERV_MOD_ACT_TRACE_ENABLE Procedure	Enables SQL tracing for a given combination of Service Name,MODULEAndACTIONGlobally unlessinstance_nameIs specified
SESSION_TRACE_DISABLE Procedure	Disables the previusly enabled trace for a given database session identifier (SID) on the local instance
SESSION_TRACE_ENABLE Procedure	Enables the trace for a given database session identifier (SID) on the local instance

This package also includes several views: v $ client_stats \V$SERV_MOD_ACT_STATS。

Client_id can be set through DBMS_SESSION.SET_IDENTIFIER.

DBMS_SYSTEM package

For more information about this package, see: http://blog.csdn.net/yidian815/article/details/12580659

In addition, we can also use the ORADEBUG tool. For example

Set a diagnostic event for a process:

Oradebug event trace name context forever, LEVEL level

Disable the configured diagnostic event:

ORADEBUG EVENT TRACE NAME CONTEXT OFF

Set a diagnostic event for a session:

ORADEBUG SESSION_EVENT trace name context forever, LEVEL level

Disable the configured diagnostic event:

ORADEBUG SESSION_EVENT TRACE NAME CONTEXT OFF

View the events set for the current SESSION

SET SERVEROUTPUT ONDECLAREl_level NUMBER;BEGINFOR l_event IN 10000..10999LOOPdbms_system.read_ev (l_event,l_level);IF l_level > 0 THENdbms_output.put_line ('Event '||TO_CHAR (l_event)||' is set at level '||TO_CHAR (l_level));END IF;END LOOP;END;

Other considerations

Determining the diagnostic event used is valid for the current Oracle database version: because the meaning and usage of diagnostic events vary with Oracle databases of different versions.

Determine the level that the diagnostic event can be set. In many cases, different levels affect the behavior represented by the Diagnostic event.

Any diagnostic events set in the INITSID. ORA file must be commented out or canceled in advance if the Oracle database needs to be upgraded.