Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle Data Warehouse SQL*Net Listener

Oracle Data Warehouse Tips by Burleson Consulting

Note:  Starting in 11gR2, the listener log is not activated by default.  You have to turn on listener logging:   logging_listener_name=on

Understanding The SQL*Net Listener

To see what the Oracle listener is doing, Oracle provides a series of listener commands, including:

  • lsnrctl reload--Refreshes the listener.

  •  lsnrctl start--Starts the listener.

  •  lsnrctl stop--Stops the listener.

  •  lsnrctl status--Shows the status of the listener.

Following is the output of a lsnrctl status command:

[oracle]ram2: lsnrctl stat

LSNRCTL for HPUX:Version - Production on 16-SEP-94 15:38:00

Copyright (a)  Oracle Corporation 1993.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=ram2)(PORT=1521))

Alias                   LISTENER
Version                 TNSLSNR for HPUX:Version
Start Date              29-AUG-94 13:50:16
Uptime                  18 days 1 hr. 47 min. 45 sec
Trace Level             off
Security                OFF
Listener Parameter File /etc/listener.ora
Listener Log File       /usr/oracle/network/log/listener.log
Services Summary...
  dev7db                has 1 service handlers
  ram2db                has 1 service handlers

The command completed successfully

lsnrctl services     - lists all servers and dispatchers
[oracle]seagull: lsnrctl services
LSNRCTL for HPUX:Version on 16-SEP-94 15:36:47

Copyright (a)  Oracle Corporation 1993. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=seagull)(PORT=1521))
Services Summary...
  tdb000                has 4 service handlers
    DISPATCHER established:1 refused:0 current:2 max:55 state:ready
      D001 (machine: seagull, pid: 4146)
    DISPATCHER established:1 refused:0 current:2 max:55 state:ready
      D000 (machine: seagull, pid: 4145)
    DISPATCHER established:0 refused:0 current:1 max:55 state:ready
      D002 (machine: seagull, pid: 4147)
    DEDICATED SERVER established:0 refused:0

The command completed successfully

As a service request is intercepted by an Oracle server, the listener may direct the request via a dedicated server, an MTS, or an existing process (pre-spawned shadow). The key is whether the connection contacts the listener via a service name or bypasses the listener with the TWO_TASK connect string. If the listener is contacted as part of the connection and the MTS parms are defined to init.ora, the client will use the MTS.

There are five basic listener commands: RELOAD, START, STOP, STATUS, and SERVICES. Based on the request, the listener decides whether to dispatch a connection to a dedicated-server process (which it spawns) or use the MTS. The programmer has several options when deciding how Oracle will manage the process. Dedicated requests can be specified by a version 1.0 connect string or by using a service name that specifies server=dedicated in the tnsnames.ora file.

Note: Local connections will use the listener if multithreaded servers are defined. Even internal invocations to Oracle (for example, sqlplus/) will add a connection to an MTS.

Managing SQL*Net Connections

Listing 9.3 describes some of the utilities you can use to manage SQL*Net sessions effectively. You should be aware that some of the examples in this section are operation system-dependent and may not apply to your environment.

Listing 9.3  Commit point strength.

commit.sql -  Reports the commit point strength for the database.

set feedback off
column name  format a30 heading 'Name'
column type  format a7  heading 'Type'
column value format a60 heading 'Value'

prompt Commit Point-strength Report Output:
select name,
                   4,'file') type,
       replace(replace(value,'@','%{sid}'),'?','%{home}') value
from   v$parameter
where  name = 'commit_point_strength';


One of the problems faced by developers using SQL*Net version 1.0 was that each incoming transaction was spawned by the listener as a separate operating system task. With SQL*Net version 2.0, Oracle now has a method for allowing the listener connection to dispatch numerous sub-processes. With the MTS, all communications to a database are handled through a single dispatcher instead of separate Unix process IDs (PIDs) on each database. This translates into faster performance for most online tasks. Even local transactions will be directed through the MTS, and you will no longer see a PID for your local task when you issue ps -ef|grep oracle.

However, be aware that the MTS is not a panacea, especially at times when you want to invoke a dedicated process for your program. For Pro*C programs and I/O-intensive SQL*Forms applications--or any processes that have little idle time--you may derive better performance using a dedicated process.

In general, the MTS offers benefits such as reduced memory use, fewer processes per user, and automatic load balancing. However, it is often very confusing to tell whether the MTS is turned on--much less working properly.

Remember the following rules of thumb when initially starting the MTS:

  •  The MTS is governed by the init.ora parameters. If no MTS parms are present in init.ora, the MTS is disabled.

  •  The MTS is used when the MTS parms are in the init.ora and requests are made by service name (such as @myplace). In other words, you must retrieve the ROWID of all version 1.0 connect strings (such as t:unix1:myplace).

  •  Each user of the MTS requires 1 K of storage, so plan to increase your SHARED_POOL_SIZE.

  •  The V$QUEUE and V$DISPATCHER system tables indicate when the number of MTS dispatchers is too low. Even though the number of dispatchers is specified in the init.ora file, you can change it online in SQL*DBA with the ALTER SYSTEM command, as follows:


  •  If you encounter problems with the MTS, you can quickly regress to dedicated servers by issuing an ALTER SYSTEM command. The following command turns off the MTS by setting the number of MTS servers to zero:


  •  In order to use OPS$, you must set two init.ora values to true (they default to false).

remote_os_authent = true
remote_os_roles = true

  •  When both SQL*Net 1.0 and 2.0 are installed, the user may connect to the server either via a dedicated server or the MTS. However, you cannot stop and restart the listener when connecting via the MTS. You must connect to SQL*DBA with a dedicated server.

  •  In some cases, the instance must be bounced if the listener is stopped, or the listener will restart in dedicated mode. Whenever an instance is to be bounced, stop the listener, shut down the instance, restart the listener, and start up the instance. The listener reads the MTS parameters only if it is running before the startup of the instance. Therefore, bouncing the listener will disable the MTS.


As mentioned earlier, the listener is a software program that runs on each remote node that listens for incoming database requests. When a request is detected, the listener may direct the request to any of the following:

  •  A dedicated server

  •  A multithreaded server

  •  An existing process or pre-spawned shadow

Note that the configuration of an Oracle listener is a direct result of the parameters specified in the startup deck for the Oracle database. This parameter file, called init.ora, contains the following parameters to define the multithreaded server and listener.

# -----------------------
# Multithreaded Server
# -----------------------





# ---------------------------
# Distributed systems options
# ---------------------------




Miscellaneous Management Tips For SQL*Net

Just as the etc/oratab file for SQL*Net version 1.0 is interpreted, the tnsnames.ora file is also interpreted. This means that you can change it at any time without fear of bouncing anything. However, changes to listener.ora require the listener to be reloaded with lsnrctl reload.

When a database is accessed remotely via a database link, SQL*Net uses the temporary tablespace on the destination database, regardless of the processor invoking the task or the original database location. That way, SQL*Net will use the temporary tablespace on the destination database--not the initiating database. In other words, applications on one processor that access another processor with a database link will use the temporary tablespaces on the terminal processor--not the processor that contains the link.

Always remember to change your $ORACLE_HOME/bin/oraenv file to unset ORACLE_SID and set TWO_TASK=sid.

The following three logs appear in SQL*Net:

  •  listener log--usr/oracle/network/log/listener.log

  •  sqlnet log--usr/oracle/network/log/sqlnet.log

  •  trace log--Destination set with the TRACE_DIRECTORY_LISTENER parameter of the etc/listener.ora file

Three levels of tracing are found in SQL*Net:

  •  lsnrctl trace admin

  •  lsnrctl trace user

  •  lsnrctl trace off

It is possible to run two listeners simultaneously, one for version 1.0 and another listener for version 2.0. If a version 1.0 connect string is sent, a version 1.0 listener (tcpctl) will be used. Conversely, if a TNS connect description is sent, the version 2.0 listener (lsnrctl) will be used. A connect description is the name of a database (such as @mydata), which maps to the tnsnames.ora on the sending side and listener.ora on the receiving side.

It is essential to note that the functions of the ORACLE_SID and TWO_TASK variables have changed between SQL*Net version 1 and SQL*Net version 2. To use the MTS while you are local to the database, you should unset the ORACLE_SID variable and set the TWO_TASK to the SID name (EXPORT TWO_TASK=mydb). If the ORACLE_SID is active, you will still be able to connect--although you will not be able to take advantage of the MTS. You must change all login scripts and ORACLE_HOME/bin/oraenv files to reflect this new functionality.

We now have three ways to establish distributed database communications with MTS. We can choose from a shared service-name (sqlplus /@ram2db) or a dedicated service name (sqlplus /@d_ram2db--prefixing the SID with d_ will direct the listener to spawn a dedicated process for your program). And, we can also use a (TWO_TASK) server connect string (sqlplus /@t:host:sid). This latter approach will bypass the MTS and use a dedicated process.

Managing Two-Phase Commits (2PCs) With SQL*Net

When a distributed update (or delete) has finished processing, SQL*Net will coordinate COMMIT processing, which means that the entire transaction will roll back if any portion of the transaction fails. The first phase of this process is a prepare phase to each node, followed by the COMMIT, and then terminated by a forget phase.

If a distributed update is in the process of issuing the 2PC and a network connection breaks, Oracle will place an entry in the DBA_2PC_PENDING table. The recovery background process (RECO) will then roll back or commit the good node to match the state of the disconnected node to ensure consistency. You can activate RECO via the ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY command.

The DBA_2PC_PENDING table contains an ADVISE column that directs the database to either commit or roll back the pending item. You can use the ALTER SESSION ADVISE syntax to direct the 2PC mechanism. For example, to force the completion of an INSERT, you could enter the following:


When a 2PC transaction fails, you can query the DBA_2PC_PENDING table to check the STATE column. You can enter SQL*DBA and use the Recover In-Doubt Transaction dialog box to force either a roll back or a commit of the pending transaction. If you do this, the row will disappear from DBA_P2C_PENDING after the transaction has been resolved. If you force the transaction the wrong way (for example, roll back when other nodes committed), RECO will detect the problem, set the MIXED column to yes, and the row will remain in the DBA_2PC_PENDING table.

Internally, Oracle examines the init.ora parameters to determine the rank that the commit processing will take. The COMMIT_POINT_STRENGTH init.ora parameter determines which of the distributed databases is to be the commit point site. In a distributed update, the database with the largest value of COMMIT_POINT_STRENGTH will be the commit point site. The commit point site is the database that must successfully complete before the transaction is updated at the other databases. Conversely, if a transaction fails at the commit point site, the entire transaction will be rolled back at all of the other databases. In general, the commit point site should be the database that contains the most critical data. Listing 9.4 shows a script that will identify a two-phase commit transaction that has failed to complete.

Listing 9.4  pending.sql reports on any pending distributed transactions.

set pagesize 999;
set feedback off;
set wrap on;
column local_tran_id  format a22 heading 'Local Txn Id'
column global_tran_id format a50 heading 'Global Txn Id'
column state          format a16 heading 'State'
column mixed          format a5  heading 'Mixed'
column advice         format a5  heading 'Advice'        

select local_tran_id,global_tran_id,state,mixed,advice
from   dba_2pc_pending
order  by local_tran_id;


If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.




Oracle training Excel
Oracle performance tuning software 


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.