ORA-609
ORA-609 is an error message that you may see as a result of timeout issues. In this case, no modification to the actual code is needed; instead, you must examine the connection and figure out what may be triggering the timeout.
The Problem
When facing ORA-609, you will see the following error message:
ORA-609: opiodr aborting process unknown ospid
The error indicates that a client connection has failed to complete or that the connection was aborted without being successfully connected. This happens when the 60-second default timeout passes or is triggered when the database session is aborted from the operating system prompt.
The Solution
To resolve this error, you must increase the values for INBOUND_ CONNECT_ TIMEOUT on both the listener side and the server side. If you are getting ORA-609 because of a connection timeout issue, increasing the values should fix the problem. You should change INBOUND_ CONNECT_ TIMEOUT to read as the following:
Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180
Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120
Above, the timeout seconds are being set to 120 in place of the default 60. While this simple change should fix most ORA-609 errors, if you continue to see this error then you should try to locate which client is causing the error. Set the sqlnet.ora file parameter to
sqlnet.ora file: DIAG_ADR_ENABLED = OFF
and reload the listener. Take a look at the PROGRAM and HOST fields to make sure that the timestamps of the sqlnet.log and alert.log errors are the same. After locating the client causing the error, you can enable client tracing. For client tracing assistance, consider contacting an Oracle expert or customer service provider.
You can also take a look at the listener.log to find timestamps that match the ORA-609 error in the alert.log. Again, once you locate the client causing the error, you can enable client tracing.
Looking Forward
Resetting the INBOUND_ CONNECT_ TIMEOUT should fix or at least reduce the chances of seeing error ORA-609. If you are unable to resolve the error by increasing the timeout seconds, you may want to contact your database administrator or Oracle consultant to resolve the issue. Locating and fixing the client causing the error may not be something that programmers who are just beginning to code can fix on their own. As always, check the credentials and certifications of the Oracle consultant before accepting services.
ORA-609 with
TNS-12537: TNS:connection closed
We almost time receive an error such as ora-609 which shows
in alert log as
TNS- TNS-12537: TNS:connection closed
ns secondary err code: 12560
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
opiodr aborting process unknown ospid (12345) as a result of
ORA-609
This error means could not attach to incoming
connection, so our database process was aborted or closed because incoming
connection was not passed to it by listener. In our case we found sqlnet error
stack as shown below
TNS-12537: TNS:connection
closed
It means the dedicated process didn’t have a client connection
anymore to work.
As we all know the process of client connections:
1. First
Client initiates a connection to the
database so it connects to the listener.
2. Then
listener starts a dedicated DB process that will receive this connection or
session.
3. After
that, this dedicate process is started and listener passes the connection from
the client to this process.
4. Then
the sever process takes the connection from the listener to continue the
handshake with the client.
5. After
that Server process and client exchange messages or information required for
establishing a session or user logon.
6. Then
the session is opened now.
In
our case, it may be when the dedicated process tries to communicate with the
client it finds that connection was closed.
Solution or
Work around:
We
can eliminate this error by increasing the value of SQLNET.INBOUND_CONNECT_TIMEOUT
in sqlnet.ora file.
By default the SQLNET.INBOUND_CONNECT_TIMEOUT value is 60
seconds.
The
sqlnet.ora file can be found in RDBMS_HOME/network/admin.
For example:
$ cd $ORACLE_HOME/network/admin
$vi sqlnet.ora
And change the value of SQLNET.INBOUND_CONNECT_TIMEOUT as:
SQLNET.INBOUND_CONNECT_TIMEOUT=300
Then
save the file.
There are many more possibilities which can cause this error
such as:
1. This can happen suppose our client closed the
connection immediately after initiating it.
2. This may occur
when firewall kill the connection.
3. Sometimes, it may happen because of Database, OS or network
performance problem.
4. This error may occur when some client crashes.
Hope this may also useful here:
Hope this may useful and helpful.
Please let us know for any concerns either by below comments or write to @contact us: https://ora-data.blogspot.in/
Regards,
ora-data Team
Have you gotten an ORA-00609 error or ORA-609 error? Learn how to fix it and what the cause is in this article.
Two Errors with Similar Codes
There are two ORA-609 errors that I have seen:
ORA-00609: could not attach to incoming connection ORA-609: opiodr aborting process unknown ospid
If you’re looking for a solution to either of them, you have come to the right place!
ORA-609: opiodr aborting process unknown ospid Cause and Solution
This error is usually a secondary error. This means that the actual cause of the issue is another error in the stack trace or list of errors.
If you get this error, take a look at the other errors that you can see.
ORA-00609: could not attach to incoming connection Cause
This error is usually due to timeout issues. It indicates that a client connection has failed, or that a connection was aborted without finishing the connection.
To resolve this error, you can either look for the issue that is causing the timeout to occur, or increase the timeout limit.
If you want to increase the timeout limit, change the INBOUND_CONNECT_TIMEOUT value on both the listener and server side. This can be done in the sqlnet.ora file and the listener.ora file.
If you can’t resolve this error using any of these methods, then contact your database administrator or contact Oracle support. There could be a range of things specific to your environment that cause this error.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!
ORA-609 : OPIODR ABORTING PROCESS UNKNOWN OSPID
ORA-609 : opiodr aborting process unknown ospid
WARNING: inbound connection timed out (ORA-3136)
Cause:
The ORA-609 error is thrown when a client connection of any kind failed to complete or aborted the connection
process before the server process was completely spawned.
Beginning with 10gR2, a default value for inbound connect timeout has been set at 60 seconds.
This is also triggered, when a DB session is killed/aborted manually from the OS prompt.
Solution:
Increase the values for INBOUND_CONNECT_TIMEOUT at both listener and server side sqlnet.ora file as a preventive measure.If the problem is due to connection timeouts,an increase in the following parameters should eliminate or reduce the occurrence of the ORA-609s.
Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180
Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120
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);