11 Replies Latest reply on Jun 17, 2020 4:30 PM by Sven W.

    Can connect using SQL Developer but not using SQLPlus with XE 18c

    LeFromage

      Hello,

       

      I recently installed OracleXE184.

       

      I can connect to the database using SQL Developer. However, I cannot using SQLPlus. When attempting to connect using SQLPlus, I'm given the following error message:

       

      ERROR:

      ORA-01034: ORACLE not available

      ORA-27101: shared memory realm does not exist

      Process ID: 0

      Session ID: 0 Serial number: 0

       

       

      What should I do? Why does it work with Developer but not SQLPlus?

       

      Thank you :-)

        • 1. Re: Can connect using SQL Developer but not using SQLPlus with XE 18c
          cormaco

          Please change your display name into something readable:

          How can I change my Display Name?

           

          SQL Developer uses JDBC, SQLplus uses OCI to connect.

          What is your Operating System? Some setup step seems to be missing.

          Post the connection window from the working connection in SQL Developer and exactly how you tried to connect with SQLplus

          • 2. Re: Can connect using SQL Developer but not using SQLPlus with XE 18c
            Dude!?

            How to you connect? What is your OS?

             

            The error you are seeing usually means the database instance isn't running. This can be the case, for example, when your SID is invalid. Keep in mind under Linux and Unix, the SID is case sensitive.

            • 3. Re: Can connect using SQL Developer but not using SQLPlus with XE 18c
              Dude!?

              SQL Developer uses JDBC, SQLplus uses OCI to connect.

              What, that's all?!

              • 4. Re: Can connect using SQL Developer but not using SQLPlus with XE 18c
                LeFromage

                Hi cormaco and Dude!

                 

                Thanks for answering.

                 

                I am using Windows 10 Pro, 64 bit.

                 

                I've attached screenshots of the connection window using SQL Developer, as well as SQLPlus. When using the same username and password for both, I can connect using SQL Developer, but not SQLPlusOracleConnectionWindow.PNGOracle not available.PNG

                 

                Dude! Could you tell me more about how to make sure the instance may not be running? Here is a screenshot of my Instance Manager:

                 

                InstanceManager.PNG

                 

                According to that, it seems that both instances are running. Could my problem be that I'm not properly specifying the correct instance? If not, how do I do that? Also, how can I make sure that my SID is correct?

                 

                It's my first time using Oracle Database, so I really appreciate your help and patience.

                 

                Thank you :-)

                • 5. Re: Can connect using SQL Developer but not using SQLPlus with XE 18c
                  L. Fernigrini

                  Show us how you connect to sqlplus  (the command line)

                   

                  try using:

                   

                  sqlplus system/<<Password>@XE   (connect to the Container (CDB) database)

                  sqlplus system/<<Password>@XEPDB1 (connect to the PDB)

                   

                  If that does not work, show us the errors, it may mean that the tnsnames file has not been configured.

                   

                  you can also set the ORACLE_SID variable and connect to the CDB, and then switch container:

                   

                   

                  SET ORACLE_SID=XE

                  sqlplus system/<<Password>

                   

                  Once you are connected (to the CDB) you can switch to the PDB

                   

                  ALTER SESSION SET CONTAINER=XEPDB1

                   

                   

                  Here is geatly explained:

                  https://oracle-base.com/articles/12c/multitenant-connecting-to-cdb-and-pdb-12cr1

                  • 6. Re: Can connect using SQL Developer but not using SQLPlus with XE 18c
                    LeFromage

                    Thank you for your answer, L. Fernigrini,

                     

                    When I try to connect to the CDB with <<Password>@XE, I get the following error message:

                     

                    ERROR:

                    ORA-12154: TNS:could not resolve the connect identifier specified

                     

                    When I try to connect to the PDB with <<Password>@XEPDB1, I get the following error message:

                     

                    ERROR:

                    ORA-01034: ORACLE not available

                    ORA-27101: shared memory realm does not exist

                    Process ID: 0

                    Session ID: 0 Serial number: 0

                     

                    When I try to set the ORACLE_SID variable by following what you wrote in your response, I get the following error:

                     

                    SP2-0735: unknown SET option beginning "ORACLE_SID..."

                     

                    Can anyone help with this? It seems nothing is working and I can't figure out what is missing.

                     

                    Thank you

                    • 7. Re: Can connect using SQL Developer but not using SQLPlus with XE 18c
                      L. Fernigrini

                      That means:

                       

                      1) the XE service name is not defined on the tnsnames file. I'm pretty sure XE is the name used by the service, but not 100%

                       

                      2) The XEPDB1 service is defined, but the DB is not up. You need to start it

                       

                      3) You need to use the SET at OS (Windows) level and then execute sqlplus.

                      Once you are connected, you may need to open the PDB (XEPDB1) You can use this to both open all PDBs and configure them to start automatically eith the CDB:

                       

                      SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
                      SQL> ALTER PLUGGABLE DATABASE ALL SAVE STATE;

                       

                       

                      Try setting the variable and connecting with SQLPlus, if it works then issue the two command I mention above and try to change container.,

                      If you cannot connect with sqlplus show us the error,

                      • 8. Re: Can connect using SQL Developer but not using SQLPlus with XE 18c
                        LeFromage

                        Dear L. Fernigrini,

                         

                        Thank you SO MUCH for your help.

                         

                        I followed your instructions and can now connect with SQLPlus.

                         

                        So, from my understanding, the issue was that the XE service name was not defined on the tnsnames file. Oddly enough, I checked out the tnsnames file just before going through with your instructions, and it seemed fine:

                         

                        XE =

                          (DESCRIPTION =

                            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.100)(PORT = 1521))

                            (CONNECT_DATA =

                              (SERVER = DEDICATED)

                              (SERVICE_NAME = XE)

                            )

                          )

                         

                        Though I couldn't connect. Now, if I SET ORACLE_SID=XE in cmd prompt before running sqlplus, I connect just fine.

                         

                        Question, though. It seems that I have to set the ORACLE_SID every time I try to connect using sqlplus. Is there some way to keep it set to XE permanently?

                         

                        Thank you again so much for your time, patience, and attention. I've spent a really long time trying to figure out what to do, and as I am not that computer proficient, I could not find the help I needed elsewhere. So again, thank you for taking the time to go through this with me.

                        • 9. Re: Can connect using SQL Developer but not using SQLPlus with XE 18c
                          Dude!?

                          I don't have XE and MS Windows at my disposal at the moment, but the underlying concepts how connections work will be the same regardless of platform.

                           

                          If you understand the following, some things will become much clearer.

                           

                          [oracle@localhost ~]$ lsnrctl status

                           

                          LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 10-JUN-2020 05:55:25

                           

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

                           

                          Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

                          TNS-12541: TNS:no listener

                          TNS-12560: TNS:protocol adapter error

                            TNS-00511: No listener

                             Linux Error: 111: Connection refused

                          This means the listener process hasn't been started.

                           

                          Yet, I can still connect, even with a user that doesn't exist.

                           

                          [oracle@localhost ~]$ sqlplus humpty/dumpty as sysdba

                           

                          SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 10 05:51:05 2020

                          Version 19.3.0.0.0

                           

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

                           

                          Connected to:

                          Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

                          Version 19.3.0.0.0

                           

                          SQL> show user

                          USER is "SYS"

                          SQL>

                           

                          So how does this work?

                           

                          The answer is you are at the command prompt of machine where the Oracle server is installed. By using "sqlplus / as sysdba" you are connecting using OS authentication and the Bequeath protocol, which connects using IPC and not TCP/IP.

                           

                          By using "as sysdba" you will always connect to the SYS schema, regardless of username and password. You need some way to start the database and you cannot use any stored username and password of the database unless it's running.

                           

                          All this requires, beside the software, is a user account that has the necessary Oracle DBA privileges, and the correct ORACLE_SID environment variable defined.

                           

                          So where does the Oracle Net*listener and tnsnames come into play? Put simply when you connect using TCP/IP or make a remote connection from another system. The format is usually "sqlplus user/password@SID" or @servicename, and "sqlplus sys/password@... as sysdba" to connect to the SYS schema. For remote connections "as sysdba" to work, you need to create a "password" file.

                           

                          SID or servicename needs to match the exact entry you have specified in the tnsnames.ora file on your local computer, which translates your connect string to a hostname, port, etc, similar like DNS resolves the TCP/IP address when you connect to www.google.com. The servicename, btw, is just a name and not necessarily a TCP/IP address.

                           

                          You do not need to create a listener configuration on your server. When the oracle database starts, PMON will automatically register with a listener named LISTENER. The listener process of course needs to be running. For example:

                           

                          [oracle@localhost ~]$ lsnrctl status

                          ...

                          Service "orcl.localhost.localdomain" has 1 instance(s).

                            Instance "ORCL", status READY, has 1 handler(s) for this service...

                           

                          Status "READY" means the listener record was created by the database. Otherwise, if it was an entry you added by hand, it would show "status unknown". Why do you need a static entry? For example, to connect remotely via TCP/IP in order to start the database.

                           

                          You can btw, connect to the database using EZconnec, which is a special form that does not use tnsnames.ora.

                          https://www.orafaq.com/wiki/EZCONNECT

                           

                          This will work without tnsnames.ora on any machine from where you wish to connect. But you need to have the listener running on the server to accept appropriate TCP/IP connections, either with a static entry you added to the listener.ora config by hand, or using a service name that was automatically registered by the database after startup.

                          • 10. Re: Can connect using SQL Developer but not using SQLPlus with XE 18c
                            L. Fernigrini

                            Dude explained a lot about Oracle network basics and connections, hope you were able to read the entire post and understand it.

                             

                            Regarding your questions, you can set the ORACLE_SID variable in Windows configuration, there are plenty of pages explaining that.

                            https://support.shotgunsoftware.com/hc/en-us/articles/114094235653-Setting-global-environment-variables-on-Windows

                             

                            Since the DB is up and you are able connect "directly" without using network, the problem is that your database is not registered with the listener (that means that the listener does not know that there is a XE database running)

                             

                            Please post the results of:

                            lsnrctl status

                            lsnrctl services

                             

                            Also, connect to the db with sql*plus directly (setting ORACLE_SID) and execute the following:

                             

                            show listener

                             

                            And show us the values of those parameters, they may be telling the DB to register to a Listener that is not the one currently running...

                            • 11. Re: Can connect using SQL Developer but not using SQLPlus with XE 18c
                              Sven W.
                              ...

                               

                              Though I couldn't connect. Now, if I SET ORACLE_SID=XE in cmd prompt before running sqlplus, I connect just fine.

                               

                              Question, though. It seems that I have to set the ORACLE_SID every time I try to connect using sqlplus. Is there some way to keep it set to XE permanently?

                               

                              You might want to check your SQLNET.ORA file. In there is defined which connect methods are allowed.
                              One line should be something like

                              NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


                              There order of those entries specifies which connection method is used first. It could be that TNSNAMES is missing in your list. In that case sqlplus would never try to read the tnsnames.ora file.

                              Also SERVICE_NAME and SID are slightly different things. However for the CDB the SID is also exposed as a SERVICE_NAME, so for standard configurations, this should not play a major role.


                              Possible, but not recommended (SERVICE_NAME is better):
                              XE =

                                (DESCRIPTION =

                                  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.100)(PORT = 1521))

                                  (CONNECT_DATA =

                                    (SERVER = DEDICATED)

                                    (SID = XE)

                                  )

                                )

                               

                               

                              If that works, then maybe your database has a configured GLOBAL_NAME parameter. The global name is the sevice_name + the domain. (could also be in SQLNET.ORA what the default domain is). Often this is set to .global or .world.

                               

                               

                              You then might want to try and add this global name parameter to the TNS Alias and/or to the service name.

                               

                              XE.global =

                                (DESCRIPTION =

                                  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.100)(PORT = 1521))

                                  (CONNECT_DATA =

                                    (SERVER = DEDICATED)

                                    (SERVICE_NAME = XE.world)

                                  )

                                )