FYI: 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 & APPLICATION Useful for Oracle Support and experienced DBAs. 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) The only 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 the 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. 2e) The concatenation of event specifications with ':' into one line also works for the text parameter file, but is rarely used. 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 "*" has to be an instance name in a Real Application Cluster configuration. 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.
================================
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) The only 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 the 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. 2e) The concatenation of event specifications with ':' into one line also works for the text parameter file, but is rarely used. 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 "*" has to be an instance name in a Real Application Cluster configuration. 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.
解决方案 »
免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货