Re: SQL*Net Question

A copy of this was sent to "Andrew Melikov" <amelikov_at_cami.com> (if that email address didn't require changing) On Mon, 6 Sep 1999 00:37:54 -0400, you wrote:

>Hi All,
>

In some versions (i'm going to guess you are using NT - you don't say, no database/sqlnet version either which makes it hard to answer really technical questions), sqlnet works like this:

• client connects on known port 1521
• server listener recieves connection
• server listener spawns new process/thread to support new connection
• new process/thread asks the OS for a free port (uses a wildcard) and gets one (say 1234 for example)
• new process/thread writes back to the client on port 1521 that they should use port 1234 on HOST a.b.c.d.
• client and server processes close 1521 and client connects to server a.b.c.d on port 1234.

That last step might really get you if you are trying to write a network forwarding app. the tnsnames.ora file might have a listener at w.x.y.z with port 1521 (thats your program) and sqlplus will at first connect to w.x.y.z. The listener on the server side however will return a full address/portno -- it'll redirect sqlplus to a.b.c.d with port 1234. sqlplus will fail since it cannot connect to a.b.c.d -- it must see a.b.c.d as w.x.y.z but will not.

If you are really really into it, i guess you could look at the protocol used in the initial handshake and get around this. You could look at the port the listener is redirecting the client to and listen on that port yourself. You could also theoritically rewrite the packet to have the correct IP address for your needs. This of course would be a bear to debug and 'unsupported'.

OTOH: If you are on Oracle 8, you can use a WINSOCK V2 API feature called Shared Sockets . This allows a socket to be shared (or passed) between multiple processes. To use this functionality set USE_SHARED_SOCKET = TRUE in the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE section of the registry.

Please Note that as WINSOCK V2 allows a socket to be shared between multiple processes, you cannot restart the listener without taking the database down first.

>I am writing a network forwarder that supposed to
>enable access to oracle server from one net to another.
>
>Looks like simple packets forwarding addressed to port 1521
>doesn't do the job.
>
>Does anyone know what ports SQLnet uses to communicate?
>In my case -- a box is listening on 1521 port. The connection
>request comes in -- it opens another socket to 1521 on a real
>(the target) machine and begins crossforwading.
>
>Now, I open up SQL*plus and at the same time monitoring the
>forwarder -- connection happens (for a few seconds) and then
>the server drops it (closes the port) and SQL*Plus displays an
>error (connection was unexpectedly lost).
>
>Any suggestions?
>
>Thanks!
>Andrew Melikov
>amelikov_at_cami.com
>
>
>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Sep 06 1999 - 07:56:49 CDT