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 oradebug close_trace.

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.

The 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 v$process view:

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: oracle@test.fatalmind.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.

Although the 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: oracle@test.fatalmind.com
SQL> oradebug flush
Statement processed.
SQL> oradebug close_trace
Statement processed.

[... skipped ...]

Oracle pid: 29, Unix process pid: 138, image: oracle@test.fatalmind.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.