We are in the middle of a network migration and we quickly discovered issues connecting to databases on Windows servers. The two networks normally can’t talk to each other but to aid in the migration we placed some of the servers behind a NAT-enabled router. You could ping the listener (tnsping) but when a connection was attempted the client would hang. (SQL*Plus, Toad, etc)
One of our network admins noticed in a network trace that the listener would redirect the client to a specific port (which is normal) but it would tell the client to use the servers IP Address, not the NAT address. So the client would try and connect to an IP address that didn’t ‘exist’.
There are two possible solutions to this problem. The first, is to modify the firewall and enable SQLNet Inspection but it wasn’t successful for us. We could connect to the database and execute small queries but large ones would hang after a certain amount of data was reached. We disabled this feature and while the network team was investigating we tried the second option.
The second option is to add the parameter:
USE_SHARED_SOCKET = TRUE
to the servers registry (regedit) under \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME<#>. When this feature is enabled, it tells the Listener to spawn a new thread on the listening port. As new connections arrive, they spawn threads as well, so in the end you have the listener and a number of connections all using port 1521. The disadvantage to this option is that bouncing the listener also disconnections all sessions.
This option worked and since this setup is temporary we’ll stick with it.
No comments:
Post a Comment