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

The Geek Diary

CONCEPTS | BASICS | HOWTO

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

How to Disable Oracle Net Tracing on a Server Process without Stopping the Process

By admin

When Oracle Net server tracing is invoked using sqlnet.ora (see example below) tracing on the affected server processes will continue throughout the process lifetime. If tracing needs to be stopped without stopping the server process the following method can be used.

Example: static server tracing using sqlnet.ora:

trace_level_server = 16
trace_directory_server = /tmp
diag_adr_enabled = off
Dynamic Oracle Net Server Tracing

Use dynamic server tracing to disable tracing without stopping the server process.

1. Identify the PID, or for Windows the thread ID, of the server process that is actively tracing. Server traces are created with the process ID in the filename so this is easily done. The trace files below are being generated by server process id’s 19213,19364, and 19367.

$ ls -al *.trc
-rw-rw---- 1 mseibt mseibt 192670 Jan 16 15:06 svr_19213.trc
-rw-rw---- 1 mseibt mseibt  80897 Jan 16 15:43 svr_19364.trc
-rw-rw---- 1 mseibt mseibt  59146 Jan 16 15:43 svr_19367.trc

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

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 16 15:06:22 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> oradebug setospid 19213
Oracle pid: 19, Unix process pid: 19213, image: oracle@bde-idm8.us.oracle.com
SQL>

3. Once the PID or thread ID has been set in oradebug tracing can be disabled dynamically.

For Oracle versions 11.2.0.3 and lesser use:

sql> oradebug dump event_tsm_test 0

For Oracle versions 11.2.0.4 and greater use:

sql> oradebug dump sqlnet_server_trace 0

11.2.0.4 example:

sql> oradebug dump sqlnet_server_trace 0
Statement processed.
sql>

Tracing will stop immediately after issuing the command while the server process will continue to run.

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

Some more articles you might also be interested in …

  1. Beginners Guide to Monitoring SQL Statements with Real-Time SQL Monitoring in Oracle Database
  2. Oracle 11g – New ASM features
  3. Upgrading to Oracle 12c using RMAN DUPLICATE with “NOOPEN” clause and “BACKUP LOCATION”
  4. How to create password file for Database on 12c ASM diskgroup
  5. FATAL: Error inserting rds_rdma
  6. ORA-30012 Database Does Not Start With UNDO_MANAGEMENT=AUTO – Oracle Database 11gr2
  7. Oracle Interview Questions : Recovery catalog for RMAN backup
  8. What are Oracle Key Vault Roles
  9. How To Change SYS user password for oracle database instance
  10. How to set custom device names using udev in CentOS/RHEL 7

You May Also Like

Primary Sidebar

Recent Posts

  • SQL script to find tables that are fragmented
  • TRUNCATE TABLE not releasing space from tablespace
  • How to reclaim entire space of an oracle database table with “Truncate Table” statement
  • Oracle SQL Script to Report Tablespace Free and Fragmentation
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary