8 Replies Latest reply: Mar 26, 2012 7:18 AM by CKPT RSS

    Getting "ORA-12514 error" while connecting first time

    919469
      Hi,

      Getting below error while connecting to the database first time.
      ORA-12514: TNS:listener does not currently know of service requested in connect


      apludc01dat25:e2cmpc1 > tnsping ora37_ap

      TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 26-MAR-2012 12:00:58

      Copyright (c) 1997, 2010, Oracle. All rights reserved.

      Used parameter files:
      /grid/product/crs/network/admin/sqlnet.ora


      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = apl11764clu01-scan-oravip.nat.bt.com)(PORT = 61901)) (CONNECT_DATA = (SERVICE_NAME = ORA37_ap))
      )
      OK (0 msec)


      apludc01dat25:e2cmpc1 > sqlplus

      SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 26 12:00:15 2012

      Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

      Enter user-name: outln@ora37_ap
      Enter password:
      ERROR:
      ORA-12514: TNS:listener does not currently know of service requested in connect
      descriptor


      Enter user-name:
      apludc01dat25:e2cmpc1 > sqlplus

      SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 26 12:00:22 2012

      Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

      Enter user-name: outln@ora37_ap
      Enter password:

      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
      With the Partitioning, Real Application Clusters, OLAP, Data Mining
      and Real Application Testing options

      SQL>

      Please help me out to resolve this issue.

      Thank you.

      Regards,
      Dasarath
        • 1. Re: Getting "ORA-12514 error" while connecting first time
          kuljeet singh -
          apludc01dat25:e2cmpc1 > sqlplus
          SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 26 12:00:22 2012
          Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
          Enter user-name: outln@ora37_ap
          Enter password:
          Connected to:
          Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
          With the Partitioning, Real Application Clusters, OLAP, Data Mining
          and Real Application Testing options
          SQL>
          your above session connected ! so are you getting above error intermittently ? are you in RAC setup ?
          • 2. Re: Getting "ORA-12514 error" while connecting first time
            CKPT
            916466 wrote:
            Hi,

            Getting below error while connecting to the database first time.
            ORA-12514: TNS:listener does not currently know of service requested in connect


            apludc01dat25:e2cmpc1 > tnsping ora37_ap

            TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 26-MAR-2012 12:00:58

            Copyright (c) 1997, 2010, Oracle. All rights reserved.

            Used parameter files:
            /grid/product/crs/network/admin/sqlnet.ora


            Used TNSNAMES adapter to resolve the alias
            Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = apl11764clu01-scan-oravip.nat.bt.com)(PORT = 61901)) (CONNECT_DATA = (SERVICE_NAME = ORA37_ap))
            )
            OK (0 msec)


            apludc01dat25:e2cmpc1 > sqlplus

            SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 26 12:00:15 2012

            Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

            Enter user-name: outln@ora37_ap
            Enter password:
            ERROR:
            ORA-12514: TNS:listener does not currently know of service requested in connect
            descriptor


            Enter user-name:
            apludc01dat25:e2cmpc1 > sqlplus

            SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 26 12:00:22 2012

            Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

            Enter user-name: outln@ora37_ap
            Enter password:

            Connected to:
            Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
            With the Partitioning, Real Application Clusters, OLAP, Data Mining
            and Real Application Testing options

            SQL>

            Please help me out to resolve this issue.

            Thank you.

            Regards,
            Dasarath
            Can you post

            $lsnrctl services
            $lsnrctl status
            When the services registered to database and the status is "READY", you should able to connect without any issues.
            Post the output here of above two commands.
            • 3. Re: Getting "ORA-12514 error" while connecting first time
              919469
              Hi,

              We are getting this error while connecting remotely.

              PFB output of listener status and services

              apl11764a001:ORA37a > lsnrctl services LISTENER_ORA37

              LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 26-MAR-2012 12:18:06

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

              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_ORA37)))
              Services Summary...
              Service "ORA37_ap" has 1 instance(s).
              Instance "ORA37a", status READY, has 1 handler(s) for this service...
              Handler(s):
              "DEDICATED" established:2 refused:0 state:ready
              LOCAL SERVER
              Service "ORA37_ap_XPT" has 1 instance(s).
              Instance "ORA37a", status READY, has 1 handler(s) for this service...
              Handler(s):
              "DEDICATED" established:2 refused:0 state:ready
              LOCAL SERVER
              Service "ORA37aXDB" has 1 instance(s).
              Instance "ORA37a", status READY, has 1 handler(s) for this service...
              Handler(s):
              "D000" established:0 refused:0 current:0 max:1022 state:ready
              DISPATCHER <machine: apl11764a001, pid: 25970>
              (ADDRESS=(PROTOCOL=tcp)(HOST=apl11764a001)(PORT=10862))
              The command completed successfully
              apl11764a001:ORA37a > lsnrctl status LISTENER_ORA37

              LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 26-MAR-2012 12:18:20

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

              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_ORA37)))
              STATUS of the LISTENER
              ------------------------
              Alias LISTENER_ORA37
              Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
              Start Date 26-MAR-2012 12:15:17
              Uptime 0 days 0 hr. 3 min. 2 sec
              Trace Level off
              Security ON: Local OS Authentication
              SNMP OFF
              Listener Parameter File /grid/product/crs/network/admin/listener.ora
              Listener Log File /grid/product/crs/log/diag/tnslsnr/apl11764a001/listener_ora37/alert/log.xml
              Listening Endpoints Summary...
              (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_ORA37)))
              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.169.4)(PORT=62037)))
              (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.169.7)(PORT=62037)))
              Services Summary...
              Service "ORA37_ap" has 1 instance(s).
              Instance "ORA37a", status READY, has 1 handler(s) for this service...
              Service "ORA37_ap_XPT" has 1 instance(s).
              Instance "ORA37a", status READY, has 1 handler(s) for this service...
              Service "ORA37aXDB" has 1 instance(s).
              Instance "ORA37a", status READY, has 1 handler(s) for this service...
              The command completed successfully

              Thank you.

              Regards,
              Dasarath
              • 4. Re: Getting "ORA-12514 error" while connecting first time
                CKPT
                Uptime 0 days 0 hr. 3 min. 2 sec
                Here is clue..
                You have started database first.
                You have started listener now before 3 minutes.

                When you start database, SMON will take some time to register database service with Listener, Mean that time you tried to login.
                When you tried to login next time, its already registered so you able to Connect.

                So you can have a trial now, whether it is connecting or not.. For sure it will connect because status is READY

                or
                Next time whenever you try to connect using TNS service try as below.

                SQL> alter system register;

                & then try connectivity.
                Thanks.

                >
                916466      
                     Newbie
                     
                Handle:      916466
                Status Level:      Newbie
                Registered:      Feb 23, 2012
                Total Posts:      10
                Total Questions:      5 (4 unresolved)
                >

                Consider closing the threads and keep the forum clean..

                Edited by: CKPT on Mar 26, 2012 5:00 PM
                • 5. Re: Getting "ORA-12514 error" while connecting first time
                  919469
                  Hi,

                  Still getting same problem. PFB details:

                  apludc01dat25:e2cmpc1 > sqlplus

                  SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 26 12:35:00 2012

                  Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

                  Enter user-name: outln@ora37_ap
                  Enter password:
                  ERROR:
                  ORA-12514: TNS:listener does not currently know of service requested in connect
                  descriptor


                  Enter user-name: outln@ora37_ap
                  Enter password:

                  Connected to:
                  Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
                  With the Partitioning, Real Application Clusters, OLAP, Data Mining
                  and Real Application Testing options

                  SQL>

                  Its getting connected second time only.

                  Regards,
                  Dasarath
                  • 6. Re: Getting "ORA-12514 error" while connecting first time
                    CKPT
                    916466 wrote:
                    Hi,

                    Still getting same problem. PFB details:

                    apludc01dat25:e2cmpc1 > sqlplus

                    SQL*Plus: Release 10.2.0.5.0 - Production on Mon Mar 26 12:35:00 2012

                    Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

                    Enter user-name: outln@ora37_ap
                    Enter password:
                    ERROR:
                    ORA-12514: TNS:listener does not currently know of service requested in connect
                    descriptor


                    Enter user-name: outln@ora37_ap
                    Enter password:

                    Connected to:
                    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
                    With the Partitioning, Real Application Clusters, OLAP, Data Mining
                    and Real Application Testing options

                    SQL>

                    Its getting connected second time only.

                    Regards,
                    Dasarath
                    Can you check Listener log file, or perform tracing so that we can see issue occurring..
                    • 7. Re: Getting "ORA-12514 error" while connecting first time
                      919469
                      Listener log file is not created. PFB listener status and log file details:

                      apl11764a001:ORA37a > lsnrctl status LISTENER_ORA37

                      LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 26-MAR-2012 13:11:24

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

                      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_ORA37)))
                      STATUS of the LISTENER
                      ------------------------
                      Alias LISTENER_ORA37
                      Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
                      Start Date 26-MAR-2012 12:15:17
                      Uptime 0 days 0 hr. 56 min. 6 sec
                      Trace Level off
                      Security ON: Local OS Authentication
                      SNMP OFF
                      Listener Parameter File /grid/product/crs/network/admin/listener.ora
                      Listener Log File /grid/product/crs/log/diag/tnslsnr/apl11764a001/listener_ora37/alert/log.xml
                      Listening Endpoints Summary...
                      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_ORA37)))
                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.169.4)(PORT=62037)))
                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.169.7)(PORT=62037)))
                      Services Summary...
                      Service "ORA37_ap" has 1 instance(s).
                      Instance "ORA37a", status READY, has 1 handler(s) for this service...
                      Service "ORA37_ap_XPT" has 1 instance(s).
                      Instance "ORA37a", status READY, has 1 handler(s) for this service...
                      Service "ORA37aXDB" has 1 instance(s).
                      Instance "ORA37a", status READY, has 1 handler(s) for this service...
                      The command completed successfully
                      apl11764a001:ORA37a > cd /grid/product/crs/log/diag/tnslsnr/apl11764a001/listener_ora37/alert/
                      apl11764a001:ORA37a > ls -lrt
                      total 0

                      I couldn't find log.xml file under /grid/product/crs/log/diag/tnslsnr/apl11764a001/listener_ora37/alert location.

                      Can you please help me how to enable tracing ?

                      Thank you.

                      Regards,
                      Dasarath
                      • 8. Re: Getting "ORA-12514 error" while connecting first time
                        CKPT
                        I couldn't find log.xml file under /grid/product/crs/log/diag/tnslsnr/apl11764a001/listener_ora37/alert location.
                        Can you please help me how to enable tracing ?
                        http://arjudba.blogspot.com/2008/04/how-to-enable-listener-logging-and.html