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).