【MOS】关于events的几篇MOS(75713.1、160178.1、436036.1、1051056.6)


How To List All The Named Events Set For A Database (文档 ID 436036.1)

In this Document


Goal

Solution

References


APPLIES TO:

Oracle Database - Enterprise Edition - Version 8.0.3.0 and later  
 Information in this document applies to any platform.  
 ***Checked for relevance on 06-Aug-2016***

GOAL

Oracle Internal Events, both named event (eg: ERRORSTACK) as numeric events (eg 10046) can be set at the session level. This document outlines how DataBase Administrators can verify which events have been set in a specific session or in the entire system.

SOLUTION

This can be achieved through the ORADEBUG functionality. The exact syntax to use depends on the Oracle version you are using. 

If you want to retrieve the events set in a specific session, then use the following oradebug commands to identify the session:

1. identify the session  

   SQL> select p.pid, p.spid, s.username  
        from v$process p, v$session s  
        where p.addr = s.paddr;  


2. Attach to the desired session  

   SQL> connect / as sysdba
   SQL> oradebug setorapid  
   - OR -  
   SQL> oradebug setospid  
   ...  

If you want to retrieve the events set in your current SESSION or at the SYSTEM level, you can use the following commands:

SQL> connect / as sysdba 
SQL> oradebug setmypid 
...  

 

Oracle 8i / 9i 

SQL> oradebug dump events  

Level 1 - Dump session group's event settings  
Level 2 - Dump process group's event settings  
Level 4 - Dump system group's event settings (Ie the instance wide events)   

 

Oracle 10g / 11g and above

SQL> oradebug eventdump  

session - Dump session group's event settings  
process - Dump process group's event settings  
system  - Dump system group's event settings(Ie the instance wide events)

Note: level needs to be specified in lowercase !  

             -- OR --

SQL> alter session set events 'immediate eventdump()'; 

SESSION - Dump session group's event settings 
PROCESS - Dump process group's event settings   
SYSTEM  - Dump system group's event settings(Ie the instance wide events)  

 

Example 1:  Oracle 9i - system events

This example illustrates how to retrieve the events set at the system level in an Oracle 9i database.

SQL> alter system set events '942 trace name errorstack level 3';   
Session altered.  

After the event is set we dump the events information

SQL> oradebug setmypid   
Statement processed.   
SQL> oradebug dump events 4;   
Statement processed.   
SQL> oradebug tracefile_name   
/u01/oracle/product/9.2.0/admin/ORCL/udump/ORCL_ora_2620.trc  

The following information will be dumped in the in the trace file ORCL_ora_2620.trc.

1. *** 2007-06-13 20:12:11.421   
2. Dump event group for level SYSTEM   
3. TC Addr Evt#(b10) Action TR Addr Arm Life   
4. 78B6584    942    1 78b65c4 0 0   
5. TR Name TR level TR address TR arm TR life TR type   
6.    ERRORSTACK 3    0 1 2 1961960  

Notice the line 2 "Dump event group for level SYSTEM" which tells you which level was used to dump the information. In our case level 1 was used to dump the session information.

In line 4 the second column value tells us the event that was set, in our case 942.

In line 6 the first column tells us the name of the event, in our case the named event "ERRORSTACK" appears. On the same line check the second column, the determines the level at which the event was set, in our case "3"

Hence from the above information was can conclude that the event "942 trace name ERRORSTACK level 3" was set at the system level

 

Example 2: Oracle 11g - events set at session user SCOTT

This example illustrates how to retrieve the events user SCOTT has set at his session in an Oracle 11g database: 

SQL> select p.pid, p.spid, s.username   
           from v$process p, v$session s   
           where p.addr = s.paddr;   

For example:   

PID SPID USERNAME   
---- ------- ---------   
15 28390 SCOTT  

Attach to the above identified session:

SQL> connect / as sysdba   
SQL> oradebug setorapid 15   
--OR--   
SQL> oradebug setospid 28390   
Unix process pid: 32493, image: oracle@jschelfh-pc (TNS V1-V3)  

Get the event information:

SQL> oradebug eventdump session   
942 trace name ERRORSTACK level 3  

 

Example 3: Oracle 11g - events set at your current session 

This example illustrates how to retrieve the events set in your current session environment in an Oracle 11g database:

SQL> alter system set events '942 trace name errorstack level 3';   
Session altered.   
SQL> oradebug tracefile_name   
/oracle/admin/L111/udump/l112_ora_32493.trc  

The following information will be dumped in the in the trace file l112_ora_32493.trc

*** ACTION NAME:() 2009-03-06 12:48:52.533   

Dumping Event (group=SYSTEM)   
942 trace name errorstack level 3  

 







Important Customer information about using Numeric Events (文档 ID 75713.1)

Important Customer Information about numeric EVENTS

This short note gives important information for any customer wanting to either:  

  A. Add an EVENT="..." line to their initialization file startup parameters
 
or
  B. Use an ALTER SESSION / SYSTEM SET EVENTS='...'; command

 Please read the information here before setting any event. If you are not sure about an EVENT then contact Oracle Support. The exact syntax to be used between the quotation marks should be given to you either from Oracle Support or from the article which directed you to this Note.

What is an EVENT ?

An   EVENT   is a special item used by the Oracle server to do one of the following:

  • Change behaviour
  • Enable collection of trace or debug information
  • Enable additional error checking or similar

When can I set an EVENT ?

You should only ever set an EVENT if:

  • Directed to do so by Oracle Support Services
  • Directed to do so by an article or bulletin which applies to the Oracle RDBMS version that you are using AND you understand what the EVENT is going to change.

It is extremely important that you understand the effects of any event that you set. If you are not sure contact Oracle Support Services.

Other IMPORTANT considerations

1) Make sure the event is valid for the Oracle release you are running  
 The behaviour of an event can change between Oracle releases.  
 For example: An event to disable a feature in one release may force a corruption in a different release.

2) Make sure you know the LEVEL that an event should be set at.
In many cases the LEVEL of an event affects the behaviour of the event. A typical event syntax is:

	EVENT="12345 trace name context forever, level 99"

 In this example the   EVENT   is   12345   and the   LEVEL   is   99  .

3. If a problem occurs when an event is set then it is worth seeing if the same problem reproduces without the event set. This is because events can enable code specific paths which are not normally used.

4. Any events set in the initSID.ora file should be re-assessed prior to any upgrade. This is important as the EVENT may have a different meaning in the release you wish to upgrade to.

Setting an EVENT in INIT.ORA

The syntax to set an event in the   init  SID  .ora file should be specified in the document which tells you to set the event. If not then contact Oracle Support.

Note that ALL events in the initSID.ora file should be grouped together on successive lines. 

For example: If you have to set event 10046 and event 10053 then
             put the entries on adjacent lines in the initSID.ora thus:

	       EVENT="10046 trace name context forever, level 12"
	       EVENT="10053 trace name context forever, level 1"

 It is sensible to always add a comment against any EVENT in the   init  SID  .ora file describing:

  • WHAT the event is for
  • WHY the event has been added
  • WHEN the event was added / by whom / and what the Oracle version was at the time
  • WHERE the information about the event came from.

When removing an event from the init.ora file it is sensible to comment it out with details of WHEN / WHERE / WHY it was removed rather than deleting the lines.

Customers using an spfile should follow the instructions in Note: 160178.1 'How to set EVENTS in the SPFILE'

Disabling an active EVENT

Some events are active for the duration of the session or instance up time. These are normally established using the syntax of '  context forever  '.

For example, when set in the current session, the following event will remain active until the session is terminated:

  ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

 Similarly, when set SYSTEM wide, the following event will remain active until the instance is shutdown:  

  ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 12';

 However, the event can be disabled at any time using the following syntax:-

  • For the current session :  
    ALTER SESSION SET EVENTS '10046 trace name context off';
  • SYSTEM wide:  
    ALTER SYSTEM SET EVENTS '10046 trace name context off';

Hence the   forever   keyword is replaced by the   off   keyword to disable the given event.

Note that enabling or disabling a SYSTEM wide event will only take effect for new connections and will not impact existing connections. For this reason it is only useful for restartable background processes.

Related Documents

Note: 160178.1   'How to set EVENTS in the SPFILE'  

How to Set Multiple Events in INIT.ORA (文档 ID 1051056.6)
 ***Checked for relevance on 03-Feb-2012***



PURPOSE
-------

How do you set multiple events in the init.ora file?
 

OVERVIEW
--------

If you have to set more than one event in the init.ora, how can 
you specify multiple events on the same line?


HOW TO SET MULTIPLE EVENTS IN INIT.ORA FILE
-------------------------------------------

The syntax to specify multiple events in the init.ora is:

    event=":: : "

you can also split the events on multiple lines by using the continuation
"\" backslash character at the end of each event and continue the next
event on the next line.

Such As:
 
    event=":\
    :\
    : \
    "


For Example:

    event="\
    10210 trace name context forever, level 10:\
    10211 trace name context forever, level 10:\
    10231 trace name context forever, level 10:\
    10232 trace name context forever, level 10"

After setting the events, bounce the instance and be sure to check
the alert.log and verify if the events are in effect.

The alert.log should show the events that are in effect, for example:

System parameters with non-default values:
  ...
  event = "10210 trace name context forever, level 10:10211 trace name 
  context forever, level 10:10231 trace name context forever, level 
  10:10232 trace name context forever, level 10"
  ...


It is also possible to set multiple events in multiple subsequent lines in the init.ora, e.g.

event="10210 trace name context forever, level 10"
event="10211 trace name context forever, level 10"
event="10231 trace name context forever, level 10"

Note that using the separate lines it is essential to set the events on subsequent lines.
Otherwise, only the last event will be used due to the generic parsing of the init.ora values.


NOTE:
=====
For current Oracle versions. e.g. 11g, you can also use the next commands 
to list the set event in a trace file:

SQL> alter session set events 'immediate eventdump(session)';

SQL> alter system  set events 'immediate eventdump(system)';

 Please note that from 11g the alter system set events also affects all current sessions, and not only the new ones.  


How To Set EVENTS In The SPFILE (文档 ID 160178.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.0.1.0 and later  
 Information in this document applies to any platform.  
 ***Checked for relevance on 29-Aug-2016***

PURPOSE


This note summarizes the syntax used to set trace and debug events when the server uses the binary server side parameter file (SPFILE).

SCOPE

Useful for Oracle Support and experienced DBAs.

DETAILS

How to Set EVENTS in the SPFILE ?
 

You need to set several events in the parameter file. You can perform this task successfully in the text based parameter file, and need to do the same operation for an instance that uses the new Oracle9i binary server side 
parameter file, also known as the SPFILE.

 

1)  An example of the text based parameter file syntax is:

 

      event="10325 trace name context forever, level 10"
      event="10015 trace name context forever, level 1"

 

    Inserting other parameters between these lines will cause the last 
    event only to be included.

 

2a) One method to set the event in the SPFILE is to use the SQL syntax:

 

     

SQL> ALTER SYSTEM SET   
           EVENT='10325 trace name context forever, level 10',  
           '10015 trace name context forever, level 1'   
           COMMENT='Debug tracing of control and rollback' SCOPE=SPFILE;     

System altered.  

 

You can also, run the below command:

SQL> alter system set event='10325 trace name context forever, level 10:10015 trace name context forever, level 1' scope=spfile;  

 

    The instance must be restarted for the events to take effect.

 

2b) The instance must have been started with an SPFILE.  Otherwise this ALTER command will fail as follows:

 

     

SQL> ALTER SYSTEM SET 
           EVENT='10325 trace name context forever, level 10',
           '10015 trace name context forever, level 1' 
           COMMENT='Debug tracing of control and rollback' SCOPE=SPFILE;

 

      ALTER SYSTEM SET
      *
      ERROR at line 1:
      ORA-32001: write to SPFILE requested but no SPFILE specified at startup

 

2c) You cannot set the event during the instance life:

  SQL> ALTER SYSTEM SET   
           EVENT='10325 trace name context forever, level 10',  
           '10015 trace name context forever, level 1'   
           COMMENT='Debug tracing of control and rollback' SCOPE=BOTH;   
      EVENT='10325 trace name context forever,  
      *  
      ERROR at line 2:  
      ORA-02095: specified initialization parameter cannot be modified  

 

      If you get an error like this, it is necessary to use SCOPE=SPFILE and restart the database.

 

2d) The command can be performed with the instance in NOMOUNT state.
    You can set events without having to open or mount the database.

 

 3) If you need to alter, add or remove an event, you have to enter the whole new list in the ALTER SYSTEM command and restart.

 

 4) To remove all events, use:
           

SQL> ALTER SYSTEM RESET EVENT SCOPE=SPFILE SID='*' ;

System altered.

 

    The asterisk ("*") in this example applies to all cases but Real Application Cluster. In a Real Application Cluster configuration environment the instance name is required instead of the asterisk.

 

 5) To have events set up immediately, typically for dumping or tracing, use:
 

SQL> ALTER SESSION SET EVENTS 'immediate trace name controlf level 2' ;

System altered.

 

 6) To configure a system-wide "triggered" event, use something like

SQL> alter system set events '942 trace name ERRORSTACK level 3';

System altered.

 

 7) To turn off non-immediate system or session events interactively, you can use a syntax like the following:      

SQL> alter system set events '942 trace name ERRORSTACK off';     

System altered.     

SQL> alter system set events='10325 trace name context off';     

System altered.

 

Note the different syntax: 
   
- "SESSION" versus "SYSTEM", "EVENTS" versus "EVENT", and no "," before the "level" keyword.
- Additionally, you can specify each event in a separate ALTER SESSION command.

REFERENCES

BUG:2942335   - ALTER SYSTEM RESET EVENT SCOPE=SPFILE SID='*' NOT RESETING EVENTS  
NOTE:168042.1   - ORA-2194 Starting up the Database  
NOTE:1051056.6   - How to Set Multiple Events in INIT.ORA  
NOTE:436036.1   - How To List All The Named Events Set For A Database