5 Replies Latest reply on Sep 5, 2015 12:30 PM by EdStevens

    Oracle Developer VM - Unable to connect

    3022347

      Hi guys,

      I've tried to solve this issue by myself reading all articles available in the net but I'm afraid this is a dead end.

      Scenario:

      OTN_Developer_Day_VM.ova downloaded from Developer Day - Hands-on Database Application Development

      Appliance imported changing only memory (increasing) and network adapter (to bridge).

      I've tried to connect to database with SQL Developer and SQLPlus getting these error messages:

       

      SQL Developer:
      Listener refused the connection with the following error: ora-12514 TNS: listener does not currently know of service requested in connect descriptor.

       

      sqlplus / as sysdba
      ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.

       

      Database Information:

      Oracle SID    : cdb1

      Pluggable DB  : orcl

      $ORACLE_SID = cdb1.

       

      dba and oper groups created with groupadd

      oracle user added to dba, oper and oinstall groups.

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

      [oracle@localhost dbs]$ id oracle

      uid=1000(oracle) gid=1000(oracle) groups=1000(oracle),10(wheel),990(vboxsf),1001(dba),1003(oper),1004(oinstall)

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

      hostname 127.0.0.1

      oracle restart

      ------------------------------------------------------------------------------------------------------------------------------------------
      lsnrctl status:
      ------------------------------------------------------------------------------------------------------------------------------------------
      [oracle@localhost ~]$ lsnrctl status

      LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 22-JUN-2015 08:00:17

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

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
      STATUS of the LISTENER
      ------------------------
      Alias                     LISTENER
      Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
      Start Date                22-JUN-2015 07:49:42
      Uptime                    0 days 0 hr. 10 min. 34 sec
      Trace Level               off
      Security                  ON: Local OS Authentication
      SNMP                      OFF
      Listener Parameter File   /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
      Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
      Listening Endpoints Summary...
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
      The listener supports no services
      The command completed successfully

       

      Any help with this will be much appreciated. I'm trying to solve this to prepare an exam and the fact of not being able to solve it it's killing me.

      Thanks in advance.

      Alfonso

        • 1. Re: Oracle Developer VM - Unable to connect
          EdStevens

          With what you've shown us (and no more) I'd guess the database itself is not started and thus is not registered with the listener.

          • 2. Re: Oracle Developer VM - Unable to connect
            3022347

            Thanks EdStevens for your time and help

            Theoretically, database seems to be started.

             

            oracle@localhost etc]$ ps -ef | grep pmon

            oracle    2942  2815  0 18:36 pts/0    00:00:00 grep --color=auto pmon

             

            But when I try to shut it down, I get the following error message:

             

            [oracle@localhost etc]$ dbshut

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

            Usage: /home/oracle/app/oracle/product/12.1.0/dbhome_1/bin/dbshut ORACLE_HOME

            Processing Database instance "cdb1": log file /home/oracle/app/oracle/product/12.1.0/dbhome_1/shutdown.log

             

            I tried to solve this following this article dbora ORACLE_HOME_LISTENER is not set, unable to start listener

            but no success either... it's a little bit frustrating for a newbie

            • 3. Re: Re: Oracle Developer VM - Unable to connect
              EdStevens

              3

              022347 wrote:

               

              Thanks EdStevens for your time and help

              Theoretically, database seems to be started.

               

              oracle@localhost etc]$ ps -ef | grep pmon

              oracle    2942  2815  0 18:36 pts/0    00:00:00 grep --color=auto pmon

               

              I don't see antything there to support your assertions that "database seems to be started."

              I have no idea why it is reporting a process a '--color=auto pmon', but that is most definitely NOT an oracle pmon process.

               

              ps should show a pmon process for the instance in question:

               

              oracle:mydbname$ ps -ef|grep pmon
              oracle    1953     1  0 13:34 ?        00:00:00 ora_pmon_mydbname
              oracle    2016  1814  0 13:35 pts/0    00:00:00 grep pmon
              

               

               

              And to refine the ps command so it doesn't report its own process

               

              oracle:mydbname$ ps -ef|grep pmon|grep -v grep
              oracle    1953     1  0 13:34 ?        00:00:00 ora_pmon_mydbname
              

               

               

               

              But when I try to shut it down, I get the following error message:

               

              [oracle@localhost etc]$ dbshut

              instead of a script that tries to be too clever by half, try stopping and starting the database directly, with sqlplus

               

              shutdown:

              oracle:mydbname$ sqlplus / as sysdba
              
              SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 3 13:34:24 2015
              
              Copyright (c) 1982, 2013, Oracle.  All rights reserved.
              
              
              Connected to:
              Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
              
              SQL> shutdown immediate
              Database closed.
              Database dismounted.
              ORACLE instance shut down.
              SQL> exit
              

               

               

              Startup

               

              oracle:mydbname$ sqlplus / as sysdba
              
              SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 3 13:34:40 2015
              
              Copyright (c) 1982, 2013, Oracle.  All rights reserved.
              
              Connected to an idle instance.
              
              SQL> startup
              ORACLE instance started.
              
              Total System Global Area  467652608 bytes
              Fixed Size                  2254304 bytes
              Variable Size             197134880 bytes
              Database Buffers          264241152 bytes
              Redo Buffers                4022272 bytes
              Database mounted.
              Database opened.
              SQL>
              

               

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

              Usage: /home/oracle/app/oracle/product/12.1.0/dbhome_1/bin/dbshut ORACLE_HOME

              Processing Database instance "cdb1": log file /home/oracle/app/oracle/product/12.1.0/dbhome_1/shutdown.log

              That whole 'dbshut' and 'dbora' business is pretty old.  I've never used them, mostly because I do not WANT my databases to start automatically on server startup.  I'm not even sure it WILL work with 12c.  The multitenant architecture has changed a lot, especially in terms of connecting, shutting down, and starting up.

              • 4. Re: Re: Oracle Developer VM - Unable to connect
                3022347

                Thanks Ed for the clarification, I still have a long way to understand this new world which Oracle for me... and I like even more every day.

                 

                You're completely right, there were only assumptions... OK, if I run the command "ps -ef|grep pmon|grep -v grep  " I don't get any process with my database name.

                 

                Database Information:

                Oracle SID    : cdb1

                Pluggable DB  : orcl

                 

                When I try to connect as you mention, I get this error message. It doesn't matter if I tried it with oracle user or root.

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

                [oracle@localhost ~]$ sqlplus / as sysdba

                 

                SQL*Plus: Release 12.1.0.2.0 Production on Sat Sep 5 11:42:43 2015

                 

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

                 

                ERROR:

                ORA-12514: TNS:listener does not currently know of service requested in connect

                descriptor

                 

                Enter user-name:

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

                 

                Thanks for your help

                • 5. Re: Re: Re: Oracle Developer VM - Unable to connect
                  EdStevens

                  3022347 wrote:

                   

                  Thanks Ed for the clarification, I still have a long way to understand this new world which Oracle for me... and I like even more every day.

                   

                  You're completely right, there were only assumptions... OK, if I run the command "ps -ef|grep pmon|grep -v grep  " I don't get any process with my database name.

                   

                  Database Information:

                  Oracle SID    : cdb1

                  Pluggable DB  : orcl

                   

                  When I try to connect as you mention, I get this error message. It doesn't matter if I tried it with oracle user or root.

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

                  [oracle@localhost ~]$ sqlplus / as sysdba

                   

                  SQL*Plus: Release 12.1.0.2.0 Production on Sat Sep 5 11:42:43 2015

                   

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

                   

                  ERROR:

                  ORA-12514: TNS:listener does not currently know of service requested in connect

                  descriptor

                   

                  Enter user-name:

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

                   

                  Thanks for your help

                  Maybe 12c is throwing something different into the mix, but up through 11g, that specific error with that specific syntax would indicate that you have the enviornment variable TWO_TASK set, and set incorrectly at that.  Let me explain a bit further.

                   

                  There are essentially two ways to specify the database when you connect.  The syntax you used

                   

                  sqlplus / as sysdba
                  

                   

                  is not specifying a net service name.  Therefore it will, by default try to establish a bequeath (inter-process -- not network) connection to the instance specified by the environment variable ORACLE_SID.

                   

                  Also, as a side issue to this explanation, the '/ as sysdba' is about connection credentials and has nothing to do with specifying the database or if the connection is bequeath or network.  To specify the same connection method for a normal user - without the special sysdba privilege, it would look like this:

                   

                  sqlplus myuser/mypassword
                  

                   

                  The other method looks like this:

                   

                  sqlplus myuser/mypassword@mydb
                  

                   

                  In this form, Oracle net services will take a look in the client's tnsnames.ora file for an entry named 'mydb'.  That will be used to resolve the network connection routing to the db server, the port used by the listener, and specify the sid or service to connect.  If it specifies a service the listener doesn't know about you get ORA-12514. 

                  See ora-12514-listener does not currently know of service 

                   

                  Now, you did not specify a net service name but you got the error anyway.  What's up with that?

                  There is another environment variable in play here.  Its name is TWO_TASK.  If you have TWO_TASK set, tns will take that as specifying a net service name, and proceed accordingly.  Let me demonstrate.

                   

                  First, take a look at my tnsnames.ora.  Notice that I have 2 entries, OKLACITY and DALLAS.  Notice they both specify SERVICE_NAME=oklacity.  In actuality, oklacity is the only instance I have on this server, so setting my tnsnames this way is just giving me two different names by which to refer to oklacity for tns connections.

                   

                  oracle:oklacity$ cat $ORACLE_HOME/network/admin/tnsnames.ora
                  # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
                  # Generated by Oracle configuration tools.
                  
                  OKLACITY =
                    (DESCRIPTION =
                      (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv01.localdomain)(PORT = 1521))
                      (CONNECT_DATA =
                        (SERVER = DEDICATED)
                        (SERVICE_NAME = oklacity)
                      )
                    )
                  
                  DALLAS =
                    (DESCRIPTION =
                      (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv01.localdomain)(PORT = 1521))
                      (CONNECT_DATA =
                        (SERVER = DEDICATED)
                        (SERVICE_NAME = oklacity)
                      )
                    )
                  

                   

                   

                  So I can specify either

                  oracle:oklacity$ sqlplus scott/tiger@oklacity
                  
                  SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 5 07:14:54 2015
                  
                  Copyright (c) 1982, 2009, Oracle.  All rights reserved.
                  
                  
                  Connected to:
                  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                  With the Partitioning, Automatic Storage Management, OLAP, Data Mining
                  and Real Application Testing options
                  
                  SQL> exit
                  Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                  With the Partitioning, Automatic Storage Management, OLAP, Data Mining
                  and Real Application Testing options
                  
                  oracle:oklacity$ sqlplus scott/tiger@dallas
                  
                  SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 5 07:15:10 2015
                  
                  Copyright (c) 1982, 2009, Oracle.  All rights reserved.
                  
                  
                  Connected to:
                  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                  With the Partitioning, Automatic Storage Management, OLAP, Data Mining
                  and Real Application Testing options
                  
                  SQL>
                  

                   

                  And if there is any doubt, we can know it went through the listener by looking at the listener log

                  05-SEP-2015 07:14:54 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oklacity)(CID=(PROGRAM=sqlplus)(HOST=vblnxsrv01.localdomain)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=64723)) * establish * oklacity * 0
                  05-SEP-2015 07:15:10 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oklacity)(CID=(PROGRAM=sqlplus)(HOST=vblnxsrv01.localdomain)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=64727)) * establish * oklacity * 0
                  

                   

                  Now, with that background, how does TWO_TASK figure in?

                  If you specify TWO_TASK, tns will take that value and treat it as your tns specification even though you did not specifically specify a tns connection on your command line.

                  First of all, let's change my tnsnames so that DALLAS points to a non-existing instance:

                  oracle:oklacity$ cat $ORACLE_HOME/network/admin/tnsnames.ora
                  # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
                  # Generated by Oracle configuration tools.
                  
                  OKLACITY =
                    (DESCRIPTION =
                      (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv01.localdomain)(PORT = 1521))
                      (CONNECT_DATA =
                        (SERVER = DEDICATED)
                        (SERVICE_NAME = oklacity)
                      )
                    )
                  
                  DALLAS =
                    (DESCRIPTION =
                      (ADDRESS = (PROTOCOL = TCP)(HOST = vblnxsrv01.localdomain)(PORT = 1521))
                      (CONNECT_DATA =
                        (SERVER = DEDICATED)
                        (SERVICE_NAME = dallas)
                      )
                    )
                  

                   

                  Now, let; first set TWO_TASK to OKLACITY, and try a connection:

                  oracle:oklacity$ export TWO_TASK=OKLACITY
                  
                  oracle:oklacity$ sqlplus scott/tiger
                  
                  SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 5 07:25:06 2015
                  
                  Copyright (c) 1982, 2009, Oracle.  All rights reserved.
                  
                  
                  Connected to:
                  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                  With the Partitioning, Automatic Storage Management, OLAP, Data Mining
                  and Real Application Testing options
                  
                  SQL>
                  

                   

                  Trust me, I could show you the listener.log entry.

                  Now let's set TWO_TASK for DALLAS.  Remember, we have a valid tnsnames entry, but it specifies a bogus instance/service name

                   

                  oracle:oklacity$ export TWO_TASK=DALLAS
                  
                  oracle:oklacity$ sqlplus scott/tiger
                  
                  SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 5 07:26:55 2015
                  
                  Copyright (c) 1982, 2009, Oracle.  All rights reserved.
                  
                  ERROR:
                  ORA-12514: TNS:listener does not currently know of service requested in connect
                  descriptor
                  

                   

                  Voila!  There's your error.

                  And if we set TWO_TASK to something that is not in tnsnames.ora

                  oracle:oklacity$ export TWO_TASK=fubar
                  
                  oracle:oklacity$ sqlplus scott/tiger
                  
                  SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 5 07:28:35 2015
                  
                  Copyright (c) 1982, 2009, Oracle.  All rights reserved.
                  
                  ERROR:
                  ORA-12154: TNS:could not resolve the connect identifier specified
                  

                   

                  So, you have to ask yourself, why am I setting TWO_TASK?  I've been working with ORACLE for nearly 30 years and have never seen a need for it.  It has always seemed to me to be a solution in search of a problem.