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.
Function | 11.1.0.7, 11.2.0.2, 11.2.0.3 | 11.2.0.4, 12.1.0.x |
---|---|---|
Enable tracing | oradebug dump event_tsm_test 16 | oradebug dump sqlnet_server_trace 16 |
Alter the trace level | oradebug dump event_tsm_test [n]* | oradebug dump sqlnet_server_trace [n]* |
Disable Tracing | oradebug dump event_tsm_test 0 | oradebug dump sqlnet_server_trace 0 |
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