oracle rac 停实例,案例:OracleRAC 报错Immediate Kill Session#

天萃荷净

运维DBA在巡检Oracle RAC时发现alert日志文件中出现大量Immediate Kill Session#: 496, Serial#: 51199错误，分析原因为BUG导致

今天在rac的一个节点上发现很多Immediate Kill Session#的错误，分析记录如下

1.Oracle 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.检查Oralce RAC数据库配置

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.

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:OracleRAC 报错Immediate Kill Session#