The role of Oracle for Windows platforms using Use_shared_socket

Two days ago a friend in QQ Group asked a question, the background is as follows:

After continuing to ask, there are the following replies:

1, the other side of the server connection no problem.

2, from the client can telnet server 1521 port.

3, Tnsping normal.

4, the other server does not set the IP trust.

5. Windows 2003 Server,oracle 10.1.0.2.0

However, Sqlplus from the client is timed out and cannot be connected.

First, look at the definition of ORA-12170:

Oerr Ora 12170

12170, 00000, "Tns:connect timeout occurred"//*cause:the server shut down because connection establishment orcommunication with a client failed to complete within the allotted timeinterval. This could be a result of the network or system delays; or this Mayindicate a malicious client is trying to cause a denial of Serviceattack 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 with the address in Sqlnet.log toidentify the source and restrict access. Note that logged addresses mayNot be reliable as they can is forged (e.g. in TCP/IP).

The general meaning is that the network or system problem timed out.

Then suggest that he can sqlnet.ora add the trace configuration and look at the trace of Sqlplus:

Trace_level_client=16

Trace_file_client=client

Trace_timestamp_client=on

Trace_directory_client= a path

But at this point the feedback says that the support staff on the server has turned on the Oracle shared connection and can now sqlplus.

Now the problem is out, what is an Oracle shared connection? What configuration do you have to access from the client? Why is this problem occurring?

In summary, this issue is related to Oracle running on Windows platforms. For Windows platforms, server-side snooping causes the client to redirect another random port when a connection request is initiated from the client. Since it is a random port, the firewall will naturally not allow this connection. As a result, the client can telnet to the listening port, but the sqlplus is timed out and cannot be connected. The reason is that the random port used by the client to communicate with the Oracle thread is rejected by the firewall. This happens only on Windows platforms, because platforms such as UNIX are supported by default for Port sharing and do not appear to be rejected by firewalls.

There are two possible solutions,

1. Use a firewall that contains the built-in sql*net agent.

Process:

1. Connect the agent and pass the connection to the listener.

2. Send the client redirect address.

3. redirect addresses via proxy connection.

4. Oracle accepts the connection.

2. Upgrade the database to more than 8.0.x, set the Use_shared_socket variable value to true in the registry.

The listener will bind and create a socket at the address specified in the "Listener.ora" file. In this socket, the listener's listening state is active. When the listener receives the connection request, the listener derives an Oracle thread on the listening port. This happens repeatedly so that eventually a listener and some established connections are using a 1521-port closed loop.

From more than 10.2, the use_shared_socket is already the default value of true, no need to modify.

MOS's 124140.1 article details this part of the text, translated as follows:

Objective:running on Windows nt,windows 2000, or a subsequent version of the operating system, connecting an Oracle NET or Net 8 connection to an Oracle database may cause the client to redirect a port number in a short port range (unless you encounter the "special remarks" below Issues in the section). The Microsoft Winsock V1.1 API does not allow a process to pass a TCP socket to another process. That is, port sharing is not allowed like in Unix systems. In order for an Oracle connection to run in a firewall environment, the customer has to use a firewall that contains the built-in sql*net agent, or a Winsock that uses Windows NT 4.0 (available from service pack#3), Windows 2000 or later V2 API, or a version of Windows 2000 or more. This feature can also be implemented in the Oracle database by using the "Use_shared_socket" parameter in the Windows registry.
Range:This article is primarily provided to DBAs and network administrators who need to access the Oracle database running on the Windows operating system through a firewall using SQL*NET,NET8 or an Oracle Net connection.
Detailed Description:as described in 66382.1, there are two ways to enable an Oracle database to run in an environment that uses a firewall. 1. Use a firewall that contains the built-in sql*net agent. The sql*net agent can open another listening process that allows the client to connect (typically on port 1610). The listener process then proxies the connection to the database and handles port redirection inside the firewall without triggering rules that prohibit access. At this point the firewall is like running the Oracle Connection Manager (Connectiong Manager or Multiprotocol switch, Multi Protocol interchange). A different point is that the Connection Manager Connection Manager is installed between the firewall and the database, and the firewall is installed allowing him to connect to the connection manager. Then the firewall will not need Oracle NET or sql*net. 2. The second method is to upgrade to 8.0.x or later, and set the parameter Use_shared_socket in the registry in order to activate port sharing (this value is true by default in Oracle 10.2 and above). This parameter can be set in Windows NT 4.0 (SP3 or higher), Windows 2000, or a later version of Winsock V2 support. This parameter also enables the use of firewalls that only support port filtering and do not have a sql*net proxy (at least a private connection). Multi-threaded servers (MTS) still need to redirect connections to a dynamic port, so sql*net proxies are also required. When Oracle is running on the Windows operating system, you can activate port sharing by setting this parameter variable in the system environment or in the Windows registry. It is recommended to set it in the Windows registry: Use_shared_socket = TRUE. How to set the system environment variables in Windows NT: Control Panel-system-environment. Windows 2000 or above, you can also use the system buttons in the Control Panel, but the environment variables need to click the "Advanced" button to locate and set. 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_Add a String_value value (to the right of the Registry window), not a key value (left side of the registry window). all of these methods require a restart of the Oracle process to allow the process to be read into the registry when it restarts.
Troubleshoot notes:you can use the ' netstat-a ' command to view the configuration. He should show all the client connections as well as the 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 activate the Oracle NET client trace by setting the following parameters in the client Sqlnet.ora file:trace_level_client= 16
Trace_directory_client = <directory>
trace_file_directory= <filename>[insert code here]you can navigate to a specific unique port by searching for the ' port ' string in the trace file.
Special notes:1. If you use the TCPS protocol or SSL, port redirection will occur. The solution is to use a firewall that supports sqlnet or NET8 connections or to configure a shared server sub-port. 2.10g and above, default use_shared_socket=true.