alter system set event和set events的区别

按照Oracle Support的建议设置event的时候发现set event和set events这两种写法效果不一样，防止忘记先记下来，oracle mos文档：How To Set EVENTS In The SPFILE (文档 ID 160178.1)

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.

验证：

[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，让其对所有进程生效。