Home » RDBMS Server » Networking and Gateways » Overriding WINDOWS FIREWALL
icon4.gif  Overriding WINDOWS FIREWALL [message #288549] Tue, 18 December 2007 00:10 Go to next message
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 #288558 is a reply to message #288549] Tue, 18 December 2007 00:34 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

How you are sure this is the problem due to firewall?
Re: Overriding WINDOWS FIREWALL [message #288562 is a reply to message #288558] Tue, 18 December 2007 00:44 Go to previous messageGo to next message
pankajkmeena
Messages: 46
Registered: September 2007
Member
because whenever i turn off firewall it is working Cool
Re: Overriding WINDOWS FIREWALL [message #288571 is a reply to message #288549] Tue, 18 December 2007 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.google.com/search?hl=en&q=Oracle+12535+firewall&meta=

Regards
Michel
icon8.gif  Re: Overriding WINDOWS FIREWALL [message #288609 is a reply to message #288571] Tue, 18 December 2007 03:13 Go to previous messageGo to next message
pankajkmeena
Messages: 46
Registered: September 2007
Member
thankx but getting same after changing registry as specified in document

can u give any other solution or link
Re: Overriding WINDOWS FIREWALL [message #288622 is a reply to message #288609] Tue, 18 December 2007 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Google returns 2530 links.
Did you read all of them?
You want more, check:
http://groups.google.com/groups/search?hl=en&lr=&num=10&q=12535+firewall+group%3Acomp.databases.oracle.*&qt_s=Recherc her

Regards
Michel
icon14.gif  Re: Overriding WINDOWS FIREWALL [message #288875 is a reply to message #288622] Wed, 19 December 2007 00:17 Go to previous messageGo to next message
pankajkmeena
Messages: 46
Registered: September 2007
Member
thankx
Smile got solution using one of link

[Updated on: Wed, 19 December 2007 00:23]

Report message to a moderator

Re: Overriding WINDOWS FIREWALL [message #288905 is a reply to message #288875] Wed, 19 December 2007 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the solution was?
It would be of some help for those that will come next.

Regards
Michel
Re: Overriding WINDOWS FIREWALL [message #289169 is a reply to message #288905] Wed, 19 December 2007 23:25 Go to previous messageGo to next message
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

Re: Overriding WINDOWS FIREWALL [message #289189 is a reply to message #289169] Thu, 20 December 2007 00:59 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for this, it will of great help for further readers.

Regards
Michel
Previous Topic: Connection Manager Performance Issue
Next Topic: ORA-12154: TNS:could not resolve service name
Goto Forum:
  


Current Time: Fri Dec 09 01:48:07 CST 2016

Total time taken to generate the page: 0.08781 seconds