10 Replies Latest reply on Jun 5, 2018 1:19 AM by rp0428

    Can connect to the local DB using sqlplus, but not SQL Developer

    1058971

      The local instance of Oracle 12c runs under Solaris 11.3 and I can connect to it in sqlplus:

       

      oracle@s11301:~$ sqlplus "/ as sysdba"

       

      SQL*Plus: Release 12.2.0.1.0 Production on Wed May 30 21:42:13 2018

       

      Copyright (c) 1982, 2016, Oracle.  All rights reserved.

       

       

      Connected to:

      Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

       

      SQL> SELECT sys_context('USERENV', 'SID'), sys_context('userenv','instance_name') from dual;

       

      SYS_CONTEXT('USERENV','SID')

      --------------------------------------------------------------------------------

      SYS_CONTEXT('USERENV','INSTANCE_NAME')

      --------------------------------------------------------------------------------

      13

      orcl1

       

       

      SQL>

       

      But when I am testing a new connection to the same instance in SQL Developer, I get the following error:

      Screenshot.png

      The port number 1539 is from the listener.ora (I don't know why it is not 1521, but don't want to touch this yet) and the SID=orcl1 is what this instance was installed under.

       

      Why SQL developer could not connect?

        • 1. Re: Can connect to the local DB using sqlplus, but not SQL Developer

          The port number 1539 is from the listener.ora (I don't know why it is not 1521, but don't want to touch this yet) and the SID=orcl1 is what this instance was installed under.

          The correct credentials can be shown by using TNSPING to the server. Post the results.

           

          For 12c you MUST USE SERVICE_NAME - not sid. The SID you refer to is NOT the same 'sid' as the database. That SID stands for System Identifier.

           

          Change the radio button to use SERVICE_NAME.

           

          Your connection using sql*plus does NOT use tnsnames.ora - it connects directly. Your sql developer 'basic' connection does NOT use tnsnames.ora either - it uses the Java JDBC driver.

           

          And that 'orc11' you are trying to connect to is the root/CDB - it is NOT a PDB. If you have a PDB and want to connect to it you will need to  provide its service name after it registers with the listener.

           

          You can see what 'services' have registered with the listener by issuing 'lsnrctl services'.

          • 2. Re: Can connect to the local DB using sqlplus, but not SQL Developer
            1058971

            It does not matter what the radio button is set to. The error is exactly the same for both SID and service name.

             

            Now that I am playing more with this instance, I am realizing that contrary to the installation guide's assertion that Oracle would start up automatically, it does not.

            I was able to use lsnctrl to start up listener and it reported:

             

            oracle@s11301:~$ lsnrctl start

             

            LSNRCTL for Solaris: Version 12.2.0.1.0 - Production on 31-MAY-2018 20:02:23

             

            Copyright (c) 1991, 2016, Oracle.  All rights reserved.

             

            Starting /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait...

             

            TNSLSNR for Solaris: Version 12.2.0.1.0 - Production

            System parameter file is /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora

            Log messages written to /u01/app/oracle/diag/tnslsnr/s11301/listener/alert/log.xml

            Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=s11301)(PORT=1539)))

            Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

             

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=s11301)(PORT=1539)))

            STATUS of the LISTENER

            ------------------------

            Alias                     LISTENER

            Version                   TNSLSNR for Solaris: Version 12.2.0.1.0 - Production

            Start Date                31-MAY-2018 20:02:26

            Uptime                    0 days 0 hr. 0 min. 3 sec

            Trace Level               off

            Security                  ON: Local OS Authentication

            SNMP                      OFF

            Listener Parameter File   /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora

            Listener Log File         /u01/app/oracle/diag/tnslsnr/s11301/listener/alert/log.xml

            Listening Endpoints Summary...

              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=s11301)(PORT=1539)))

              (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

            The listener supports no services

            The command completed successfully

            Then I realized that the DB is not running too:

             

            oracle@s11301:~$ sqlplus "/ as sysdba"

             

            SQL*Plus: Release 12.2.0.1.0 Production on Thu May 31 20:05:24 2018

             

            Copyright (c) 1982, 2016, Oracle.  All rights reserved.

             

            Connected to an idle instance.

             

            SQL> startup

            ORACLE instance started.

             

            Total System Global Area 1610612736 bytes

            Fixed Size            8785112 bytes

            Variable Size          520094504 bytes

            Database Buffers     1073741824 bytes

            Redo Buffers            7991296 bytes

            Database mounted.

            Database opened.

            SQL> SELECT sys_context('USERENV', 'SID'), sys_context('userenv','instance_name'), sys_context('userenv', 'service_name') from dual;

             

            SYS_CONTEXT('USERENV','SID')

            --------------------------------------------------------------------------------

            SYS_CONTEXT('USERENV','INSTANCE_NAME')

            --------------------------------------------------------------------------------

            SYS_CONTEXT('USERENV','SERVICE_NAME')

            --------------------------------------------------------------------------------

            237

            orcl1

            SYS$USERS

             

             

            SQL> Show parameter service_name

             

            NAME                     TYPE     VALUE

            ------------------------------------ ----------- ------------------------------

            service_names                 string     orcl

            SQL> quit     

            So now the DB is running and the listener is running too, but I still cannot connect the SQL Developer. The error is different though:

             

            Screenshot.png

            • 3. Re: Can connect to the local DB using sqlplus, but not SQL Developer

              That exception is now telling you that the SERVICE_NAME you are using is NOT a service known by the listener.

               

              You are using two other methods to look at a 'service name' value but the ONLY services that matter if you are trying to connect using the listener are the services that the listener has registered.

               

              I already told you how to list the listener services so don't know why you used those other two instead of the one that counts.

               

              It does not matter what the radio button is set to. The error is exactly the same for both SID and service name.

              Well - it DOES MATTER what the radio button is set to if you want to connect. I understand your first error  might be the same either way but that doesn't mean the the button value doesn't matter.

               

              1. list the listener services - those are the ONLY SERVICE_NAME values you can use

              2. do a tnsping - to confirm the connection properties

               

              You may now have started the root/CDB but PDBs will NOT startup by default.

              • 4. Re: Can connect to the local DB using sqlplus, but not SQL Developer
                1058971

                When this instance was installed, its SID was set to orcl and its service name to orcl1. Neither allows to connect.

                I get a feeling that the installation is seriously messed up. After I start the DB from sqlplus, and try to start the listener, it always prints that it supports no services:

                 

                oracle@s11301:/u01/app/oracle/product/12.2.0/dbhome_1/bin$ lsnrctl start

                 

                LSNRCTL for Solaris: Version 12.2.0.1.0 - Production on 03-JUN-2018 09:07:08

                 

                Copyright (c) 1991, 2016, Oracle.  All rights reserved.

                 

                Starting /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait...

                 

                TNSLSNR for Solaris: Version 12.2.0.1.0 - Production

                System parameter file is /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora

                Log messages written to /u01/app/oracle/diag/tnslsnr/s11301/listener/alert/log.xml

                Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=s11301)(PORT=1539)))

                Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

                 

                Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=s11301)(PORT=1539)))

                STATUS of the LISTENER

                ------------------------

                Alias                     LISTENER

                Version                   TNSLSNR for Solaris: Version 12.2.0.1.0 - Production

                Start Date                03-JUN-2018 09:07:08

                Uptime                    0 days 0 hr. 0 min. 0 sec

                Trace Level               off

                Security                  ON: Local OS Authentication

                SNMP                      OFF

                Listener Parameter File   /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora

                Listener Log File         /u01/app/oracle/diag/tnslsnr/s11301/listener/alert/log.xml

                Listening Endpoints Summary...

                  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=s11301)(PORT=1539)))

                  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

                The listener supports no services

                The command completed successfully

                Last I had to install Oracle it was 11 XE under Linux. The install went smoothly w/o a hiccup, and I could connect right after a reboot and everything worked OOB. But here under Solaris, after following the installation guide to the letter, and having issues along the way which were not metioned, I am discovering that there is a long list of maintenance items also not mentioned anywhere, to complete. One of them is auto-starting the DB. I used the dbstart script as instructed here https://docs.oracle.com/database/121/UNXAR/strt_stp.htm#UNXAR140  and it printed error messages:

                 

                oracle@s11301:/u01/app/oracle/product/12.2.0/dbhome_1/bin$ ./dbstart

                ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener

                Usage: ./dbstart ORACLE_HOME

                cat: cannot open /etc/oratab: No such file or directory

                cat: cannot open /etc/oratab: No such file or directory

                cat: cannot open /etc/oratab: No such file or directory

                cat: cannot open /etc/oratab: No such file or directory

                But according to the document itself, it should be using the file from another path:

                 

                On Oracle Solaris:

                $ cat /var/opt/oracle/oratab 

                How can I showcase this DB engine, if I cannot install it for the life of me, and it takes weeks to tighten loose ends, while its competition MS SQL and IBM DB2 install OOB with a literally 1-click install, and are up and running in minutes? I wanted to demonstrate the power of code in the packages, such as FTP, HTTP and XML etc, all that I wanted to do, and these errors and issues rained on me like cats and dogs. The impression was totally ruined by this heavily high-maintenance install process that cannot even complete. I simply do not get it how on their own OS they cannot create a 1-click install for their own DB engine. This is oh, so lame.

                • 5. Re: Can connect to the local DB using sqlplus, but not SQL Developer

                  If you have installation or other Oracle issues you need to post those questions in the General DB forum.

                  General Database Discussions

                   

                  If 'lsnrctl services' shows no services then there is NOTHING for that listener to connect you to.

                   

                  It means no services have registered with THAT listener.

                   

                  They may not have registered with that particular listener but may be registered with a different listener if there is one.

                   

                  A DB will attempt to register periodically so if after a few minutes (usually only 30 seconds or so) the listener shows no services then the DB may not be up and running or may be configured for a different port/listener or may have configuration problems.

                   

                  Please repost in the DB forum where we can help you with that problem.

                  • 6. Re: Can connect to the local DB using sqlplus, but not SQL Developer
                    1058971

                    These questions you are posing might make sense to you, but when I know that I just installed a vanilla Solaris 11.3 and a vanilla Oracle 12c DB, in my mind there can be no "other listener" as nothing could have possibly created it. This is a brand new installation. Re re-posting in another forum... Sure, I will, maybe in a few years when things improve on the vendor's side. As no one is interested in Oracle anymore around here, seeing how it is virtually impossible to get the environment up and running in the first place, using Oracle's only components: Solaris, 12c DB, and SQL Developer, and following the install guide like a bible.

                    • 7. Re: Can connect to the local DB using sqlplus, but not SQL Developer

                      but when I know that I just installed a vanilla Solaris 11.3 and a vanilla Oracle 12c DB, in my mind there can be no "other listener" as nothing could have possibly created it.

                      Yes - YOU know that - but try to understand that on the forum we can ONLY based our comments on what you post. We can NOT see your machine and we have no way of knowing what, or how many, versions of things you may have tried to install.

                      Re re-posting in another forum... Sure, I will, maybe in a few years when things improve on the vendor's side.

                      Again - we are TRYING to help you but your problem is an Oracle DB/installation issue and has NOTHING to do with Sql developer.

                       

                      The Oracle experts that can help you generally will NOT see this thread of yours in the Sql Developer forum. They WILL SEE it if you move it, or repost it, in the forum I referred you too.

                       

                      As no one is interested in Oracle anymore around here, seeing how it is virtually impossible to get the environment up and running in the first place, using Oracle's only components: Solaris, 12c DB, and SQL Developer, and following the install guide like a bible.

                      By 'around here' I assume you mean in your company. Because the volunteers on the forums are certainly still interested in Oracle.

                       

                      But getting 'the environment up and running' is a database issue - it has NOTHING to do with Sql Developer.

                       

                      I understand you posted in this forum because you thought your connection issue is something to do with Sql developer and how you were configuring it.

                       

                      That is WRONG - your connection issue is because your DB has NOT registered with the listener - as you showed your listener supports no services.

                       

                      Experts in Sql Developer can't help you with that problem.

                       

                      Experts in the other forum can help you with that. Search that other forum for threads about registration and listener issues.

                       

                      1. Your DB may be trying to register with different parameters.

                      2. The listener registration may be failing - the listener log often has info about the reasons

                      3. your Db may not even be up and running - some DBs are not configured to start up automatically when a reboot occurs

                      4. your PDBs, if any, may not be up and running - they are, by default, not configured to start up automaticall

                       

                      You can get help with any, and all, of those in the other forum.

                      • 8. Re: Can connect to the local DB using sqlplus, but not SQL Developer
                        1058971

                        I have misunderstood the purpose of this forums then. If they are only visited by independent 3d party volunteers, I am not interested in participating anymore, as apparently none of these issues are reaching Oracle and never going to be addressed, so all of this is summarily pointless.

                        • 9. Re: Can connect to the local DB using sqlplus, but not SQL Developer
                          EdStevens

                          1058971 wrote:

                           

                          I have misunderstood the purpose of this forums then. If they are only visited by independent 3d party volunteers, I am not interested in participating anymore, as apparently none of these issues are reaching Oracle and never going to be addressed, so all of this is summarily pointless.

                          If you would stay and actually try to work with the volunteers, they could probably solve your problem.  Thousands of people have successfully installed Oracle and created databases by simply reading and following the Installation Guide.  That would not be possible if there were a problem that Oracle (corp) needed to "address".  Most likely there is still something in the installation and configuration that you've missed, in spite of your assertion to the contrary.  I know where my money would be . . .

                          • 10. Re: Can connect to the local DB using sqlplus, but not SQL Developer

                            I have misunderstood the purpose of this forums then.

                            Perhaps you have.

                             

                            The forums are a PUBLIC place for ANYONE, anywhere in the world, to ask questions about Oracle products and to seek help for problems or issues they are having with those products.

                             

                            There are separate forums for each of the major product areas.

                             

                            Each of the forums are monitored by volunteers from all over the world that have expertise, or an interest, in the subject matter of that forum. Some of the volunteers indeed actually work for Oracle but, in the forums, are NOT speaking for Oracle but rather for themselves. In fact Jeff Smith, the manager of the Sql Developer team, participates on this Sql Dev forum regularly. I can't speak for him but a likely reason he hasn't chimed in on your thread is that your problems really have NOTHING to do with Sql Developer - at least not as far as I can tell.

                             

                            The forums are NOT for reporting bugs. That should be done using a MOS account that is part of a paid support contract.

                            If they are only visited by independent 3d party volunteers, I am not interested in participating anymore

                            That is certainly your prerogative.

                             

                            as apparently none of these issues are reaching Oracle and never going to be addressed, so all of this is summarily pointless.

                            You can reach Oracle directly using your MOS account.

                            https://support.oracle.com/epmos/faces/MosIndex.jspx?_afrLoop=246919069789361&_afrWindowMode=0&_adf.ctrl-state=mieog04e7…

                             

                            A MOS account is the ONLY direct channel for getting paid support for Oracle products.

                             

                            The issues you mention in your thread are common problems that MANY OTHER PEOPLE have had with database installations. Your issues in particular have been addressed MANY times in threads on the General Database forum I referred you to once I was certain that  your issues had NOTHING to do with Sql Developer.

                             

                            At least some of the problems you report are likely rather simple installation/configuration issues. You have provided a lot more detail info than most posters provide.

                             

                            Unfortunately you provided it in the wrong forum for the problems you are having. There is no 'fault' involved - I directed you to the General Database forum because, as an active participant on that forum, I happen to know it is where you can get the best help for the problems you are having.

                             

                            When you need band-aids the best place to get them is a pharmacy or drug-store. You might get lucky asking for them at the gas station but  the pharmacy has experts that can help you much better.

                             

                            The forums are FREE - and, as John Arbuckle used to say 'you get what you pay for'.

                             

                            Sorry you think you are getting the run around but if you still want help then use the 'Action' selection at the top right of the screen and move the thread to the General Database forum.