Database connection issues sql developer 20.2

DeeTeeAkl

    Hi,

     

    I have recently migrated to a new windows 10 laptop.  I installed the latest Sql Developer version and hooked it up to Java JDK 1.8.0_261.  I have set the advanced options to use Oracle Client (using instaclient 1.6) and OCI/Thick Driver. 

     

    Basically Sql Developer cant seem to hold a database connection.  I connect, run a query get results, and then I get a message about reconnecting.  This is bad enough but now, if I dont run any sort of query for just a few minutes, Sql developer loses the connection and just spins trying to reconnect.  I cant close the tool normally so I go to task manager and kill the process.  I start sql developer back up and it connects without issue and the query runs fine ... and round we go again.

     

    Any thoughts, suggestion, offerings of emotional support welcome!

      • 1. Re: Database connection issues sql developer 20.2
        thatJeffSmith-Oracle

        What instant client are you using?

         

        What if you just use JDBC Thin (no client)?

         

        Are you a member of a resource consumer group that has an idle/disconnect plan directive in play?

        • 2. Re: Database connection issues sql developer 20.2
          DeeTeeAkl

          Jeff,

           

          Thanks for getting back to me.

           

          1. The version I am using is instantclient_19_6

           

          2. I had issues with maintaining connection when on my windows7 laptop and I raised an SR.  Support told me to install instantclient and setup sql developer to use it.  This resolved the issues I had at the time so I assumed I needed it on my new setup.  I will try without it.

           

          3. I am not entirely sure what you mean by resource consumer group.  I am connecting to our on premise Ebusiness suite environments if that helps.

          • 3. Re: Database connection issues sql developer 20.2
            DeeTeeAkl

            BTW I tried switching back to thin client and I have the same issue.  Sql developer is working fine in that it responds, I can open edit windows, open new connections and run queries .. but the connections I had opened no longer function.  If I view task progress it shows a message like "statementRunner Task (Running)".. the task cant be cancelled (option greyed out).  The only way to recover is kill the process and start again.

            • 4. Re: Database connection issues sql developer 20.2
              Glen Conway

              Some of your comments here open more questions, so it would be nice to clarify a couple of things.  You say you have switched between the thin and thick JDBC drivers while using jdk1.8.0_261 and SQL Dev 20.2.

               

              1. Does that mean you are aware of, and have dealt appropriately with, the problem that 20.2 has with using u261?

                  That is, u261 no longer includes msvcr100.dll as noted in "Unable to launch the Java Virtual Machine" ... msvcr100.dll with JDK 1.8u261

               

              2. You also say

                       but the connections I had opened no longer function

                  Does that mean you switched between thin and thick without restarting SQL Dev?  Some settings changes take effect without a restart, but I am not certain if a driver change is one of those.

               

              As for Oracle Database resource consumer groups, IDLE_TIME limits, and so on, it would be best to ask those administering your environment.

              • 5. Re: Database connection issues sql developer 20.2
                DeeTeeAkl

                1) Yes I am fully aware of this. 

                I have no problem with connecting to an instance.  The problem seems to be that SQL developer cannot maintain the connection.  As per Jeff's suggestion I switched back to thin client and this has made no difference.

                 

                2) There are no database resource consumer groups or any idle time control that would be causing this issue.

                On further testing this issue can occur immediately after connecting to the database.  Basically the SQL session stops working, I dont get any error message or warning that its going to happen.  I can be trying to open a package, running a query, basically any operation that requires the connection to be open and one second it works and the next it stops and the only option I have is to kill the application and try again.  The exact same operation will then work with no issue .. until the next time.

                • 6. Re: Database connection issues sql developer 20.2
                  gfury

                  We seem to have the same issue.

                   

                  SQL Developer 20.2 (Win10) configured to use the OCI driver of the 19c client.  We must use this configuration because we use Radius authentication.

                   

                  Connections intermittently drop and hang.  Once a query hangs, the only way to resolve the issue is to kill SQL Developer via the task manager.

                  • 7. Re: Database connection issues sql developer 20.2
                    thatJeffSmith-Oracle

                    a connection drops, it doesn't hang

                     

                    the UI can hang if the connection becomes unavailable

                     

                    You need to find out why your connections are dropping. It might help to see if this happens to you in SQLcl as well.

                    • 8. Re: Database connection issues sql developer 20.2
                      gfury

                      Still wrestling with this.   It's intermittent.

                       

                      On the DB side the session is waiting for "SQL*Net break/reset to client"

                       

                      I'm going to try sqlcl with the oci option.

                      • 9. Re: Database connection issues sql developer 20.2
                        2772914

                        I have the same problem as you. Win 10, SQL Developer 20.2 and I use Radius authentication. The database connection drops several times in SQL Developer.

                         

                        I also use Toad and I don't have this problem there.

                        • 10. Re: Database connection issues sql developer 20.2
                          gfury

                          That's our environment.  Migrating from TOAD to SQL Developer.  Using Radius authentication (which requires using OCI).  TOAD has no issues. 

                          The difficult thing is that the issue is intermittent.

                           

                          We've got several people at out site trying to figure this out ....

                          • 11. Re: Database connection issues sql developer 20.2
                            kgilli01

                            We recently updated to 20.2 and started experiencing the same types of issues.  SQL Developer randlomly seems to hang (unresponsive) have to close with task manager.

                             

                            We ran perfectly fine on earlier releases with windows 10.  Nothing else changed except going to 20.2 with JRE bundled.

                            • 12. Re: Database connection issues sql developer 20.2
                              Glen Conway

                              Anyone experiencing a hang on SQL Developer should...

                              1) Open a Cmd window. Do not kill anything using Task Manager

                              2) Run a Java utility called jps to find the process ID (PID) for oracle.ide.osgi.boot.OracleIdeLauncher

                              3) Run a Java utility called jstack for that PID to get the JVM full thread. Best to redirect output to a file so nothing is lost by scrolling past the console size limit.

                              4) Post the output back here (using the Advanced Editor so formatting is retained).

                              Note that the bundled JRE no longer contains jstack and jps as it did back in the days of Java 6 and 7 (pre-4.1 SQL Developer), so you will need to have a full Java JDK installed somewhere.

                               

                              Edit: Unless you use the alternate method of

                              1) Run sqldeveloper\sqldeveloper\bin\sqldeveloper.exe from a Cmd window that has its console size limit configured to be large

                              2) Use Ctrl+Break to get the JVM full thread dump.

                              • 13. Re: Database connection issues sql developer 20.2
                                Tim St. H.

                                Full dump files included per your notes in the links below.

                                This dump was taken when the SQL Developer UI has locked and becomes unresponsive.  The message states the connection is close, that is fine, but the UI breaks down.

                                 

                                Note: Same workstation has a SQL*Plus connection simultaneously remaining active without interruption.

                                 

                                "AliveLockThread.CRP0-OAQCPU-APPS" #503 prio=6 os_prio=0 tid=0x0000000028709800 nid=0x4b28 waiting for monitor entry [0x0000000062f8f000]

                                  java.lang.Thread.State: BLOCKED (on object monitor)

                                at oracle.jdbc.driver.PhysicalConnection.isClosed(PhysicalConnection.java:2573)

                                - waiting to lock <0x000000008c967260> (a oracle.jdbc.driver.T2CConnection)

                                at oracle.jdbc.driver.T2CConnection.isClosed(T2CConnection.java:80)

                                at oracle.jdbc.proxy.oracle$1dbtools$1raptor$1proxy$1driver$1oracle$1RaptorProxyOJDBCConnection$2oracle$1jdbc$1internal$1OracleConnection$$$Proxy.isClosed(Unknown Source)

                                at oracle.javatools.db.AbstractDatabase$AliveTester.run(AbstractDatabase.java:487)

                                - locked <0x000000008c972820> (a oracle.jdbc.proxy.oracle$1dbtools$1raptor$1proxy$1driver$1oracle$1RaptorProxyOJDBCConnection$2oracle$1jdbc$1internal$1OracleConnection$$$Proxy)

                                at java.lang.Thread.run(Thread.java:748)

                                 

                                https://gitlab.com/Sthilaire/sqldevelopernotes/-/raw/master/locking_issue/2020.09.20-sqldev_dump_3.txt

                                 

                                Details:

                                Oracle Instant Client 19.8

                                SQL Dev Version 20.2.0.175

                                Build 175.1842

                                • 14. Re: Database connection issues sql developer 20.2
                                  Glen Conway

                                  OK, perfect.

                                   

                                  The UI is hanging since the main event thread (AWT-EventQueue-0) is

                                  - waiting to lock <0x000000008c967260> (a oracle.jdbc.driver.T2CConnection)

                                  which is locked by

                                  "QueryThread" #502 ...

                                  and also blocking

                                  "AliveLockThread.CRP0-OAQCPU-APPS" #503 ...

                                     java.lang.Thread.State: BLOCKED (on object monitor)

                                          at oracle.jdbc.driver.PhysicalConnection.isClosed(PhysicalConnection.java:2573)

                                          - waiting to lock <0x000000008c967260> (a oracle.jdbc.driver.T2CConnection)

                                          at oracle.jdbc.driver.T2CConnection.isClosed(T2CConnection.java:80)

                                  as mentioned in your post.

                                   

                                  So, like thatJeffSmith-Oracle said,

                                  the UI can hang if the connection becomes unavailable

                                   

                                  You need to find out why your connections are dropping.

                                  Cheers

                                   

                                  Edit: And use SQLcl (which also uses JDBC) as basis for comparison rather than SQL*PLus.

                                  1 2 上一个 下一个