Monday 3 April 2017

ORA-609 with TNS-12537: TNS:connection closed



          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

5 comments:


  1. Hi Admin,

    QLNET.INBOUND_CONNECT_TIMEOUT in sqlnet.ora file is clear here and thanks for info.
    But what is INBOUND_CONNECT_TIMEOUT_listener and useful?

    Thank you.

    ReplyDelete

  2. Dear User,

    Thank you visiting here and really appriciate your question.

    Actually INBOUND_CONNECT_TIMEOUT_listener is a parameter in listener.ora file.
    And we use this parameter to specify the time in seconds for the client to complete its connect request to the listener after the network connection had been established.
    And if that listener does not receive the client request in the time specified in
    INBOUND_CONNECT_TIMEOUT_listener parameter then it terminates the connection.

    Hope above points clear your query.
    I will post you a details n my next post soon about error log and ora alerts.

    Thanks,

    ReplyDelete
  3. Hi- Thanks for creating this blog with good info!

    Did you miss the S or it is parameter?

    QLNET.INBOUND_CONNECT_TIMEOUT


    or
    SQLNET.INBOUND_CONNECT_TIMEOUT

    ReplyDelete
  4. Hi- Thanks for creating this blog with good info!

    Did you miss the S or it is parameter?

    QLNET.INBOUND_CONNECT_TIMEOUT


    or
    SQLNET.INBOUND_CONNECT_TIMEOUT

    ReplyDelete
    Replies

    1. Hi Abdul,

      Thank you very much for notice it. You are right.
      We have corrected it.

      Regards,
      ora-data Team

      Delete

Thank you for your comments and suggestions. It's good to talk you.