Functions of using USE_SHARED_SOCKET in Oracle on Windows

A friend asked a question in the QQ group two days ago. The background is as follows:

After continuing to ask, I have the following reply:

1. The connection to the other server is normal.

2. The slave client can telnet port 1521 of the server.

3. tnsping is normal.

4. No IP trust is set for the peer server.

5. windows 2003 server, Oracle 10.1.0.2.0

However, sqlplus from the client times out and cannot be connected.

First, look at the definition of ORA-12170:

Oerr ora 12170.

12170,000 00, "TNS: Connect timeout occurred" // * Cause: The server shut down because connection establishment or communication with a client failed to complete within the allotted time interval. this may be a result of network or system delays; or this may indicate that a malicious client is trying to cause a Denial of Service attack on the server.
// * Action: If the error occurred because of a slow network or system, reconfigure one or all of the parameters SQLNET. INBOUND_CONNECT_TIMEOUT, SQLNET. SEND_TIMEOUT, SQLNET. RECV_TIMEOUT in sqlnet. ora to larger values.
If a malicious client is suspected, use the address in sqlnet. log to identify the source and restrict access. note that logged addresses may not be reliable as they can be forged (e.g. in TCP/IP ).

This generally means timeout due to network or system problems.

We recommend that you add the trace configuration to sqlnet. ora and check the sqlplus trace:

TRACE_LEVEL_CLIENT = 16

TRACE_FILE_CLIENT = CLIENT

TRACE_TIMESTAMP_CLIENT = ON

Trace_directory_client = a path

However, it is reported that the support staff on the server has enabled the Oracle shared connection, and now sqlplus can be used.

Now the problem arises. What is an Oracle shared connection? What configuration can be accessed from the client? Why is this problem?

To sum up, this problem is related to Oracle running on Windows platform. When a connection request is initiated from a client on a Windows platform, the server listener will redirect the client to another random port. Since it is a random port, the firewall naturally does not allow this connection. Therefore, the client can telnet to listen to the port, but sqlplus times out and cannot be connected. The reason is that the random port used by the client to communicate with the Oracle thread will be rejected by the firewall. This situation only occurs on the Windows platform, because UNIX and other platforms support Port Sharing by default, and the port will not be rejected by the firewall.

There are two feasible solutions,

1. Use a firewall containing built-in SQL * Net proxy.

Process:

1. Connect the proxy and send the connection to the listener.

2. Send the client redirection address.

3. Use a proxy to connect to the redirection address.

4. Oracle accepts the connection.

2. Upgrade the database to 8.0.x or above and set the USE_SHARED_SOCKET variable value in the Registry to TRUE.

The listener binds and creates a socket on the address specified in the "listener. ora" file. In this socket, the listener is in the ACTIVE status. When the listener receives connection requests, the listener derives an Oracle thread from the listener port. This situation occurs repeatedly, so that a listener and some established connections are all in the closed loop using port 1521.

From 10.2 to above, USE_SHARED_SOCKET is already the default value of TRUE, and no modification is required.

This part of MOS 124140.1 is detailed:

Objective: To connect to Oracle Net or Net 8 in a Windows NT, Windows 2000, or later operating system, the client may be redirected to a port number in a short port range (unless there is a problem in the "special remarks" section below ). Microsoft WINSOCK V1.1 API does not allow a process to pass a TCP socket to another process. That is, Port Sharing in UNIX systems is not allowed. To enable Oracle connections to run in a firewall environment, you have to use a firewall containing the built-in SQL * Net proxy, or use Windows NT 4.0 (available in Service Pack #3) windows 2000 or later, or Windows 2000 or later. This feature can also be implemented by using the "USE_SHARED_SOCKET" parameter in the Windows registry.
Scope: This article mainly provides DBAs and network administrators who need to access Oracle databases running on Windows operating systems through the firewall using SQL * Net, Net8, or Oracle Net connections.
Description: As described in 66382.1, there are two ways to run the Oracle database in a firewall environment. 1. Use a firewall containing built-in SQL * Net proxy. The SQL * Net proxy can start another listening process (usually on port 1610) that connects the client ). The listening process then acts as a proxy for the connection to the database and processes the port redirection inside the firewall, without triggering access prohibition rules. At this time, the firewall is like running the Oracle Connection Manager (Connectiong Manager or Multi-Protocol switch, Multi Protocol Interchange ). The difference is that the Connection Manager is installed between the firewall and the database, and allows the Connection Manager to be connected when the firewall is installed. Therefore, the firewall does not need Oracle Net or SQL * Net. 2. the second method is to upgrade to version 8.0.x or later and set the USE_SHARED_SOCKET parameter in the Registry to activate Port Sharing (the default value is TRUE in Oracle 10.2 and later versions ). This parameter can be set in a later version supported by Windows NT 4.0 (SP3 or higher), Windows 2000, or Winsock V2. With this parameter, you can also use a firewall that only supports port filtering and does not have an SQL * Net proxy (at least a dedicated connection. The multi-threaded server (MTS) still needs to redirect the connection to a dynamic port, so it also needs the SQL * Net proxy. When Oracle runs on Windows, you can set this parameter variable in the system environment or Windows Registry to activate Port Sharing. We recommend that you set USE_SHARED_SOCKET = TRUE in the Windows registry. In Windows NT, set system environment variables: Control Panel-system-environment. You can also use the system button of the control panel for Windows 2000 or later, but you need to click the "advanced" button for environment variables to locate and set them. You can also use the Windows Registry Settings: In Oracle release 8.0 \ HKEY_LOCAL_MACHINE \ SOFTWARE \ ORACLEIn Oracle release 8.1 \ HKEY_LOCAL_MACHINE \ SOFTWARE \ ORACLE \ HOME <#>
In Oracle release 9 or later \ HKEY_LOCAL_MACHINE \ SOFTWARE \ ORACLE \ Key _ <Home> adds a STRING_VALUE (right side of the Registry window), not a KEY value (left side of the Registry window ). All these methods require the restart of the Oracle process so that the registry can be read when the process is restarted.
Troubleshoot remarks: You can run the 'netstat-a' command to view the configuration. It should show all client connections and listening ports.

C:\>netstat -anActive ConnectionsProto  Local Address Foreign Address StateTCP    0.0.0.0:1521        0.0.0.0:0             LISTENINGTCP    198.51.100.9:1521   198.51.100.6:55769    ESTABLISHEDTCP    198.51.100.9:1521   198.51.100.91:13452   ESTABLISHEDTCP    198.51.100.9:1521   198.51.100.92:13203   ESTABLISHEDTCP    198.51.100.9:1521   198.51.100.92:13202   ESTABLISHEDTCP    198.51.100.9:1521   203.0.113.166:12331   ESTABLISHEDTCP    198.51.100.9:1521   203.0.113.10:12123    ESTABLISHEDTCP    198.51.100.9:1521   203.0.113.10:11252    ESTABLISHEDTCP    198.51.100.9:1521   192.0.2.22:14524      ESTABLISHEDTCP    198.51.100.9:1521   192.0.2.20:13524      ESTABLISHEDTCP    198.51.100.9:1521   192.0.2.102:13452     ESTABLISHEDTCP    198.51.100.9:1521   192.0.2.121:1342      ESTABLISHED

You can set the following parameters in the client SQLNET. ORA file to activate the Oracle Net client trace: TRACE_LEVEL_CLIENT = 16
TRACE_DIRECTORY_CLIENT = <directory>
TRACE_FILE_DIRECTORY = <filename> [Insert code here] A unique port can be located by searching the 'Port' string in the trace file.
Special remarks: 1. If TCPS or SSL is used, port redirection will occur. The solution is to use a firewall that supports Sqlnet or Net8 connections or configure a Shared Server distribution port. 2. 10 Gb or above. The default value is USE_SHARED_SOCKET = TRUE.