 Overriding WINDOWS FIREWALL [message #288549] |
Tue, 18 December 2007 00:10  |
pankajkmeena
Messages: 46
Registered: September 2007
|
Member |
|
|
Help me on this Topic
==========
I am unable to Override windows firewall for sqlplus .
I dont want to off firewall protection but just want to put sqlplus (port 1521) in exception list
I tried by adding both sqlplus program and its port in exception list but it does not work . Gives
ORA-12535: TNS:operation timed out
below is the log generated for droped packets by Firewall
#Version: 1.5
#Software: Microsoft Windows Firewall
#Time Format: Local
#Fields: date time action protocol src-ip dst-ip src-port dst-port size tcpflags
tcpsyn tcpack tcpwin icmptype icmpcode info path
2007-12-18 11:27:37 DROP TCP 10.0.13.201 10.0.13.145 3310 4321 48 S 2383010072 0 65535 - - - RECEIVE
2007-12-18 11:27:40 DROP TCP 10.0.13.201 10.0.13.145 3310 4321 48 S 2383010072 0 65535 - - - RECEIVE
2007-12-18 11:27:46 DROP TCP 10.0.13.201 10.0.13.145 3310 4321 48 S 2383010072 0 65535 - - - RECEIVE
2007-12-18 11:27:57 DROP TCP 10.0.13.201 10.0.13.145 3324 4334 48 S 331588257 0 65535 - - - RECEIVE
2007-12-18 11:28:00 DROP TCP 10.0.13.201 10.0.13.145 3324 4334 48 S 331588257 0 65535 - - - RECEIVE
2007-12-18 11:28:05 DROP TCP 10.0.13.201 10.0.13.145 3332 4341 48 S 405269986 0 65535 - - - RECEIVE
2007-12-18 11:28:06 DROP TCP 10.0.13.201 10.0.13.145 3324 4334 48 S 331588257 0 65535 - - - RECEIVE
2007-12-18 11:28:08 DROP TCP 10.0.13.201 10.0.13.145 3332 4341 48 S 405269986 0 65535 - - - RECEIVE
2007-12-18 11:28:14 DROP TCP 10.0.13.201 10.0.13.145 3332 4341 48 S 405269986 0 65535 - - - RECEIVE
2007-12-18 11:29:14 DROP TCP 10.0.13.201 10.0.13.145 3381 4388 48 S 3569391427 0 65535 - - - RECEIVE
2007-12-18 11:29:17 DROP TCP 10.0.13.201 10.0.13.145 3381 4388 48 S 3569391427 0 65535 - - - RECEIVE
2007-12-18 11:29:19 DROP TCP 10.0.13.201 10.0.13.145 3387 4393 48 S 2818393322 0 65535 - - - RECEIVE
2007-12-18 11:29:22 DROP TCP 10.0.13.201 10.0.13.145 3387 4393 48 S 2818393322 0 65535 - - - RECEIVE
2007-12-18 11:29:23 DROP TCP 10.0.13.201 10.0.13.145 3381 4388 48 S 3569391427 0 65535 - - - RECEIVE
2007-12-18 11:29:28 DROP TCP 10.0.13.201 10.0.13.145 3387 4393 48 S 2818393322 0 65535 - - - RECEIVE
2007-12-18 11:30:42 DROP TCP 10.0.13.201 10.0.13.145 3443 4456 48 S 3988680112 0 65535 - - - RECEIVE
2007-12-18 11:30:45 DROP TCP 10.0.13.201 10.0.13.145 3443 4456 48 S 3988680112 0 65535 - - - RECEIVE
LISTNER FILE
# LISTENER.ORA Network Configuration File: f:\oracle\ora92\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DXP1BSBN13122)(PORT = 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = f:\oracle\ora92)
(PROGRAM = extproc)
)
)
TRACE_LEVEL_LISTENER = NONE
TNS FILES
PROD1_P =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DXP1BSBN13122)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD1)
)
)
[Updated on: Thu, 20 December 2007 00:57] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Overriding WINDOWS FIREWALL [message #289169 is a reply to message #288905] |
Wed, 19 December 2007 23:25  |
pankajkmeena
Messages: 46
Registered: September 2007
|
Member |
|
|
Solution is
==============================
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<sid>.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 section] Case 1
[Edit section] 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 section] 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).Also Add Oracle.exe and listener.exe file to exception list of program.
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 section] 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 section] Case 2
[Edit section] 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 section] 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)"
[Updated on: Thu, 20 December 2007 00:58] by Moderator Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
got solution using one of link