Search My Oracle Blog

Custom Search

27 November, 2008

Tracing a Process -- Tracing DBWR

-Do- try this at home. DON'T try this at work !

Here, I see my DBWR process does not have tracing enabled currently :

SQL> !ps -ef |grep dbw
2001 4051 1 0 22:11 ? 00:00:00 asm_dbw0_+ASM
ora10204 4101 1 0 22:12 ? 00:00:00 ora_dbw0_ORT24FS
ora10204 4157 4154 0 22:13 pts/1 00:00:00 /bin/sh -c ps -ef |grep dbw
ora10204 4159 4157 0 22:13 pts/1 00:00:00 grep dbw

SQL> select s.sid, s.serial#, s.sql_trace from v$session s, v$process p
2 where s.paddr=p.addr
3 and p.spid=4101;

SID SERIAL# SQL_TRAC
---------- ---------- --------
167 1 DISABLED

SQL>


Now, I enable tracing on DBWR :

SQL> exec dbms_monitor.session_trace_enable(167,1);

PL/SQL procedure successfully completed.

SQL> select s.sid, s.serial#, s.sql_trace from v$session s, v$process p
2 where s.paddr=p.addr
3 and p.spid=4101;

SID SERIAL# SQL_TRAC
---------- ---------- --------
167 1 ENABLED

SQL>


I can see a trace file being written to :

ora10204>pwd
/oracle_fs/ora10204/admin/ORT24FS/bdump
ora10204>ls -ltr|tail -3
-rw-r----- 1 ora10204 dba 900 Nov 27 22:12 ort24fs_lgwr_4103.trc
-rw-r----- 1 ora10204 dba 1659410 Nov 27 22:13 alert_ORT24FS.log
-rw-r----- 1 ora10204 dba 6457 Nov 27 22:18 ort24fs_dbw0_4101.trc
ora10204>cat ort24fs_dbw0_4101.trc |head -20
/oracle_fs/ora10204/admin/ORT24FS/bdump/ort24fs_dbw0_4101.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle_fs/ora10204/product/10.2.0.4
System name: Linux
Node name: linux64
Release: 2.6.18-53.el5
Version: #1 SMP Sat Nov 10 19:37:22 EST 2007
Machine: x86_64
Instance name: ORT24FS
Redo thread mounted by this instance: 1
Oracle process number: 5
Unix process pid: 4101, image: oracle@linux64 (DBW0)

*** 2008-11-27 22:15:20.886
*** SERVICE NAME:(SYS$BACKGROUND) 2008-11-27 22:15:20.866
*** SESSION ID:(167.1) 2008-11-27 22:15:20.866
WAIT #0: nam='rdbms ipc message' ela= 625573 timeout=64 p2=0 p3=0 obj#=-1 tim=1199018868033615
WAIT #0: nam='rdbms ipc message' ela= 2930717 timeout=300 p2=0 p3=0 obj#=-1 tim=1199018870984135
WAIT #0: nam='rdbms ipc message' ela= 2930648 timeout=300 p2=0 p3=0 obj#=-1 tim=1199018873915147
ora10204>


I disable tracing :

SQL> exec dbms_monitor.session_trace_disable(167,1);

PL/SQL procedure successfully completed.

SQL> select s.sid, s.serial#, s.sql_trace from v$session s, v$process p
2 where s.paddr=p.addr
3 and p.spid=4101;

SID SERIAL# SQL_TRAC
---------- ---------- --------
167 1 DISABLED

SQL>


I then see that the trace file is no longer being updated. (Unfortunately, stopping tracing on a process does NOT write any message like "tracing stopped" to the trace file, you have to watch the trace file or run an "fuser" command on Unix/Linux to see if it is still being wrttien to (on Windows, I guess the file would be locked by the process if tracing hasn't been disabled).

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016