SAP Notes 596423 Events and SPFILE

Symptom

This note describes how to set events when the Oracle database was started with SPFILE.

Other terms

event, events, init.ora, init<SID>.ora, SPFILE, parameter, Oracle 9i, server parameter file

Reason and Prerequisites

You are using Oracle 9i or higher and you are using the SPFILE
(= binary server side parameter file) as parameter file.

Solution

(For further information on SPFILEs, see SAP Note 601157.)

This note gives an overview on how to set events in the database (especially multiple events) when using the SPFILE.

You can use the following command to check whether the instance uses the SPFILE:
show parameter spfile.
  If the check returns a value under VALUE, the instance was started with SPFILE, if not, it was started with init<SID>.ora.
You can already issue the command in the NOMOUNT status.

Previously, events were entered as follows in init<SID>.ora (example):
event="10181 trace name context forever, level 10"
event="10183 trace name context forever, level 1"
If there are other parameters in between, only the last event is executed.

The best way to set events in the SPFILE is to use SQL statements:
SQL> ALTER SYSTEM SET
      EVENT='10181 trace name context forever, level 10',
            '10183 trace name context forever, level 1'
      COMMENT='my comment, user, date'
      SCOPE=SPFILE;

System altered.

To activate the events, you must restart the instance.

The following are possible error messages:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup
--> The Instance was not started with SPFILE (see above)
ORA-0295: specified initialization parameter cannot be modified
--> SPFILE was not specified as SCOPE for this/these event/s.


Note:
1) You can already issue the command in the NOMOUNT status.
2) Events that have already been set are deleted if they are not specified again.

   You can display the events that have already been set with:
     show parameter event
3) A sequence of events is separated with ",".
4) To delete all events, use:

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

System altered.

   For RAC, you must specify the instance name instead of "*".
5) Another option to specify several events is to use
  'event1:event2:...'. However, we no longer recommend this.
   (For further information, see Note 601157.)

Exception:
For events that are executed immediately, for example, dumps or tracing, the syntax is still as follows:

   SQL> ALTER SESSION SET EVENTS='10053 trace name context forever';

Session altered.