Discussion :

[jcdelmas]

Bonjour,

je voudrais savoir comment forcer un serveur et client ORACLE 9.2 pour utiliser un port défini derrière un DMZ.

merci

[orafrance]

tu peux mettre la clé de registre USE_SHARED_SOCKETà TRUE dans HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

Ainsi, un seul port doit être ouvert sur le firewall

[jcdelmas]

si je modifie le registre.. seul le port 1521 sera utilisé ?

je dois faire ça sue le client et le serveur ?

[jcdelmas]

ok .. je pense avoir trouvé :

1. Add TCP Port 1521 to the exceptions list of Windows Firewall (this is the default listening port for client connections to the Oracle listener)

2. Force all Oracle client connections to use Port 1521. After Oracle has set up a client connection using the listener on Port 1521, it uses a random TCP port for the actual connection to the server. To avoid having to figure out what port Oracle is using per connection and add it to the exception list, you must force all client connections to go through Port 1521. To do this, you must add a string value USE_SHARED_SOCKET=TRUE in the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE section of the registry

The workaround I listed above is for the client machine only. . When this is complete, you should restart your client computer for the changes to take effect.

liens : http://blogs.msdn.com/vsdata/archive...17/323719.aspx

[orafrance]

ok .. je pense avoir trouvé :

1. Add TCP Port 1521 to the exceptions list of Windows Firewall (this is the default listening port for client connections to the Oracle listener)

2. Force all Oracle client connections to use Port 1521. After Oracle has set up a client connection using the listener on Port 1521, it uses a random TCP port for the actual connection to the server. To avoid having to figure out what port Oracle is using per connection and add it to the exception list, you must force all client connections to go through Port 1521. To do this, you must add a string value USE_SHARED_SOCKET=TRUE in the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE section of the registry

The workaround I listed above is for the client machine only. . When this is complete, you should restart your client computer for the changes to take effect.

liens : http://blogs.msdn.com/vsdata/archive...17/323719.aspx

bah oui... c'est ce que je disais

[orafrance]

non que le client, et oui, seul le port du listener sera utilisé.

[jcdelmas]

dsl... réponses croisées

[jcdelmas]

si je puis me permettre d'ajouter :

Use of USE_SHARED_SOCKET with Oracle 8.1.7 and Windows NT/2000

In short:
1. If you are using Oracle 8.1.7 install patch 2376472, that is Oracle 8.1.7.4, you can get it at Metalink
2. Add USE_SHARED_SOCKET to your registry (there are 2 possible locations, depending on your Oracle version)
3. Restart the computer
4. Do some local connection testing using a client computer (tnsping, sqlplus, netstat)
5. Open the port in the firewall
6. Do some connection testing from outside the firewall (tnsping, sqlplus, netstat)

Here are the details:

The USE_SHARED_SOCKET parameter allows you to use a single port for oracle communications, by default TCP 1521.
You must have in mind that this might slow down connections a little when many clients are connected (50 or more, depending on different conditions).
Also if the listener service shuts down all connections will be broken.

This parameter only works fine with Oracle Patch 8.1.7.4, you can get it at Oracle Metalink homepage, and here is the detail for the patch:

Patch 2376472
Oracle Database Family: Patchset 8.1.7.4 PATCH SET FOR ORACLE DATA SERVER
Release 8.1.7.4
Updated 14-JUN-2002
Size 112MB
File name p2376472_8174_WINNT.zip

I would advise you to install this patch before you create any databases in your system, it's easier.
If you already have some databases created there's no problem, just follow the additional steps after installing the patch.

Troubleshooting: Oracle services have to be stopped during installation of the patch, in my case I started windows in safe mode and then installed the patch. Also because my computer has an Intel Xeon I had to rename 2 files called symcjit.dll to symcjit.old, because (at least in my case) this java jit compiler does not work with this processor, the same is true for Intel Pentium 4.
Almost for sure TNSPING command already works at this point but not SQLPLUS, isn't it? Don't worry, the update will make USE_SHARED_SOCKET work.

Step 1: Installing patch, done.


Next we have to add the USE_SHARED_SOCKET parameter to the registry.
Place the parameter under the following locations:
For Oracle 8.0 use \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
For Oracle 8i or newer use \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME#
(Where # is the number of your HOME, usually zero "HOME0")

Add USE_SHARED_SOCKET (New, Alphanumeric Value), then set its value to TRUE

Then restart your computer.


Step 2: Add USE_SHARED_SOCKET parameter into the registry, set it to TRUE and restart, done.


From some client computer do some connection tests.
Use:
C:\> tnsping your_oracle_service 100
(That will ping you database service 100 times to check connection times)
Usually tnsping works fine even before USE_SHARED_SOCKETS have been set, because it doesn't make a connection to the service.

Use:
C:\> sqlplus user/password@your_oracle_service

If connection succeeds you'll see something like this:

SQL*Plus: Release 8.1.7.0.0 - Production on Jue Sep 22 12:32:19 2005
(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
JServer Release 8.1.7.4.1 - Production

SQL>

Then in the SQL> prompt you can do some query to do some more testing, but we are already connected.

While doing your tnsping and during your connection with sqlplus try this command in the server:
C:\> NETSTAT

There you can see if connections are being made using only your oracle port (usually 1521)
You'll see something like this:

Active Connections

Proto Local Address Foreign Address State
TCP server:1036 server:1521 ESTABLISHED
TCP server:1043 server:1088 ESTABLISHED
TCP server:1088 server:1043 ESTABLISHED
TCP server:1521 server:1036 ESTABLISHED
TCP server:1316 CLIENT02:netbios-ssn TIME_WAIT
TCP server:1317 CLIENT02:netbios-ssn TIME_WAIT
TCP server:1318 CLIENT02:netbios-ssn TIME_WAIT
TCP server:1521 CLIENT04:1336 TIME_WAIT
TCP server:1521 CLIENT04:1337 TIME_WAIT
TCP server:1521 CLIENT04:1338 TIME_WAIT
TCP server:1521 CLIENT04:1339 TIME_WAIT
TCP server:1521 CLIENT04:1340 TIME_WAIT
TCP server:1521 CLIENT04:1341 TIME_WAIT
TCP server:1521 CLIENT04:1342 TIME_WAIT
TCP server:1521 CLIENT04:1343 TIME_WAIT
TCP server:1521 CLIENT04:1344 TIME_WAIT
TCP server:1521 CLIENT04:1345 TIME_WAIT
TCP server:1521 CLIENT04:1347 ESTABLISHED

There we see that in column "Local Address" only port 1521 is being used for our client computer "CLIENT04" (among other ports and clients connected)


You can use any other kind of connections you would like to test, in my case tnsping and sqlplus were enough.

Troubleshooting:
On your server check the files listener.log and sqlnet.log they can give you some good information about connections.
Delete them both to start fresh (you'll have to stop the listener service to delete the listener.log file, you do so with running "C:\> LSNRCTL" then using the STOP command, deleting the file, then restarting the service using the START command and then the EXIT command)


Step 3: Local tests using tnsping and sqlplus, done.


Now you have to open your Oracle port in the firewall, usually TCP port 1521

Troubleshooting:
Sometimes you'll have a modem, a router, a firewall or a mix of them, consider that you might configure some of them or all to make port forwarding work.
Also considering security issues: 1521 is a very well known port, you can change it. Also consider Listener password and some others, check your options well.

After opening the port do some testing from outside the firewall (depending on your configuration you can even try testing using an internet Dial-up connection).

Do your TNSPING, your SQLPLUS connection, your NETSTAT on the server and so on.

Troubleshooting:
In my experience I had no problems with clients working with a personal firewall so I had not to open any port on those personal firewalls.