Oracle Tech Zone

Home > Knowledge, Tuning > alter system set event和set events的区别

alter system set event和set events的区别

June 12th, 2009 Romeo Leave a comment Go to comments

今天按照Oracle Support的建议设置event的时候发现set event和set events这两种写法效果不一样，防止忘记先记下来。

1. alter system set event ：此方法对spfile生效，无法对memory生效

SQL&gt; ALTER system SET event='1461 trace name errorstack level 3';
ALTER system SET event='1461 trace name errorstack level 3'
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
 
SQL&gt; ALTER system SET event='1461 trace name errorstack level 3' scope=spfile;
System altered.

验证是否已成功更改：

SQL&gt; CREATE pfile='/home/oracle/1.ora' FROM spfile;
File created.
SQL&gt; !

[oracle@rac01 ~]$ grep event 1.ora
*.event='1461 trace name errorstack level 3'

这种方法的话，需要重启DB。下面Rollback这个参数更改

SQL&gt; ALTER system SET event='1461 trace name errorstack off' scope=spfile;
System altered.
SQL&gt; CREATE pfile='/home/oracle/2.ora' FROM spfile;
File created.
SQL&gt; !

[oracle@rac01 ~]$ grep event 2.ora
*.event='1461 trace name errorstack off'

2. alter system set events：此方法对memory生效，无法对spfile生效

SQL&gt; ALTER system SET events '1461 trace name errorstack level 3';
 
System altered.
SQL&gt; !

验证：

[oracle@rac01 ~]$ tail -n1 /oracle/admin/strm1/bdump/alert_strm1.log
OS Pid: 9070 executed alter system set events '1461 trace name errorstack level 3'

这个参数更改没有反应到spfile中

SQL&gt; CREATE pfile='/home/oracle/3.ora' FROM spfile;
File created.
SQL&gt; !

[oracle@rac01 ~]$ grep event 3.ora
*.event='1461 trace name errorstack off'

其实这里的1461是个BUG 6085625，Metalink上可以参照NOTE: 461911.1

另外需要注意的是，RAC环境中用方法2动态设置event的话需要在所有instance设置。不过理解了上面的区别的话这个也就很容易明白了。

注意：设置1461 event无法对已经存在的进程生效，对于设置之后新建立的进程才会有效果。所以，不得不，我们还是得重启DB，让其对所有进程生效。

Categories: Knowledge, Tuning Tags: 区别, set event, set events

Comments (3) Leave a comment

ahuzl

March 15th, 2010 at 17:53 | #1

Reply | Quote

thanks for your information, it is very helpful.
I wanna take this good document to my blog,may I?
Romeo

March 15th, 2010 at 18:23 | #2

Reply | Quote

@ahuzl
Of course you can.
hansen

July 15th, 2010 at 14:13 | #3

Reply | Quote

其实这个旧在8i环境中就有指出，可以利用 alter system set events来设定系统事件上，直接有效，也可以在pfile利用提供的参数 event来设定，重启生效。你这上面的和它是一个意思，这是oracle的规定来的。