Understanding Dynamic Oracle Net Server Tracing

Oracle Net tracing is controlled by sqlnet.ora parameters which are static for the lifetime of RDBMS server processes. Since there is a single sqlnet.ora file for an instance, trace settings apply to all server processes. This can pose difficulties in live production environments. Dynamic server tracing allows for tracing at a per-process level and can be enabled ad hoc on any running server process. This is useful for tracing individual server-side processes of a specific client connection or for tracing background server processes.

1. Find the process ID (PID) of the server process that will be traced, for Windows locate the thread ID.

a. Identifying the PMON or LREG background server process. (Useful for tracing instance registration.) [For tracing a background server process such as PMON or LREG.]

Oracle 11.x

[oracle@admin]$ ps -ef |grep pmon
oracle   26601     1  0 16:45 ?        00:00:00 ora_pmon_[instance_name]

Oracle 12.x

[oracle@admin ~]$ ps -ef |grep lreg
oracle   30396     1  0 09:12 ?        00:00:00    [instance_name]

While listener interaction for instance registration is normally associated with the PMON process, in Oracle 12 instance registration duties are performed by the background process LREG. Use “ps -ef | grep lreg” when the server version is Oracle 12.

b. Identifying a connected client’s associated server process. [Tracing a dedicated server process associated with a connected client].

Refer to “Client / Server Connections” in the section “Identifying Server Processes”

2. Connect as sysdba and use oradebug to set the PID (or Windows thread ID) of the target server process identified in 1a or 1b.

[oracle@admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 30 14:55:38 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.


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

SQL> oradebug setospid 26601
Oracle pid: 25, Unix process pid: 26601, image: @ (TNS V1-V3)

SQL>

3. Once the PID or thread ID has been set in oradebug tracing can be enabled and disabled dynamically. The change in trace level is effective immediately when the command is executed. The trace level can also be changed to a different value as needed.

The oradebug dump syntax changes with respect to version. For Oracle versions 11.2.0.4 and higher use “dump sqlnet_server_trace”, for Oracle versions 11.2.0.3 and lower use “dump event_tsm_test”.

* Where [n] is the new trace level.

Trace output is directed to the standard ADR trace repositories unless an alternate location is specified in sqlnet.ora. To redirect output to a specific location use the following syntax in the server sqlnet.ora:

SQLNET.ORA
----------
TRACE_DIRECTORY_SERVER = /TMP
DIAG_ADR_ENABLED = OFF

To redirect the trace output location of background processes the above settings must be in sqlnet.ora before instance startup. To locate the trace file when ADR is enabled use: oradebug tracefile_name.

SQL> oradebug tracefile_name
/home/app/oracle/diag/rdbms/[instance]/[instance]/trace/[instance]_ora_11390.trc

Identifying Server Processes

1. Client / Server Connections.

Use a privileged account, e.g., sysdba, to identify the associated server side process of a connected client (the client user name must be unique). This method will also identify the associated dispatcher process when shared server is in use.

The following examples show a fictitious user named JOHN:

SQL> select s.username, p.spid
from v$session s, v$process p
where p.addr = s.paddr
and s.username like 'JOHN'
/

USERNAME                       SPID
------------------------------ ------------------------
JOHN                           16317

2. Client / Dispatcher Connections (Shared Server)

In shared server environments, Oracle Net tracing should target the dispatcher process. Use a privileged account, e.g., sysdba, to identify the associated dispatcher process of a connected client (the client user name must be unique). Note that server tracing invoked on a dispatcher will trace all clients using that dispatcher.

SQL> select s.username, d.name
from v$dispatcher d, v$session s, v$circuit c
where c.dispatcher = d.paddr
and c.saddr = s.saddr
and s.username like 'JOHN'
/

USERNAME                       NAME
------------------------------ ----
JOHN                           D004

Then grep for the dispatcher pid using the dispatcher id.

[oracle@admin]$ ps -ef |grep d004
oracle    2841     1  0 11:14 ?        00:00:00 ora_d004_[instance]

3. Other methods (advanced)

In some situations an alternative to the above queries may be required to identify a clients associated server processes. One angle is to use system tools like netstat, lsof, or pfiles to identify the network ports used by a client and server pair and then match them back to the server PID. The available system tools vary by platform so only one example will be provided but the overall concept is the similar for most platforms, match the client and server ports.

Potential commands:

$ lsof -n -P -i :[port]   ### (gets a PID using a known port)
$ lsof -p [PID] | grep TCP    ### (gets a port using a known PID)