1 2 Previous Next 16 Replies Latest reply on May 19, 2020 5:23 PM by AnnieM

    ORA-12541 error when attempting a database link in Oracle SQL Developer

    AnnieM

      Greetings,

       

      PL/SQL Release 10.1.0.5.0 - Production

      Oracle SQL Developer Version 19.2.1.247

      Windows 7

       

      Within a SQL worksheet connected to one of our databases, I attempted a link to another one of our campus' databases:

       

      create public database link

      Banner

      using 'xxx:1528/yyy';

       

      where  xxx = hostname

      and yyy = serviceName

      .

       

      And then tried to execute this query:

       

      select

      HS.BANNER_CD,

      HS.HS_NAME,

      BN.STVSBGI_DESC

      from ADMISSIONS.AMHGHSCT HS

      INNER JOIN STVSBGI@Banner BN

      ON HS.BANNER_CODE = BN.STVSBGI_CODE ;

       

      (When I access that other database referenced above as Banner within a difference SQL worksheet, I can run a query, select * from STVSBGI

      without errors)

       

      But for the database link I received this error:

      ORA-12541: TNS:no listener

      1. 12541. 00000 - "TNS:no listener"

      *Cause:    The connection request could not be completed because the listener

      is not running.

      *Action:   Ensure that the supplied destination address matches one of

      the addresses used by the listener - compare the TNSNAMES.ORA entry with

      the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to

      go by way of an Interchange). Start the listener on the remote machine.

       

      Does anyone know what the issue is here? I am not a DBA. And our DBA, reviewed quickly but couldn't see an issue either

       

      Thank you for your help and i hope you are doing well at this time,

       

      Annie

        • 1. Re: ORA-12541 error when attempting a database link in Oracle SQL Developer
          EdStevens

          AnnieM wrote:

           

          Greetings,

           

          PL/SQL Release 10.1.0.5.0 - Production

          Oracle SQL Developer Version 19.2.1.247

          Windows 7

           

          Within a SQL worksheet connected to one of our databases, I attempted a link to another one of our campus' databases:

           

          create public database link

          Banner

          using 'xxx:1528/yyy';

           

          where xxx = hostname

          and yyy = serviceName

          .

           

          And then tried to execute this query:

           

          select

          HS.BANNER_CD,

          HS.HS_NAME,

          BN.STVSBGI_DESC

          from ADMISSIONS.AMHGHSCT HS

          INNER JOIN STVSBGI@Banner BN

          ON HS.BANNER_CODE = BN.STVSBGI_CODE ;

           

          (When I access that other database referenced above as Banner within a difference SQL worksheet, I can run a query, select * from STVSBGI

          without errors)

           

          But for the database link I received this error:

          ORA-12541: TNS:no listener

          1. 12541. 00000 - "TNS:no listener"

          *Cause: The connection request could not be completed because the listener

          is not running.

          *Action: Ensure that the supplied destination address matches one of

          the addresses used by the listener - compare the TNSNAMES.ORA entry with

          the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to

          go by way of an Interchange). Start the listener on the remote machine.

           

          Does anyone know what the issue is here? I am not a DBA. And our DBA, reviewed quickly but couldn't see an issue either

           

          Thank you for your help and i hope you are doing well at this time,

           

          Annie

          Any DBA that can't figure that out isn't worth the title.

          In your query, the "@Banner" is referencing a database link named "BANNER".  The definition of that db link will indicate a host (name or IP address), a port number (most likely 1521, and no good reason to be anything else), and either a SID or a SERVICE_NAME (preferably the latter).  There should be an oracle listener process running at that host and port.  Your error indicates, definitively, that the request got to the specified host and port, but there was no oracle listener service there.

           

          My guess is that your erstwhile "DBA" checked all of his listeners, and finding them running, saw no reason to look any further.  This would indicate that the db link BANNER is mis-configured.

          • 2. Re: ORA-12541 error when attempting a database link in Oracle SQL Developer
            AnnieM

            Thank you, Ed!

             

            He is a wearer of many hats, predominantly a programmer, and I sent the question at a busy time for him.


            I can check into what you say!

            I appreciate your time and help!


            Annie

            • 3. Re: ORA-12541 error when attempting a database link in Oracle SQL Developer
              EdStevens

              I should clarify about the definition of the database link.

              Looking at the syntax for creating it, here, the element to focus on for this problem is "USING connect_string".

               

              Reading down further, we come to

               

              USING 'connect string'

              Specify the service name of a remote database. If you specify only the database name, then Oracle Database implicitly appends the database domain to the connect string to create a complete service name. Therefore, if the database domain of the remote database is different from that of the current database, then you must specify the complete service name.

              See Also:

              Oracle Database Administrator's Guide for information on specifying remote databases

              In simple terms, the "service name" is also sometimes referred to 'net service name'.  What it really comes down to is 'entry in tnsnames.ora'.  If you were to create a link like this:

               

              create database link mylink

              connect to scott identified by "tiger"

              using 'mydb';

              Then when you issue

               

              select * from mytable@mylink;

              It would check the tnsnames.ora (or other names resolution method) for an entry named 'MYLINK'.  It would then use that entry to resolve host, port, and database service name or sid (preferably service name).  Instead of a net service name, the 'using' clause can also be coded with the actual hard-coded name resolution, as found in tnsnames.ora. Most people prefer touse the net service name.

              • 4. Re: ORA-12541 error when attempting a database link in Oracle SQL Developer
                AnnieM

                Hi Ed,
                Thank you, again!

                I appreciate your effort to help me out!
                I believe I used the full service name that is in the properties:"Details" box and also in TNSNAMES.ORA (please see below for that).

                 

                However, the name "Banner" was just a name I came up with; it has no relevance or reference to anything. Could that the issue?

                 

                We use "Pulse Secure" to connect to the Colorado State system (I will call it "BNR" for reference here -- sorry, I do not know how much info I can give without giving important information to potential hackers, etc.).

                If I don't do Pulse Secure first I cannot connect at all to BNR for running queries. I receive an error:

                 

                 

                I was connected to Pulse Secure when I tried the create database link.

                 

                I checked and BNR is in TNSNAMES.ORA (again, i changed the real name to BNR).

                 

                BNR.WORLD =

                  (DESCRIPTION =

                   (ADDRESS_LIST =

                    (ADDRESS = (COMMUNITY = TCP.world)

                               (PROTOCOL = TCP)

                               (Host = xxx)                              --- redact corresponding to original post redact

                               (Port = 1528)))

                  (CONNECT_DATA = (SERVICE_NAME = yyy)))    -- redact corresponding to original post

                 

                ~~~~~~~~~~~~~~~~~~~~~~~

                I hope that his helpful and my redacts didn't confuse matters.

                 

                Thank you, again, and all the best,
                Annie

                • 5. Re: ORA-12541 error when attempting a database link in Oracle SQL Developer
                  cormaco

                  Two ideas:

                  - Is the name resolution for hostname xxx working correctly?

                  - Since this listener is using an nonstandard port, access might be blocked by a firewall.

                  • 6. Re: ORA-12541 error when attempting a database link in Oracle SQL Developer
                    AnnieM

                    Thank you, Cormaco,

                     

                    What is "name resolution for hostname?"

                    I can connect to run queries, just not create the database link.

                     

                    I can inquire with our tech folks on the 2nd.

                     

                    Thank you so much for your help and I hope your day is a good one!

                    Annie

                    • 7. Re: ORA-12541 error when attempting a database link in Oracle SQL Developer
                      cormaco

                      What is "name resolution for hostname?"

                      I mean, do you get the correct IP-Adress for this hostname.

                      What do you get when you enter:

                      ping xxx

                      • 8. Re: ORA-12541 error when attempting a database link in Oracle SQL Developer
                        AnnieM

                        Thank you!

                         

                         

                        I redacted the IP address.  How would I know if it is the correct one?

                         

                        Thank you, again,

                        Annie

                        • 9. Re: ORA-12541 error when attempting a database link in Oracle SQL Developer
                          EdStevens

                          cormaco wrote:

                           

                          Two ideas:

                          - Is the name resolution for hostname xxx working correctly?

                          - Since this listener is using an nonstandard port, access might be blocked by a firewall.

                          Actually, we don't know that the listener is using a non-standard port.  We only know that the tnsnames is configured thinking that the listener is using a non-standard port.  That, in itself, could explain the reported error message (ORA-12541: TNS:no listener) which would be entirely consistent.  The request got to some server, but there was no listener on the port specified in the tnsnames.  That server very well could have a listener on some other port - like the default 1521.

                           

                          As for firewall blocking .. if the were the case the request would have never reached the server and so would have resulted in a different error, most likely a network timeout.

                           

                           

                          • 10. Re: ORA-12541 error when attempting a database link in Oracle SQL Developer
                            EdStevens

                            AnnieM wrote:

                             

                            Thank you, Cormaco,

                             

                            What is "name resolution for hostname?"

                            All network routing eventually has to be by IP address. "name resolution for hostname" is referring to the method used (within the network configuration) to resolve a server's 'name' to an actual IP address.  This is a few layers away from orcle, and oracle is oblivious to it.  However, it could come into play if you specify HOST=myserver, and then the network resolves 'myserver' to the IP address of a server different than the one you intended.

                             

                            I can connect to run queries, just not create the database link.

                            But didn't you say in your msg #4 that you had created a link?  Whichever, someone obviously created it ..

                            • 11. Re: ORA-12541 error when attempting a database link in Oracle SQL Developer
                              EdStevens

                              AnnieM wrote:

                               

                              Thank you!

                               

                               

                              I redacted the IP address. How would I know if it is the correct one?

                               

                              Thank you, again,

                              Annie

                              You really wouldn't know for sure.  All this proves is that the network was able to resolve the server name to the IP address of some server on the network.  But as long as you correctly specified the server name - the server hosting your database -  there's no practical reason to believe it actually resolved to an incorrect ip address.

                              • 12. Re: ORA-12541 error when attempting a database link in Oracle SQL Developer
                                AnnieM

                                Hi Ed,
                                Thank you.

                                Regarding: "But didn't you say in your msg #4 that you had created a link?  Whichever, someone obviously created it .."

                                I didn't get an error message when I wrote the "CREATE DATABASE LINK" statement

                                I got an error when I referenced the database link in the SQL Select query (the @Bannner)

                                 

                                "as long as you correctly specified the server name - the server hosting your database -  there's no practical reason to believe it actually resolved to an incorrect ip address."

                                It sounds like then the IP address and host name are OK though since I can query tables in the database (?)

                                 

                                I did try changing the port to 1521 in SQL Developer and received the message:

                                "Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection"

                                 

                                Do you have any suggestions on what could pinpoint the error?


                                Thank you so much for your time and help!
                                Annie

                                • 13. Re: ORA-12541 error when attempting a database link in Oracle SQL Developer
                                  EdStevens

                                  More observations:

                                  You said you 'tried' to create the link as this:

                                   

                                  create public database link

                                  Banner

                                  using 'xxx:1528/yyy';

                                   

                                  If you did that, then you are directly providing the network routing info, and so your tnsnames.ora is never referenced. I've never created a link this way, using 'instant client' syntax, but since you are getting through to the db server (that's the only way you could get ORA-12541) it must be ok.

                                   

                                  That then raises the question, both here and in the tnsnames.ora, why you are specifying port 1528?  By default, the listener runs on port 1521, though the DBA could certainly configure it to use some other port.  Specifying the correct server but wrong port would most certainly result in your ORA-12541.

                                   

                                    Also, you questioned your use of 'BANNER' for the name of the db link.  That is immaterial.  That is simply an alias to be referenced by your SQL.  What matters is that the rest of the link definition refer to the correct server and the correct listener port.

                                  • 14. Re: ORA-12541 error when attempting a database link in Oracle SQL Developer
                                    AnnieM

                                    Hi Ed (and group)

                                     

                                    I said "I tried" because I did not know if it succeeded or I wrote it correctly (I have never done a database link in Oracle, only in the SQL Server environment, which is set up differently).

                                     

                                    Because the select statement referencing the link failed, I have been assuming that I wrote the Create Database link incorrectly or something else triggered the failure.

                                     

                                    The Port 1528 was given to me to use by CSU (whose database I am trying to connect to via the database link), along with the hostName and serviceName.

                                    It is what is being used successfully to run queries vs the database. So hostname, port, servicename are all correct.


                                    I am the first/only person to decide to try to link that database to our own to compare data in the two databases (this would facilitate some of the work I do).

                                    My workaround is to export CSU table data from their database and then import that file into our database, just extra steps.

                                     

                                    I hope that helps and thank you, again,

                                    Annie

                                    1 2 Previous Next