Listeners on NT and port 1521.

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 multipleports 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.

This article was taken from Tom Kyte's asktom pages (http://asktom.oracle.com).