Sessions Get Killed if Connection Use Default Service name (Same as db_name) (Doc ID 730315.1)

今天一個朋友碰到一點問題，目標環境是AIX RAC 10.2.0.4，alert日誌裡出現

Thu Jan 6 09:04:13 2011
ALTER SYSTEM SET service_names='orcl' SCOPE=MEMORY SID='orcl1';
Thu Jan 6 09:04:13 2011
Immediate Kill Session#: 252, Serial#: 25
Immediate Kill Session: sess: 70000020821ec30 OS pid: 201078
Thu Jan 6 09:04:13 2011
Process OS id : 201078 alive after kill
Errors in file
Thu Jan 6 09:04:13 2011
ALTER SYSTEM SET service_names='' SCOPE=MEMORY SID='orcl1';
Immediate Kill Session#: 253, Serial#: 1332
Immediate Kill Session: sess: 70000020a247e98 OS pid: 270550
Thu Jan 6 09:04:14 2011
Process OS id : 270550 alive after kill
Errors in file /opt/oracle/app/admin/orcl/udump/orcl1_ora_209652.trc
Immediate Kill Session#: 254, Serial#: 348
Immediate Kill Session: sess: 70000020f2424c8 OS pid: 221658

在metalink上查了一下，Doc ID 730315.1，db_name和service_name相同時就有可能觸發這個問題。

解決方法是修改service_name，使之與db_name不同，或者升級至10.2.0.5

[@more@]

Document TitleSessions Get Killed if Connection Use Default Service name (Same as db_name) (Doc ID 730315.1)

Modified Date LabelModifiedModified Date25-AUG-2010Document Type LabelTypeDocument TypePROBLEMStatusARCHIVED(EXTERNAL)Priority3To Bottom

In this Document
Symptoms
Cause
Solution
References

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 - Release: 10.2 to 10.2
Information in this document applies to any platform.
Symptoms
alert.log report "Immediate Kill Session" messages, but no one issued such command.

ALTER SYSTEM SET service_names='' SCOPE=MEMORY SID='ORCL1';
...
Immediate Kill Session#: 1606, Serial#: 23
Immediate Kill Session: sess: 0x447e141c0 OS pid: 21802
Immediate Kill Session#: 1607, Serial#: 25
Immediate Kill Session: sess: 0x446e27580 OS pid: 21751
Immediate Kill Session#: 1608, Serial#: 32
Immediate Kill Session: sess: 0x447e15670 OS pid: 21804
Immediate Kill Session#: 1610, Serial#: 56
Immediate Kill Session: sess: 0x447e16b20 OS pid: 20036
Immediate Kill Session#: 1611, Serial#: 21
Immediate Kill Session: sess: 0x446e29ee0 OS pid: 19893
...
ALTER SYSTEM SET service_names='ORCL' SCOPE=MEMORY SID='ORCL1';

Cause
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.

Solution
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.

References