This discussion is archived
10 Replies Latest reply: Oct 16, 2012 2:00 PM by trent RSS

utl_http and tns:destination unreachable

trent Expert
Currently Being Moderated
I am running the OTN Dev Days VM in VirtualBox. Typically at home with the network settings specified as bridged with the interface set as wlan0. However, yesterday, I connected it up on the work network - so the settings remained the same except for the interface was changed to eth0, since that is the interface used. All is fine.

However, after returning home, and starting up the machine, I seem no longer be able to use utl_http to issue http requests. I get the following error:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-12543: TNS:destination host unreachable
ORA-06512: at line 6
29273. 00000 - "HTTP request failed"
*Cause:    The UTL_HTTP package failed to execute the HTTP request.
*Action:   Use get_detailed_sqlerrm to check the detailed error message.
Fix the error and retry the HTTP request.>

And that is with running the following sample code (or anything for that matter):
declare
  req utl_http.req;
  res utl_http.resp;
begin

  req := utl_http.begin_request('http://google.com');
  res := utl_http.get_response(req);
  utl_http.end_response(res);

end;
I have had a little look around, but the only other examples I see is people having troubles connecting to the database, however, there is no trouble there.

In case it is of any use, here is the tnsname.ora:
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

TTORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
and listener.ora:
# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST = 
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    )

    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 80))
      (PROTOCOL_STACK =
         (PRESENTATION = HTTP)
         (SESSION = RAW)
      )
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 21))
      (PROTOCOL_STACK =
         (PRESENTATION = FTP)
         (SESSION = RAW)
      )
    )
  )
ADR_BASE_LISTENER = /home/oracle/app/oracle
Networking works fine. I can verify this with:
$ ping google.com
PING google.com (74.125.237.8) 56(84) bytes of data.
64 bytes from syd01s04-in-f8.1e100.net (74.125.237.8): icmp_seq=1 ttl=57 time=12.2 ms
64 bytes from syd01s04-in-f8.1e100.net (74.125.237.8): icmp_seq=2 ttl=57 time=13.3 ms>

and:
$ telnet google.com 80
Trying 2404:6800:4006:800::1001...
telnet: connect to address 2404:6800:4006:800::1001: No route to host
Trying 74.125.237.8...
Connected to google.com (74.125.237.8).
Escape character is '^]'.
^]
telnet> exit
?Invalid command
telnet> Connection closed.>

There's not much in the VM, that I could just re-create it, but figured it'd be better to figure out what is going on! Anyone have any ideas? Much appreciated!
  • 1. Re: utl_http and tns:destination unreachable
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    the reason might be in the IP Address you are setting in tnsnames.ora and listener.ora

    0.0.0.0 is the IP address that a computer has when it is not connected to a TCP/IP network.

    I don't know exactly how you set up your VM but you might probably use 127.0.0.1 (localhost) instead.

    Regards.
    Al
  • 2. Re: utl_http and tns:destination unreachable
    trent Expert
    Currently Being Moderated
    Tried updating as per your suggestion:
    # tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.
    
    LISTENER_ORCL =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    
    
    ORCL =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl)
        )
      )
    
    TTORCL =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = orcl)
        )
      )
    
    # listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
    # Generated by Oracle configuration tools.
    
    LISTENER =
      (DESCRIPTION_LIST = 
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
        )
    
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 80))
          (PROTOCOL_STACK =
             (PRESENTATION = HTTP)
             (SESSION = RAW)
          )
        )
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 21))
          (PROTOCOL_STACK =
             (PRESENTATION = FTP)
             (SESSION = RAW)
          )
        )
      )
    ADR_BASE_LISTENER = /home/oracle/app/oracle
    This results in me not being able to connect remotely (SQL Developer: IO Error), so not quite sure what that is about. That issue aside, I can connect via sql*plus on the server, and running the aforementioned code still produces the same error.
  • 3. Re: utl_http and tns:destination unreachable
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    trent wrote:

    However, after returning home, and starting up the machine, I seem no longer be able to use utl_http to issue http requests. I get the following error:
    ORA-29273: HTTP request failed
    ORA-06512: at "SYS.UTL_HTTP", line 1130
    ORA-12543: TNS:destination host unreachable
    ORA-06512: at line 6
    29273. 00000 - "HTTP request failed"
    *Cause:    The UTL_HTTP package failed to execute the HTTP request.
    *Action:   Use get_detailed_sqlerrm to check the detailed error message.
    Fix the error and retry the HTTP request.>
    The error has nothing to do with the Oracle client (tnsnames), nor with the Oracle Listener. The mere fact that the client makes a successful server connection and is able to execute PL/SQL code (despite that code failing), means that client-server connectivity between Oracle client and Oracle server is not the problem.

    PL/SQL code is executed inside the PL/SQL engine that resides inside the Oracle server (shared or dedicated) process that services that client session.

    So when that code fails with the above error, it means that the Oracle server process was unable to connect to the specified web server using the specified tcp port. So in other words, it would seem that the VM server cannot route to the Internet and reach Internet addresses.

    This is not an Oracle issue, nor a PL/SQL issue.

    Correct the VM server's networking and routing if you want to enable s/w running in it, to reach the Internet. (not a good idea in principle to expose your Oracle server directly to the Internet - it should rather be done via NAT and proxies and so on)
  • 4. Re: utl_http and tns:destination unreachable
    trent Expert
    Currently Being Moderated
    Oh, ok. At first I thought it was a network issue, but then I figured since by all other means I can access the internet through the machine (firefox, telnet, etc), would mean it was some issue with Oracle. Earlier on when I was getting help, it was suggested to me there was some network information stored in the database somewhere, and hasn't been updated. Sounds like that is incorrect information then.

    Alright, i'll try setting up the connection in some other way to see if that resolves the issue. Just seems odd it was working before and during connecting to the work network, but no longer after, even though the settings are the same.
  • 5. Re: utl_http and tns:destination unreachable
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Do you mean that the VM can resolve Internet hostnames and have Internet IP access?

    If that is the case, then one would assume that UTL_TCP (the bottom PL/SQL layer for the UTL_HTTP package) will work without network connectivity problems to the same hosts and IP addresses.

    Basic tests:

    As the oracle o/s user (owner of the ORACLE_HOME and Oracle instance), hostname resolution needs to work.You can use nslookup <host> or ping <host> to determine whether that works.

    If that works, then you can test http access via tenet <host> 80 - a HTTP v1.0 GET can be done by entering "+GET / HTTP/1.0+" and pressing enter twice.

    If this works, there should not be any failed network connectivity from PL/SQL that is executed by database server processes owned by the same oracle o/s user.

    Keep in mind though that with 11g, Oracle introduced ACLs. And PL/SQL code will need a valid ACL to step outside the Oracle environment for making a network call.
  • 6. Re: utl_http and tns:destination unreachable
    trent Expert
    Currently Being Moderated
    Correct, the VM can resolve internet addresses.

    Test: NSLOOKUP:
    nslookup google.com
    Server:         192.168.1.254
    Address:        192.168.1.254#53
    
    Non-authoritative answer:
    Name:   google.com
    Address: 74.125.237.3
    Name:   google.com
    Address: 74.125.237.1
    Name:   google.com
    Address: 74.125.237.0
    Name:   google.com
    Address: 74.125.237.5
    Name:   google.com
    Address: 74.125.237.9
    Name:   google.com
    Address: 74.125.237.2
    Name:   google.com
    Address: 74.125.237.8
    Name:   google.com
    Address: 74.125.237.14
    Name:   google.com
    Address: 74.125.237.6
    Name:   google.com
    Address: 74.125.237.4
    Name:   google.com
    Address: 74.125.237.7
    Test: PING:
    ping google.com
    PING google.com (74.125.237.3) 56(84) bytes of data.
    64 bytes from syd01s04-in-f3.1e100.net (74.125.237.3): icmp_seq=1 ttl=57 time=13.9 ms
    64 bytes from syd01s04-in-f3.1e100.net (74.125.237.3): icmp_seq=2 ttl=57 time=13.1 ms
    64 bytes from syd01s04-in-f3.1e100.net (74.125.237.3): icmp_seq=3 ttl=57 time=12.3 ms
    
    --- google.com ping statistics ---
    3 packets transmitted, 3 received, 0% packet loss, time 2003ms
    rtt min/avg/max/mdev = 12.327/13.136/13.902/0.643 ms
    Test: TELNET:
    telnet google.com 80
    Trying 2404:6800:4006:800::1001...
    telnet: connect to address 2404:6800:4006:800::1001: No route to host
    Trying 74.125.237.3...
    Connected to google.com (74.125.237.3).
    Escape character is '^]'.
    GET / HTTP/1.0
    
    HTTP/1.0 302 Found
    Location: http://www.google.com.au/
    Cache-Control: private
    Content-Type: text/html; charset=UTF-8
    Set-Cookie: PREF=ID=22bc170975fdf0ef:FF=0:TM=1350382088:LM=1350382088:S=GZijabazPqewNhUk; expires=Thu, 16-Oct-2014 10:08:08 GMT; path=/; domain=.google.com
    Set-Cookie: NID=64=SMYVWtGyhZ-GoDBt2gG1TCSkCUDEV_qxytSgFcG7jgYp6nLbKlDVpGJSBSx38jdD2yg-INNK5pZf8NYyaJ4c03iXa_qy1vBW8ZWbxmXf5E7fLve2yB2xLxcM3htrpJfZ; expires=Wed, 17-Apr-2013 10:08:08 GMT; path=/; domain=.google.com; HttpOnly
    P3P: CP="This is not a P3P policy! See http://www.google.com/support/accounts/bin/answer.py?hl=en&answer=151657 for more info."
    Date: Tue, 16 Oct 2012 10:08:08 GMT
    Server: gws
    Content-Length: 222
    X-XSS-Protection: 1; mode=block
    X-Frame-Options: SAMEORIGIN
    
    <HTML><HEAD><meta http-equiv="content-type" content="text/html;charset=utf-8">
    <TITLE>302 Moved</TITLE></HEAD><BODY>
    <H1>302 Moved</H1>
    The document has moved
    <A HREF="http://www.google.com.au/">here</A>.
    </BODY></HTML>
    Connection closed by foreign host.
    So, from all accounts, all seems to be fine. (Right?)

    I had set up the ACL previously, so that should be all good on that front. But just to be sure, I recreated the ACL - to no avail.
  • 7. Re: utl_http and tns:destination unreachable
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    In that case try the IPv4 address directly and not the hostname from your PL/SQL code. Simplify the connectivity test to see if this makes a difference. (e.g. using something like TcpPortPing as listed in {message:id=10111306})

    You'll need to use tests like these to isolate the problem. UTL_TCP uses the standard socket interface of the kernel. So a working socket interface via telnet from the oracle o/s user, and not from UTL_TCP, is not expected behaviour.
  • 8. Re: utl_http and tns:destination unreachable
    trent Expert
    Currently Being Moderated
    Interesting.

    It seems to work by specifying IP address:
    select tcpportping('74.125.237.1', 80) tt
    from dual
    
    TT
    --
     0 
    
    select tcpportping('google.com', 80) tt
    from dual
    
    TT
    --
    -1 
    So for some reason, not resolving hostnames.

    Edited by: trent on Oct 17, 2012 12:22 AM

    After running it a few times, it seems like it occasionally resolves
    set serveroutput on
    
    declare
      l_count number;
    begin
    
    for i in 1..20 loop
    
      select tcpportping('google.com', 80) into l_count
      from dual;
      
      dbms_output.put_line(i || ':'||l_count);
    
    end loop;
    
    end;
    
    anonymous block completed
    1:-1
    2:-1
    3:.01
    4:-1
    5:-1
    6:-1
    7:-1
    8:-1
    9:-1
    10:-1
    11:-1
    12:-1
    13:-1
    14:-1
    15:-1
    16:-1
    17:-1
    18:-1
    19:-1
    20:-1
    Edited by: trent on Oct 17, 2012 12:27 AM

    Actually, there probably is some other underlying problems. After runnig the above, I tested ping again; it seems that sometimes it doesn't want to resolve.

    Anyways, thanks for your help :)
  • 9. Re: utl_http and tns:destination unreachable
    EdStevens Guru
    Currently Being Moderated
    trent wrote:
    I am running the OTN Dev Days VM in VirtualBox. Typically at home with the network settings specified as bridged with the interface set as wlan0. However, yesterday, I connected it up on the work network - so the settings remained the same except for the interface was changed to eth0, since that is the interface used. All is fine.
    <snip>
    I'm not a network guy and have never used the http packages, but since you are on VirtualBox, allow me to explain how I set up my VB based vm's. I have several running on my Win7 Home Premium laptop - running either Oracle Linux 5.7 x86-64 or Win Server 2003. Perhaps it will help.

    When I set up a vm on my laptop, I have three non-negotiable objectives:
    1) it should have a fixed IP address to act as a "proper" server
    2) the vm should not be visible to my company or ISP network admins.
    3) it needs to be able to reach the internet so as to access oracle's public yum server

    Unfortunately, the way VB handles its networking, the only way to reach the internet is to use either NAT or Bridged. Bridged means that my vm has an IP on the LAN, and so is visible to the net admins. The way VB implemented NAT, it requires a DHCP address. (VMware implements NAT with bridging, which allows a single virtual NIC to have a fixed IP address and still hide behind the host os when talking to the LAN)

    Two achieve my prime objectives in VB I have to create two network adapters, eth0 and eth1.
    Eth0 is configured as 'host only'. This allows me to give the vm a fixed IP address, but that address is visible only to the host os or other vms on the same VB host network.
    eth1 is configured as NAT. This allows me to reach the internet while hiding behind the host os.

    With the above setup, my vm can reach the internet, it cannot be seen by my network admins, it has a fixed IP address that can be referenced by clients running on the host os, including sqlplus and putty. I create an entry for it in the local hosts file on both the host os and the vm itself.
  • 10. Re: utl_http and tns:destination unreachable
    trent Expert
    Currently Being Moderated
    After employing your technique, the procedure seems to successfully run now. Thanks for the tip.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points