1 2 3 Previous Next 36 Replies Latest reply on May 2, 2017 11:34 PM by jgarry

    ORA-12514: TNS:listener

    Bob MacDonald

      I have searched for my error in the forum and there is no part of the suggested solutions I can follow since I cannot connect to the database

      Everything was working fine April 5. I went on three weeks holiday and last night I had a problem with my app login, but I was able to connect in sql developer.

      Now I cannot connect in sql plus or in sql developer. The message in SQL+ is

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

       

      In sql developer it is similar: Test failed: Listener refused the connection with the following error ORA 12505, TNS:listener does not currently know of SID in connect descriptor.

      I have reviewed the path and other oracle environment variables and while they weren't there, putting them in has made no difference.

      What could have happened while I was away (apart from several reboots for Windows and Dell and anti virus software)?

       

      I am the only user. This is a very minimal setup. I could restore from the last backup if this would help, but I have never done this so I don't know what the result would be. My own data is completely recoverable from exports if a database restore would fix the problem..

       

      Thanks for your help

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

      ORACLE_HOME is C:\oraclexe\app\oracle\product\11.2.0\server

      ORACLE_BASE C:\oraclexe\app\oracle

      ORACLE_SID (never heard of this one before) is XE - is this wrong ?

       

      My TNSNAMES.ORA is unchanged (C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN)

      XE =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = TCP)(HOST = DELL-DESKTOP)(PORT = 1521))

          (CONNECT_DATA =

            (SERVER = DEDICATED)

            (SERVICE_NAME = XE)

          )

        )

       

      EXTPROC_CONNECTION_DATA =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

          )

          (CONNECT_DATA =

            (SID = PLSExtProc)

            (PRESENTATION = RO)

          )

        )

       

      ORACLR_CONNECTION_DATA =

        (DESCRIPTION =

          (ADDRESS_LIST =

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

          )

          (CONNECT_DATA =

            (SID = CLRExtProc)

            (PRESENTATION = RO)

          )

        )

       

      The listener.ora is in the same place

      SID_LIST_LISTENER =

        (SID_LIST =

          (SID_DESC =

            (SID_NAME = PLSExtProc)

            (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

            (PROGRAM = extproc)

          )

          (SID_DESC =

            (SID_NAME = CLRExtProc)

            (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

            (PROGRAM = extproc)

          )

        )

       

      LISTENER =

        (DESCRIPTION_LIST =

          (DESCRIPTION =

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

            (ADDRESS = (PROTOCOL = TCP)(HOST = DELL-DESKTOP)(PORT = 1521))

          )

        )

       

      DEFAULT_SERVICE_LISTENER = (XE)

        • 1. Re: ORA-12514: TNS:listener

          Could you post the output from the following please?

           

          tnsping xe

           

          lsnrctl stat

           

          lsnrctl services

          • 2. Re: ORA-12514: TNS:listener
            Rasheed_

            post lsnrctl status  output

            • 3. Re: ORA-12514: TNS:listener
              Rasheed_

              login to DB

              sqlplus / as sysdba

               

              sho parameter db_name

               

              --for getting SID

               

              select instance_name from v$instance;

               

              --for service_name

               

              sho parameter service_name

               

               

              If you are specifying service_name in tns , make sure listener status show same service with READY status (at least not BLOCKED

              • 4. Re: ORA-12514: TNS:listener
                AndrewSayer

                Does the database live on the same system as your sql developer? Or is it maintained remotely?

                 

                The error means that your connection request has managed to make it through to a listener fine, but the service name you requested isnt currently registered with the listener. This usually means the database is not online.

                 

                If the database is local (which would be my guess as you have a listener file) then you can start it up, from cmd:

                sc query OracleServiceXE

                <is the service running?>

                <yes>ok next step

                <no>sc start OracleServiceXE

                      <if you had to start the service then give it a minute or two>

                set oracle_sid=XE

                sqlplus / as sysdba

                startup

                 

                What happens now when you connect?

                If it doesn't work then copy and paste the full output from the above commands and also:

                lsnrctl status

                • 5. Re: ORA-12514: TNS:listener
                  EdStevens

                  Bob MacDonald wrote:

                   

                  I have searched for my error in the forum and there is no part of the suggested solutions I can follow since I cannot connect to the database

                  Have you tried connecting without the listener?  Since this all appears to be a local XE database, there is no reason you shouldn't be able to open a command prompt and:

                   

                  sqlplus / as sysdba
                  

                   

                  You say you looked everywhere on the forum.  Did you look elsewhere?  Might I suggest ORA-12514: TNS:listener does not currently know of service :: edstevensdba

                   

                   

                  Everything was working fine April 5. I went on three weeks holiday and last night I had a problem with my app login, but I was able to connect in sql developer.

                  Now I cannot connect in sql plus or in sql developer. The message in SQL+ is

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

                   

                  In sql developer it is similar: Test failed: Listener refused the connection with the following error ORA 12505, TNS:listener does not currently know of SID in connect descriptor.

                  I have reviewed the path and other oracle environment variables and while they weren't there, putting them in has made no difference.

                  On a Windows system you typically won't see the ORA* enviornment variables in the command line enviornment.  They are in the registry.

                   

                  What could have happened while I was away (apart from several reboots for Windows and Dell and anti virus software)?

                   

                  I am the only user. This is a very minimal setup. I could restore from the last backup if this would help, but I have never done this so I don't know what the result would be. My own data is completely recoverable from exports if a database restore would fix the problem..

                  There is zero indication at this point that the database is in need of any type of recovery.  Do not go there simply because you don't know what else to do.  When you find yourself in a hole, the first rule is STOP DIGGING.

                   

                  As others have said, you need to establish that the database itself is running and is known to the listener.  You've been asked to show the output of 'lsnrctl status'.  That is far, far more revealing than the contents of listener.ora

                   

                  The first thing to check, since this is a Windows system, is if the Windows service for the database is running.  Go to the 'services' control panel ('services.msc' from the command line, if you can't find it the ever-changing Windows menu system) and look for something like OracleServiceXE  (I can't remember the exact name).  It should be started.

                   

                  Next, try to log on to the database as a local connection with 'sqlplus / as sysdba'.  This connection will not need the listener.  Show us the full, exact command and the full, exact result.

                   

                   

                  • 6. Re: ORA-12514: TNS:listener
                    John Thorton

                    Post excerpt from alert_XE.log file that shows the database has been started & is now up & online

                     

                    ora-12514 ALWAYS only occurs due to a problem on DB Server system.
                    One cause of this problem is when the Oracle database is down & needs to be started.
                    A remote client send a request to the Listener asking to be connected to a specific service.
                    If/when the listener does not know anything about that service, the listener responds with ora-12514

                    post results from the following two OS commands:
                    lsnrctl status
                    lsnrctl service

                    Since every connection request to the listener gets logged, listener.log file will contain a line with 12514 as status code.

                    This line contains valuable debugging details. So post this line & surrounding lines.

                    for additional debugging suggestions read the URL below:
                    http://edstevensdba.wordpress.com/2011/03/19/ora-12514/

                    • 7. Re: ORA-12514: TNS:listener
                      Bob MacDonald

                      Lots of replies - sorry I didn't know if email was enabled so I tried a few things that will set me back

                      PS (I am a retired programmer, not a DBA - lots new here)

                      here is output of lnsrctl

                      LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 01-MAY-2017 14:23:14

                       

                       

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

                       

                       

                      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

                      STATUS of the LISTENER

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

                      Alias                     LISTENER

                      Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production

                      Start Date                01-MAY-2017 14:11:19

                      Uptime                    0 days 0 hr. 11 min. 54 sec

                      Trace Level               off

                      Security                  ON: Local OS Authentication

                      SNMP                      OFF

                      Default Service           XE

                      Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora

                      Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\DELL-DESKTOP\listener\alert\log.xml

                      Listening Endpoints Summary...

                        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))

                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DELL-DESKTOP)(PORT=1521)))

                      Services Summary...

                      Service "CLRExtProc" has 1 instance(s).

                        Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

                      Service "PLSExtProc" has 1 instance(s).

                        Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

                      The command completed successfully

                       

                      I am now able to connect to the database with sqlplus - but sqldeveloper still gives the same problem - as if it was looking for a listener. In the logs I saw a listener_died message and a Windows 32 internal error.

                      01-MAY-2017 14:08:24 * service_died * CLRExtProc * 12547

                      TNS-12547: TNS:lost contact

                      TNS-12560: TNS:protocol adapter error

                        TNS-00517: Lost contact

                         32-bit Windows Error: 54: Unknown error

                       

                      tnsping says OK and finds the tnsnames file correctly

                       

                      services returns the following

                       

                       

                      LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 01-MAY-2017 14:35:48

                       

                       

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

                       

                       

                      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

                      Services Summary...

                      Service "CLRExtProc" has 1 instance(s).

                        Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

                          Handler(s):

                            "DEDICATED" established:0 refused:0

                               LOCAL SERVER

                      Service "PLSExtProc" has 1 instance(s).

                        Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

                          Handler(s):

                            "DEDICATED" established:0 refused:0

                               LOCAL SERVER

                      The command completed successfully

                      • 8. Re: ORA-12514: TNS:listener
                        AndrewSayer

                        "I am now able to connect to the database with sqlplus - but sqldeveloper still gives the same problem - as if it was looking for a listener"


                        How are you connecting with sqlplus? Are you using the tnsnames entry or are you using local connection? i.e are you

                        sqlplus / as sysdba

                        or

                        sqlplus someone/password@xe

                        ?

                         

                        How are you connecting via sql developer? (same questions, are you using the tnsnames entry / any other way through the listener or are you using a local connection?)

                         

                        Your lsnrctl output suggests your XE database has not been started up and registered with the listener, so it can only be connected to locally, once connected locally you should be able to start it up.

                        • 9. Re: ORA-12514: TNS:listener
                          Bob MacDonald

                          I am able to connect as someone/password and as sysdba as you suggested

                           

                          In sqldeveloper the string looks like this someone@//localhost:1521/xe

                           

                          I have tried to create separate connections but the answer is always 12505 - unknown SID

                          • 10. Re: ORA-12514: TNS:listener
                            AndrewSayer

                            Bob MacDonald wrote:

                             

                            I am able to connect as someone/password and as sysdba as you suggested

                             

                            In sqldeveloper the string looks like this someone@//localhost:1521/xe

                             

                            I have tried to create separate connections but the answer is always 12505 - unknown SID

                            You need to show us EXACTLY what you tried in sqlplus. A screenshot of your attempt in sqldeveloper would help.

                             

                            If you are using @xe in sqlplus then the output of

                            tnsping xe

                            would be good.

                            • 11. Re: ORA-12514: TNS:listener
                              Bob MacDonald

                              I have a second instance on a separate machine and the tns and listener files are identical yet it works fine

                              • 12. Re: ORA-12514: TNS:listener
                                AndrewSayer

                                Bob MacDonald wrote:

                                 

                                I have a second instance on a separate machine and the tns and listener files are identical yet it works fine

                                You're not using tnsnames in sqldeveloper, you've chosen to use an EZConnect string instead (which BTW must be enabled by your sqlnet.ora file)

                                What might look identical to you, might not be. We have no idea.

                                 

                                But I don't think we really need to venture to this comparison.

                                • 13. Re: ORA-12514: TNS:listener
                                  Bob MacDonald

                                  continuing to search ...

                                  sqlnet.ora is

                                  # This file is actually generated by netca. But if customers choose to

                                  # install "Software Only", this file wont exist and without the native

                                  # authentication, they will not be able to connect to the database on NT.

                                   

                                  SQLNET.AUTHENTICATION_SERVICES = (NTS)

                                  in both instances

                                   

                                  thanks

                                  • 14. Re: ORA-12514: TNS:listener
                                    Bob MacDonald

                                    I tried @xe in sqlplus and it failed on the 12514 error

                                     

                                    so my current presenting problem is that I cannot connect via sqlplus or through my main app.

                                     

                                    thanks for helping me get this far

                                     

                                    how do I change sql developer to avoid the EZconnect?

                                     

                                    Bob

                                    1 2 3 Previous Next