8 Replies Latest reply on Jan 25, 2016 10:09 PM by Kyle {unique}

    Proper Way to Reconnect to a Database?

    BobbyJ

      What are the proper steps for reconnecting to a database once the connection is lost?

       

      I generally have SQL Developer open with multiple worksheets all day long. I go from meeting to meeting, sometimes going from wired to wireless, and multiple sleep/awake cycles in between. I lose connection to the database multiple times throughout the day.

       

      I never know the proper way to reconnect to the database. I generally don't even think about it until I try running a query from one of the open worksheets and it tells me there is an IO error or closed connection.

       

      I have tried going to the Connections and right-clicking and choosing "Connect" or "Reconnect" since "Disconnect" is normally grayed out. Neither seems to work. The thing that does work is using Alt-F10 to open a new worksheet, choosing a connection, waiting for the new worksheet to open then go back to the original worksheet that was already open and executing my query. After doing all of this, the query will normally run.


      I assume there is a faster, or more reliable way of reconnecting and I just don't know about it since I'm a new SQL Developer user.


      I appreciate your patience with my question while I'm still learning. Thanks.

        • 1. Re: Proper Way to Reconnect to a Database?
          BPeaslandDBA

          I right-click and choose Reconnect and it works fine for me.


          Which version of SQL Dev are you running?

           

          Cheers,
          Brian

          • 2. Re: Proper Way to Reconnect to a Database?
            BobbyJ

            That's strange. I assume that when you say you right-click and choose Reconnect that you are doing so on the Connections tab and right clicking on the connection.I am on the latest, v4.1.3.20 running on Win7 Pro using JDK 1.8.0_65.

             

            I tried a test this morning:

            • I was connected to the db via wired and VPN connection.
            • I disconnected from the wired/VPN connection and started the wireless connection.
            • I tried running a query and received an error message "IO Error: Connection reset by peer: socket write error"
            • I then tried the right-click on the Connection and choose "Reconnect". And it worked. The connection was re-established and then I was able to run the query.

             

            I wonder if the other times when "Reconnect" doesn't seem to work if in addition to losing the connection, my database session is timing out. That could very well be possible, I'll have to pay more attention.

             

            Is there a different process for re-establishing the connection when both the session connection is lost and the database session is timed out?

             

            Thanks again for your help.

            • 3. Re: Proper Way to Reconnect to a Database?
              BPeaslandDBA

              I assume that when you say you right-click and choose Reconnect that you are doing so on the Connections tab and right clicking on the connection.

               

              Yes, that is what I meant.

               

              • I disconnected from the wired/VPN connection and started the wireless connection.
              • I tried running a query and received an error message "IO Error: Connection reset by peer: socket write error"

               

              This is expected. Your connection to the database was severed. The TCP socket has been tampered with so the app (SQL Dev) can't write data to it any more without error.

               

               

              • I then tried the right-click on the Connection and choose "Reconnect". And it worked. The connection was re-established and then I was able to run the query.

               

               

              And this is how I would expect it to work. After your network connection has been re-established, you would have to reconnect to the database.

               

              I wonder if the other times when "Reconnect" doesn't seem to work if in addition to losing the connection, my database session is timing out. That could very well be possible, I'll have to pay more attention.

               

               

              That could be. PMON may have cleaned up a session since the app was no longer connected.

               

              Is there a different process for re-establishing the connection when both the session connection is lost and the database session is timed out?

               

               

              Well you can reconnect in SQL Dev. Whether or not the instance has killed the session or not, SQL Dev will be recreating a new session when you choose to Reconnect. It won't use the old session in the db instance.

               

              Cheers,
              Brian

              • 4. Re: Proper Way to Reconnect to a Database?
                Kyle {unique}

                Plan B is to never let it disconnect....

                 

                http://scristalli.github.io/SQL-Developer-4-keepalive/

                 

                This is an extension that says it will keep your connection alive.

                • 5. Re: Proper Way to Reconnect to a Database?
                  BPeaslandDBA

                  Will that extension survive the OP's laptop switching networks (wired to wireless) and falling asleep?

                   

                  Thanks,

                  Brian

                  • 6. Re: Proper Way to Reconnect to a Database?
                    Kyle {unique}

                    I do not know, but I think it should because the program shouldn't care bout the network stack.

                     

                     

                    The extension looks like it periodically sends some kind of ping to the server.  There may a slight delay as the connection (netowrk stack) figures out a new path to the target, but if the route exists, SQL Developer shouldn't care

                    • 7. Re: Proper Way to Reconnect to a Database?
                      BPeaslandDBA

                      But a keepalive ping won't help the OP here. Let's say the OP has done, in the OP's own words, "multiple sleep/awake cycles". In that case, the laptop is asleep and won't be able to send the ping. In this case, PMON has detected the dead connection and killed the session.

                       

                       

                      Cheers,
                      Brian

                      • 8. Re: Proper Way to Reconnect to a Database?
                        Kyle {unique}

                        Possibly.  But I don't think anyone cares if it is the same connection or a new one.  Frequently after a sleep cycle I make a change in a package and click the compile button and it automatically reconnects.  If the ping add-on has already done that then there would already be a connection.

                         

                        The only time you care if it is the same connection if if you have a result set open in a tab and you want to edit it and save your change.  In that case you'ld need to re-run your select. 

                         

                        We're both speculating about an add-on that neither of us has used and a use-case that we have not encountered.  If the OP tries the extension it either will or will not solve his issues.  Empirical data beats theory every time.