Data source in suspended state: BEA-001156 error because maximum number of sessions was exceeded in the database (ORA-00018)

Recently, I worked a Service Request where a data source was in suspended state. In the log files it could be seen a BEA-001156 error message, and the stack trace (obviously shortened in this example) contained something like the following:
<BEA-001156> <Stack trace associated with message 001129 follows:
java.sql.SQLException: ORA-00018: maximum number of sessions exceeded
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:441)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOSESSKEY(T4CTTIoauthenticate.java:404)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:385)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:546)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:236)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:280)
at oracle.jdbc.xa.client.OracleXADataSource.getPooledConnection(OracleXADataSource.java:469)
at oracle.jdbc.xa.client.OracleXADataSource.getXAConnection(OracleXADataSource.java:156)
at oracle.jdbc.xa.client.OracleXADataSource.getXAConnection(OracleXADataSource.java:101)
...


Seeing at the error message at the top, it is clearly a session handling problem at database level. Note that, depending on how your application is designed/programmed, recursive sessions can be created and sometimes it could be hard to track all of them, even more in periods of high load.

When this type of issue occur, the most common solution is to increase the SESSIONS parameter of the init.ora configuration file.

It is usually recommended to preserve 50% of the SESSIONS value for recursive sessions.
Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About


Principal Technical Support Engineer in the Engineered (Systems) Enterprise Support Team - EEST.
Former member of the Coherence and Java Technologies Support Teams.

Search

Archives
« February 2015
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
       
       
Today