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@admin]$ ps -ef |grep pmon oracle 26601 1 0 16:45 ? 00:00:00 ora_pmon_[instance_name]
[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 126.96.36.199.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 188.8.131.52.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 184.108.40.206 and higher use “dump sqlnet_server_trace”, for Oracle versions 220.127.116.11 and lower use “dump event_tsm_test”.
|Feature||18.104.22.168, 22.214.171.124, 126.96.36.199||188.8.131.52, 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/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.
$ lsof -n -P -i :[port] ### (gets a PID using a known port) $ lsof -p [PID] | grep TCP ### (gets a port using a known PID)