Oracle linux use shared socket
You can use the Windows parameters listed here with Oracle Net Services.
Oracle Net Service first checks for the parameters as environment variables, and uses the values defined. If environment variables are not defined, then Oracle Net Services searches for these parameters in the registry.
- About LOCAL Parameter
You can use the parameter LOCAL to connect to Oracle Database without specifying a connect identifier in the connect string. - About TNS_ADMIN Parameter
You can add the parameter TNS_ADMIN to change the directory path of Oracle Net Services configuration files from the default location of ORACLE_HOME \network\admin . - About USE_SHARED_SOCKET Parameter
You can set the parameter USE_SHARED_SOCKET to true to enable the use of shared sockets.
About LOCAL Parameter
You can use the parameter LOCAL to connect to Oracle Database without specifying a connect identifier in the connect string.
The value of the parameter LOCAL is any connect identifier, such as a net service name. For example, if the parameter LOCAL is specified as finance , you can connect to a database from SQL*Plus with:
SQL> CONNECT SMITH Enter password: password SQL> CONNECT SMITH@finance Enter password: password Oracle Net checks if LOCAL is defined as an environment variable or as a parameter in the registry, and uses finance as the service name. If it exists, then Oracle Net connects.
About TNS_ADMIN Parameter
You can add the parameter TNS_ADMIN to change the directory path of Oracle Net Services configuration files from the default location of ORACLE_HOME \network\admin .
For example, if you set TNS_ADMIN to ORACLE_HOME \test\admin , then the configuration files are used from ORACLE_HOME \test\admin .
About USE_SHARED_SOCKET Parameter
You can set the parameter USE_SHARED_SOCKET to true to enable the use of shared sockets.
If this parameter is set to true , the network listener passes the socket descriptor for client connections to the database thread. As a result, the client does not establish a new connection to the database thread and database connection time improves. Also, all database connections share the port number used by the network listener, which can be useful if you are setting up third-party proxy servers.
This parameter only works in the dedicated server mode in a TCP/IP environment. If this parameter is set, you cannot use the Oracle database listener to spawn Oracle Database. To spawn a dedicated server for an instance of Oracle Database not associated with the same Oracle home as the listener and have shared socket enabled, you must also set parameter USE_SHARED_SOCKET for both Oracle homes.
azad’s Blog
PURPOSE
This article is intended to present the solutions to the problems with connecting the Oracle client to the Oracle database behind a firewall. The major reference here is the well-known Note 125021.1 [1]from Oracle Metalink, the secure customer extranet only accessible for Oracle customers and employees.
[edit]
Oracle Behind a Firewall
During a SQLPlus connection to the Oracle database, a remote Oracle client will check the database name supplied in the sqlplus line (sqlplus user/password@database)and search the tnsnames.ora file or the names server for a match. Once it obtains the address for the database server, the client will start an attempted connection to the server. The listener on the server is contacted, and then the information about the free port on the server is sent back to the client via the listener for the actual connection. Upon receiving the information, the client will try to connect the database server on that port.
Port redirection may occur during that process depending on the operating system, the configuration of the init.ora file and/or the Oracle product under discussion. Port redirection requires the Oracle client to connect to the database using a different port (usually a randomly selected TCP port) than the default or originally configured one. If there is no firewall between the server and the client, port redirection will not affect the actual connection. However, if port redirection does occur with the server behind a firewall, the client will be likely to suffer from a connectivity failure. The reason is simple: the newly assigned port based on port redirection is often blocked by the firewall. Such failures are not uncommon on Windows platforms.
[edit]
Case 1
[edit]
Problem Briefing
Reported Database Versions: Oracle 8.x – 9.2.0.7;
Server OS: Windows 2003 Sp2, Windows XP Sp2;
Client OS: Windows 2000 Sp4, Windows XP Sp2, Windows 2003 Sp2;
Description: When the Windows firewall is enabled on the Oracle database server, the Oracle client connecting the server often receives such error messages as ora-12203 and ora-12535:
ORA-12203: TNS:unable to connect to destination.
There may be an error in the fields entered or the server may not be ready for a connection. You can check the server and retry, or continue.
ORA-12535: TNS:operation timed out
There may be an error in the fields entered or the server may not be ready for a connection. You can check the server and retry, or continue.
[edit]
Solution
Step 1: Check to see whether Oracle Port 1521 has been added to the Windows Firewall exception list on the Oracle database server machine. If not, add Oracle Port 1521 to the Windows Firewall exception list on the server machine (which you can do through the Windows Firewall dialog in Control Panel > Windows Firewall, or adding it in the registry).
Step 2: Enable USE_SHARED_SOCKET on the Oracle database server. That will force the server machine to share its port 1521 and thus all clients will stay on that port when connecting to the database. Noticeably, port redirection will not occur with USE_SHARED_SOCKET enabled. To do this, you must add a string value USE_SHARED_SOCKET=TRUE in the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE section of the server’s registry. (Refer to Endnote 1)
Here is the text to create a reg key for both steps to add to your registry:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\StandardProfile\GloballyOpenPorts\List]
“1521:TCP”=”1521:TCP:*:Enabled:Oracle Port 1521”
[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE]
“USE_SHARED_SOCKET”=”TRUE”
Save the text into a file with the extension “.reg”. Then double-click the file on the server machine to add the information to its registry.
[edit]
Notice
The workaround here, suggested in Oracle Metalink Note 125021.1, is a WINSOCK V2 API feature called Shared Sockets. This feature allows a socket to be shared among 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. Noticeably, as WINSOCK V2 allows a socket to be shared between multiple processes, the listener cannot be restarted without shutting down the database first.
A downfall of this solution is all connetions will stay on the listener port. If the listener is stopped or restarted all the connections will be severed from the database. Furthermore, USE_SHARED_SOCKET could be a performance bottleneck with multiple connections to the database. Please use it deliverately if many simultaneous connections to the database are involved.
[edit]
Case 2
[edit]
Problem Briefing
Reported Database Versions: Oracle 8.x – 9.2.0.7;
Server OS: Unix, including AIX, HP-UX, Linux and Solaris
Description: Connectivity failures may occur on Oracle database servers behind a firewall if they run in multi-threaded server mode(MTS). The reason is that Oracle Multi-Threaded Server (MTS) on Unix platforms will cause port redirection, and that the reassigned port is likely to be blocked.(Refer to Endnote 1)
[edit]
Solution
The workaround is to specify the port in the mts parameters of the init.ora file. The dispatcher will then be allowed to use the specified port, instead of the randomly selected port. Make sure that the specified port is open on the firewall. The following example shows the ports set to 8000 and 8001. Set the relevant parameters according to your individual systems.
Example
mts_dispatchers=”(address=(protocol=tcp)(host=hostname)(port=8000))(dispatchers=1)”
mts_dispatchers=”(address=(protocol=tcp)(host=hostname)(port=8001))(dispatchers=1)”
[edit]
ENDNOTE
1. Here is the reference from Oracle Metalink Note 125021.1:
A remote Oracle client making a connection to an Oracle database can fail if there is a firewall installed between the client and the server if there is port redirection. The firewall will block the port when the Oracle client connects to the database and can fail with the client receiving Oracle error ora-12203 or ora-12535. The Client connection failure is due to port redirection from the Windows operating system. Port redirection requires the Client to connect to the database using a different port than originally configured in the configuration ora files. Oracle MTS on Unix platforms, (without specifying the address with the ports in the init ora file), Oracle SSL, and NT platforms will cause port redirection.
Note 125021.1 suggests the following workarounds:
1. Firewall Vendor: The first solution is to contact the firewall vendor and see if they have an upgrade to allow for oracle connectivity with OS port redirection. If the firewall software can be upgraded, it is the best solution to follow.
2. Connection Manager: Use Connection Manager so that the TNS CONNECT following the REDIRECT happens on the server side of the firewall.
3. Use_Shared_Socket: A third solution for NT servers is to add the use_shared_socket = true into the registry (see 124140.1). This will allow the OS to share port 1521 and clients will then stay on 1521 when connecting to the database and will not be port redirected. A downfall of this option is all connetions will stay on the listener port and if the listener is stopped or restarted all the connections will be severed from the database.
2. Oracle Metalink Note 125021.1 also mentions how to solve the connecting problem in the SSL scenario:
Using SSL will cause Port redirection. The workaround is to select and set the ports using MTS in the init.ora, or by setting the Ports with Connection Manager in the cman.ora file.
Share this:
Like this:
Related
This entry was posted on July 8, 2009 at 11:39 am and is filed under DBA. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.