Re: Orphan database connections

you might try enabling sqlnet tracing at level 16 on the server. be sure to set the trace_unique_client (name may be somewhat different, docs not in front of me)

then close both a client window that acts as expected and one of the anomalous ones.

the trace files can be interpreted with trcasst.

it would also be interesting to compare the raw contents from the moment of disconnection

On Thu, Sep 5, 2019 at 07:23 Jeffrey Beckstrom <jbeckstrom_at_gcrta.org> wrote:

> We tried this with a database on Windows and the problem reproduces there,
> so not just a Linux database issue.
>
> This does not happen on any of our Windows 7 PCs.
>
> Installed a Trial version of Toad yesterday. Created a connection and then
> closed the Windows. Toad correctly cleaned up after itself.
>
> Oracle is leaning towards a client OS / Network issue since nobody else is
> experiencing this. Just don't know where to look.
> >>> Chris Taylor <christopherdtaylor1994_at_gmail.com> 9/5/19 10:10 AM >>>
> That's very puzzling what you are seeing.
>
> So the client window did in fact close it's connection as far as it was
> concerned and cleaned up the socket.
>
> But the server allowed the socket to remain open on its side. So, it's not
> a client problem and appears to be server -side.
>
> Chris
>
> On Thu, Sep 5, 2019, 6:55 AM Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>
> wrote:
>
>> Yes it was.
>> >>> Chris Taylor <christopherdtaylor1994_at_gmail.com> 9/4/19 11:34 PM >>>
>> That looks like your socket died / ended on the workstation because the
>> only socket you have open is to port 22 after you kill the window.
>> Was it still open on the DB ?
>>
>> Chris
>>
>> On Wed, Sep 4, 2019 at 2:47 PM Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>
>> wrote:
>>
>>> Before close window:
>>> C:\WINDOWS\system32>netstat -anob |findstr -i 65237 - My putty session???
>>> TCP zz.z.z.zzz:65237 xx.zz.zz.z:22 ESTABLISHED 13080
>>>
>>> C:\WINDOWS\system32>netstat -anob |findstr -i 65235
>>> TCP zz.z.z.zzz:65235 xx.xx.xx.x:1521 ESTABLISHED 12676
>>>
>>> After close the window:
>>> C:\WINDOWS\system32>netstat -anob |findstr -i 65237
>>> TCP zz.z.z.zzz:65237 xx.xx.xx.x:22 ESTABLISHED 13080
>>>
>>> C:\WINDOWS\system32>netstat -anob |findstr -i 65235
>>>
>>> C:\WINDOWS\system32>
>>> >>> Chris Taylor <christopherdtaylor1994_at_gmail.com> 9/4/19 3:21 PM >>>
>>> Try this:
>>> 1. Use sqlplus as your test subject
>>> 2. Open sqlplus and connect to the db on the windows 10 client
>>> 3. Open the db server do a netstat -anop | grep -i <your clients
>>> ip_address> (you should find a port open that maps to your workstation ip)
>>> 4. Open a command prompt on your windows client and do a netstat -anob |
>>> findstr -i port# (from step 3)
>>> 5. Close the sqlplus window (not exit the program)
>>> 6. Do another netstat -anob | findstr for the port# and check the status
>>> ... if its in TCP_TIMEDWAIT then its a time_wait indefinite issue
>>> ... if the socket is actually dead/gone on the client, then I am at a
>>> loss
>>> ... if the socket is showing CLOSE_WAIT then you might have a tcp close
>>> wait time issue (not sure the param that controls that on windows off top
>>> of my head however)
>>>
>>> Chris
>>>
>>> On Wed, Sep 4, 2019 at 2:01 PM Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>
>>> wrote:
>>>
>>>> As a test, I installed Quest Toad (trial) and performed test. Upon
>>>> closing the Window the database connections were terminated. Could be that
>>>> Toad captured the close Window command and cleaned up after itself before
>>>> terminating.
>>>>
>>>> A packet trace shows Toad sending a "FIN" to the database while
>>>> Oracle's programs send a "RST" as the final transmission.
>>>> >>> Chris Taylor <christopherdtaylor1994_at_gmail.com> 9/4/19 12:18 PM >>>
>>>>
>>>> Tcp_timedwaitdelay indefinite perhaps?
>>>>
>>>> Something in the TCP stack keeping the socket open on the clients PC
>>>>
>>>> Chris
>>>>
>>>> On Wed, Sep 4, 2019, 10:57 AM Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>
>>>> wrote:
>>>>
>>>>> We are in the very early stages of a desktop migration to Windows 10.
>>>>> To date, only 3 IT personnel that use Oracle have a Windows 10 PC. Of
>>>>> those, two are experiencing a problem. If you are in SQL Plus or SQL
>>>>> Developer and do not exit/close the connection prior to closing the
>>>>> application's window, the database connection remains after the program is
>>>>> terminated. This does not occur on any of our Windows 7 desktops. We have
>>>>> not been able to identify a difference between the PC that does terminate
>>>>> the database connection upon the window being closed and the two that do
>>>>> not. All of our PCs start from a standard image.
>>>>>
>>>>> Oracle is suggesting looking at the network, however, all of the PCs
>>>>> are inside the firewall and if in SQL PLus you state "exit" or in SQL
>>>>> Developer you right click the connection and choose disconnect, the
>>>>> connections are terminated. The issue only arises on 2 out 3 PCs if just
>>>>> close the Window without taking any other actions first.
>>>>>
>>>>> Any suggestions on what may be the cause? Does Windows 10 not send a
>>>>> termination message and thus you now have ensure you break the connection
>>>>> manually?
>>>>>
>>>>> Jeffrey Beckstrom
>>>>> Lead Database Administrator
>>>>> Information Technology Department
>>>>> Greater Cleveland Regional Transit Authority
>>>>> 1240 W. 6th Street
>>>>> <https://www.google.com/maps/search/1240+W.+6th+Street+Cleveland,+Ohio+44113?entry=gmail&source=g>
>>>>> Cleveland, Ohio 44113
>>>>> <https://www.google.com/maps/search/1240+W.+6th+Street+Cleveland,+Ohio+44113?entry=gmail&source=g>
>>>>>
>>>>>
>>>> ------------------------------
>>>> This email has been scanned for spam and viruses. Click here
>>>> <https://attseg.cloud-protect.net/index01.php?mod_id=11&mod_option=logitem&mail_id=1567613908-JTJaO40Mavju&r_address=jbeckstrom%40gcrta.org&report=1>
>>>> to report this email as spam.
>>>>
>>>>
>>> ------------------------------
>>> This email has been scanned for spam and viruses. Click here
>>> <https://attseg.cloud-protect.net/index01.php?mod_id=11&mod_option=logitem&mail_id=1567624912-cRCapqps999s&r_address=jbeckstrom%40gcrta.org&report=1>
>>> to report this email as spam.
>>>
>>>
>
> ------------------------------
> This email has been scanned for spam and viruses. Click here
> <https://attseg.cloud-protect.net/index01.php?mod_id=11&mod_option=logitem&mail_id=1567692632-jFHasm09NPJA&r_address=jbeckstrom%40gcrta.org&report=1>
> to report this email as spam.
>
> --
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Principal Consultant at Pythian
Pythian Blog http://www.pythian.com/blog/author/still/ Github: https://github.com/jkstill

--
http://www.freelists.org/webpage/oracle-l