CODEWITHSUNDEEP
javaoracle-databaseconnection-poolingc3p0
Rishi P
Rishi P

Reputation: 287

Oracle database alert opiodr aborting process ORA-609

I am running a batch java application. The application runs every 10/20 minutes in my Production and UAT environment and I get database alerts like this:

Thu Feb 06 15:15:08 2014 opiodr aborting process unknown ospid (28246400) as a result of ORA-609

After researching a bit on the internet the suggested fix for these alerts is to change INBOUND_CONNECT_TIMEOUT as:

Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180
Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

We have changed the setting on the database server side but don't know where to change in the client application. We are using c3p0 to create a connection pool and we are setting only these parameters:

dataSource.setAcquireRetryDelay(30000); 
dataSource.setMaxPoolSize(50);
dataSource.setMinPoolSize(20);
dataSource.setInitialPoolSize(10);

We have other web services running on the same server as the batch application and they use Tomcat's DBCP pool and they don't seem to create any alerts. Also strangely enough, our batch application doesn't generate the alerts in lower test environments. They happen once in a while but the UAT and PROD environments get these alerts very frequently based on the schedule. Any suggestions what configurations to set in the c3p0 pool or should I try changing to another pool API like DBCP?

Update: I have added a few more parameters in the datasource and the frequency of alerts has reduced. I added the following and the number of alerts have gone down from 15 an hour to 4 an hour.

dataSource.setLoginTimeout(120);
dataSource.setAcquireRetryAttempts(10);
dataSource.setCheckoutTimeout(60000);

Upvotes: 4

Views: 25015

Answers (1)

Rishi P
Rishi P

Reputation: 287

I moved to DBCP connection pooling and it seems to have fixed the issue. I tried changing a few more c3p0 settings mentioned above but nothing changed. The alerts were reduced but didn't go completely. So we decided to try DBCP. I am using all default values in DBCP except for the pool size. I'm using the tomcat version of DBCP available in tomcat's lib folder (tomcat-dbcp.jar).

Upvotes: 2

Related Questions