ANBOB™

How to enable SQL*Net Tracing with Oracle Instant Client 11G http://www.anbob.com/archives/2073.html

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.

Availability

Available in 11.2.0.4, 12.1.0.1 (onward)
A One-off patch is available for 11.2.0.3, 11.2.0.2 and 11.1.0.7, use patch 16694777
Cumulative Patch inclusion (DBPSU) with 11.2.0.2.11 and 11.2.0.3.7
Exadata bundle patch inclusion (BP12)

Online / Hot Patch capable

All of the one-off patches that enable dynamic server tracing in versions 11.1.0.7, 11.2.0.2, and 11.2.0.3 are “hot patch” capable. Hot patching allows for the patching of the Oracle Home without stopping the database.

Online patching is available since 11.1.0.6. It will be delivered starting with RDBMS 11.2.0.2.0.

Here I’m going to demonstrate such a online patching process:
Environment:
OEL 5.8, x86_64 –> RDBMS 11.2.0.3 for Linux x86_64

1. Check actual patch state
[oracle@db231 16694777]$ opatch lsinventory

2. Switch to extracted patch location:

[oracle@db231 ~]$ unzip p16694777_112030_Linux-x86-64.zip
[oracle@db231 ~]$ cd 16694777/
[oracle@db231 16694777]$ vi README.txt

3. Patch with online method

For Non-RAC Environments (Standalone Databases)

$ opatch apply online -connectString :::

For RAC Environments

$ opatch apply online -connectString <SID_Node1>:<Username_Node1>:<Password_Node1>:<Node1_Name>,<SID_Node2>:<Username_Node2>:<Password_Node2>:<Node2_Name>,<SID_NodeN>:<Username_NodeN>:<Password_NodeN>:<NodeN_Name>

note my case env. is single instance .

[oracle@db231 16694777]$ opatch apply online -connectString anbob:sys:oracle

4. Verify opatch inventory
[oracle@db231 16694777]$ opatch lsinventory

… output had truncated
Patch (online) 16694777: applied on Tue Jan 07 14:44:32 CST 2014
Unique Patch ID: 16329952
Created on 7 May 2013, 06:52:30 hrs PST8PDT
Bugs fixed:
16694777

5. Verify enabled patch:

[oracle@db231 ~]$ sqlplus / as sysdba

sys@ANBOB>oradebug patch list

Patch File Name                                   State
================                                =========
bug16694777.pch                                  ENABLED

Now let’s tracing a dedicated server process associated with a connected client

create a connect from sqlplus client.

C:\>sqlplus anbob/anbob@192.168.168.231:1521/anbob.com

tracing server prorcess on server side.

sys@ANBOB>select spid,sid from v$process a,v$session b where a.addr=b.paddr and b.username='ANBOB';

SPID                                      SID
------------------------ --------------------
2049                                     1342

sys@ANBOB>oradebug setospid 2049
Oracle pid: 23, Unix process pid: 2049, image: oracle@db231

-- enable tracing
sys@ANBOB>oradebug dump event_tsm_test 16
Statement processed.
sys@ANBOB>oradebug tracefile_name
/u01/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_2049.trc
sys@ANBOB>

— create new session tail -f <tracefile_name> , do something on client, the flowing was tracefile output.

*** 2014-01-07 15:16:23.773
2014-01-07 15:16:23.773658 : nttfprd:socket 36 had bytes read=162
2014-01-07 15:16:23.773750 : nttfprd:exit
2014-01-07 15:16:23.773770 : nsbasic_brc:type=6, plen=162
2014-01-07 15:16:23.773787 : nsbasic_brc:what=1, tot =162
2014-01-07 15:16:23.773804 : nsbasic_brc:packet dump
2014-01-07 15:16:23.773825 : nsbasic_brc:00 A2 00 00 06 00 00 00  |........|
2014-01-07 15:16:23.773845 : nsbasic_brc:00 00 11 69 17 01 01 00  |...i....|
2014-01-07 15:16:23.773863 : nsbasic_brc:00 00 04 00 00 00 03 5E  |.......^|
2014-01-07 15:16:23.773882 : nsbasic_brc:18 61 80 00 00 00 00 00  |.a......|
2014-01-07 15:16:23.773900 : nsbasic_brc:00 01 11 00 00 00 01 0D  |........|
2014-01-07 15:16:23.773917 : nsbasic_brc:00 00 00 01 01 00 00 00  |........|
2014-01-07 15:16:23.773935 : nsbasic_brc:00 01 00 00 00 00 00 00  |........|
2014-01-07 15:16:23.773953 : nsbasic_brc:00 00 00 00 00 00 00 01  |........|
2014-01-07 15:16:23.773971 : nsbasic_brc:00 01 01 01 00 00 00 00  |........|
2014-01-07 15:16:23.773988 : nsbasic_brc:00 00 00 00 01 01 00 00  |........|
2014-01-07 15:16:23.774006 : nsbasic_brc:00 00 00 00 00 00 00 00  |........|
2014-01-07 15:16:23.774024 : nsbasic_brc:00 00 00 00 11 73 65 6C  |.....sel|
2014-01-07 15:16:23.774041 : nsbasic_brc:65 63 74 20 2A 20 66 72  |ect.*.fr|
2014-01-07 15:16:23.774059 : nsbasic_brc:6F 6D 20 74 61 62 01 00  |om.tab..|
2014-01-07 15:16:23.774077 : nsbasic_brc:00 00 00 00 00 00 00 00  |........|
2014-01-07 15:16:23.774094 : nsbasic_brc:00 00 00 00 00 00 00 00  |........|
2014-01-07 15:16:23.774112 : nsbasic_brc:00 00 00 00 00 00 00 00  |........|
2014-01-07 15:16:23.774130 : nsbasic_brc:00 00 01 00 00 00 00 00  |........|
2014-01-07 15:16:23.774147 : nsbasic_brc:00 00 00 00 00 00 00 00  |........|
2014-01-07 15:16:23.774165 : nsbasic_brc:00 00 00 00 00 00 00 00  |........|
2014-01-07 15:16:23.774182 : nsbasic_brc:00 00                    |..      |
2014-01-07 15:16:23.774199 : nsbasic_brc:exit: oln=0, dln=152, tot=162, rc=0
2014-01-07 15:16:23.774215 : nioqrc:exit
2014-01-07 15:16:23.776067 : nioqts:exit
2014-01-07 15:16:23.776212 : nioqts:exit
2014-01-07 15:16:23.776429 : nsmore2recv:entry
...

sys@ANBOB>oradebug dump event_tsm_test 0
Statement processed.

tip:

reference note  1550897.1

打赏

微信扫一扫，打赏作者吧～