• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Dynamic Oracle Net Server Tracing

by admin

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
Note: 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.

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.

Note: 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“.
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

Filed Under: 18c, oracle, Oracle 11g, oracle 12c

Some more articles you might also be interested in …

  1. Substitution variable in PL/SQL
  2. Oracle OS watcher (OSWatcher) – Understanding oswiostat
  3. Basics of client connectivity in Oracle Data Guard configuration
  4. Oracle ASM 12c – New Features with examples
  5. How to Find the SQL_ID for a SQL statement or PL/SQL block
  6. Oracle Tablespace Transport for a Single Partition
  7. How To Run ggsci In “silent” Mode
  8. Oracle RMAN interview questions
  9. Unable to export realm protected table using data pump
  10. Adding Users to Oracle Passwordfile (Oracle Passwordfile Authentication)

You May Also Like

Primary Sidebar

Recent Posts

  • fprintd-delete Command Examples in Linux
  • fprintd-delete: command not found
  • foreman: command not found
  • foreman Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright