connect to Oracle server behind load balancer
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: connect to Oracle server behind load balancer

  1. #1
    Join Date
    Apr 2001
    Posts
    257

    Exclamation connect to Oracle server behind load balancer

    Hi,

    I have an Oracle server running on Windows 2000 and another one running on Solaris/Sparc. They have same confiugrations and are both placed behind a load balancer, which in a way is like a firewall and I have to open up cerntain ports to allow access.

    The problem is I can use SQL*Plus from outside the load balancer to connect to the Solaris based Oracle but get "ora-12535 tns operation timed out" from the windows 2000 based Oracle. They both have 8.1.7.3 and are in dedicated server mode. I can ping and tnsping both servers/databases just fine. On the load balancer, I have port 1521 open for both of them.

    I was told once a listener gets a request, it passes on to another port to handle the remaining of the request. Thus I need to open up other ports as well. But does listener use a range of ports or it's completely random? I just don't understand why the Oracle on Solaris is OK but not on Windows 2000.

    Any suggestions are very much apprecated!

  2. #2
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    Answer from Asktom.oracle.com

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

    This article describes how to get around firewall problems with SQL*Net/Net*8
    on NT Servers


    Listeners on NT commonly listen on port 1521. It is a common misconception that
    if you allow access in to and out of the firewall by enabling access through
    port 1521, that SQL*Net clients will be able to connect. To understand why the
    connect will fail, it is necessary to understand how a SQL*Net
    connection on NT works.

    When a client initiates a connect, a TCP connection is established with port
    1521. A TNS CONNECT packet is then sent to the listener. On UNIX systems the
    listener process will fork a new Oracle process to deal with the new incoming
    connection. With UNIX, forked processes will inherit the resources owned by the
    parent process, in other words file handles and TCP sockets.

    Earlier releases of SQL*Net for Windows NT used the WINSOCK V1.1 API. With this
    version of WINSOCK there is no capabaility of passing a TCP socket between two
    processes, and no way to inherit a TCP socket. To work around this restriction
    a new thread of execution is created by the main Oracle process and a local
    connection is made between the listener and this new thread. The newly created
    Oracle thread randomly selects a new TCP port, for example port 1087, to use for
    the connection request and informs the listener of the new port to be used.

    The listener now needs to inform the client that they need to REDIRECT the
    connection attempt to this newly selected networking endpoint. The listener now
    sends a TNS REDIRECT packet to the client with details of the new port to
    reconnect to. The client drops the existing TCP connection and then issues a TCP
    Connect sequence to the new TCP port, and this is then followed by a TNS Connect
    packet. If all is well and the Oracle server is able to process the incoming
    connection request, then the server thread will respond with a TNS ACCEPT packet
    and data will begin to flow.

    So, if you enable connects through port 1521 on your firewall, you can now see
    that after the REDIRECT packet has been sent to the client, the connect will
    fail as port 1087 is not enabled in the firewall. As the REDIRECT port that gets
    generated is entirely at random, you cannot enable access through multiple
    ports in the firewall as you have no idea which ports will get allocated.

    To workaround this problem there are several options:

    1. Configure the firewall to limit IP addresses rather than port numbers. This
    is not a very secure option.

    2. Use Connection Manager so the TNS CONNECT following the REDIRECT happens the
    server side of the firewall.

    3. If you are on Oracle 8, you can use a WINSOCK V2 API feature called Shared
    Sockets . This allows a socket to be shared (or passed) between multiple
    processes. To use this functionality in a single Oracle Home enviroment, set
    USE_SHARED_SOCKET = TRUE in the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE section of
    the registry. If you are using Multiple Oracle Homes, change to the desired
    Oracle 8 Home and view the oracle.key file in ORACLE_HOME\BIN to find which
    registry key to add USE_SHARED_SOCKET to.

    Please Note that as WINSOCK V2 allows a socket to be shared between multiple
    processes, you cannot restart the listener without taking the database down
    first.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  3. #3
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    Or you can configure shared servers with certain dispatcher port.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  4. #4
    Join Date
    Apr 2001
    Posts
    257
    Thanks, Calvin.

    I wonder how much overhead does it have if I use USE_SHARED_SOCKET = TRUE workaround. It doesn't seem so good when many connections sharing the same port. However, it seems to be the solution with least modifications.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width