今天在rac的一个节点上发现很多Immediate Kill Session#的错误，分析记录如下
1.alert日志内容
Sun Jan 1 02:12:28 2012 ALTER SYSTEM SET service_names='' SCOPE=MEMORY SID='ora9i1'; Sun Jan 1 02:12:28 2012 Immediate Kill Session#: 496, Serial#: 51199 Immediate Kill Session: sess: 0x406bfa26b78 OS pid: 12900 Immediate Kill Session#: 497, Serial#: 38504 Immediate Kill Session: sess: 0x406bfa280e0 OS pid: 12496 Immediate Kill Session#: 499, Serial#: 45296 Immediate Kill Session: sess: 0x406bfa2abb0 OS pid: 12467 Immediate Kill Session#: 502, Serial#: 18910 Immediate Kill Session: sess: 0x406bfa2ebe8 OS pid: 28887 Immediate Kill Session#: 503, Serial#: 26631 Immediate Kill Session: sess: 0x406bfa30150 OS pid: 20749 Immediate Kill Session#: 508, Serial#: 63586 Immediate Kill Session: sess: 0x406bfa36c58 OS pid: 27614 Immediate Kill Session#: 512, Serial#: 43388 Immediate Kill Session: sess: 0x406bfa3c1f8 OS pid: 4021 Immediate Kill Session#: 516, Serial#: 33975 Immediate Kill Session: sess: 0x406bfa41798 OS pid: 18481 Immediate Kill Session#: 517, Serial#: 24240 Immediate Kill Session: sess: 0x406bfa42d00 OS pid: 823 Immediate Kill Session#: 526, Serial#: 59767 Immediate Kill Session: sess: 0x406bfa4eda8 OS pid: 12529 Immediate Kill Session#: 527, Serial#: 45765 Immediate Kill Session: sess: 0x406bfa50310 OS pid: 6059 …………………… Sun Jan 1 02:22:29 2012 ALTER SYSTEM SET service_names='ora9i' SCOPE=MEMORY SID='ora9i1';
2.数据库配置
2.1)A节点相关配置
SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- ora9i1 SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux IA64: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> show parameter name; NAME TYPE VALUE ------------------------------------ ---------- -------------------- db_file_name_convert string db_name string ora9i db_unique_name string ora9i global_names boolean FALSE instance_name string ora9i1 lock_name_space string log_file_name_convert string service_names string ora9i
2.2)B节点相关配置
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
ora9i2
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux IA64: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> show parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string ora9i
db_unique_name string ora9i
global_names boolean FALSE
instance_name string ora9i2
lock_name_space string
log_file_name_convert string
service_names string SYS$SYS.KUPC$C_2_2012010601100
6.ORA9I, ora9i, SYS$SYS.KUPC$S
_2_20120106011006.ORA9I
3.查看MOS，寻找解决方案
3.1)产生该问题原因
This is caused by unpublished Bug 6955040 ALL THE SESSIONS LOST CONNECTION AFTER KILLING CRSD.BIN. The problem is when CRSD is killed or crashed and restarted, CRSD will run resource check action but CRS resource status will not be available at that time. Then in instance check action, it fails to get the preferred node VIP resource status and considered the preferred node VIP resource is not running. Therefore, instance check action will remove the default database service name and disconnect sessions connected using default database service name. This causes messages "ALTER SYSTEM" and "Immediate Kill Session" printed in alert log.
3.2)解决方案
1) The fix is included in 10.2.0.5 patchset and 11.1.0.7 patchset.
Apply the patchset once they are available.
OR
2) Configure a service name other than the default one (same as db_name),
and get user to use the non-default service name for connection.
