Connecting to Oracle bypassing the listener (aka a "bequeath connection")

Whenever you connect to Oracle from a client  running on the same machine as the database without specifying a net service name in the connect string, then you can connect to Oracle bypassing the listener. This is called a Bequeath connection. For example, when you dont specify @<net service name>, then you connect to Oracle without a listener. In the following example I have set the environment variable ORACLE_SID, so as to connect to a specific instance:



$ sqlplus nikos/nikos

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 2 21:55:38 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

nikos@NIKOSDB> 

Now I issue the command:
lsnrctl services


The lsnrctl services command displays information about the services, such as whether
the services have any dedicated, prespawned server processes or dispatched processes asso-
ciated with them, and how many connections have been accepted and rejected per service. In our case, we are only interested for the connections established.

As you show above I have connected to the database. Still the listener "sees" no connections, since we have bypassed the listener:


$ lsnrctl services

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 02-JAN-2013 21:55:43

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=nikos-lap)(PORT=1521)))
Services Summary...
...
  Instance "nikosdb", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
...
The command completed successfully.

Now lets try once again, but this time specifying a net service name to be resolved on the client with the a tnsnames.ora file (This is called Local Naming method):


nikos@NIKOSDB> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

$ sqlplus nikos/nikos@nikosdbhome

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 2 21:56:03 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

nikos@NIKOSDB> 

And lets check the listener again:


$ lsnrctl services

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 02-JAN-2013 21:56:07

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=nikos-lap)(PORT=1521)))
Services Summary...
...
  Instance "nikosdb", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER
...
The command completed successfully

Voila! One connection via the listener this time.

Another way to monitor the type of network connection, is via the v$session_connect_info dynamic performance view. Here is an example:

$ sqlplus nikos/nikos

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 4 10:51:08 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

nikos@NIKOSDB> select network_service_banner from v$session_connect_info
  2  where sid=sys_context('userenv','sid');

NETWORK_SERVICE_BANNER
----------------------------------------------------------------------------------------------------
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 11.2.0.1.0 - Production
Oracle Advanced Security: authentication service for 32-bit Windows: Version 11.2.0.1.0 - Productio
Oracle Advanced Security: NTS authentication service adapter for 32-bit Windows: Version 2.0.0.0.0
Oracle Advanced Security: encryption service for 32-bit Windows: Version 11.2.0.1.0 - Production
Oracle Advanced Security: crypto-checksumming service for 32-bit Windows: Version 11.2.0.1.0 - Prod


and using the listener:

$ sqlplus nikos/nikos@nikosdbhome

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 4 10:55:27 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

nikos@NIKOSDB>  select network_service_banner from v$session_connect_info
  2  where sid=sys_context('userenv','sid');

NETWORK_SERVICE_BANNER
---------------------------------------------------------------------------------------------------
Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 11.2.0.1.0 - Production
Oracle Advanced Security: authentication service for 32-bit Windows: Version 11.2.0.1.0 - Productio
Oracle Advanced Security: NTS authentication service adapter for 32-bit Windows: Version 2.0.0.0.0
Oracle Advanced Security: encryption service for 32-bit Windows: Version 11.2.0.1.0 - Production
Oracle Advanced Security: crypto-checksumming service for 32-bit Windows: Version 11.2.0.1.0 - Prod

cheers,
OL