Oracle Database 11g -- Internal Mechanics of Connecting to the Oracle Database

Problem

You want a better understanding on how clients connect to the Oracle Databases

Solution

When a client wants to connect to an Oracle Database instance, the client first sends connection request to the listener. When the listener receives the client request, depending on the database version, OS, and connection type, the listener performs one of the following:

• in case of shared servers, hands off the connection directly to a dispatcher 
• in case of dedicated servers, spawns a dedicated server, and hands off the connection to the dedicated server process
• sends a redirect message back to the client with details of the dispatcher or dedicated server process, and then the client connects directly to the dispatcher or the dedicated server process

 

How redirect connections established:

 

1. A client sends a NSPTCN packet to the listener

2. The listener then gets this NSPTCN packet, and it can do one of two things:

• If client requests for a shared session, it will send a NSPTRD (redirect) packet having the dispatcher ipaddress or hostname and port to the client
• If client requests for a dedicated session, it will spawn a shadow server process, and if on Windows, by default, will send over an NSPTRD packet to the client with the ipaddress and port of the server process

3. The client receives this packet, and next, will attempt to connect to either dispatcher or the server process directly

Example:

The initial packets sent to the listener on port 1521 in trace file:

niotns: Calling address: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) (HOST=<server1>)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=<DB_NAME>.<DB_DOMAIN>)))
nladget: entry
nladget: exit
nscall: entry
nscall: connecting...
nsc2addr: entry
nttbnd2addr: entry
nttbnd2addr: port resolved to 1521

The received packet from the listener telling the client to use 1729 port:

nscon: recving a packet
nsprecv: entry
nsbal: entry
nsbgetfl: entry
nsbgetfl: normal exit
nsmal: entry
nsmal: 44 bytes at 0xb892d0
nsmal: normal exit
nsbal: normal exit
nsprecv: reading from transport...
nttrd: entry
nttrd: socket 232 had bytes read=64
nttrd: exit
nsprecv: 64 bytes from transport
nsprecv: tlen=64, plen=64, type=5
nsprecv: packet dump
nsprecv:00 40 00 00 05 00 00 00 |.@......|
nsprecv:00 36 28 41 44 44 52 45 |.6(ADDRE|
nsprecv:53 53 3D 28 50 52 4F 54 |SS=(PROT|
nsprecv:4F 43 4F 4C 3D 74 63 70 |OCOL=tcp|
nsprecv:29 28 48 4F 53 54 3D 31 |)(HOST=x|
nsprecv:33 38 2E 32 2E 32 31 33 |xx.y.zzz|
nsprecv:2E 36 31 29 28 50 4F 52 |.00)(POR|
nsprecv:54 3D 31 37 32 39 29 29 |T=1729))| <- port change
nsprecv: normal exit
nscon: got NSPTRD packet
nscon: got 54 bytes connect data
nscon: exit (0)

The client resolving the connection to port 1729:

nscall: connecting...
nsc2addr: entry
nttbnd2addr: entry
nttbnd2addr: port resolved to 1729
nttbnd2addr: using host IP address: xxx.y.zzz.00
nttbnd2addr: exit
nsc2addr: normal exit

The port that is assigned to the client is randomly chosen by the operating system and cannot be modified. It can be any free port available (usually above port 1024) that the server determines is not is use by any other software or hardware device. 

Probable Issues in Firewall Environments

As seen in the above example, when the client receives the redirect packet, the client will reconnect to the different port. This is where usually connect problems happen, since the firewall will block new incoming connections to unknown ports.

To resolve the issue we need prevent the listener to send redirect packets, and ensure that clients are sent NSPTRS (resend) packet:

For Oracle Database version 10.2+:

• the listener by default uses port sharing and hands off connections

For Oracle Database version 10.1 and earlier:

• If db server is on Windows, and client wants a dedicated session, enable USE_SHARED_SOCKET to TRUE, and this will cause listener to hand off the socket end to the shadow server process, and the shadow server will send over the NSPTRS (resend) packet to the client. Thus, no disconnection of the underlying TCP connection ever happens, and so, there is no need of a reconnection
• If db server is on any platform, and client wants a shared session, enable DIRECT_HANDOFF_TTC_ = ON so that listener does not send an NSPTRD to the client. The dispatcher will then send over an NSPTRS packet to the client over the same socket that was established between the client and the listener

In Shared Servers configurations it is also possible to use the Oracle Connection Manager (CMAN) or setting specific ports for the dispatchers