Connecting to Oracle bypassing the listener (aka a "bequeath connection")
A bequeath connection
An IPC (Inter-Process Communication) 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 HR/HR
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
HR@HRDB>
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 LINUX X64 bit: Version 12.1.0.1.7 - Production on 02-JAN-2015 21:55:43
Copyright (c) 1991, 2015, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=HR-lap)(PORT=1521)))
Services Summary...
...
Instance "HRdb", 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):
HR@HRDB> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.7 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ sqlplus HR/HR@HRdb
SQL*Plus: Release 12.1.0.1 Production on Wed Jan 2 21:56:03 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.7 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
HR@HRDB>
And lets check the listener again:
$ lsnrctl services
LSNRCTL for 64 bit LINUX: Version 12.1.0.1.7 - Production on 02-JAN-2015 21:56:07
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=HR-lap)(PORT=1521)))
Services Summary...
...
Instance "HRdb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
...
The command completed successfully
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 HR/HR
SQL*Plus: Release 12.1.0.1.7Production on Wed Dec 4 10:51:08 2015
Copyright (c) 1982, 2015, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.7 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
HR@HRDB> select network_service_banner from v$session_connect_info where sid=sys_context('userenv','sid');
NETWORK_SERVICE_BANNER
----------------------------------------------------------------------------------------------------
Oracle Bequeath NT Protocol Adapter for Linux: Version 12.1.0.1.0 - Production
Authentication service for Linux: Version 12.1.0.1.0 - Production
Encryption service for Linux: Version 12.1.0.1.0 - Production
Crypto-checksumming service for Linux: Version 12.1.0.1.0 - Production
and using the listener:
$ sqlplus HR/HR@HRdb
SQL*Plus: Release 12.1.0.1.7 Production on Wed Dec 4 10:55:27 2015
Copyright (c) 1982, 2015, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.7 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
HR@HRDB> select network_service_banner from v$session_connect_info where sid=sys_context('userenv','sid');
NETWORK_SERVICE_BANNER
--------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.1.0 - Production
Encryption service for Linux: Version 12.1.0.1.0 - Production
Crypto-checksumming service for Linux: Version 12.1.0.1.0 - Production
Check the connections and their types:
SELECT S.SID, S.OSUSER,S.PROGRAM,S.USERNAME,S.MACHINE, SCI.NETWORK_SERVICE_BANNER,S.LOGON_TIME,S.STATUS
FROM V$SESSION S INNER JOIN V$SESSION_CONNECT_INFO SCI
ON S.SID = SCI.SID
WHERE S.USERNAME = UPPER('scott')
AND SCI.NETWORK_SERVICE_BANNER LIKE '%IPC%'
OR SCI.NETWORK_SERVICE_BANNER LIKE INITCAP('%BEQ%')
AND S.TYPE <> 'BACKGROUND'
ORDER BY LOGON_TIME;
| SID | OSUSER | PROGRAM | USERNAME | MACHINE | NETWORK_SERVICE_BANNER | LOGON_TIME | STATUS |
|---|---|---|---|---|---|---|---|
9
| oracle | sqlplus@linux01 (TNS V1-V3) | SCOTT | linux01 | Oracle Bequeath NT Protocol Adapter for Linux: Version 12.1.0.1.7- Production | 22.12.2015 15:35:49 | INACTIVE |
1160
| oracle | sqlplus@linux01 (TNS V1-V3) | SCOTT | linux01 | Unix Domain Socket IPC NT Protocol Adaptor for Linux: Version 12.1.0.1.7 - Production | 22.12.2015 15:40:44 | INACTIVE |
