Large Number of Sessions in Database with Program Name Oraagent.bin

Applies to:

Oracle Net Services - Version 11.2.0.2 and later
Oracle Server - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 26-DEC-2012***

Symptoms

11.2.0.2 RAC database shows many sessions with program name of oraagent.bin<hostname>, which are never removed.
Sample output :

SQL> select s.sid, s.logon_time, p.spid, s.process, s.program from
v$session s, v$process p where s.paddr = p.addr and s.program like '%oraagent%' and s.logon_time < SYSDATE-1 order by s.logon_time;
SID      LOGON_TIME         SPID      PROCESS   PROGRAM
-------- -------------------------- ----------- --------------- --------------------------------------------------
1015    09-03-2011:20:44:18   10822      10623          oraagent.bin@sample.com (TNS V1-V3)
293      09-03-2011:20:44:18   10824      10623          oraagent.bin@sample.com (TNS V1-V3)
871      09-03-2011:20:44:18   10833      10623          oraagent.bin@sample.com (TNS V1-V3)
11        09-03-2011:22:25:56   30794      30464          oraagent.bin@sample.com (TNS V1-V3)
436      09-03-2011:22:47:58   20707      20514          oraagent.bin@sample.com (TNS V1-V3)
152      10-03-2011:00:30:51   2228        2093            oraagent.bin@sample.com (TNS V1-V3)
1017    10-03-2011:00:30:51   2232        2093            oraagent.bin@sample.com (TNS V1-V3)
294      10-03-2011:01:33:23   14541      14307          oraagent.bin@sample.com (TNS V1-V3)
1013    10-03-2011:01:33:23   14545      14307          oraagent.bin@sample.com (TNS V1-V3)
151      10-03-2011:01:33:23   14546      14307          oraagent.bin@sample.com (TNS V1-V3)
295      10-03-2011:03:54:16   32539      32384          oraagent.bin@sample.com (TNS V1-V3)
730      10-03-2011:03:54:16   32541      32384          oraagent.bin@sample.com (TNS V1-V3)
1018    10-03-2011:04:53:25   20203      19882          oraagent.bin@sample.com (TNS V1-V3)
874      10-03-2011:04:53:25   20197      19882          oraagent.bin@sample.com (TNS V1-V3)
586      10-03-2011:04:53:25   20195      19882          oraagent.bin@sample.com (TNS V1-V3)
731      10-03-2011:06:11:05   12921      12728          oraagent.bin@sample.com (TNS V1-V3)
153      10-03-2011:06:43:13   4417        4034            oraagent.bin@sample.com (TNS V1-V3)
431      10-03-2011:06:43:13   4419        4034            oraagent.bin@sample.com (TNS V1-V3)
297      10-03-2011:06:43:13   4425        4034            oraagent.bin@sample.com (TNS V1-V3)
732      10-03-2011:07:21:51   1027        32500          oraagent.bin@sample.com (TNS V1-V3)
8          10-03-2011:09:31:04   19914      18710          oraagent.bin@sample.com (TNS V1-V3)
734      10-03-2011:12:53:47   22039      21506          oraagent.bin@sample.com (TNS V1-V3)
4          10-03-2011:12:53:47   22040      21506          oraagent.bin@sample.com (TNS V1-V3)
309      10-03-2011:13:20:12   7483        6420            oraagent.bin@sample.com (TNS V1-V3)
156      10-03-2011:13:20:12   7481        6420            oraagent.bin@sample.com (TNS V1-V3)
597      10-03-2011:15:36:50   28148      27237          oraagent.bin@sample.com (TNS V1-V3)
882      10-03-2011:15:36:50   28151      27237          oraagent.bin@sample.com (TNS V1-V3)
450      10-03-2011:19:54:06   29750      28921          oraagent.bin@sample.com (TNS V1-V3)
1030    10-03-2011:19:54:06   29749      28921          oraagent.bin@sample.com (TNS V1-V3)
884      10-03-2011:21:39:50   32278      31873          oraagent.bin@sample.com (TNS V1-V3)
741      10-03-2011:21:39:50   32272      31873          oraagent.bin@sample.com (TNS V1-V3)
1016    10-03-2011:22:43:32   16557      16077          oraagent.bin@sample.com (TNS V1-V3)
446      10-03-2011:23:17:10   5454        4483            oraagent.bin@sample.com (TNS V1-V3)
13        10-03-2011:23:17:10   5458        4483            oraagent.bin@sample.com (TNS V1-V3)
306      12-03-2011:15:35:10   24931      24769          oraagent.bin@sample.com (TNS V1-V3)
590      13-03-2011:02:18:55   30230      29814          oraagent.bin@sample.com (TNS V1-V3)
441      13-03-2011:02:18:55   30232      29814          oraagent.bin@sample.com (TNS V1-V3)
875      13-03-2011:02:41:28   17367      1044            oraagent.bin@sample.com (TNS V1-V3)

These hung processes numbers will build up over time and can cause issues along the lines of maximum  process limits to be exhausted on the system.ORA-00020: maximum number of processes (xxx) exceeded, might be triggered.
PS output shows the processes are local, ie bequeath connection from the same machine.

$ ps -ef |grep -i 1044
oracle 1044 1 0 Mar15 ? 00:00:00 oracleOITM21 (DESCRIPTION=(LOCAL =YES)(ADDRESS=(PROTOCOL=beq)))

LSOF on the process will show multiple pipes for the process.

$ lsof -p 1044 | grep pipe
oracle 1044 oracle 188r FIFO 0,6 187781116 pipe
oracle 1044 oracle 189w FIFO 0,6 187781116 pipe
oracle 1044 oracle 212r FIFO 0,6 187781117 pipe
oracle 1044 oracle 213r FIFO 0,6 187781062 pipe
oracle 1044 oracle 214w FIFO 0,6 187781062 pipe
oracle 1044 oracle 215r FIFO 0,6 187781063 pipe
oracle 1044 oracle 216w FIFO 0,6 187781063 pipe
oracle 1044 oracle 217w FIFO 0,6 187781117 pipe
oracle 1044 oracle 218r FIFO 0,6 187781139 pipe
oracle 1044 oracle 221w FIFO 0,6 187781140 pipe

Strace of hung process shows

1044 07:35:57.232994 read(30, <unfinished ...>

A further sympton can be that the operating system process is seen, but there is no entry in the dictionary for the session.

Changes

Upgrade to 11.2.0.2

Cause

The cause of this problem has been identified in RAC Bug:11877079 Hundreds of oraagent.bin@hostname sessions in 11.2.0.2 database.

Solution

As the hung process are local , they are using IPC protocol. Timeout parameters such as DCD or send and receive timeout, can not be used. These are only for TCP.

Workaround would be to remove the process from operating system level, ie a kill -9 <PID>
Issue will be fixed in the patch set release 11.2.0.3

Download patch for your operating system as required. Oracle Developement recommended more than one patch to be installed.

At the time of writing the following are available

Linux
o MergePatch:11938226 for 11.2.0.2.1 (11804954 11744313 11871469 11877079)
o MergePatch:12334600 for 11.2.0.2.1 GI Bundle 1 (10299006 11877079)
o MergePatch:12347844 for 11.2.0.2.2 (11744313 10299006 11069614 1168409 11871469 11977079)
o MergePatch:12531175 for 11.2.0.2.2 + PSU ( 11877079 10299006)

Linux and Solaris
o MergePatch:12362639 for 11.2.0.2.2 GI bundle 2 (11877079 10299006)

References

NOTE:1287496.1 - Many Connections From oraagent.bin to ASM or Database Instance (Likely ORA-00020)
BUG:11877079 - HUNDREDS OF ORAAGENT.BIN@HOSTNAME SESSSIONS IN 11.2.0.2 DATABASE