Oracle Data Warehouse SQL*Net Listener

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 2.0.15.0.0 - 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))

STATUS of the LISTENER
------------------------
Alias                   LISTENER
Version                 TNSLSNR for HPUX:Version 2.0.15.0.0-Production
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 2.0.15.0.0-Production on 16-SEP-94 15:36:47

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

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.

MANAGING A MULTITHREADED SERVER (MTS)

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).

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

MANAGING THE LISTENER PROCESS

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.

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.