1 2 3 Previous Next 32 Replies Latest reply on Sep 29, 2017 1:41 PM by thatJeffSmith-Oracle

    SQL Developer v4.2 Connection Reset Error appearing randomly!

    Sam_P
    Sam_P May 8, 2017 9:32 PM

      Hello,

       

      I'm using the latest SQL Developer Version 4.2.0.17.089 and every now & then, in a random fashion, I encounter the following error:-

       

      SQL_Developer_Connection_Reset_Error.jpg

       

      It occurs in a random fashion and in this case, it ocurred when I simply launched SQL Developer, clicked on a saved connection, navigated to a TABLE to open it and got this error.

       

      I remember that was also an issue in SQL Developer (BETA) version - see this link: https://community.oracle.com/message/14058314#14058314 but looks like it hasn't been fully resolved?

       

      Sameer

        • 1. Re: SQL Developer v4.2 Connection Reset Error appearing randomly!
          Gary Graham-Oracle
          Gary Graham-Oracle May 8, 2017 10:38 PM (in response to Sam_P)

          This has been an issue in the 4.2.x releases to-date whenever SQL Developer uses the 12.2 JDBC driver, the driver shipped with 4.2.  If I recall correctly, the driver's behavior changed for 12.2, invalidating a coding technique we used to interrupt processing under certain circumstances and instead results in these connection resets.

           

          For 4.2 production (4.2.0.17.089.1709), all code directly controlled by our team was adjusted to stop using that technique, but some code inherited from the JDeveloper IDE still uses it.  This will get fixed.  Your options are...

           

          1. Use Tools > Preferences > Database Advanced to point to an Oracle 12.1 (a home or instant client) and use the 12.1 JDBC driver

          2. Revert to 4.1.x and use by default (no Database Advanced client setting) the 12.1 driver shipped with it.

          3. Wait for a possible patch or the next release (I have no idea which or when).

          • 2. Re: SQL Developer v4.2 Connection Reset Error appearing randomly!
            Sam_P
            Sam_P May 8, 2017 11:14 PM (in response to Gary Graham-Oracle)

            Thanks Gary.

             

            We have Oracle Database Server Enterprise Edition version 11.2.0.4.

             

            Our entire team has Oracle Client (32-Bit) version 11.2.0.3 installed on our Windows 7 PCs and we have configured SQL Developer to use this Oracle Home Client 11.2.0.3 (i.e. Thick driver) configuration, as shown:-

             

            SQL_Developer_Connection_Reset_Error_2.jpg

            All tests are successful within the Oracle Home Client configuration section (when I hit the TEST button).

             

            If I un-check the Use OCI/Thick driver check-box option, will it make any difference?

            • 3. Re: SQL Developer v4.2 Connection Reset Error appearing randomly!
              Gary Graham-Oracle
              Gary Graham-Oracle May 9, 2017 7:18 AM (in response to Sam_P)

              we have configured SQL Developer to use this Oracle Home Client 11.2.0.3 (i.e. Thick driver) configuration...

              If I un-check the Use OCI/Thick driver check-box option, will it make any difference?

              My prior comments focused on an issue with the 12.2 JDBC driver, but since you are using 11.2.0.3 instead, I have to confess I am not sure.  Are you saying connections using OCI/Thick are less stable in 4.2 production than 4.1.3 or 4.1.5?

               

              You may want to experiment with JDBC thin to see if connection stability improves,

              java - Oracle thin driver vs. OCI driver. Pros and Cons? - Stack Overflow

               

              but keep in mind that OCI does provide certain features that you might depend on and not wish to forego:

              https://docs.oracle.com/cd/B28359_01/java.111/b31224/instclnt.htm

              • 4. Re: SQL Developer v4.2 Connection Reset Error appearing randomly!
                Sam_P
                Sam_P May 9, 2017 4:56 PM (in response to Gary Graham-Oracle)

                Hi Gary,

                 

                I'm not sure if the OCI/Thick driver is less stable in SQL Developer v4.2 but quite frankly, I'm just trying to toggle these specific settings and observing if the disconnects occur or not.

                 

                Based on the screenshot above of the settings, any chance if you could explain the relationship between the "Use Oracle Client" setting (say, if enabled) and "Use OCI/Thick driver" setting (say, if disabled)? Do they go hand-in-hand together OR are they mutually exclusive?

                 

                I was under the impression that if you enable  "Use Oracle Client" setting, it would automatically use the OCI libraries found in Oracle Client's OCI sub-folder located at C:\oracle\product\11.2.0\client_1\oci\

                 

                Appreciate your advise.

                • 5. Re: SQL Developer v4.2 Connection Reset Error appearing randomly!
                  Sam_P
                  Sam_P May 9, 2017 5:13 PM (in response to Sam_P)

                  Forgot to add, all of our PCs are Windows 7 (x64) and we use JDK (and JRE) version 1.8.0.121 (32-Bit), Oracle Client version 11.2.0.3 (32-Bit) and SQL Developer v4.2.0 (32-Bit).

                   

                  So, all the software stack is on 32-Bit throughout to keep it consistent. For compatibility reasons, our IT DBA team does not want us to migrate to 64-Bit stack. On a separate machine, for testing purposes, I did a quick test with installing 64-Bit software stack of JDK (and JRE), Oracle Client and SQL Developer (versions identified above) and still the connection reset behaviour was happening.

                  • 6. Re: SQL Developer v4.2 Connection Reset Error appearing randomly!
                    Gary Graham-Oracle
                    Gary Graham-Oracle May 9, 2017 6:39 PM (in response to Sam_P)

                    Based on the screenshot above of the settings, any chance if you could explain the relationship between the "Use Oracle Client" setting (say, if enabled) and "Use OCI/Thick driver" setting (say, if disabled)? Do they go hand-in-hand together OR are they mutually exclusive?

                    When the Use Oracle Client checkbox is ticked (and configured correctly so that PATH points to a Oracle Home or Instant Client installation), then SQL Developer uses...

                        the ojdbc jar (say, ojdbc8.jar for a 12.2.0.1 client) included in that installation

                      rather than

                        the ojdbc jar found in SQL Developer's sqldeveloper\jdbc folder.

                     

                    Next, given that Use Oracle Client checkbox is ticked, if Use OCI/Thick driver checkbox is also ticked, then SQL Developer will attempt to make its JDBC database connections using the OCI/Thick driver (Type 2) rather than the Thin driver (Type 4).

                     

                    So these two checkboxes work together, with the second having a dependency on the first.

                     

                    I recall that once after enabling these (and verifying the Configure step  via the Test button), SQL Developer would not prompt me to restart so the change could take effect.  I eventually nuked my the system folder containing my user settings to get it to switch over.  If you have any doubts, you can always use the search widget within Help > About > Properties tab to check if the property sqldeveloper.oci.available is set to true and if the jdbc.library property points to the intended ojdbc jar file.

                     

                    Hope this helps

                    • 7. Re: SQL Developer v4.2 Connection Reset Error appearing randomly!
                      Gary Graham-Oracle
                      Gary Graham-Oracle May 9, 2017 7:05 PM (in response to Sam_P)

                      I may be wrong but, given the change in the 12.2 JDBC driver mentioned previously, I am under the impression that...

                      1. Either a patch to 4.2 production or the next release will resolve the connection reset issue when using the 12.2 JDBC driver.

                      2. Avoid using 4.1.x with a 12.2 JDBC driver.

                      3. Using 4.2 production with a pre-12.2 JDBC driver (11.2.0.3 thru 12.1.0.2) may result in a connection reset frequency on par with 4.1.x.

                      4. Finally, it may be that if connection resets become a serious issue for certain users, it would be best to stay with 4.1.x and older drivers.

                       

                      The best person to explain this is out of the office until later this week, but we should be able to get further clarification.

                      • 8. Re: SQL Developer v4.2 Connection Reset Error appearing randomly!
                        Sam_P
                        Sam_P May 9, 2017 9:45 PM (in response to Gary Graham-Oracle)

                        Thanks Gary for your clear explanation about the dependency between the two settings.

                         

                        I have enabled Use Oracle Client checkbox and it is configured correctly with TEST button resulting into Success.

                         

                        I also verified using Help --> About --> Properties that indeed jdbc.library is correctly pointing to C:\oracle\product\11.2.0\client_1\jdbc\lib\ojdbc6.jar and not C:\sqldeveloper\jdbc\lib\ojdbc8.jar, plus sqldeveloper.oci.available = true

                         

                        Currently, I have disabled the Use OCI/Thick driver checkbox purposely so that I can see whether the issue gets better or worse. Originally, when it was enabled (ticked), it was giving me connection resets frequently. At the moment, it has not but probably it is too early to draw any conclusions.

                         

                        I'll continue observing this behaviour and keep you posted, hopefully, by then, your colleague will be back.

                        • 9. Re: SQL Developer v4.2 Connection Reset Error appearing randomly!
                          Sam_P
                          Sam_P May 10, 2017 6:59 PM (in response to Gary Graham-Oracle)

                          Hey Gary,

                           

                          Forgot to describe earlier how our team works and connects to various schemas.

                           

                          When a new user joins our team, they get assigned a username & pwd by the IT DBA team so that they can connect to their own PERSONAL SCHEMA simply using their username and pwd. However, we discourage any team member to work off of their PERSONAL SCHEMA in "silo" and for other reasons, like if and when they leave the company, their PERSONAL SCHEMA gets blown off by the IT DBA team, hence, we would lose all the objects they had created in their PERSONAL SCHEMA.

                           

                          In order to avoid this, we have created several common, shared SUBJECT-AREA SPECIFIC SCHEMAS (based on projects or whatever), where all the team members PROXY CONNECT to these SUBJECT SCHEMAS and work collaboratively. In this case, all our team members connect to these schemas using USERNAME[SUBJECT_SCHEMA_NAME] and pwd.

                           

                          Given that SQL Developer is notoriously ignorant in accommodating PROXY_CONNECT method in its other features (I have 2 other SRs created for us not being able to use certain features due to this), it made me realize if OCI/Thick driver (Type 2) connection is LESS friendly to PROXY CONNECTION method as compared to OCI/Thick driver (Type 4) connection, which is more accommodating to PROXY CONNECTION method?

                           

                          My team and I are starting to notice that as soon as we un-checked the OCI/Thick driver setting and relaunched SQL Developer, we're no longer seeing the issue, albeit, it may be a bit pre-mature to conclude but much better than before, where we would experience multiple connection resets in an hour!

                           

                          thatJeffSmith-Oracle

                          • 10. Re: SQL Developer v4.2 Connection Reset Error appearing randomly!
                            Gary Graham-Oracle
                            Gary Graham-Oracle May 10, 2017 11:09 PM (in response to Sam_P)

                            I really cannot comment much about Thin vs Thick driver stability, or regular vs proxy connection issues, based just on my own experience.  That is why I added a link to a Stack Overflow discussion in reply 3 that includes this comment (emphasis mine)...

                            Unless you have a dependency on a feature that is only available in the JDBC-OCI driver and not in the JDBC-thin driver, the recommendation from Oracle is to use thin. The most recent Oracle Database features such as Transaction Guard or Application Continuity are only available in the JDBC-thin driver. The thin driver is also more used than the JDBC-OCI driver so bugs will be fixed more quickly. It's considered as more stable. If you're still not convinced think that Oracle Weblogic Server ships with the JDBC-thin driver only.

                            It is encouraging to hear your user experience thus far seems much improved using the Thin driver.  Thanks for the feedback!

                             

                            Edit: Another comment regarding the OCI/Thick driver. It is a well-known fact that attempts to interrupt SQL and PL/SQL are more likely to succeed if the OCI/Thick driver is in use. This is because Thick has many more points in the code where interrupts get checked for.  So it makes sense when using Thin that a database server will be much less likely to respond to any interrupts from a client, regardless of being necessary or just as a by-product of an obsolete workaround.

                            • 11. Re: SQL Developer v4.2 Connection Reset Error appearing randomly!
                              Sam_P
                              Sam_P May 12, 2017 4:30 PM (in response to Gary Graham-Oracle)

                              Hey Gary,

                               

                              Happy Friday!

                               

                              Unfortunately, the connection resets are back not only for me but my entire team is starting to report.

                               

                              The only difference is one connection reset per day instead of several in an hour. So, the issue still remains.

                               

                              Would appreciate if you could follow-up with your colleague once he's back.

                               

                              Thanks in advance!

                              • 12. Re: SQL Developer v4.2 Connection Reset Error appearing randomly!
                                Gary Graham-Oracle
                                Gary Graham-Oracle May 12, 2017 5:25 PM (in response to Sam_P)

                                I gave John a ping, and perhaps he can provide additional advice. Given your current configuration, it may be that the reduction in connection resets may be as good as it gets.

                                 

                                Do I understand correctly that, using some prior SQL Developer release with a 11.2.0.3 JDBC OCI/Thick driver, you did not experience connection resets?

                                • 13. Re: SQL Developer v4.2 Connection Reset Error appearing randomly!
                                  Sam_P
                                  Sam_P May 12, 2017 5:44 PM (in response to Gary Graham-Oracle)

                                  Thanks Gary,

                                   

                                  Prior to the upgrade, our entire team were on SQL Developer 4.1.5 using Oracle Client 11.2.0.1 but as part of the upgrade, we upgraded both SQL Developer to 4.2.0 and Oracle Client 11.2.0.3. Previously, we did not have the setting Use OCI/Thick driver enabled  and Use Oracle Client setting was failing (when clicking on the TEST button since it said, we needed at least Oracle Client 11.2.0.3 for it to work).

                                   

                                  After the upgrade, Use Oracle Client setting is working fine (TEST button resulting into Success) and we also enabled Use OCI/Thick driver setting but it started to create a lot of connection resets. I am not 100% sure that if this is the setting that is causing the issue, however, it seems to have some effect in reducing the frequency of the connection resets, compared to when it is enabled. However, since the issue still remains, probably there's something else that could be causing this issue.

                                  • 14. Re: SQL Developer v4.2 Connection Reset Error appearing randomly!
                                    Gary Graham-Oracle
                                    Gary Graham-Oracle May 12, 2017 6:36 PM (in response to Sam_P)

                                    Thanks for the update.  Please note it is highly probable our QA team focuses on a specific configuration of Java/JDBC driver/Oracle client (if any) for any given release.  Your particular configuration may or may not have been tested.  Tested configurations likely use the Thin driver unless a feature requires OCI/Thick.  For example...

                                     

                                    1. SQL Developer 4.1.3:  12.1.0.2 JDBC driver compiled with JDK 1.6  (early adopter versions with 11.2.0.3 JDBC driver)

                                    2. SQL Developer 4.1.5:  12.1.0.2 JDBC driver compiled with JDK 1.7  (running under JDK 1.8.0_102)

                                    3. SQL Developer 4.2.0:  12.2.0.1 JDBC driver compiled with JDK 1.8  (running under JDK 1.8.0_121)

                                     

                                    You can see we try to use the most up-to-date versions coincident with our release cycles.

                                    1 2 3 Previous Next