今天按照Oracle Support的建议设置event的时候发现set event和set events这两种写法效果不一样,防止忘记先记下来。
1. alter system set event :此方法对spfile生效,无法对memory生效
SQL> 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> ALTER system SET event='1461 trace name errorstack level 3' scope=spfile;
System altered.
验证是否已成功更改:
SQL> CREATE pfile='/home/oracle/1.ora' FROM spfile;
File created.
SQL> !
[oracle@rac01 ~]$ grep event 1.ora *.event='1461 trace name errorstack level 3'
这种方法的话,需要重启DB。下面Rollback这个参数更改
SQL> ALTER system SET event='1461 trace name errorstack off' scope=spfile;
System altered.
SQL> CREATE pfile='/home/oracle/2.ora' FROM spfile;
File created.
SQL> !
[oracle@rac01 ~]$ grep event 2.ora *.event='1461 trace name errorstack off'
2. alter system set events:此方法对memory生效,无法对spfile生效
SQL> ALTER system SET events '1461 trace name errorstack level 3';
System altered.
SQL> !
验证:
[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> CREATE pfile='/home/oracle/3.ora' FROM spfile;
File created.
SQL> !
[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,让其对所有进程生效。