How to set EVENTS in the SPFILE


Purpose
=======

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

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 only 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.

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

2b) The instance must have been started with an SPFILE:

      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

2d) The command can be performed with the instance in NOMOUNT state.
    You can set events without having to mount nor open 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.

Note the different syntax: 

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

Related Documents
=================

Note 1051056.6 How to set multiple events in Init.Ora
Note 168042.1  ORA-2194 Starting up the Database

References
==========

Bug 2942335 ALTER SYSTEM RESET EVENT SCOPE=SPFILE SID='*' NOT RESETING EVENTS
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: