10 Replies Latest reply: Jul 11, 2014 2:03 PM by jiri1 RSS

    developerday - Database App Development VM - cannot connect sqlplus

    jiri1

      Database App Development VM

      Followed instructions below. When trying sqlplus / as sysdba, appears: TNS:listener does not currently know of service requested in connect descriptor

      Is there any instruction missing or is there a bug?

      Thank you

       

       

      Step 1. Download and install Oracle VM VirtualBox on your host system.

      Step 2. Download the files (the use of a download manager is highly recommended):

       

      DownloadOTN_Developer_Day_VM.ova (6,032,354,304 bytes, md5sum: 470ab16bdc9cd38f6fb4720835a0954d)

      Step 3. Import your VM: File > Import Appliance to launch Appliance Import Wizard. ClickChoose... to browse to the directory you re-assembled all the files in and select theOTN_Developer_Day_VM.ova then click Next> to begin importing the virtual machine. It will prompt you to agree to the appropriate developer licenses while importing. You will see 'Oracle Developer Days (Powered Off)' when it is finished importing.

      Step 4. Test your VM: Once the import has completed, double-click the OTN Developer Days VM. Click OK to close the Virtualbox Information dialogs. When you get to the Enterprise Linux 6 screen you can now login. (Username and password is oracle.) Allow the process to complete; it is ready when you see a terminal window, which you can close. Once you are finished working in the guest VM you can shut it down via System > Shut Down; this will return the guest VM to the Powered Off state.


        • 2. Re: developerday - Database App Development VM - cannot connect sqlplus
          jiri1

          Thank you. This is good article and I had read it already before posting on this forum.

          I didn't want to try static registration as you describe, but dynamic, because always it works.

          "Dynamic registration is accomplished when the pmon process of the database instance contacts the listener and requests registration. This occurs at instance startup, and every few minutes during the life of the instance."

          Problem is that I cannot connect to instance by SQLPLUS, so is there a way how to do it?

          • 3. Re: developerday - Database App Development VM - cannot connect sqlplus
            Zoltan Kecskemethy

            sqlplus should no go to the listener if you are oracle software owner (oracle user, in dba group) and you have ORACLE_SID environment variable setup when you issue "sqlplus / as sysdba"

            So this is is kind of strange error to me. Did you try to login from locally from the system?

            Check your settings:

            issue the following commands and copy paste full log / output to us:

            echo $ORACLE_SID

            whoami

            id oracle

            sqlplus "/ as sysdba"

            • 4. Re: developerday - Database App Development VM - cannot connect sqlplus
              jiri1

              Hi,

               

              here are results:

               

              [oracle@private ~]$ echo $ORACLE_SID

              orcl

               

              [oracle@private ~]$ whoami

              oracle

               

              [oracle@private ~]$ id oracle

              uid=500(oracle) gid=500(oracle) groups=500(oracle)

               

              [oracle@private ~]$ sqlplus "/ as sysdba"

               

               

              SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 9 15:09:22 2014

               

               

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

               

               

              ERROR:

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

              descriptor

               

               

               

               

              Enter user-name:

               

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

               

              Maybe problem is with current user hostname (private.rionet.cz - I don't know why VM gets this one, it is provider's network). But when changing hostname to localhost (tns listener runs on localhost), same error appears:

               

              [oracle@localhost ~]$ lsnrctl status

               

               

              LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 09-JUL-2014 15:33:26

               

               

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

               

               

              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

              STATUS of the LISTENER

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

              Alias                     LISTENER

              Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production

              Start Date                09-JUL-2014 15:01:44

              Uptime                    0 days 0 hr. 31 min. 43 sec

              Trace Level               off

              Security                  ON: Local OS Authentication

              SNMP                      OFF

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

              Listener Log File         /u01/app/oracle/product/12.1.0/dbhome_1/log/diag/tnslsnr/private/listener/alert/log.xml

              Listening Endpoints Summary...

                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=80))(PROTOCOL_STACK=(PRESENTATION=HTTP)(SESSION=RAW)))

                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=21))(PROTOCOL_STACK=(PRESENTATION=FTP)(SESSION=RAW)))

                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

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

              The listener supports no services

              The command completed successfully

               

              [oracle@localhost ~]$ sqlplus "/ as sysdba"

               

               

              SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 9 15:35:30 2014

               

               

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

               

               

              ERROR:

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

              descriptor

              • 5. Re: developerday - Database App Development VM - cannot connect sqlplus
                Zoltan Kecskemethy

                I can see an issue. Oracle user is not in dba group!

                 

                See this is one of my server:

                oracle@dbserv:~> id oracle

                uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba),502(oper)

                 

                oinstall, dba, oper are all groups you should use according to the installation manual.

                dba group is required to able to connect to the database without password afaik.

                 

                Check if dba group is exists:

                oracle@dbserv:~> grep dba /etc/group

                dba:x:501:oracle

                 

                If yes you need to add oracle to this group either editing this file as root to get similar to my result. Or by using usermod command.

                If group does not exist add it using groupadd command and add in oracle user after.

                 

                Once done re-login with oracle user and try sqlplus "/ as sysdba" and see it works now.

                • 6. Re: developerday - Database App Development VM - cannot connect sqlplus
                  jiri1

                  Unfortunatelly this issue doesn't cause error with listener.

                  I added groups:

                   

                  [oracle@private ~]$ id oracle

                  uid=500(oracle) gid=500(oracle) groups=500(oracle),54321(oinstall),54322(dba)

                  [oracle@private ~]$ sqlplus / as sysdba

                   

                   

                  SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 10 14:50:55 2014

                   

                   

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

                   

                   

                  ERROR:

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

                  descriptor

                   

                  On another machine I tried to remove group DBA from user Oracle and ORA error was displaying permission denied.

                  • 7. Re: developerday - Database App Development VM - cannot connect sqlplus
                    jiri1

                    SOLVED OUT

                     

                    Problem was with hostname "private.rionet.cz" which I mentioned before.

                    Steps:

                    root# hostname 127.0.0.1 (hostname localhost didn't help)

                    root# /etc/init.d/oracle restart

                     

                    now

                    [oracle@localhost ~]$ lsnrctl status

                     

                     

                    LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 10-JUL-2014 15:59:27

                     

                     

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

                     

                     

                    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

                    STATUS of the LISTENER

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

                    Alias                     LISTENER

                    Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production

                    Start Date                10-JUL-2014 15:53:16

                    Uptime                    0 days 0 hr. 6 min. 12 sec

                    Trace Level               off

                    Security                  ON: Local OS Authentication

                    SNMP                      OFF

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

                    Listener Log File         /u01/app/oracle/product/12.1.0/dbhome_1/log/diag/tnslsnr/127/listener/alert/log.xml

                    Listening Endpoints Summary...

                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=80))(PROTOCOL_STACK=(PRESENTATION=HTTP)(SESSION=RAW)))

                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=21))(PROTOCOL_STACK=(PRESENTATION=FTP)(SESSION=RAW)))

                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

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

                    Services Summary...

                    Service "orcl" has 1 instance(s).

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

                    Service "orclXDB" has 1 instance(s).

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

                    Service "pdb1" has 1 instance(s).

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

                    The command completed successfully

                     

                    Anyway, can anybody advice me where this Virtual Machine takes hostname?

                    Thank you

                    • 8. Re: developerday - Database App Development VM - cannot connect sqlplus
                      jiri1

                      Zoltan, do you have any idea, why user oracle cannot still connect as sysdba? (sys/oracle works)

                       

                      [oracle@localhost ~]$ id oracle

                      uid=500(oracle) gid=500(oracle) groups=500(oracle),54321(oinstall),54322(dba)

                       

                      [oracle@localhost ~]$ sqlplus / as sysdba

                       

                       

                      SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 10 16:01:49 2014

                       

                       

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

                       

                       

                      ERROR:

                      ORA-01017: invalid username/password; logon denied

                       

                       

                       

                       

                      Enter user-name:

                      • 9. Re: developerday - Database App Development VM - cannot connect sqlplus
                        Zoltan Kecskemethy

                        > On another machine I tried to remove group DBA from user Oracle and ORA error was displaying permission denied.

                        that's right. This is the error that you get when your user is not in dba group.

                         

                        > root# hostname 127.0.0.1 (hostname localhost didn't help)

                        Yes I saw in your first output (I requested) that you have listener issues as well.

                        No services were present in status output.

                        Oracle strongly recommends to use static IP for a database server. I guess you use DHCP and dynamic address for your server so that's why you cannot use other IP address in your configuration than localhost (which is kind of static).

                        So if you want permanent good solution for you server you should use static IP and configure all services to use that.

                         

                        > ORA-01017: invalid username/password; logon denied

                        As of this error I'm not sure if I can tell you what's the problem.

                        Can you access the database using: sqlplus "sys/syspwd@orcl as sysdba"

                         

                        Sysdba access requires a password file in $ORACLE_HOME/dbs folder check if "orapworcl" file exists if not you may need to create it using orapwd command.

                        • 10. Re: developerday - Database App Development VM - cannot connect sqlplus
                          jiri1

                          >> Oracle strongly recommends to use static IP for a database server. I guess you use DHCP and dynamic address for your server so that's why you cannot use other IP address in your configuration than localhost (which is >> kind of static).

                          >> So if you want permanent good solution for you server you should use static IP and configure all services to use that.

                           

                          I have no problem with service since I registered it successfully first. No matter if I set static or dynamic IP.

                          But if static is recommended, I will use it.

                          It works now, so it is ok.

                           

                          >> Can you access the database using: sqlplus "sys/syspwd@orcl as sysdba"

                           

                          yes

                           

                          >> Sysdba access requires a password file in $ORACLE_HOME/dbs folder check if "orapworcl" file exists if not you may need to create it using orapwd command.

                           

                          File is present, but password for oracle could be missing, but it is not a problem