1 2 3 Previous Next 36 Replies Latest reply on May 2, 2017 11:34 PM by jgarry Go to original post
      • 15. Re: ORA-12514: TNS:listener

        Please connect to the database using "/ as sysdba" and post the results of the following :

        show parameter service

        show parameter local_listener

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

          service is XE

          local_listener is null

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

            Bob MacDonald wrote:

             

            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

            One thing at a time. You're still not sharing anything using copy and paste, you're turning it into a guessing game.

             

            Has the database even started up? Nothing really suggests it has (and this would be completely obvious if you were copy and pasting exactly what you are doing and what you see!)

             

            To start up, you'll need to connect locally like

            set oracle_sid=xe

            sqlplus / as sysdba

             

            then start up like

             

            startup

             

            (yes that was easy)

             

            Once it's started, it will register with the listener (again assuming), then you can connect using the listener with @xe or chosing the tnsnames connection type in sqldeveloper and choosing the appropriate entry.

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

              Bob MacDonald wrote:

               

              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

              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/



               

              [oracle@vbgeneric oracle]$ oerr ora 12505

              12505, 00000, "TNS:listener does not currently know of SID given in connect descriptor"

              // *Cause:  The listener received a request to establish a connection to a

              // database or other service. The connect descriptor received by the listener

              // specified a SID for an instance (usually a database instance) that either

              // has not yet dynamically registered with the listener or has not been

              // statically configured for the listener. This may be a temporary condition

              // such as after the listener has started, but before the database instance

              // has registered with the listener.

              // *Action:

              //  - Wait a moment and try to connect a second time.

              //  - Check which instances are currently known by the listener by executing:

              //    lsnrctl services <listener name>

              //  - Check that the SID parameter in the connect descriptor specifies

              //    an instance known by the listener.

              //  - Check for an event in the listener.log file.

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

                The Real Rob the Relic wrote:

                 

                Please connect to the database using "/ as sysdba" and post the results of the following :

                show parameter service

                show parameter local_listener

                He's getting to the listener, but asking to connect to DB that listener does not know about!

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

                  The database is started. I can select from, insert into and delete from the data.

                   

                  The problem is connecting with something other than sqlplus.

                   

                  The app and sql developer have been working just fine for over a year. They stopped working while I was on holiday. The log file looks normal until April 28 - but I do notice some earlier instances of the service dying - yet I had never noted them before.

                   

                  Fri Apr 28 16:52:12 2017

                  28-APR-2017 16:52:12 * service_update * xe * 0

                  Fri Apr 28 17:00:33 2017

                  28-APR-2017 17:00:33 * service_update * xe * 0

                  28-APR-2017 17:00:42 * service_update * xe * 0

                  Fri Apr 28 17:00:45 2017

                  28-APR-2017 17:00:45 * service_update * xe * 0

                  Fri Apr 28 17:02:12 2017

                  28-APR-2017 17:02:12 * service_update * xe * 0

                  Fri Apr 28 17:03:56 2017

                  No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DELL-DESKTOP)(PORT=8080))(Presentation=HTTP)(Session=RAW))

                  28-APR-2017 17:03:56 * service_died * xe * 12547

                  TNS-12547: TNS:lost contact

                  Fri Apr 28 17:07:18 2017

                  System parameter file is C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora

                  Log messages written to C:\oraclexe\app\oracle\diag\tnslsnr\DELL-DESKTOP\listener\alert\log.xml

                  Trace information written to C:\oraclexe\app\oracle\diag\tnslsnr\DELL-DESKTOP\listener\trace\ora_3472_4668.trc

                  Trace level is currently 0

                   

                   

                  Started with pid=3472

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

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

                  Listener completed notification to CRS on start

                   

                   

                  TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE

                  Fri Apr 28 17:08:24 2017

                  Dynamic address is already listened on (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DELL-DESKTOP)(PORT=1521)))

                  28-APR-2017 17:08:24 * service_register * xe * 0

                  28-APR-2017 17:08:27 * service_update * xe * 0

                  Fri Apr 28 17:08:41 2017

                  28-APR-2017 17:08:41 * service_update * xe * 0

                  Fri Apr 28 17:09:15 2017

                  Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DELL-DESKTOP)(PORT=8080))(Presentation=HTTP)(Session=RAW))

                  28-APR-2017 17:09:15 * service_update * xe * 0

                  28-APR-2017 17:09:18 * service_update * xe * 0

                  28-APR-2017 17:09:23 * service_update * xe * 0

                  Fri Apr 28 17:10:32 2017

                   

                  I found another log file that suggested I should change a parameter

                  Fatal NI connect error 12638, connecting to:

                  (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

                   

                   

                    VERSION INFORMATION:

                    TNS for 32-bit Windows: Version 11.2.0.2.0 - Production

                    Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 11.2.0.2.0 - Production

                    Time: 28-APR-2017 17:03:55

                    Tracing not turned on.

                    Tns error struct:

                      ns main err code: 12638

                     

                  TNS-12638: Credential retrieval failed

                      ns secondary err code: 0

                      nt main err code: 0

                      nt secondary err code: 0

                      nt OS err code: 0

                  Change - SQLNET.AUTHENTICATION_SERVICES= (NTS)
                  To: -
                  SQLNET.AUTHENTICATION_SERVICES= (NONE)

                   

                  it didn't help

                  • 21. Re: ORA-12514: TNS:listener
                    jgarry

                    Understand:

                     

                    If there is no @ sign in the connect string, it is a local connection using the BEQ protocol.

                     

                    If there is an @ sign in the connect string, it is a remote connection, even if it is on the same machine.  One way or another, SQL*NET needs to be told where to go, possibly including connect string contents, certain environment variables (local or system), maybe some registry, or alias resolution from the tnsnames.ora file.  There might be more than one tnsnames.ora file on the machine, so one environment may send tnsping to one, while another environment may use another.  Google for how to set things for sql*developer.

                     

                    Did you happen to mention the version of XE?

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

                      You still haven't shown us HOW you are connecting with sqlplus and how you are try to connect with sql developer.

                       

                      Copy and paste of log files is not helpful when we can't even see what youre even trying.

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

                        I have shown this but the text I have posted is too long to find it. Thanks to everyone for trying to help this non-dba.

                         

                        I can connect as user/password with no @

                         

                        sql developer asks for a password etc but it inserts the @ itself name/password@//localhost:1521/xe

                         

                        I do not seem to have direct control over the generated string from sql developer.

                        It also saves its files in a form that is incompatible with sqlplus - this is a real nuisance. It seems it has only a line feed and not a line feed with a cr.

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

                          I did not mention the version of XE - but it is recent within the last year or so. How do I tell?

                          • 25. Re: ORA-12514: TNS:listener
                            jgarry

                            Sorry, after I posted I noticed you have 11.2 in your path, so that's version 11 XE.  You can also get a clue from when you go into sqlplus.  When connected, select * from v$version;

                            An actual version is something like 11.2.0.2, as opposed to a marketing title like 11g.

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

                              It is 11.2.0.2 TNS for 32-bit Windows

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

                                by the way, on my identically configured laptop, an old surface, I can connect with the @xe instantly. No errors there.

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

                                  Bob MacDonald wrote:

                                   

                                  by the way, on my identically configured laptop, an old surface, I can connect with the @xe instantly. No errors there.

                                  It is NOT identically configured otherwise it would be acting identically!

                                   

                                  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/

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

                                    What is preventing you from posting a screen shot of your connection properies in SQL Dev.  You have been asked for it multiple times.  Until we see it, we can only guess what you are doing wrong with it.