Under very rare circumstances, I need Oracle SQL trace files from a long period of time. Because trace files usually grow large—especially over several days—there is the need to rotate the trace file during that time so that they can be compressed and put away. The problem is that there is no “rotate tracefile“ button in Oracle. However, I have found a “undocumented feature“ that does exactly that—without disabling tracing.
My procedure uses the
close_trace call of
oradebug. This call closes the currently written trace file for a session. Alex Gorbachev has used this to delete big trace files that are still open. My procedure goes a little bit further and exposes one more side effect of
Warning: The technique described here uses the undocumented and unsupported
oradebug facility. Although I am not aware of any negative side effects of this procedure, the use of this method takes place at your own risk.
oradebug utility can be used from the SQL*Plus prompt. If you have an account with sufficient rights (sigh) you can attach to any session using the
oradebug setorapid command. To obtain the required Oracle PID, just query the
SQL> SELECT s.sid, s.serial#, p.pid FROM v$session s, v$process p WHERE s.paddr=p.addr; SID SERIAL# PID ---------- ---------- ---------- 1 1 2 [... skipped ...] 20 3126 21 26 rows selected. SQL> oradebug setorapid 21 Oracle pid: 21, Unix process pid: 145, image: email@example.com SQL>
Once attached to a specific process, every
oradebug operation is applied to that particular process. The trick to rotate the trace file is very simple and exploits a “feature” of UNIX file systems; that is, that the name of the file is used only at the moment the file is opened. Once the file is open, all access is managed with the so called
inode and the name is not relevant for the read and write operations anymore. The
inode system has some more implications, one of them is frequently used to rotate log files without data loss. Another implications makes it hard to delete open files, as Alex Gorbachev explained.
The log rotation trick is to rename the open file and then cause the writing process to re-open that file. All write operations to the renamed file will be written properly to new file name—more correctly expressed: to the file which can be accessed under the new name. If the writing process re-opens the file under its original name, it will create a new file because there is no file with that name anymore. Every subsequent write operation goes to the new file. The original file will not grow anymore and can be taken away safely.
oradebug command name
close_trace doesn’t suggest that the trace file is reopened, it is—if SQL tracing is enabled. So we have everything to rotate a trace file. Consider the following example:
$ ls -lrt TEST_ora_14457.trc* -rw-r----- 1 oracle oinstall 14758723 2010-02-01 TEST_ora_14457.trc $ mv TEST_ora_14457.trc TEST_ora_14457.trc.old
After renaming the file, the file still grows as the SQL trace is written. You can now use
oradebug close_trace to actually rotate the file:
SQL> oradebug close_trace Statement processed. SQL>
The verification in the file system shows that the ‘old’ file became bigger, and the new file is also filling up:
$ ls -lrt TEST_ora_14457.trc* -rw-r----- 1 oracle oinstall 18878704 2010-02-01 TEST_ora_14457.trc.old -rw-r----- 1 oracle oinstall 1555972 2010-02-01 TEST_ora_14457.trc $
Because I often need to trace many sessions, I wrote a very tiny script (
switch_traces.sql) that creates another script to re-open all trace files:
set echo off set feedback off set pages 0 set heading off set define off spool oradebug_close_traces.sql select cmd from ( select pid, 1 o, 'oradebug setorapid ' || pid cmd from v$process UNION ALL select pid, 2 o, 'oradebug flush' from v$process UNION ALL select pid, 3 o, 'oradebug close_trace' from v$process) order by pid,o; spool off prompt prompt c&p >>> @oradebug_close_traces.sql <<< to switch traces set feedback on set pages 1000 set heading on set echo on
This script will close all traces in all sessions and continue tracing to new files if SQL tracing is enabled. You might want to add additional
where clauses to limit your scope—e.g., to skip background processes. The script will also issue a
flush before the
close_trace. I don’t know if this is required or not, it doesn’t seem to make any harm. The use of the script is very simple:
SQL> @switch_traces.sql SQL> set echo off oradebug setorapid 1 oradebug flush oradebug close_trace [... skipped ...] oradebug setorapid 29 oradebug flush oradebug close_trace c&p >>> @oradebug_close_traces.sql <<< to switch traces SQL> @oradebug_close_traces.sql SQL> oradebug setorapid 1 ORA-00072: process "Unix process pid: 0, image: PSEUDO" is not active SQL> oradebug flush Statement processed. SQL> oradebug close_trace Statement processed. SQL> oradebug setorapid 2 Oracle pid: 2, Unix process pid: 137, image: firstname.lastname@example.org SQL> oradebug flush Statement processed. SQL> oradebug close_trace Statement processed. [... skipped ...] Oracle pid: 29, Unix process pid: 138, image: email@example.com SQL> oradebug flush Statement processed. SQL> oradebug close_trace Statement processed. SQL>
In case you have not forgotten to rename your trace files you will see the new files growing now.
Read also my previous article To Trace or Not to Trace for information about fine grained SQL tracing.