Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2005
    Posts
    108

    Unanswered: Keep-Alive with sqlnet.ora

    Hi All,
    This is about how to keep idle sessions alive. I have Oracle 10.2.0.3 running on a Linux box. The network firewall disconnects sessions when they are idle for 30 minutes. A thread at AskTom, http://asktom.oracle.com/pls/asktom/...:1338602652320, suggests using Dead Client Detection (sqlnet.ora) to prevent disconnections. Per one of the posts, it worked. But, I could not make it work for me. What I did was mannually adding one line (the second) to my sqlnet.ora so the whole file contains two lines:

    Code:
    NAMES.DIRECTORY_PATH= (TNSNAMES)
    SQLNET.EXPIRE_TIME = 20
    What else do I need to make the change effective? I tried restarting the listener and the DB instance, but still no luck. Am I missing anything?

    Your advice is highly appreciated.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I could not make it work for me.

    I can not make my car work.
    Tell me what is wrong, please.

    What were you expecting to happen?
    EXACTLY how did you execute your "tests"?

    Code:
    SQLNET.EXPIRE_TIME
    
    Purpose
    
    Use parameter SQLNET.EXPIRE_TIME to specify a the time interval, in minutes, 
    to send a probe to verify that client/server connections are active. Setting a 
    value greater than 0 ensures that connections are not left open indefinitely, 
    due to an abnormal client termination. If the probe finds a terminated 
    connection, or a connection that is no longer in use, it returns an error, 
    causing the server process to exit. This parameter is primarily intended for 
    the database server, which typically handles multiple connections at any one time.
    
    Limitations on using this terminated connection detection feature are:
    
        *
    
          It is not allowed on bequeathed connections.
        *
    
          Though very small, a probe packet generates additional traffic that may downgrade network performance.
        *
    
          Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance.
    Last edited by anacedent; 07-14-08 at 00:10.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2005
    Posts
    108
    What I expected was that when my session had been idle for more than 30 minutes, it was still kept alive.

    Here are my test steps:
    1. used SQL*Plus to connect to the Oracle DB server.
    2. waited for 31 minutes w/o doing anything.
    3. executed a SQL statament to check if the session was still alive.

    Result: the session was disconnected.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What I expected was that when my session had been idle for more than 30 minutes, it was still kept alive.

    WHY did you have that expectation?
    Where in the definition above leads you to conclude client session would
    be kept alive?

    It says, "If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit.".
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2005
    Posts
    108
    I read this part in Oracle's manual, but did you read the URL in my original post?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I read this part in Oracle's manual,
    Please post URL to Oracle Manual

    >did you read the URL in my original post?
    The reference to SQLNET.EXPIRE_TIME was made by a user
    & in my opinion is flat out WRONG as your test demonstrates.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Sep 2008
    Posts
    1
    Quote Originally Posted by anacedent
    >
    The reference to SQLNET.EXPIRE_TIME was made by a user
    & in my opinion is flat out WRONG as your test demonstrates.
    Actually it was originally suggested by Tom in the reply to the original post.

    It is also suggested in Metalink Note:257650.1.

  8. #8
    Join Date
    Oct 2012
    Posts
    2
    The SQLNET.EXPIRE_TIME setting is used to let the database detect a dead connection. The side effect is that the database will send an empty packet after the expire_time and you could use that as a keep alive. But not all firewalls will see an empty packet as traffic and will disconnect you anyway.

    Also be sure to set the EXPIRE_TIME setting in the sqlnet.ora at the database side.
    Last edited by ErikNL; 10-01-12 at 10:08.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by ErikNL View Post
    The SQLNET.EXPIRE_TIME setting is used to let the database detect a dead connection. The side effect is that the database will send an empty packet after the expire_time and you could use that as a keep alive. But not all firewalls will see an empty packet as traffic and will disconnect you anyway.

    Also be sure to set the EXPIRE_TIME setting in the sqlnet.ora at the database side.
    You never get a second chance to make a first impression.
    I doubt that OP has been waiting FOUR years for your response.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Oct 2012
    Posts
    2
    You are right, but I did not reply for the original poster. I replied for anyone who finds this thread through Google, like me.

  11. #11
    Join Date
    Jan 2015
    Posts
    1
    Quote Originally Posted by ErikNL View Post
    You are right, but I did not reply for the original poster. I replied for anyone who finds this thread through Google, like me.
    ErikNL - and me; I'm having the same problem with clients randomly disconnecting.

    Anacedent - pretty harsh, dude. Who p*ssed in your vinegar?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •