On Metalink check out the following note:
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab
ase_id=NOT&p_id=132729.1
This is the Technical Library "Connection Manager and Firewalls " index.
eg check out:
How to enable USE_SHARED_SOCKET on WINNT and Windows 2000 124140.1
Oracle And Firewalls : Answers To Frequently Asked Questions 2084440.6
Oracle Connectivity with Firewalls 125021.1
Firewalls, Windows NT and Redirections 66382.1
Solving Firewall problems on NT 68652.1
Have fun,
Bruce
>From 2 postings in July last year:
"
I had another type of problem with firewalls and MTS connections, but that
wouldn't apply to a dedicated connection. The problem I had was with MTS
was
that the dispatchers were randomly assigning port numbers and I couldn't
control through the firewall what to keep open. I finally found that you
can
add to the init.ora file an entry in the mts_dispatchers what port to assign
to
the dispatchers.
mts_dispatchers =
"(address=(partial=true)(protocol=tcp)(host=db.gotdata.net)(port=1104))(disp
atchers=1)(SESSIONS=20)(CONNECTIONS=10)(mul=OFF)(pool=OFF)"
In that example I made one dispatcher use port 1104, I just added multiple
lines with each dispatcher to permanently assign them to a port, and opened
those ports for incomming connections on the firewall.
"
and
"
The issue here is getting SQL*Net to connect through a firewall. If that
works, then Designer will work. On NT by default, ports 1521 and/or 1526
are used for a connection, but a random port is selected for communication
from the server back to the client. If you are on version 8.0.X, there is a
specific parameter that can be set to keep communication on a specific port.
Then, you could open the firewall for that port and have things work. The
other option is to see if the firewall you are using specifically supports
Oracle SQL*Net. Check with your firewall vendor for specifics. Excepts
from
technical notes and documentation included below.
===========================================================
On Windows NT, when a connect request comes in to the listener, the listener
spawns and Oracle thread. This thread is a listening thread, and is started
on a wild-card address - meaning that the thread is listening for
connections on the current I.P. address, and an unused port number given to
the thread by the networking software. The Oracle thread will contact the
listener using IPC and inform the listener of its listening address,
connection load, and some other status information. The listener sends back
to the client a REDIRECT address. This tells the client to reconnect to the
newly spawned Oracle thread.
Since this Oracle thread is on a random port (a range of ports cannot be
defined), the firewall will not let the connection through. The resulting
error is usually a TNS-12203.
There are two ways to resolve this issue.
The first way is to use a firewall that has a SQLNet proxy built into it.
The way this works is that the SqlNet proxy starts another listening
processes (usually on port 1610). This causes the firewall to act as a
Multi Protocol Interchange. So, by using the tnsnav.ora file on the client,
you connect to port 1610 (the firewall). The firewall passes the connection
to the server. The server gives a redirect to the client. The client
reconnects to the firewall proxy on port 1610, and the firewall passes the
connection to the Oracle thread on the wild-card listening address. Here's
what the connection flow would look like:
1. connect to proxy and pass connection to listener
2. send redirect to client
3. connect to redirected address via the proxy
4. oracle accepts the connection
firewall
||
+------+ <--------2--------||-------2------ +---------+
|client| || |listener |(port_21)
+------+ --------1------> proxy ----1------> +---------+
A \ /||\
| \---------3-------/ || \-----3------> +---------+
| || |
oracle |(port=xxxx)
+--------------4---------||-------4------- +---------+
The second way to resolve this issue is to upgrade the server to 8.0.x and
use the USE_SHARED_SOCKET parameter in the registry. With this method, it
doesn't matter what kind of firewall you have. The syntax for this
parameter is:
USE_SHARED_SOCKET = TRUE
Place the parameter in the registry under HKEY_LOCAL_MACHINE:Software:Oracle
Restart Oracle and the listener for the parameter to take effect. Here's
how USE_SHARED_SOCKET works. The listener binds and creates a socket on the
address specified in the listener.ora file. On this socket, there is a
LISTEN state active that is used by the listener. When a new connection
comes in to the listener, the listener spawns an Oracle thread on the
listening port (i.e. 1521). This happens over and over again so that you
have a listener and several established connections using port 1521.
Pictorially this scenario would look like this:
+---<O>--------<O>----<O>---<O>--+
| |
| This square represents <O>
| a listening socket for |
| port 1521. |
<O> |
| <O> = oracle thread <O>
| <L> = listener |
| |
+-<O>-------<L>--<O>------<O>----+
The operating system then does a poll() or a select() on the socket to test
for any data. If any of the threads have data, a signal handler is used to
contact the application and inform it of the new data.
The disadvantage of USE_SHARED_SOCKET is that if the listener shuts down,
all connections are disolved.
Finally, a very common question concerning the listener and port numbers is
why different port numbers show up in the listener.log file. What you are
seeing is the client's source port and client's source IP address. Here's
how this relates to your firewall.
If I want to make a TCP connection to a server (let's say with TELNET), I
need to create a socket. To create a socket, I need 4 pieces of
information: A source IP and port, and a destination IP and port. So let's
use TELNET as an example (the listening port for the TELNET process is 23 on
the server):
source destination
+-----------+---------------+
IP |138.2.12.8 |185.45.67.53 |
+-----------+---------------+
port | xx | 23 |
+-----------+---------------+
Notice I have labeled the source port as 'xx'. What happens is that the
networking software on the client chooses at random, or in sequential order,
a valid port (between 1024 and 65535) so the client can send and receive
data. This is what you are seeing in the listener.log file.
Will the be a problem with the firewall?
No. The firewall will restrict incoming connections, but will freely let
any connection on any port out (which is okay). Here's what it might look
like:
Firewall
<-------------||---------\
<-------------||---------\\
[CLIENT]----------------->|| \---[SERVER]
<-------------||---------//
<-------------||---------/
>From the 8.1.5 Documentation -
USE_SHARED_SOCKET
You can set the USE_SHARED_SOCKET parameter 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 need to 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.
On Windows NT 4.0 Service Pack3 or earlier, enabling this option precludes
bringing the network listener up or down in a case where a database
connection spawned by the network listener is active. Therefore, you may
need to shut down all of the databases serviced by a network listener before
you can bring down and restart a network listener. This results from the way
shared sockets have been implemented in WINSOCK2. WINSOCK2 does not allow a
reliable thread to a network listener on any port on which other connections
are also active. This is not an issue on Windows NT 4.0 Service Pack 4 or
later. Oracle recommends that you upgrade to a later service pack.
This parameter only works in dedicated server mode in a TCP/IP environment.
If this parameter is set, you cannot use the 8.1.5 listener to spawn Oracle
7.x databases. To spawn an Oracle 8.0.x database from an 8.1.5 listener with
the shared socket enabled, you must also set the variable USE_SHARED_SOCKET
for the 8.0.x Oracle home.
"
-----Original Message-----
Sent: Tuesday, 19 June 2001 1:37
To: Multiple recipients of list ORACLE-L
Kevin,
You can allocate a shared port is you use MTS.
In that case, both the connection and data share the same port on the
server.
I believe that Oracle recommends no more than 10 connections per shared
port, but YMMV.
This is not a case for NT-bashing. You might want to research a little
further next time.
Actually, I thought that Cygwin is NT-bashing :)
http://sources.redhat.com/cygwin/
Paul
-----Original Message-----
Sent: Monday, June 18, 2001 10:45 PM
To: Multiple recipients of list ORACLE-L
Hahahhah, I looked all over the place, even metaslop to find out if I could
do this, I couldn't. According to everything that I read there is no way to
know what port oracle is going to allocate for the connection. Do you mean
that it would use say port 1521 for every connection that is made? Love the
NT bashing! 7 years, please, like that would ever happen!!
----- Original Message -----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Friday, June 01, 2001 11:50 PM
> KK - you probably got something on this already, but if
> you're on NT (making appropriate gagging or adulatory noises)
> then there is a way to have the listener use the same port
> to listen and maintain connections.
>
> I gather it's deprecated, since a listener bounce snaps
> the connections.
>
> But, f*ck it, it's NT. It's not like you'll be keeping those
> connections up for seven years straight or anything.
>
> -----Original Message-----
> To: Multiple recipients of list ORACLE-L
> Sent: 6/1/2001 3:55 PM
>
> i went through this same issue recently. Unfortunatley my only option
> was
> to open up all ports to the specific db server to specific IP's. I
> don't
> like doing it, but I had to. If you look on Metaslop there is some
> information on this problem. There are fixes for some routers, check
> and
> see if yours is one of them.
>
> -----Original Message-----
> Turner
> Sent: Friday, June 01, 2001 3:11 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Anyone have any recommendations on getting sqlplus connections to a
> database
> running
> MTS which has a router in between that is filtering except on port 1521?
> I
> can open
> up additional ports but opening a whole slew of them for MTS's random
> port
> allocation
> has put the network guys in shock.
>
> We have set SERVER=DEDICATED in tnsnames to get connections thru for now
> and
> I'm looking
> at CMAN for a permanent fix, but does anyone else think they have a
> better
> way?
>
> Thanks, Dave Turner
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Reardon, Bruce (CALBBAY)
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
