Wednesday, December 16, 2015

Oracle BEQUEATH connection


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


bequeath connection 


  • runs on your local host
  • bypasses the listener
  • the protocol creates the server process for you directly

    An IPC (Inter-Process Communication) connection
  • will use the native protocol on each OS, but uses the generic term "IPC" for all of them
  • can only be used when the Client and Server reside on the same host
  • can only be used by having the Client connect through the Oracle Listener
  • the Database Listener must be configured to listen on an IPC endpoint
  • the listener spawns the server process for you



  • 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;

    SIDOSUSERPROGRAMUSERNAMEMACHINENETWORK_SERVICE_BANNERLOGON_TIMESTATUS
    9
    oraclesqlplus@linux01 (TNS V1-V3)SCOTTlinux01Oracle Bequeath NT Protocol Adapter for Linux: Version  12.1.0.1.7- Production22.12.2015 15:35:49INACTIVE
    1160
    oraclesqlplus@linux01 (TNS V1-V3)SCOTTlinux01Unix Domain Socket IPC NT Protocol Adaptor for Linux: Version 12.1.0.1.7 - Production22.12.2015 15:40:44INACTIVE