Dynamic Oracle Net Server Tracing

Dynamic tracing V/s Static 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.

Tracing a background server process such as PMON or LREG

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

Identifying the PMON or LREG background server process. (Useful for tracing instance registration.)

Oracle 11.x

$ ps -ef |grep pmon
oracle   26601     1  0 16:45 ?        00:00:00 ora_pmon_N11203

Oracle 12.x

$ ps -ef |grep lreg
oracle   30396     1  0 09:12 ?        00:00:00 ora_lreg_N12101

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

$ 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: oracle@bde-idm8.us.oracle.com (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.

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/mseibt/app/oracle/diag/rdbms/n11203/N11203/trace/N11204_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.

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

USERNAME                       SPID
------------------------------ ------------------------
MIKE                           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 'MIKE'
/

USERNAME                       NAME
------------------------------ ----
MIKE                           D004

Then grep for the dispatcher pid using the dispatcher id.

$ ps -ef |grep d004
oracle    2841     1  0 11:14 ?        00:00:00 ora_d004_N11204

3. Other methods (advanced):

In some situations, an alternative to the above queries may be required to identify a client’s 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 similar for most platforms, match the client and server ports.

Potential commands:

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

Solaris

# pfiles -F [PID] | grep sockname   (gets a port using a known PID)

A working example

Getting the associated server PID of Solaris client connection to a database on Linux. The client username is “scott”.

1. Identify the client’s sqlplus PID on the Solaris system for user scott.

$ ps -ef |grep sqlplus
mseibt 25856 25828   0 11:31:48 pts/2       0:01 sqlplus mike@n11203
mseibt 25872 25862   0 11:33:35 pts/4       0:00 sqlplus scott@n11203

2. With the PID of the scott connection (25872) the “pfiles” utility will provide the client port (34948).

$ pfiles -F 25872 | grep sockname
sockname: AF_INET 10.141.155.182  port: 34948
sockname: AF_INET 0.0.0.0  port: 0

3. On the Linux server use “lsof” to find the client port (34948) which then reveals the server process PID (3557).

$ lsof -n -P -i :34948
COMMAND  PID   USER   FD   TYPE  DEVICE SIZE NODE NAME
oracle  3557 mseibt   12u  IPv6 1378536       TCP 10.141.157.33:1581->10.141.155.182:34948