1 2 Previous Next 15 Replies Latest reply on Nov 20, 2017 12:32 PM by 3589332

    ORA-12505, TNS:listener

    3589332

      Hello i recently installed Oracle and Oracle SQL Developer together with JDM, as i try to create a connection i get the following error.

       

      An error was encountered performing the requested operation:

       

      Listener refused the connection with the following error:

      ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

       

      Vendor code 12505

       

      What i tried to do was:

      1) go to Services and see if there something is not working. But everything seems to be ON.

      2) Then I went and checked the listener.ora and tnsnames.ora I tried a few different tutorials online but none of them worked.

      3) Then in those files changed the host to localhost, my machine's name, dns from a tutorial and none worked. Also tried restarting the services but also didn't work.

      4) I tried to re-install the program but that also didn't help.

       

      some pictures of my settings : https://i.stack.imgur.com/TamFv.png https://i.stack.imgur.com/iZNXM.png https://i.stack.imgur.com/jKwxQ.png  https://i.stack.imgur.com/qTssn.png

       

      I have more than one services of the listener which the people from stackoverflow pointed out. I hope i will find some help here.

       

      Thanks in advance!

        • 1. Re: ORA-12505, TNS:listener
          Gary Graham-Oracle

          A couple of comments....

          1. It is unusual to use an IP address in tnsnames.ora, unless perhaps you have a static IP address. Typically it would be machine's name. Can you even "ping" that address (192.168.0.45)?

          2. Multiple listeners can be configured to work together, but unless you know what you are doing, you should use just one.  And if you have multiple Oracle versions installed, it is simpler to use only the listener of the higher version.

           

          Edit:

          And since you get a 12505 error message when using XE for the SID or SERVICE_NAME, it suggests the 11g_home listener is trying to service your request.  And, of course, one would expect the two distinct instances to use distinct port numbers, maybe 1521 and 1522. If you have both using 1521 that would be a non-starter.

          • 2. Re: ORA-12505, TNS:listener

            i recently installed Oracle and Oracle SQL Developer together with JDM,

            Ol - but what versions? Post the FULL versions

            ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

             

            That means you reached the machine and listener and are using a sid/service name that doesn't exist. Pretty much like having a letter returned and marked: no such address

             

             

            some pictures of my settings

            I, for one, am NOT going to go download stuff from some unknown web site.

             

            Post the settings you are using.

            • 4. Re: ORA-12505, TNS:listener
              3589332

              The version of my Oracle is 11.2.0 and the Oracle Developer is 17.3.1.279  Build 279.0537

               

              My settings are:

              tnsnames.ora

              XE =

                (DESCRIPTION =

                  (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-BLHK0J3)(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)

                  )

                )

               

              listener.ora

               

              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 = localhost)(PORT = 1521))

                  )

                )

               

               

              DEFAULT_SERVICE_LISTENER = (XE)

               

              my services:

              qTssn.png

              • 5. Re: ORA-12505, TNS:listener
                3589332

                I changed the hostname back to the name of my machine, but nothing happened.

                For the different ports where should I change them in the listener.ora and tnsnames.ora files?

                • 6. Re: ORA-12505, TNS:listener
                  Gaz in Oz

                  You seem to have three Oracle listeners defined in your services panel

                  Two of which are in the "Running" state. The third, (although you don't show it), I am guessing the (D...) is saying "Disabled"?

                  One listener is capable of serving many instances. As you have two running, this may be an issue, unless they are listening on different ports.

                  Start up a "Run as Administrator" command prompt and issue the following commands and COPY/PASTE (not screenshot) the results, preferably in a fixed font, say Courier New:

                  C:\>lsnrctl status

                  ...

                  C:\>sqlplus / as sysdba

                  ...

                  SQL> select instance_name, status from v$instance;

                  ...

                  On the information you have currently posted, it also looks like there is an XE instance running, "OracleServiceXE". Based on just that, try stopping the "OracleOraDb11g_homeTNSListener" service and try and connect.

                  • 7. Re: ORA-12505, TNS:listener
                    Gaz in Oz

                    ...three listeners suggests you have at least three database server components installed.

                    If you do really want more than one listener running, you need to configure them to co-exist.

                    One thought is the XE db has either registered itself with the "wrong" listener, or has not registered itself with either of the running listeners (and as stated, if both the running listeners are listening on the same port, that will be an issue in of itself).

                    BTW what is JDM?

                    • 8. Re: ORA-12505, TNS:listener
                      3589332

                      I tried doing the commands in cmd runned as administrator but even then i can't connect Capture.PNG

                       

                      Capture2.PNG@

                      • 9. Re: ORA-12505, TNS:listener
                        3589332

                        Java Development Kit (JDK) My bad for Saying JDM. Im not sure if the lXE db is registered to  "wrong" listener

                         

                        Capture22.PNG

                        • 10. Re: ORA-12505, TNS:listener
                          EdStevens

                          Others have mentioned this somewhat in passing, but I'll say it more forecfully:

                           

                          Get rid of the extra listeners!  Run only one listener.  Configure it to use the default port of 1521.  Use the machine's actual host name (not 'localhost' and not an ip address) in both listener.ora and tnsnames.ora.  Better yet, just remove all copies of listener.ora.

                           

                          Remove the 'local_listener' parameter from any/ all databases.

                           

                          Stay after school and write the following on the blackboard 100 times:

                           

                          One single listener, using the default name of listener and the default port of 1521 is quite capable of -- indeed, was designed  to -- support multiple Oracle instances of multiple versions running from multiple ORACLE_HOMEs.

                           

                          If you have installed multiple oracle homes, run your single listener from the highest version.  If you have installed Grid Infrastructure, run your single listener from the GI home.

                           

                          The listener does not require a listener.ora file.  Without the file, the listener will start with all default values, and this is sufficient for the vast, vast majority of installation.  I recommend you run without a listener.ora until such time as you come across a situation that actually requires it.

                           

                          Your database uses the value of the LOCAL_LISTENER parameter to register itself with the listener.  If the parameter is not set, the database will, by default, try to register with the listener using the same values (host and port) as are default for the listener.  Thus, if you remove listener.ora and the local_listener parameter, the default values of both will match.

                           

                          Also, while this hasn't been mentioned explicitly - you do not need an ORACLE_HOME for each database.  Multiple databases of the same version and edition can and should run from the same ORACLE_HOME.

                          • 11. Re: ORA-12505, TNS:listener

                            That doesn't show ANY database being registered.

                             

                            A 'basic' connection does NOT use TNSNAMES.ORA

                             

                            Stop ALL of your listeners and then start only ONE. If the 'lsnrctl services' doesn't show your DB then the DB isn't registered with it so you won't be able to connect until it does.

                            • 12. Re: ORA-12505, TNS:listener
                              EdStevens

                              As RP pointed out, your 'lsnrctl status' shows that NO databases are known to your listener.  This is consistent with the error reported by SQL Dev.  And also as RP mentioned, if your SQL Dev "connection type" is 'BASIC', then it is not using tnsnames.ora at all, so whatever you have in that file is irrelevant.  If you want to use tnsnames.ora (no reason to with SQL Dev) then switch your "connection type" to 'TNS' and select a tnsnames.ora entry from the drop-list that comes with that connection type:

                               

                              As for the error you show with sqlplus at the command line, that is totally different.  When you do NOT give sqlplus a net service name ('sqlplus scott/tiger@mydb') then sqlplus attempts a BEQUEATH (local, interprocess) connection with the local database identified by the value of the environment variable %ORACLE_SID%.  If this variable is not set to the value of a database instance that at least has its windows service started, you get the indicated error.

                               

                              All of the above to point out more  issues.  But the bottom line comes back to

                              1) get rid of all copies of 'listener.ora'

                              2) get rid of all listener services.  Don't just stop them. Delete them.  I leave it as an exercise for the student to research 'how to delete a windows service'.

                              3) start a single listener from the highest version ORACLE_HOME you have installed.  To do this without any Windows services (you just deleted them all, remember?) do the following:

                               

                              3a) In your SYSTEM VARIABLES control panel, put your higest-version ORACLE_HOME\bin first in the PATH.

                              3b) at a command prompt:

                              c:> set ORACLE_HOME=c:\oraclexe\app\oracle\product\11.2.0\server
                              c:> lsnrctl start
                              

                               

                              On line 01, use the value for the proper, HIGHEST VERSION, oracle home on your system.  The one I show is from the output of the one of three listeners you showed, but we have no assurance that is the one you should be using.

                               

                              On line 02, starting the listener will also create the Windows service if it has not already been created. And if you followed my instructions, it will not have been created at this point.

                               

                              Once you get all that sorted out and show us evidence of same, we can go back and address any client configuration issues.  Until you get the multiple-listener issue corrected we are wasting our time looking at client configurations.

                              • 13. Re: ORA-12505, TNS:listener
                                3589332

                                Hello thanks for your feedback i did the things you said i should do, here is some proof:

                                 

                                Deleted all services.PNGremoved the listener.ora.PNGfailed.PNG

                                • 14. Re: ORA-12505, TNS:listener

                                  That explains why your first post showed 'automatic' for listener xe but it wasn't running.

                                   

                                  Did you delete ALL listeners?

                                   

                                  Now delete that one and reinstall it.

                                   

                                  If you don't have a listener you will ONLY be able to connect locally. You could use ANY of the listeners but, as Ed said, you should use the latest version.

                                   

                                  Then make sure the listeners starts.

                                   

                                  Then make sure the databases register with it.

                                  1 2 Previous Next